重重封鎖,讓你一條資料都拿不到《死磕MySQL系列 十三》

發表於2021-12-27

在開發中有遇到很簡單的SQL卻執行的非常慢,甚至只查詢一行資料。

咔咔遇到的只有兩種情況,一種是MySQL伺服器CPU佔用率很高,所有的SQL都執行的很慢直到超時,程式也直接502,另一種情況是行鎖造成的鎖等待。

接下來咔咔帶領大家看看各種為難SQL執行的場景,本期文章帶大家再熟悉一下MySQL中的鎖

最新文章

死磕MySQL系列總目錄

什麼?還在用delete刪除資料《死磕MySQL系列 九》

MySQL統計總數就用count(*),別花裡胡哨的《死磕MySQL系列 十》

為什麼MySQL字串不加引號索引失效?《死磕MySQL系列 十一》

開啟order by的大門,一探究竟《死磕MySQL系列 十二》

一、MDL鎖

現在你應該知道要聊的是MDL,這個鎖很少有開發人員去關注,在開發中並沒有實際的語法來開啟或關閉鎖。

這個特性是在MySQL5.5引入的,目的是為了解決一張表同時在做查詢和修改表結構,這種情況必定會造成查詢結果跟表結構無法對應。

所以,當你訪問一個表時會預設加上MDL鎖,MDL鎖的互斥關係跟共享鎖、排它鎖是一樣的,讀寫互斥,寫寫互斥。

MDl鎖是在事務提交後才會釋放,執行期間一直持有。

同時你需要知道MDL鎖的操作會形成一個佇列,佇列中寫鎖獲取優先順序高於讀鎖,一旦出現MDL寫鎖等待,會阻塞後續該表的所有CURL操作。

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

當執行緒A通過DDL時手裡握著表的MDL寫鎖,而執行緒B的查詢需要獲取MDL讀鎖,所以執行緒B就一直處於鎖等待狀態。

在生產環境是堅決不可以直接修改表結構的,如果你的表非常大的話會很容易造成業務所有的CURD處於堵塞。

解決方案

大表DDL可以使用pt-online-schema-change這個工具來處理,具體怎麼用後續文章會跟大家分享出來。

若不小心線上上執行了修改表結構,可以通過show processlist命令來查詢,不過這個命令在查詢上很不方便,可以使用performance_schema和sys系統庫來進行查詢。前提是你的MySQL引數performance_schema=on,在MySQL8.0.26版本中,這個引數是預設開啟的,若你所在的版本沒有開啟時可以開啟。

然後就可以執行select blocking_pid from sys.schema_table_lock_waits,就可以看到當前持有MDL鎖的執行緒ID,直接使用kill命令即可。

二、全域性鎖

在MySQL強人“鎖”難《死磕MySQL系列 三》的文章中給大家聊到了全域性鎖,使用語法flush table t with read lock 或者 flush table with read lock

指定表名時就鎖定指定表,未指定時表示鎖定所有表。

這兩個語句執行是非常快的,一般不會造成SQL堵塞,但防火、防盜你也防不住有其它執行緒的語句把flush語句堵塞住。

執行緒A執行大事物,需要執行10s

執行緒B執行flush table t with read lock

執行緒C執行select * from evt_sms where id = 1

所以執行緒C哪怕是隻查詢一條資料在10s內也是返回不了結果的,執行緒B的flush 命令需要等執行緒A的事務執行完畢,而執行緒C此時卻被未執行的執行緒B堵塞著。

解決方案

一般出現這種情況只需要執行show processlist就可以看到堵塞執行緒C的執行緒是那個,同樣直接使用kill掉對應的執行緒即可。

三、行鎖

這個場景是非常好模擬的,接下來讓我們一起看看

執行緒A正常修改大批量資料執行語句為update evt_sms set code = 123 where id > 11089

執行緒B執行select * from evt_sms where id = 120365 lock in share mode

在文章開頭就跟大家簡單的說了一句,MySQL中讀鎖與寫鎖、寫鎖與寫鎖互斥,所以執行緒B會一直等待執行緒A的事務提交之後才能返回結果。

解決方案

