mysql在update,非主鍵索引更新引起死鎖問題

更新時間：2023年08月29日 10:08:23 作者：susu1083018911

這篇文章主要介紹了mysql在update,非主鍵索引更新引起死鎖問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

mysql在update,非主鍵索引更新引起死鎖

1.mysql存儲引擎

Innodb：支持事務(wù)，更新時采用行級鎖，并發(fā)性高

MyISAM：不支持事務(wù)，更新時表鎖，并發(fā)性差

因此使用Innodb才會發(fā)生死鎖，從mysql5.6開始默認(rèn)引擎Innodb

2.update更新過程

行級鎖并不是直接鎖記錄，而是鎖索引，如果一條SQL語句用到了主鍵索引，mysql會鎖住主鍵索引；如果一條語句操作了非主鍵索引，mysql會先鎖住非主鍵索引，再鎖定主鍵索引。

反之：

如果操作用到了主鍵索引會先在主鍵索引上加鎖，然后在其他索引上加鎖。
如果沒有用到索引，則進(jìn)行全表掃描，鎖表。

當(dāng)where條件為非主鍵索引，執(zhí)行update時，會經(jīng)過一下步驟：

1）先獲取非主鍵索引的行級鎖；

2）由數(shù)據(jù)庫基本原理可知，where條件為非主鍵索引時，會發(fā)生回表查詢，進(jìn)而再獲得主鍵索引的行級鎖；

3）更新完畢，進(jìn)行事務(wù)提交。

根據(jù)上述步驟可知，對于非主鍵索引的update操作，其加鎖過程并非原子操作，而且是分別需要獲取不同索引的行級鎖，可能會產(chǎn)生死鎖：

假如：

一條update語句用到主鍵索引和非主鍵索引，則獲取鎖的順序是先獲取主鍵索引，再獲取非主鍵索引；

而同時，另一條update語句只用到非主鍵索引，則獲取鎖的順序是先獲取非主鍵索引，再獲取主鍵索引，二者正好發(fā)生在步驟 1）和 2）中間，則會造成鎖。

3.解決方案

where條件加主鍵索引

先上鎖查詢查出來，在根據(jù)主鍵更新

逐條更新

行級鎖是鎖索引：

由于MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現(xiàn)鎖沖突的

mysql批量update死鎖

項(xiàng)目使用了多線程，同時調(diào)用service中的update方法更新數(shù)據(jù)，之前由于在update方法上加了synchronized做了線程同步，沒有出現(xiàn)mysql update死鎖的問題。

但是由于update更新耗時比較長，synchronized鎖住對象，導(dǎo)致調(diào)用service中的其他方法阻塞，效率地下，于是優(yōu)化synchronized，移到方法內(nèi)部的同步代碼段，然后雖然效率提高了，但是mysql總是出現(xiàn)死鎖的bug：

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found
when trying to get lock; try restarting transaction;

發(fā)生原因

T1:begin tran select * from table lock in share mode update table set column1='hello'

T2:begin tran select * from table lock in share mode update table set column1='world'

假設(shè) T1 和 T2 同時達(dá)到 select，T1 對 table 加共享鎖，T2 也對 table 加共享鎖，當(dāng) T1 的 select 執(zhí)行完，準(zhǔn)備執(zhí)行 update 時，根據(jù)鎖機(jī)制，T1 的共享鎖需要升級到排他鎖才能執(zhí)行接下來的 update.在升級排他鎖前，必須等 table 上的其它共享鎖（T2）釋放，同理，T2 也在等 T1 的共享鎖釋放。于是死鎖產(chǎn)生了。

因此，當(dāng)sql發(fā)出一個update請求之后，數(shù)據(jù)庫會對表中的每條記錄加上共享鎖。

然后數(shù)據(jù)庫會根據(jù)where條件，將符合條件的記錄轉(zhuǎn)換為排他鎖（mysql innodb默認(rèn)對索引加鎖），我們的多個線程update時，就出現(xiàn)了上面的情況，發(fā)生了死鎖。