MySQL技術内幕 InnoDB存儲引擎：阻塞、死鎖、鎖更新

1、堵塞

因為不同鎖之間的相容性關系，在有些時刻一個事務中的鎖需要等待另外一個事務中的鎖釋放它所占用的資源，這就是堵塞。

• 參數innodb_lock_wait_timeout用來控制等待的時間，預設50秒，是可以動态設定的。
• 參數innodb_rollback_on_timeout用來設定是否在等待逾時時對進行中的事務進行復原操作。預設是OFF。（靜态參數，無法是mysql運作時修改）

在預設情況下InnoDB存儲引擎不會復原逾時引發的錯誤異常。比如一個事務中執行兩條insert 語句，第一條正常執行，第二條出現逾時異常，這時innodb不會復原整個事務，即第一條insert語句，已經送出到資料庫。

2、死鎖

死鎖是指兩個或兩個以上的事務在執行過程中，因争奪鎖資源而造成的一種互相等待現象。

解決死鎖問題最簡單方式是不要有等待，将任何的等待都轉化為復原，并且事務重新開始。毫無疑問，這的确可以避免死鎖問題的産生。但是性能很差。

解決死鎖問題最簡單方法是逾時，即當兩個事務互相等待時，當一個等待時間超過門檻值時，其中一個事務進行復原，另外一個事務就能繼續進行。雖然簡單，但是僅僅根據FIFO進行復原，若逾時的事務占據權重比較大，會浪費較多時間。

目前資料庫都普遍采用wait-for graph(等待圖)的方式來進行死鎖檢測。可以發現wait-for graph是一種較為主動的死鎖檢測機制，在每個事務請求鎖并發生等待時都會判斷是否存在回路，若存在則有死鎖，通常來說，InnoDB存儲引擎會選擇復原undo量最小的事務

InnoDB存儲引擎并不會復原大部分的錯誤異常，但是死鎖除外。發現死鎖後，InnoDB存儲引擎會馬上復原一個事務，這點是需要注意的。如果在應用程式中捕獲了1213這個錯誤，其實并不需要對其進行復原。

3、鎖更新

鎖更新（Lock Escalation）是指将目前鎖的粒度降低。舉例來說，資料庫可以把一個表的1 000個行鎖更新為一個頁鎖，或者将頁鎖更新為表鎖。如果資料庫的設計中認為鎖是一種稀有資源，而且想避免鎖的開銷，那資料庫中會頻繁出現鎖更新現象。

Microsoft SQL Server資料庫的設計認為鎖是一種稀有的資源，在适合的時候會自動地将行、鍵或者分頁級鎖更新為更粗粒度的表級鎖。這種更新保護了系統資源，防止系統使用太多的記憶體來維護鎖，從一定程度上提高了效率。

即使在Microsoft SQL Server 2005的版本之後，SQL Server資料庫支援了行鎖，但是其設計和InnoDB存儲引擎完全不同，在以下情況下依然可能發生鎖更新：

• 由一句單獨的SQL語句在一個對象上持有的鎖數量超過了門檻值，預設的這個門檻值為5000。值得注意的是，如果是不同對象的話，則不會發生鎖更新。
• 鎖資源占用的記憶體超過了激活記憶體的40%時，就會發生鎖更新。

在Microsoft SQL Server資料庫中，由于鎖是一種稀有的資源，是以鎖更新會帶來一定的效率提髙。但是鎖更新帶來的一個問題卻是因為鎖粒度的降低而導緻并發性能的降低。

IrnioDB存儲引擎不存在鎖更新的問題。因為其不是根據每個記錄來産生行鎖的，相反，其根據每個事務通路的每個頁對鎖進行管理的，采用的是位圖的方式。是以不管一個事務鎖住頁中一個記錄還是多個記錄，其開銷通常都是一緻的。

假設一張表有3 000 000個資料頁，每個頁大約有100條記錄，那麼總共有300 000 000條記錄。若有一個事務執行全表更新的SQL語句，則需要對所有記錄加X鎖。若根據每行記錄産生鎖對象進行加鎖，并且每個鎖占用10位元組，則僅對鎖管理就需要差不多需要3GB的記憶體。而InnoDB存儲引擎根據頁進行加鎖，并采用位圖方式，假設每個頁存儲的鎖資訊占用30個位元組，則鎖對象僅需90MB的記憶體。由此可見兩者對于鎖資源開銷的差距之大。

本文整理自：《​​MySQL技術内幕 InnoDB存儲引擎​​》

個人微信公衆号：