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

1.mysql存儲(chǔ)引擎

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

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

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

2.update更新過(guò)程

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

反之：

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

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

1）先獲取非主鍵索引的行級(jí)鎖；

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

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

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

假如：

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

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

3.解決方案

where條件加主鍵索引

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

逐條更新

行級(jí)鎖是鎖索引：

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

mysql批量update死鎖

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

但是由于update更新耗時(shí)比較長(zhǎng)，synchronized鎖住對(duì)象，導(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 同時(shí)達(dá)到 select，T1 對(duì) table 加共享鎖，T2 也對(duì) table 加共享鎖，當(dāng) T1 的 select 執(zhí)行完，準(zhǔn)備執(zhí)行 update 時(shí)，根據(jù)鎖機(jī)制，T1 的共享鎖需要升級(jí)到排他鎖才能執(zhí)行接下來(lái)的 update.在升級(jí)排他鎖前，必須等 table 上的其它共享鎖（T2）釋放，同理，T2 也在等 T1 的共享鎖釋放。于是死鎖產(chǎn)生了。

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

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

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論