系列文章

一、原來一條select語句在MySQL是這樣執行的《死磕MySQL系列 一》

二、一生摯友redo log、binlog《死磕MySQL系列 二》

前言

最近數據庫老是出現下面死鎖情況，藉著這倆種情況出發詳細的理解一下MySQL中的鎖。

Lock wait timeout exceeded; try restarting transaction

Deadlock found when trying to get lock; try restarting transaction

一、MySQL中有那些鎖

全局鎖

根據全局兩個字，就可以肯定的是給一個整體加上鎖。全局鎖就是對整個數據庫實例加鎖。

對於flush tables with read lock，執行完成後整庫就處於只讀狀態，所有語句將被堵塞，包括增刪改查、創建表、修改表結構等語句。

表鎖

表鎖大家都非常熟悉了，執行命令lock tables kaka read ,kaka2 write直到unlock tables之前，其它線程是無法對kaka寫kaka2讀的。

執行命令的這個線程也只可以對kaka讀，kaka2寫。

行鎖

行鎖是在引擎層由各個引擎自己實現的。在MySQL中Innodb存儲引擎支持行鎖，若不支持行鎖意味着並發控制只能使用表鎖，對於這種引擎的表，同一張表上任何時刻只能有一個更新在執行，這就會影響到業務並發度。（由於篇幅的原因，下期細談）

二、全局鎖

演示執行flush tables with read lock命令後數據庫處於什麼狀態。

終端1執行全局鎖命令

端口2執行刪除操作，它不會直接執行成功，而是在端口1解鎖後返回。

這個SQL需要3分鐘的執行時間，這3分鐘就是咔咔打開終端2並連接數據庫的時間。

現在見證了開篇所說的全局鎖直接讓整個庫處於只讀狀態，這裡只演示了刪除操作其它的幾個操作自己嘗試一下。

在蔣老師的文章中看到全局鎖最典型的場景是用於邏輯備份，即是將整個庫的每一個表都select存儲成文本。

現在，你想想這種場景是在什麼需要下出現的。

假如只有一個主庫，執行了全局鎖整庫處於只讀狀態，那麼業務基本停擺，產品無法使用。

此時你會有疑問我在從庫上備份啊！備份期間，不能執行主庫同步過來的binlog的，數據量如果非常大，將引發主從延遲過大，必須進行全量備份。

以上是全局鎖引發的負面情況，但再看備份不加全局鎖會出現什麼問題。

相信大多數小夥伴都開發過支付類項目，接下來就用支付案例讓大家很清晰的理解備份不加全局鎖引發的問題。

發起一個邏輯備份。如果一個用戶在備份期間購買了你公司的服務，在業務邏輯先扣除用戶餘額，然後給用戶添加你公司對應的產品。

顯然，這個邏輯沒有問題的，但在特殊案例下執行備份操作就會引發問題。

若在時間順序上先備份用戶餘額，然後用戶發起購買，接着備份用戶購買的產品表。

一個非常清晰的問題出現了，用戶餘額沒減成功但用戶卻獲得了對應的產品。

從用戶的角度出發那是賺大發了，但這種執行順序如果反過來的話就會產生不一樣的結果。

先備份用戶產品表，然後備份用戶餘額表，就會出現用戶錢花了東西沒得着，這還得了，用戶都是衣食父母這不是再割父母的韭菜。

也就是說，在備份不加鎖的話，不同表之間的執行備份的順序不同，如果某個表在備份的過程中進行了更新並且成功備份而關聯的表已經備份完成無法再進行跟新，此時就會出現數據不一致。

在MVCC那篇文章中提到了一個非常重要的概念一致性視圖（read view），一致性視圖是根據快照讀那一刻所有未提交事務的集合，前提是隔離級別為可重複。

這時你應該知道要說什麼了，沒錯就是官方大大給提供的邏輯備份工具mysqldump。

mysqldump的備份原理是通過協議連接到 MySQL 數據庫，將需要備份的數據查詢出來，將查詢出的數據轉換成對應的insert 語句，當我們需要還原這些數據時，只要執行這些 insert 語句，即可將對應的數據還原。

例如備份test庫的命令為mysqldump -uroot -p test > /backup/mysqldump/test.db。

當mysqldump使用參數–single-transaction時，備份數據之前會啟動一個事物，拿到一致性視圖（read view），所以在整個備份的過程中是支持更新的。

既然有了官方大大提供的mysqldump工具為何還要使用flush tables with read lock來將整表鎖住呢？

