剖析MYSQL鎖機制

剖析MYSQL鎖機制

數據庫鎖設計的初衷是處理併發問題。作爲多用戶共享的資源，當出現併發訪問的時候，數據庫需要合理地控制資源的訪問規則。鎖是計算機協調多個進程或線程併發訪問某一資源的機制。在數據庫中，數據是一種供許多用戶共享的資源。數據庫的鎖機制，就是數據庫爲了保證數據的一致性，而使各種共享資源在被併發訪問變得有序所設計的一種規則。

從對數據操作的類型分類：

• 讀鎖（共享鎖）：針對同一份數據，多個讀操作可以同時進行，不會互相影響
• 寫鎖（排他鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖

根據加鎖的範圍，MySQL 裏面的鎖大致可以分成全局鎖、表級鎖和行鎖三類

爲了儘可能提高數據庫的併發度，每次鎖定的數據範圍越小越好，理論上每次只鎖定當前操作的數據的方案會得到最大的併發度，但是管理鎖是很耗資源的事情（涉及獲取，檢查，釋放鎖等動作），因此數據庫系統需要在高併發響應和系統性能兩方面進行平衡，因此需要根據具體的業務需求選用合適的鎖。

全局鎖

全局鎖是對整個數據庫實例加鎖，加鎖後整個數據庫處於只讀狀態，之後的數據更新語句（數據的增刪改）、數據定義語句（包括建表、修改表結構等）和更新類事務的提交語句都將被阻塞執行。

MYSQL加全局鎖的方式是 mysql> flush tables with read lock; 釋放全局鎖的命令爲：mysql>unlock tables；,或者斷開加鎖的session的連接即可。

顯然，一旦加上全局鎖，整個數據庫就不能更新，即新數據無法寫入，這是非常影響業務的。因此全局鎖的典型適用範圍就是給數據庫做全局邏輯備份(mysqldump),也就是把整庫每個表都 select 出來存成文本。

讓整個數據庫處於只讀狀態是有比較大的風險的：

• 在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就能停止
• 在從庫上備份，那麼備份期間從庫不能執行主庫同步過來的binlog，會導致主從延遲

但是如果在全局備份時不加全局鎖，可能會導致數據的不一致性，即如果已經將某一個數據備份了之後，又對該數據進行了修改，那麼就會造成主從數據庫不一致的現象，並且binlog日誌也會有不一致。也就是說，不加鎖的話，備份系統備份的得到的庫不是一個邏輯時間點，這個數據是邏輯不一致的。

官方自帶的邏輯備份工具是 mysqldump。當 mysqldump 使用參數–single-transaction的時候，導數據之前就會啓動一個事務，來確保拿到一致性快照視圖。但是並不是所有的引擎都支持single-transaction，MyISAM 這種不支持事務的引擎，如果備份過程中有更新，總是隻能取到最新的數據，那麼就破壞了備份的一致性。因此single-transaction 方法只適用於所有的表使用事務引擎的庫。如果有的表使用了不支持事務的引擎，那麼備份就只能通過 FTWRL 方法。

既然要全庫只讀，爲什麼不使用 set global readonly=true 的方式？

• readonly=true可以令全局進行只讀狀態，但是要小心發生異常。如果執行 FTWRL 命令之後由於客戶端發生異常斷開，那麼 MySQL 會自動釋放這個全局鎖，整個庫回到可以正常更新的狀態。而將整個庫設置爲 readonly 之後，如果客戶端發生異常，則數據庫就會一直保持 readonly 狀態，這樣會導致整個庫長時間處於不可寫狀態，風險較高。並且readonly 對super用戶權限無效。

表級鎖

MySQL 裏面表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（meta data lock，MDL)

表鎖的語法是 lock tables … read/write，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。如果在某個線程 A 中執行 lock tables t1 read, t2 write; 這個語句，則其他線程寫 t1、讀寫 t2 的語句都會被阻塞。同時，線程 A 在執行 unlock tables 之前，也只能執行讀 t1、讀寫 t2 的操作，也不能去訪問其他表。

另一類表級的鎖是 MDL（metadata lock)。MDL 不需要顯式使用，在訪問一個表的時候會被自動加上，MDL 的作用是，保證讀寫的正確性。在 MySQL 5.5 版本中引入了 MDL，當對一個表做增刪改查操作的時候，加 MDL讀鎖；當要對錶做結構變更操作的時候，加 MDL 寫鎖。讀鎖是不互斥的，多個讀鎖可以同時加上去，但加上讀鎖後不可以加上去寫鎖，寫鎖是互斥的，加上寫鎖後不可以再加其他鎖。

