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ā)生了死鎖。
總結(jié)
以上為個人經(jīng)驗(yàn),希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
Mysql徹底解決中文亂碼問題的方案(Illegal mix of collations for operation)
mysql數(shù)據(jù)庫和中文支持很不友好,經(jīng)常見到“Illegal mix of collations for operation”錯誤,該如何解決呢?下面小編給大家?guī)砹薽ysql數(shù)據(jù)庫中涉及到哪些字符集及徹底解決中文亂碼的解決方案,非常不錯,一起看看吧2016-08-08MySQL數(shù)據(jù)庫中的TRUNCATE?TABLE命令詳解
這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫中TRUNCATE?TABLE命令的相關(guān)資料,Truncate Table“清空表”的意思,它對數(shù)據(jù)庫中的表進(jìn)行清空操作,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-05-05MYSQL?Binlog恢復(fù)誤刪數(shù)據(jù)庫詳解
MySQL一旦誤刪數(shù)據(jù)庫之后恢復(fù)數(shù)據(jù)很麻煩,這里記錄一下艱辛的恢復(fù)過程,這篇文章主要給大家介紹了關(guān)于如何利用MySQL的binlog恢復(fù)誤刪數(shù)據(jù)庫的相關(guān)資料,需要的朋友可以參考下2022-11-11You have an error in your SQL&
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version2023-02-02Mysql連接本地報錯:1130-host?...?is?not?allowed?to?connect?t
這篇文章主要給大家介紹了關(guān)于Mysql連接本地報錯:1130-host?...?is?not?allowed?to?connect?to?this?MySQL?server的解決方法,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-03-03Mysql鎖機(jī)制之行鎖、表鎖、死鎖的實(shí)現(xiàn)
本文主要介紹了Mysql鎖機(jī)制之行鎖、表鎖、死鎖的實(shí)現(xiàn),文中通過示例代碼介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下2022-03-03mysql注入之長字符截斷,orderby注入,HTTP分割注入,limit注入方式
這篇文章主要介紹了mysql注入之長字符截斷,orderby注入,HTTP分割注入,limit注入方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-11-11MySql,MVCC實(shí)現(xiàn)及其機(jī)制,快照讀在RC,RR下的區(qū)別說明
這篇文章主要介紹了MySql,MVCC實(shí)現(xiàn)及其機(jī)制,快照讀在RC,RR下的區(qū)別說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04windows 64位下MySQL 8.0.15安裝教程圖文詳解
本文通過圖文并茂的形式給大家介紹了MySQL 8.0.15安裝教程(windows 64位),非常不錯,具有一定的參考借鑒價值,需要的朋友可以參考下2019-04-04