近日遇到一個比較奇怪的deadlock錯誤, 錯誤詳情:
Deadlock found when trying to get lock; try restarting transaction; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException...
跟蹤代碼后最終定位到一段業(yè)務(wù)邏輯:
delete from A where no = $no;
insert into A(no, value) values($no, "value");
印象中mysql一直是使用行級鎖, 為什么此處在并發(fā)時會發(fā)生死鎖呢? 唯一的解釋是mysql在這邊鎖住的不只一行數(shù)據(jù).
簡單搜索之后, 發(fā)現(xiàn)mysql的鎖分為三種(按照鎖定的行數(shù)劃分):
1.record lock：記錄鎖掌动，也就是僅僅鎖著單獨的一行
2.gap lock：區(qū)間鎖四啰，僅僅鎖住一個區(qū)間（注意這里的區(qū)間都是開區(qū)間，也就 是不包括邊界值粗恢，至于為什么這么定義柑晒？innodb官方定義的）
3.next-key lock：record lock+gap lock，所以next-key lock也就半開半閉區(qū)間眷射，且是下界開匙赞，上界閉。(為什么這么定義妖碉？innodb官方定義的)
由于此處是在明確指定了no=XX的情況下拋出了死鎖異常, 并且no建立的是普通索引, 所以此處mysql使用的應(yīng)該是next-key lock(查看何種情況下使用何種鎖 https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html).
下面來舉個手冊上的例子看看next-key lock是如何上鎖的涌庭。假如一個索引的行有10,11,13,20
那么可能的next-key lock的包括：
(無窮小, 10]
(10,11]
(11,13]
(13,20]
(20, 無窮大)
下面分析何種情況下會發(fā)生死鎖.
結(jié)合業(yè)務(wù)邏輯, 執(zhí)行新增操作時也會執(zhí)行一樣的邏輯, 先進(jìn)行delete.
例如,現(xiàn)在表student中有四條數(shù)據(jù):
現(xiàn)在要新增一條數(shù)據(jù), no = 21, 這時候會先進(jìn)行delete, 線程會鎖住(20, 無窮大)這塊區(qū)間, 加入這個時候另一個線程正在新增另一條數(shù)據(jù) no = 22, 線程也會鎖住(20, 無窮大)這塊區(qū)間就會發(fā)生死鎖.
下面看具體實驗:
創(chuàng)建一張表student.
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`no` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_no` (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1
插入數(shù)據(jù):
INSERT INTO student (no,name) VALUES(10, "Jim");
INSERT INTO student (no,name) VALUES(11, "Kimi");
INSERT INTO student (no,name) VALUES(13, "Tom");
INSERT INTO student (no,name) VALUES(20, "Mike");
執(zhí)行兩個事務(wù):
session 1:
begin;
delete from student where no = 21;
session 2:
begin;
delete from student where no = 22;
此處解釋一下, 此時,session 1和session 2都會對區(qū)間(20, 無窮大)加鎖, 而區(qū)間鎖只是用來防止其他事務(wù)在區(qū)間中插入數(shù)據(jù)，區(qū)間x鎖 與區(qū)間S鎖效果是一樣的(只要不是插入操作), 因此兩個session都會持有鎖.
參考:https://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html
Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap. Thus, a gap X-lock has the same effect as a gap S-lock.
繼續(xù)執(zhí)行:
session 1:
INSERT INTO student (no,name) VALUES(21, "Zhoubing");
此時session 1阻塞(因為session 2持有區(qū)間鎖), 如圖:
session 2:
INSERT INTO student (no,name) VALUES(22, "Zhoubing");
此時session 2死鎖(因為session 1持有區(qū)間鎖), 如圖:
.
總結(jié), delete之后進(jìn)行insert有可能發(fā)生死鎖, 因為delete可能會持有區(qū)間鎖, 而區(qū)間鎖是可重入的(只要不是插入數(shù)據(jù)).
解決方案:
將事務(wù)隔離級別將為read commit.
參考資料:
1.Next-Key Locks
2.Locks Set by Different SQL Statements in InnoDB
