通俗易懂的Mysql的鎖(全域鎖、表鎖、行鎖)

前言

資料庫設計鎖的初衷是處理并發問題，作為多用戶共享的資源，當出現并發訪問的時候，資料庫需要合理地控制資源的訪問規則，而鎖就是用來實作這些訪問規則的重要資料結構，

根據加鎖的范圍，MySQL里面的鎖大致可以分成全域鎖、表級鎖和行鎖三類，

全域鎖

顧名思義，全域鎖就是對整個資料庫實體加鎖，Mysql 提供了一個全域讀鎖的方法，命令是：

Flush tables with read lock (FTWRL)

可以使用以下命令解鎖：

unlock tables;

這個命令可以讓整個資料庫處于可讀的狀態，使用此命令，會阻塞以下操作：

1、資料更新陳述句（資料的增刪改）
2、資料定義陳述句（包括建表、修改表結構等）
3、更新類事務的提交陳述句

全域鎖使用場景

全域鎖的典型使用場景是，做全庫邏輯備份， 比如，主從復制，也就是把整個庫的所有表都 select 出來存成文本，

有一種做法，是通過FTWRL確保不會有其他執行緒對資料庫做更新，然后對整個庫做備份，注意，在備份程序中整個庫完全處于只讀狀態，

那么，資料庫處于可讀狀態的危險性：

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

看來，加全域鎖不太好，那我們看看不加全域鎖，會出現什么問題呢?

不加鎖的話，備份得到的庫不是一個邏輯時間點，這個視圖是邏輯不一致的，也就是說，由于不能在同一邏輯時間點進行備份，所以可能會出現在備份程序中，有一些增刪改操作，影響備份資料的一致性，

現在的問題是，不加鎖的話得不到一致性視圖，那有沒有辦法解決呢？

答案是肯定的，就是在可重復讀隔離級別下開啟一個事務，

官方自帶的邏輯備份工具是mysqldump，當mysqldump使用引數–single-transaction的時候，導資料之前就會啟動一個事務，來確保拿到一致性視圖，而由于MVCC的支持，這個程序中資料是可以正常更新的，

有了這個邏輯備份工具，再使用上 –single-transaction 引數，可以得到一致性視圖，而且還不影響資料正常更新，多香！

那問題來了，有了“真香”工具，還用什么 FTWRL 啊，雖然能讓整庫處于可讀狀態，但還會阻塞很多正常操作，“真香”工具的前提是，引擎要支持這個隔離級別，比如，對于MyISAM這種不支持事務的引擎，如果備份程序中有更新，總是只能取到最新的資料，那么就破壞了備份的一致性，這時，我們就需要使用FTWRL命令了，

所以，single-transaction方法只適用于所有的表使用事務引擎的庫，如果有的表使用了不支持事務的引擎，那么備份就只能通過FTWRL方法，

還有一種方式能讓整庫處于只讀狀態，就是：

set global readonly=true

確實readonly方式也可以讓全庫進入只讀狀態，但我還是會建議你用FTWRL方式，主要有兩個原因：

1、在有些系統中，readonly的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫，因此，修改global變數的方式影響面更大，不建議你使用，
2、在例外處理機制上有差異，如果執行FTWRL命令之后由于客戶端發生例外斷開，那么MySQL會自動釋放這個全域鎖，整個庫回到可以正常更新的狀態，而將整個庫設定為readonly之后，如果客戶端發生例外，則資料庫就會一直保持readonly狀態，這樣會導致整個庫長時間處于不可寫狀態，風險較高，

業務的更新不只是增刪改資料（DML)，還有可能是加欄位等修改表結構的操作（DDL），不論是哪種方法，一個庫被全域鎖上以后，你要對里面任何一個表做加欄位操作，都是會被鎖住的，

表鎖

表級別的鎖有兩種：

1、表鎖
	表鎖的語法是 lock tables … read/write
	與FTWRL類似，可以用unlock tables主動釋放鎖，也可以在客戶端斷開的時候自動釋放，lock tables語法除了會限制別的執行緒的讀寫外，
	也限定了本執行緒接下來的操作物件，
	
	舉個例子, 如果在某個執行緒A中執行lock tables t1 read, t2 write; 這個陳述句，則其他執行緒寫t1、讀寫t2的陳述句都會被阻塞，
	同時，執行緒A在執行unlock tables之前，也只能執行讀t1、讀寫t2的操作，連寫t1都不允許，自然也不能訪問其他表，

	在還沒有出現更細粒度的鎖的時候，表鎖是最常用的處理并發的方式，而對于InnoDB這種支持行鎖的引擎，一般不使用lock tables命令
	來控制并發，畢竟鎖住整個表的影響面還是太大，

