咨詢(xún)電話(huà)
微信掃一掃
新聞動(dòng)態(tài)
怎么解決MySQL死鎖問(wèn)題?
網(wǎng)站優(yōu)化 發(fā)布者:ou3377 2021-12-08 09:15 訪(fǎng)問(wèn)量:163
咱們使用 MySQL 大概率上都會(huì )遇到死鎖問(wèn)題,這實(shí)在是個(gè)令人非常頭痛的問(wèn)題。本文將會(huì )對死鎖進(jìn)行相應介紹,對常見(jiàn)的死鎖案例進(jìn)行相關(guān)分析與探討,以及如何去盡可能避免死鎖給出一些建議。
話(huà)不多說(shuō),開(kāi)整!
死鎖是并發(fā)系統中常見(jiàn)的問(wèn)題,同樣也會(huì )出現在數據庫MySQL的并發(fā)讀寫(xiě)請求場(chǎng)景中。當兩個(gè)及以上的事務(wù),雙方都在等待對方釋放已經(jīng)持有的鎖或因為加鎖順序不一致造成循環(huán)等待鎖資源,就會(huì )出現“死鎖”。常見(jiàn)的報錯信息為 Deadlock found when trying to get lock...
。
舉例來(lái)說(shuō) A 事務(wù)持有 X1 鎖 ,申請 X2 鎖,B事務(wù)持有 X2 鎖,申請 X1 鎖。A 和 B 事務(wù)持有鎖并且申請對方持有的鎖進(jìn)入循環(huán)等待,就造成了死鎖。
如上圖,是右側的四輛汽車(chē)資源請求產(chǎn)生了回路現象,即死循環(huán),導致了死鎖。
從死鎖的定義來(lái)看,MySQL 出現死鎖的幾個(gè)要素為:
為了分析死鎖,我們有必要對 InnoDB 的鎖類(lèi)型有一個(gè)了解。
MySQL InnoDB 引擎實(shí)現了標準的行級別鎖:共享鎖( S lock ) 和排他鎖 ( X lock )
不同事務(wù)可以同時(shí)對同一行記錄加 S 鎖。 如果一個(gè)事務(wù)對某一行記錄加 X 鎖,其他事務(wù)就不能加 S 鎖或者 X 鎖,從而導致鎖等待。
如果事務(wù) T1 持有行 r 的 S 鎖,那么另一個(gè)事務(wù) T2 請求 r 的鎖時(shí),會(huì )做如下處理:
T2 請求 S 鎖立即被允許,結果 T1 T2 都持有 r 行的 S 鎖 T2 請求 X 鎖不能被立即允許
如果 T1 持有 r 的 X 鎖,那么 T2 請求 r 的 X、S 鎖都不能被立即允許,T2 必須等待 T1 釋放 X 鎖才可以,因為 X 鎖與任何的鎖都不兼容。共享鎖和排他鎖的兼容性如下所示:
間隙鎖鎖住一個(gè)間隙以防止插入。假設索引列有2, 4, 8 三個(gè)值,如果對 4 加鎖,那么也會(huì )同時(shí)對(2,4)和(4,8)這兩個(gè)間隙加鎖。其他事務(wù)無(wú)法插入索引值在這兩個(gè)間隙之間的記錄。但是,間隙鎖有個(gè)例外:
如果索引列是唯一索引,那么只會(huì )鎖住這條記錄(只加行鎖),而不會(huì )鎖住間隙。 對于聯(lián)合索引且是唯一索引,如果 where 條件只包括聯(lián)合索引的一部分,那么依然會(huì )加間隙鎖。
next-key lock 實(shí)際上就是 行鎖+這條記錄前面的 gap lock 的組合。假設有索引值10,11,13和 20,那么可能的 next-key lock 包括:
(負無(wú)窮,10],(10,11],(11,13],(13,20],(20,正無(wú)窮)
在 RR 隔離級別下,InnoDB 使用 next-key lock 主要是防止幻讀
問(wèn)題產(chǎn)生。
InnoDB 為了支持多粒度的加鎖,允許行鎖和表鎖同時(shí)存在。為了支持在不同粒度上的加鎖操作,InnoDB 支持了額外的一種鎖方式,稱(chēng)之為意向鎖( Intention Lock )。意向鎖是將鎖定的對象分為多個(gè)層次,意向鎖意味著(zhù)事務(wù)希望在更細粒度上進(jìn)行加鎖。意向鎖分為兩種:
意向共享鎖( IS ):事務(wù)有意向對表中的某些行加共享鎖 意向排他鎖( IX ):事務(wù)有意向對表中的某些行加排他鎖
由于 InnoDB 存儲引擎支持的是行級別的鎖,因此意向鎖其實(shí)不會(huì )阻塞除全表掃描以外的任何請求。表級意向鎖與行級鎖的兼容性如下所示:
插入意向鎖是在插入一行記錄操作之前設置的一種間隙鎖,這個(gè)鎖釋放了一種插入方式的信號,即多個(gè)事務(wù)在相同的索引間隙插入時(shí)如果不是插入間隙中相同的位置就不需要互相等待。假設某列有索引值2,6,只要兩個(gè)事務(wù)插入位置不同(如事務(wù) A 插入3,事務(wù) B 插入4),那么就可以同時(shí)插入。
橫向是已持有鎖,縱向是正在請求的鎖:
在進(jìn)行具體案例分析之前,咱們先了解下如何去讀懂死鎖日志,盡可能地使用死鎖日志里面的信息來(lái)幫助我們來(lái)解決死鎖問(wèn)題。
后面測試用例的數據庫場(chǎng)景如下:MySQL 5.7 事務(wù)隔離級別為 RR
表結構和數據如下:
測試用例如下:
通過(guò)執行show engine innodb status 可以查看到最近一次死鎖的日志。
事務(wù)號為2322,活躍 6秒,starting index read 表示事務(wù)狀態(tài)為根據索引讀取數據。常見(jiàn)的其他狀態(tài)有:
mysql tables in use 1
說(shuō)明當前的事務(wù)使用一個(gè)表。
locked 1
表示表上有一個(gè)表鎖,對于 DML 語(yǔ)句為 LOCK_IX
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
LOCK WAIT
表示正在等待鎖,2 lock struct(s)
表示 trx->trx_locks 鎖鏈表的長(cháng)度為2,每個(gè)鏈表節點(diǎn)代表該事務(wù)持有的一個(gè)鎖結構,包括表鎖,記錄鎖以及自增鎖等。本用例中 2locks 表示 IX 鎖和lock_mode X (Next-key lock)
1 row lock(s)
表示當前事務(wù)持有的行記錄鎖/ gap 鎖的個(gè)數。
MySQL thread id 37, OS thread handle 140445500716800, query id 1234 127.0.0.1 root updating
MySQL thread id 37
表示執行該事務(wù)的線(xiàn)程 ID 為 37 (即 show processlist; 展示的 ID )
delete from student where stuno=5
表示事務(wù)1正在執行的 sql,比較難受的事情是 show engine innodb status
是查看不到完整的 sql 的,通常顯示當前正在等待鎖的 sql。
***** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2322 lock_mode X waiting
RECORD LOCKS 表示記錄鎖, 此條內容表示事務(wù) 1 正在等待表 student 上的 idx_stuno 的 X 鎖,本案例中其實(shí)是 Next-Key Lock 。
事務(wù)2的 log 和上面分析類(lèi)似:
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X
顯示事務(wù) 2 的 insert into student(stuno,score) values(2,10) 持有了 a=5 的 Lock mode X
| LOCK_gap,不過(guò)我們從日志里面看不到事務(wù)2執行的 delete from student where stuno=5;
這點(diǎn)也是造成 DBA 僅僅根據日志難以分析死鎖的問(wèn)題的根本原因。
RECORD LOCKS space id 11 page no 5 n bits 72 index idx_stuno of table cw****.****student trx id 2321 lock_mode X locks gap before rec insert intention waiting
表示事務(wù) 2 的 insert 語(yǔ)句正在等待插入意向鎖 lock_mode X locks gap before rec insert intention waiting ( LOCK_X + LOCK_REC_gap )
表結構和數據如下所示:測試用例如下:日志分析如下:
排他行鎖( Xlocks rec but no gap )
間隙鎖
會(huì )申請鎖住(,10],(10,20]之間的 gap 區域。gap 鎖4-10之間
, 故需事務(wù) T2 的第二條 insert 語(yǔ)句要等待事務(wù) T1 的 S-Next-key Lock 鎖
釋放,在日志中顯示 lock_mode X locks gap before rec insert intention waiting 。表結構如下,無(wú)數據:測試用例如下:死鎖分析:
可以看到兩個(gè)事務(wù) update 不存在的記錄,先后獲得間隙鎖( gap 鎖)
,gap 鎖之間是兼容的所以在update環(huán)節不會(huì )阻塞。兩者都持有 gap 鎖,然后去競爭插入意向鎖
。當存在其他會(huì )話(huà)持有 gap 鎖的時(shí)候,當前會(huì )話(huà)申請不了插入意向鎖,導致死鎖。
定位更少的行,減少鎖競爭
。大事務(wù)
,盡量將大事務(wù)拆成多個(gè)小事務(wù)來(lái)處理,小事務(wù)發(fā)生鎖沖突的幾率也更小。固定的順序
訪(fǎng)問(wèn)表和行。比如兩個(gè)更新數據的事務(wù),事務(wù) A 更新數據的順序為 1,2;事務(wù) B 更新數據的順序為 2,1。這樣更可能會(huì )造成死鎖。(運行了 start transaction 或設置了autocommit 等于0)
,那么就會(huì )鎖定所查找到的記錄。主鍵/索引
去查找記錄,范圍查找增加了鎖沖突的可能性,也不要利用數據庫做一些額外額度計算工作。比如有的程序會(huì )用到 “select … where … order by rand();”這樣的語(yǔ)句,由于類(lèi)似這樣的語(yǔ)句用不到索引,因此將導致整個(gè)表的數據都被鎖住。減少連接的表
,將復雜 SQL 分解
為多個(gè)簡(jiǎn)單的 SQL。以下文章來(lái)源于狼王編程 ,作者狼王
文章連接: http://www.gostscript.com/wzyh/787.html
版權聲明:文章由 晨展科技 整理收集,來(lái)源于互聯(lián)網(wǎng)或者用戶(hù)投稿,如有侵權,請聯(lián)系我們,我們會(huì )立即刪除。如轉載請保留
晨展解決方案
晨展新聞