1、問題復(fù)現(xiàn)
今天有業(yè)務(wù)研發(fā)反饋，在并發(fā)場景下，有一個業(yè)務(wù)操作會爆死鎖錯誤。通過日志我們復(fù)原了兩個死鎖發(fā)生的過程
經(jīng)過簡化后，造成死鎖的兩個事務(wù)如下，為了讓大家可以直接復(fù)現(xiàn)。我這里自己構(gòu)造了完整數(shù)據(jù)，大家可以拿來直接使用
#數(shù)據(jù)結(jié)構(gòu)
CREATE TABLE `test_deadlock` (
`id` bigint NOT NULL,
`billid` bigint NOT NULL COMMENT '單據(jù)id',
`totalnum` bigint NOT NULL COMMENT '總箱數(shù)',
`modifytime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄修改時間',
#...省略其它無關(guān)字段
PRIMARY KEY (`id`),
KEY `idx_billid` (`billid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
#SESSION1 SQL
delete from test_deadlock where billid=1001;
insert into test_deadlock (`id`,`billid`,`totalnum`) values (1,1001,0);
UPDATE test_deadlock force index (idx_billid)
SET totalnum = 10
WHERE billid = 1001;
#SESSION2 SQL
delete from test_deadlock where billid=1001;
insert into test_deadlock (`id`,`billid`,`totalnum`) values (2,1001,0);
UPDATE test_deadlock force index (idx_billid)
SET totalnum = 10
WHERE billid = 1001;
看到兩個SESSION語句后。 研發(fā)說，我第一句delete 語句就把行鎖住了，為什么還是會出現(xiàn)死鎖呢？研發(fā)的提問讓我先入為主的覺得這個死鎖是不可能產(chǎn)生的。我先結(jié)合代碼和日志，確定造成死鎖的整個SQL，通過分析能肯定回滾的事務(wù)整個事務(wù)中只有這三條SQL(排除掉其中小部份查詢),而這三個SQL只有這一張表。再通過回滾時間去確定產(chǎn)生死鎖的另一個事務(wù)。通過分析，能確定就是session2造成了死鎖。
這個時候，在這里卡住了，只從語義上說，的確第一條delete 就會持有鎖了。沒法，只有又去翻代碼了解業(yè)務(wù)，通代碼最后了解到，該事務(wù)這種寫法，是為了新增與修改代碼復(fù)用。不管數(shù)據(jù)庫中有沒有，先刪除后插入，即適于用修改，也能適用于新增。
當(dāng)表里沒有數(shù)據(jù)的時候，delete就鎖不住行，現(xiàn)在我們來看死鎖過程。
第一步：
將兩個session的隔離模式都設(shè)為RC.我們生產(chǎn)環(huán)境用的是RC
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
第二步：
分別執(zhí)行刪除語句。在session1中執(zhí)行下面語句
begin;
delete from test_deadlock where billid=1001;
在session2中也執(zhí)行下面語句
begin;
delete from test_deadlock where billid=1001;
第三步：
分別執(zhí)行插入語句
session1
insert into test_deadlock (`id`,`billid`,`totalnum`) values (1,1001,0);
session2
insert into test_deadlock (`id`,`billid`,`totalnum`) values (2,1001,0);
第四步：
分別執(zhí)行更新語句
session1
UPDATE test_deadlock force index (idx_billid)
SET totalnum = 10
WHERE billid = 1001;
session2
UPDATE test_deadlock force index (idx_billid)
SET totalnum = 10
WHERE billid = 1001;
在session2執(zhí)行完成后。 死鎖出現(xiàn)
第五步。兩個session 分別執(zhí)行 rollback;這是一個好習(xí)慣！
ROLLBACK;
2、死鎖分析
我的更新語句中強制索引force index (idx_billid) 是因為測試數(shù)據(jù)少。必須這樣寫不然就會走主鍵。而生產(chǎn)環(huán)境表記錄多。會自動走idx_billid 不需要強制索引。
我們回到死鎖前一刻觀察鎖情況。即在 第四步session1執(zhí)行了更新后查看
select engine_transaction_id,object_name,index_name,lock_type,lock_mode,lock_status,lock_data from performance_SCHEMA.DATA_locks;
事務(wù)1的鎖情況
test_deadlock 表意向鎖 持有
idx_billid 索引記錄 1001，1的行鎖 持有
PRIMARY主鍵記錄 1的行鎖 持有
idx_billid 索引錄記 1001, 2的行鎖 等待事務(wù)2釋放
事務(wù)2的鎖情況
test_deadlock 表意向鎖 持有
idx_billid 索引記錄 1001，2的行鎖 持有
當(dāng)去執(zhí)行session2的update語句的時候。session2會等待idx_billid 1001，1的 行鎖。即上面session1持有鎖的第2行 index_name = idx_billid lock_data = 100,1。這樣就形成了相互等待。 死鎖形成。
3、死鎖避免
經(jīng)過死鎖分析，我們知道。當(dāng)update 更新一行，走索引時，持有鎖的情況會是這樣：先持有符合條件索引行的鎖，再持有主鍵行的鎖。
delete 與update同理。 當(dāng)我們要避免這種情況時，delete 與update 要求 where條件只能帶主鍵
UPDATE test_deadlock
SET totalnum = 10
WHERE id = 1;
當(dāng)where條件是主鍵時，持有鎖情況
只有表意向鎖和主鍵的行鎖
UPDATE test_deadlock force index (idx_billid)
SET totalnum = 10
WHERE billid = 1001;
當(dāng)where條件是索引字段值時，持有鎖情況
先持有索引行的鎖，再持有主鍵行的鎖。
所以我們要求：
delete from table where 主鍵 = ？
update table set col=? where 主鍵= ？
當(dāng)兩個session都換為主鍵后。上述語句交叉并發(fā)執(zhí)行，不會發(fā)生死鎖，大家可以在測試環(huán)境用本貼數(shù)據(jù)和SQL做驗證。
#SESSION1
begin;
delete from test_deadlock where id=1;
insert into test_deadlock (`id`,`billid`,`totalnum`) values (1,1001,0);
UPDATE test_deadlock
SET totalnum = 10
WHERE id = 1;
#SESSION2
begin;
delete from test_deadlock where id=2;
insert into test_deadlock (`id`,`billid`,`totalnum`) values (2,1001,0);
UPDATE test_deadlock
SET totalnum = 10
WHERE id = 2;