2、元資料鎖（meta data lock，MDL)
	MDL不需要顯式使用，在訪問一個表的時候會被自動加上，MDL的作用是，保證讀寫的正確性，
	
	在MySQL 5.5版本中引入了MDL，當對一個表做增刪改查操作的時候，加MDL讀鎖；當要對表做結構變更操作的時候，加MDL寫鎖，
		(1)讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪改查，
		(2)讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性，因此，如果有兩個執行緒要同時給一個表加欄位，其中一個要等另一個執行完才能開始執行，

下面，我們來看一個事務是如何執行的？

我們可以看到session A先啟動，這時候會對此表加一個MDL讀鎖，由于session B需要的也是MDL讀鎖，因此可以正常執行，

之后session C會被blocked，是因為session A的MDL讀鎖還沒有釋放，而session C需要MDL寫鎖，因此只能被阻塞，

如果只有session C自己被阻塞還沒什么關系，但是之后所有要在此表上新申請MDL讀鎖的請求也會被session C阻塞，前面我們說了，所有對表的增刪改查操作都需要先申請MDL讀鎖，就都被鎖住，等于這個表現在完全不可讀寫了，

如果某個表上的查詢陳述句頻繁，而且客戶端有重試機制，也就是說超時后會再起一個新session再請求的話，這個庫的執行緒很快就會爆滿，

所以，事務中的MDL鎖，在陳述句執行開始時申請，但是陳述句結束后并不會馬上釋放，而會等到整個事務提交后再釋放，

我們考慮一個問題，如何安全的做DDL？

首先要解決長事務，因為事務不提交就會一直占用MDL鎖，可以查到當前執行中的事務，如果你要做DDL變更的表剛好有長事務在執行，
要考慮先暫停DDL，或者kill掉這個長事務，

但如果變更的是一個熱點表，請求頻繁，但由不得不DDL，該如何做呢？？

這時候kill可能未必管用，因為新的請求馬上就來了，比較理想的機制是，在alter table陳述句里面設定等待時間，如果在這個指定的等待時間
里面能夠拿到MDL寫鎖最好，拿不到也不要阻塞后面的業務陳述句，先放棄，之后開發人員或者DBA再通過重試命令重復這個程序，

行鎖

上面，我們聊了MySQL的全域鎖和表級鎖，下面我們來說說MySQL的行鎖，我們都知道 Mysql 的架構分為三層，Client , Server , Engine ，而 Mysql 的行級鎖是在 引擎層實作的，各引擎層有自己的行鎖實作方式，當然，不是所有的引擎都支持行鎖，比如：MyISAM 就不支持行級鎖，這也是為什么MyISAM被InnoDB取代的重要原因之一，

下面，我們來聊聊 InnoDB 的行鎖，以及如何通過減少鎖沖突來提高業務并發度，

顧名思義，行級鎖其實很好理解，就是鎖定表中的一行資料，當操作A要更新一行資料，操作B也要更新同一行資料，那操作B就要等到操作A執行結束后再執行，

下面，我們介紹一個概念，兩階段鎖，

兩段鎖

什么是兩段鎖？顧名思義一下，就是鎖分為了兩個階段，是的，分為加鎖和解鎖這兩個階段，而加鎖一定是在解鎖之前的，

我們來看個例子理解什么是兩段鎖，下面操作中，事務B的 update 陳述句執行會出現什么現象？假設欄位 id 是表 t 的主鍵，

這個問題的關鍵在于，事務A在什么時候釋放鎖，

InnoDB事務中，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放，這個就是兩階段鎖協議，

知道了這個設定，對我們使用事務有什么幫助呢？那就是，如果你的事務中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發度的鎖盡量往后放，舉個例子，

假設你負責實作一個電影票在線交易業務，顧客A要在影院B購買電影票，我們簡化一點，這個業務需要涉及到以下操作：

1、從顧客A賬戶余額中扣除電影票價；
2、給影院B的賬戶余額增加這張電影票價；
3、記錄一條交易日志，

為了保證操作的安全性，我們要把這三個步驟放到一個事務中，那你會怎么安排這三個陳述句的順序呢？

試想如果同時有另外一個顧客C要在影院B買票，那么這兩個事務沖突的部分就是陳述句2了，因為它們要更新同一個影院賬戶的余額，需要修改同一行資料，

根據兩階段鎖協議，不論你怎樣安排陳述句順序，所有的操作需要的行鎖都是在事務提交的時候才釋放的，所以，如果你把陳述句2安排在最后，比如按照3、1、2這樣的順序，那么影院賬戶余額這一行的鎖時間就最少，這就最大程度地減少了事務之間的鎖等待，提升了并發度，

現在由于你的正確設計，已經提升了并發度，但是，依然沒有解決你的困擾，

如果這個影院做活動，可以低價預售一年內所有的電影票，而且這個活動只做一天，于是在活動時間開始的時候，你的MySQL就掛了，你登上服務器一看，CPU消耗接近100%，但整個資料庫每秒就執行不到100個事務，這是什么原因呢？