分析一下,執行緒B執行的語句新增的是讀鎖,能被堵住的只有是寫鎖,所以可以直接在sys.innodb_lock_waits表中查到佔著這個寫鎖的是誰。

執行語句select * from evt_sms sys.innodb_lock_waits where lock_table='kaka.evt_sms'\G

這個試驗就不演示了,復現過程也十分簡答可以自己看一下哈!輸出結果的最後一行就是解決方案,帶著你的答案來到評論區

四、快照讀引發的問題

瞭解過MVCC實現原理的大概率都會看到過當前讀、快照讀這兩個詞,如果你還不知道它們是什麼就好好記一下。

當前讀

執行select語句時加上共享鎖、排它鎖的操作就是當前讀。

例:select * from evt_sms where id = 1 lock in share mode

這裡的共享鎖、排它鎖也就是常說的讀鎖、寫鎖

在MySQL的Innodb儲存引擎中進行DML操作時會預設新增排它鎖

上邊這個例子,select語句一旦加上了共享鎖其它執行緒是不能修改當前記錄的,因此當前讀讀取的資料庫就是最新的資料

快照讀

快照讀的前提是隔離級別不是序列級別,序列級別的快照讀會退化為當前讀,快照讀的出現是為了提高事務併發性,其實現也是基於MVCC的

MVCC在某種情況下可以認為是行鎖的一個變種,但要知道的是在很多情況是不會有加鎖行為的

這時你應該記住快照讀獲取的資料不是最新的,有可能是之前版本的資料

實現MVCC的三大因素隱式欄位、undo log、read-view,read-view就是通過快照讀產生的,它是由查詢的那一時間所有未提交事務ID組成的陣列,和已經建立的最大事務ID組成的。然後通過本執行緒的事務ID在read-view中進行對比

為什麼說快照讀會引發查詢遲遲不返回結果

上文給大家提了一個東西undo log,都知道undo log是回滾日誌,查詢慢的原因也在這裡

執行緒A先開啟一個事務

執行緒B開啟對id為1的資料行進行更新

由於id = 1的資料很多所以會產生很多的版本鏈,這裡就認為是5萬個

執行緒A執行了select * from evt_sms where id = 1就會遲遲返回不了結果

此時執行緒B並沒有提交事務,所以執行緒A的查詢需要根據版本鏈一直回退到5W個undo log之前,也就是這裡導致查詢非常慢

下圖是一個咔咔之前做的undo log版本鏈圖

執行緒A的查詢是快照讀,執行查詢時會產生read-view,read-view會把執行緒A、執行緒B的事務存放在一個陣列中,然後用一定的規則進行判斷執行緒A能看到的資料是什麼。

比對規則是什麼

trx_id為當前的事務ID,min_id、max_id為當前啟動事務的最大事務ID和最小事務ID

如果落在trx_id<min_id,表示此版本是已經提交的事務生成的,由於事務已經提交所以資料是可見的

如果落在trx_id>max_id,表示此版本是由將來啟動的事務生成的,是肯定不可見的

若在min_id<=trx_id<=max_id時

如果row的trx_id在陣列中,表示此版本是由還沒提交的事務生成的,不可見,但是當前自己的事務是可見的 如果row的trx_id不在陣列中,表明是提交的事務生成了該版本,可見 在這裡還有一個特殊情況那就是對於已經刪除的資料,在之前的undo log日誌講述時說了update和delete是同一種型別的undo log,同樣也可以認為delete就是update的特殊情況。

當刪除一條資料時會將版本鏈上最新的資料複製一份,然後將trx_id修改為刪除時的trx_id,同時在該記錄的頭資訊中存在一個delete flag標記,將這個標記寫上true,用來表示當前記錄已經刪除。

在查詢時按照版本鏈的規則查詢到對應的記錄,如果delete flag標記位為true,意味著資料已經被刪除,則不返回資料。

五、總結

本期文章通過MDL鎖、全域性鎖、行鎖、undo log說明查詢一條資料頁遲遲不返回的問題,可以看到大多數都是一些理論知識,有些東西看著看著也就理解其中的含義了。

這裡需要注意的是不要把MDL和DML搞混淆了,這可是兩個東西,MDL指的是鎖、而DML指的是資料庫的增刪改查。

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

相關文章