通常來說，表級鎖更適合於以查詢爲主，只有少量按索引條件更新數據的應用，如Web應用。

MyISAM 的表鎖有兩種模式：

• 表共享讀鎖 （Table Read Lock）：不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；
• 表獨佔寫鎖 （Table Write Lock）：會阻塞其他用戶對同一表的讀和寫操作；

MyISAM 表的讀操作與寫操作之間，以及寫操作之間是串行的。當一個線程獲得對一個表的寫鎖後， 只有持有鎖的線程可以對錶進行更新操作。 其他線程的讀、 寫操作都會等待，直到鎖被釋放爲止。

默認情況下，寫鎖比讀鎖具有更高的優先級：當一個鎖釋放時，這個鎖會優先給寫鎖隊列中等候的獲取鎖請求，然後再給讀鎖隊列中等候的獲取鎖請求。

不過需要注意的是，事務中的 MDL 鎖，在語句執行開始時申請，但是語句結束後並不會馬上釋放，而會等到整個事務提交後再釋放，因此要謹慎使用，可能導致死鎖。

因此如果有長事務需要MDL鎖，那麼由於事務一直不提交，就會一直佔着 MDL 鎖。在 MySQL 的information_schema 庫的 innodb_trx 表中，你可以查到當前執行中的事務。如果你要做 DDL 變更的表剛好有長事務在執行，要考慮先暫停 DDL，或者 kill 掉這個長事務。對於熱點表，請求非常頻繁，那麼在 alter table 語句裏面設定等待時間，如果在這個指定的等待時間裏面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞後面的業務語句，先放棄。

還有補充的是，在自動加鎖的情況下，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，所以 MyISAM 表不會出現死鎖。【因爲沒有事件】

行鎖

行鎖的開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高。InnoDB 是支持行鎖的，MyISAM 引擎就不支持行鎖。

行鎖是兩階段鎖，在 InnoDB 事務中，行鎖是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。因此，爲了儘可能提高併發度，加快處理速度，如果事務中需要鎖多個行，要把最可能造成鎖衝突、最可能影響併發度的鎖儘量往後放。

行鎖很容易引發死鎖，如下面的例子:

事務 A 在等待事務 B 釋放 id=2 的行鎖，而事務 B 在等待事務 A 釋放 id=1 的行鎖。 事務 A 和事務 B 在互相等待對方的資源釋放，就是進入了死鎖狀態.

當出現死鎖以後，有兩種策略：

• 一種策略是，直接進入等待，直到超時。這個超時時間可以通過參數 innodb_lock_wait_timeout 來設置。

• 另一種策略是，發起死鎖檢測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行。將參數 innodb_deadlock_detect 設置爲 on，表示開啓這個邏輯。

第一種方案中，超時值的設定就比較麻煩，如果超時值設置太大，那麼可能導致等待時間過長，對線上服務產生非常不好的影響，如果超時值設定太小，會產生比較多的誤傷，正常的鎖等待可能會被誤判。

所以，正常情況下我們還是要採用第二種策略，即：主動死鎖檢測，而且 innodb_deadlock_detect 的默認值本身就是 on。主動死鎖檢測在發生死鎖的時候，是能夠快速發現並進行處理的，但是它也是有額外負擔的。

每當一個事務被鎖的時候，就要看看它所依賴的線程有沒有被別人鎖住，如此循環，最後判斷是否出現了循環等待，也就是死鎖，因此對每個新來的被堵住的線程，都要判斷會不會由於自己的加入導致了死鎖，這是一個時間複雜度是 O(n) 的操作，因此整體是O( n 2 n^2 n2)的時間複雜度。

通常來說，行級鎖則更適合於有大量按索引條件併發更新少量不同數據，同時又有併發查詢的應用，如一些在線事務處理（OLTP）系統。

InnoDB 實現的兩種類型的行鎖：

• 共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
• 排他鎖（X）：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖

加鎖機制

樂觀鎖與悲觀鎖是兩種併發控制的思想，可用於解決丟失更新問題

樂觀鎖會「樂觀地」假定大概率不會發生併發更新衝突，訪問、處理數據過程中不加鎖，只在更新數據時再根據版本號或時間戳判斷是否有衝突，有則處理，無則提交事務。用數據版本（Version）記錄機制實現，這是樂觀鎖最常用的一種實現方式。

悲觀鎖會「悲觀地」假定大概率會發生併發更新衝突，訪問、處理數據前就加排他鎖，在整個數據處理過程中鎖定數據，事務提交或回滾後才釋放鎖。另外與樂觀鎖相對應的，悲觀鎖是由數據庫自己實現了的，要用的時候，我們直接調用數據庫的相關語句就可以了。