死鎖與死鎖檢測

當并發系統中不同執行緒出現回圈資源依賴，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都進入無限等待的狀態，稱為死鎖，這里我用資料庫中的行鎖舉個例子，

這時候，事務A在等待事務B釋放id=2的行鎖，而事務B在等待事務A釋放id=1的行鎖， 事務A和事務B在互相等待對方的資源釋放，就是進入了死鎖狀態，當出現死鎖以后，有兩種策略：

1、直接進入等待，直到超時，這個超時時間可以通過引數innodb_lock_wait_timeout來設定，
2、發起死鎖檢測，發現死鎖后，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行，將引數innodb_deadlock_detect設定為on，表示開啟這個邏輯，

在InnoDB中，innodb_lock_wait_timeout的默認值是50s，意味著如果采用第一個策略，當出現死鎖以后，第一個被鎖住的執行緒要過50s才會超時退出，然后其他執行緒才有可能繼續執行，對于在線服務來說，這個等待時間往往是無法接受的，

但是，我們又不可能直接把這個時間設定成一個很小的值，比如1s，這樣當出現死鎖的時候，確實很快就可以解開，但如果不是死鎖，而是簡單的鎖等待呢？所以，超時時間設定太短的話，會出現很多誤傷，

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

每個新來的被堵住的執行緒，都要判斷會不會由于自己的加入導致了死鎖，這是一個時間復雜度是O(n)的操作，假設有1000個并發執行緒要同時更新同一行，那么死鎖檢測操作就是100萬這個量級的，雖然最終檢測的結果是沒有死鎖，但是這期間要消耗大量的CPU資源，因此，你就會看到CPU利用率很高，但是每秒卻執行不了幾個事務，

問題的癥結在于，死鎖檢測要耗費大量的CPU資源，

你可以考慮通過將一行改成邏輯上的多行來減少鎖沖突，還是以影院賬戶為例，可以考慮放在多條記錄上，比如10個記錄，影院的賬戶總額等于這10個記錄的值的總和，這樣每次要給影院賬戶加金額的時候，隨機選其中一條記錄來加，這樣每次沖突概率變成原來的1/10，可以減少鎖等待個數，也就減少了死鎖檢測的CPU消耗，

這個方案看上去是無損的，但其實這類方案需要根據業務邏輯做詳細設計，如果賬戶余額可能會減少，比如退票邏輯，那么這時候就需要考慮當一部分行記錄變成0的時候，代碼要有特殊處理，

小結

這篇文章聊了Mysql的三種鎖粒度，全域鎖，表鎖，行鎖，其中，全域鎖和表鎖都是 Mysql 層級的鎖，而行鎖是引擎層的鎖，

全域鎖，顧名思義，是將整個庫鎖住，什么情況下會將整個庫加鎖呢？當然是做整庫備份的時候，備份的時候我們要求資料只讀，因為不能出現在備份程序中資料在變化，所以，可以使用FTWRL命令來讓整個庫處于只讀的狀態，但這種方法，對于增刪改以及DDL操作等是不支持的，會阻塞這些行為，那還有一種方式讓整個庫處于只讀的狀態，就是使用 mysqldump 的 –single-transaction引數，不僅可以讓資料庫處于只讀的狀態，而且還不影響其他增刪改的一系列操作，何樂而不為，但使用這個工具的前提是，資料庫的所有表都要支持 只讀事務隔離級別，滿足這個條件，才可以使用，否則，還是乖乖使用 FTWRL命令 來備份吧，

表級別的鎖，分為兩種：表鎖和MDL鎖，在還沒有出現更細粒度的鎖的時候，表鎖是最常用的處理并發的方式，當然，如果有InnoDB引擎支持的行級鎖，一般還是不使用表級鎖的，鎖的粒度越小，并發度越高，其中，MDL鎖分為讀鎖和寫鎖，而讀鎖之間不影響的，讀鎖與寫鎖，寫鎖與寫鎖是互斥的，需要等一個執行完另一個才能執行，

行鎖，不是所有的引擎都支持行級鎖，像 MyISAM 引擎就不支持行級鎖，這也是為什么 InnoDB 替代 MyISAM 的主要原因之一，行鎖，就一個概念是兩階段鎖，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放，還一個概念是，死鎖與死鎖檢測，出現死鎖后，會有兩種策略，一種是等待超時，另一種是，檢測機制發現有死鎖時，就回滾死鎖鏈條中的某一個事務，讓其他事務得以執行，當然第二種策略是比較合理的，但也有一些問題，比如：如果所有的事務都要進行死鎖檢測，死鎖檢測這個動作要耗費大量的CPU資源，文中提到了一個將一行改成邏輯上的多行來減少鎖沖突的方案，但需要做詳細的設計，還需要做一些特殊處理，