別忘記了剛提到的可以在備份過程中進行更新，可以更新的前提是可以得到一致性視圖，獲取一致性視圖的前提是開啟事務。這裡你應該清楚，不是所有存儲引擎都支持事物。

如果有的表使用了別的存儲引擎不支持事物，那麼就只能使用flush tables with read lock方法，說到這裡希望大家盡量在創建表時都選擇Innodb存儲引擎。

看着好一會了，還能記得咱們要幹什麼嗎？需求是全庫處於只讀狀態。

如果你搭建過MySQL的主從架構，就會知道主庫用來寫數據，從庫用來讀數據並且從庫不支持寫入操作，可以實現這樣的效果都是來自於參數readonly。

同樣執行set global readonly=true也可以達到整庫只讀狀態，那麼為什麼從一開始沒有給大家說這個方案，那是有原因的。

一是，剛剛提到的搭建主從架構需要使用readonly來判斷主庫於從庫。

二是，在異常處理的方式不同。如果使用flush talbes with read lock命令客戶端異常後MySQL會自動釋放全局鎖，讓整個庫回到正常狀態。而整庫設置為readonly後，一旦發生異常就會一直處於只讀狀態，導致整庫長時間處於不可寫狀態。

所以說數據庫一旦加上全局鎖後數據的增刪改、修改表結構、修改字段等操作都會被鎖住。

三、表鎖

表鎖跟全局鎖釋放的命令一致unlock tables，同樣客戶端斷開的時候也會自動釋放。

在老一輩的革命前輩處理並發都是用的表鎖，應該都知道鎖表的影響雖不及鎖庫影響大，但在今天鎖的粒度已經支持到行鎖了（前提是使用Innodb存儲引擎，就沒必要再使用行鎖來處理並發了。

再來看錶鎖中的另一位哥們「元數據鎖」（metalock）簡稱「MDL」，這個鎖估計很少人知道，因為在實際開發過程中是不會有實際的語法來開啟或關閉。

這個特性是在MySQL5.5版本後引入的，就是為了解決A線程正在查詢一個表的數據，在這期間B線程修改了表的數據結構，那麼就會造成查詢的結果跟表結構對不上，這肯定是不行的。

當你訪問一個表時會默認加上MDL寫鎖，不管在任何時候記住讀鎖於讀鎖之間不互斥，讀鎖與寫鎖，寫鎖與寫鎖之間互斥，知道行鎖的共享鎖、排它鎖也是這麼個理。

那麼MDL 不需要顯示調用，那它是在什麼時候釋放的?

回答是：「MDL 是在事務提交後才會釋放，這意味着事務執行期間，MDL 是一直持有的。」

那麼看一個場景。

首先，線程A開啟事務並執行查詢語句時，對錶加上了MDL鎖。

然後，線程B執行的是查詢，並不會堵塞住，因為讀與讀並不衝突。

接着，線程C修改表結構，此時的線程A還未提交事務，MDL還未釋放，這時的線程因無法獲取到MDl寫鎖，就會被阻塞。

最後線程D執行查詢會發生什麼呢？

答案是堵塞。

到這裡按照正常的邏輯，線程C沒有獲取到MDL的寫鎖，線程D是可以申請到MDL讀鎖的，那為什麼還會堵塞呢！

這是因為申請MDL鎖的操作會形成一個隊列，隊列中寫鎖獲取優先級高於讀鎖，一旦出現MDL寫鎖等待，會阻塞後續該表的所有CURL操作。

到這裡你有沒有後背發涼，一旦你在一個未提交事務之後執行了DDL操作，那麼等到的結果就是MySQL掛掉，客戶端會有重試機制，DDL後所有CURD會在超時後重新發起請求，這個庫的線程會很快爆滿。

既然這樣如何給表安全的執行DDL操作呢？

首先，必須解決到長事務，事務不提交MDL鎖就無法釋放。

然後，在MySQL系統表裡找到infomation_schema庫中的innodb_trx，可以查看當前正在執行中的事務ID，這個表在事務那期文章中也沒少提。

接着，你是不是想kill掉這些長事務然後執行DDL不就得了。

試想一下，當你kill掉的下一刻一個新的事務又進來了，同時你又執行了DDL操作，後果是什麼應該清楚了哈！這種操作肯定是不行的。

官方大大怎麼會允許這種情況發生呢！

於是當你執行DDL操作時alter table kaka wait 30 add name可以加一個等待時間，如果在這個等待時間拿到MDL寫鎖最好，拿不到也不能堵塞後邊的業務邏輯，先放棄。再重試執行這個命令。

四、總結

「

堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的互聯網上能給你帶來一點幫助，我是咔咔，下期見。

」