數(shù)據(jù)庫版本
5.7.26
事務(wù)隔離級別
RR
Case A
建表語句一
CREATE TABLE `test13` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int(11) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
`env` varchar(64) NOT NULL DEFAULT '' COMMENT '使用環(huán)境',
PRIMARY KEY (`id`),
KEY `idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
insert into `test13` (`code`,`uid`,`env`) values (14,5,'env'),(14,10,'env'),(14,15,'env'),(14,15,'env'),(12,20,'env'),(12,20,'env');
事務(wù)列表一
| 時間軸 | 事務(wù)1 | 事務(wù)2 |
|---|---|---|
| T1 | begin | begin |
| T2 | update test13 set code = 15 where uid in (5,20);Query OK, 0 rows affected(0.00 sec)Rows matched: 3 Changed: 0 Warnings: 0 | |
| T3 | update test13 set code = 13 where uid = 15; RROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
通過指令 show engine innodb status看到的信息是事務(wù)2在等待主鍵上的行鎖
Case B
建表語句二
CREATE TABLE `x` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) NOT NULL DEFAULT '0',
`d` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c` (`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
表記錄
事務(wù)列表二
| 時間軸 | 事務(wù)1 | 事務(wù)2 |
|---|---|---|
| T1 | begin | begin |
| T2 | update x set d = 1 where c in (5,10);Query OK, 0 rows affected (0.00 sec)Rows matched: 2 Changed: 0 Warnings: 0 | |
| T3 | update x set d = 1 where c = 7;Query OK, 0 rows affected (0.00 sec)Rows matched: 1 Changed: 0 Warnings: 0 | |
| T4 | commit; | |
| T5 | commit; |
事務(wù)順利執(zhí)行，沒有發(fā)生任何鎖等待虏肾。
分析
參考MySQL 加鎖處理分析
對上面的case進(jìn)行分析徙赢。兩個Case很類似，都是開啟兩個事務(wù)岩睁，第一個事務(wù)通過二級非唯一索引 使用 in 對記錄進(jìn)行更新，第二個事務(wù)通過同一個二級非唯一索引對記錄進(jìn)行等值更新
CaseB為例進(jìn)行分析
事務(wù)1 首先會獲取uid索引上面行記錄為5和10上Next-key lock，分別是(3,5),5,(5,7),(7,10),10,(10,12) 然后再獲取對應(yīng)主鍵上面的記錄鎖
事務(wù)2 首先會獲取uid索引上面行記錄為7上的Next-key lock, 分別是(5,7),7,(7,10) 然后獲取對應(yīng)主鍵上面的記錄鎖
因為gap鎖之間是可以相互兼容的挫酿，uid上面的行鎖沒有沖突余赢，對應(yīng)的主鍵也沒有發(fā)生競爭芯义，所以CaseB可以順利執(zhí)行。
caseA中事務(wù)2為什么會等待事務(wù)1持有的鎖呢没佑？
首先對CaseA的Update語句轉(zhuǎn)為對應(yīng)的Select語句查看對應(yīng)的查詢計劃
我驚奇的發(fā)現(xiàn)第一條SQL沒有使用上索引毕贼，而是執(zhí)行了全表掃描，這就意味全表加鎖蛤奢，獲取了主鍵所有的行鎖 鬼癣，而第二條SQL是走索引的陶贼，但是與第一條SQL發(fā)生了鎖競爭。
第二個疑問待秃，為什么Case B沒有發(fā)生鎖競爭拜秧，接下來對CaseB進(jìn)行轉(zhuǎn)換Select
發(fā)現(xiàn)這條SQL是走索引的，跟我之前分析的一致章郁。
那么問題就轉(zhuǎn)換為為什么 CaseA中的第一條SQL就走全表掃描枉氮？
索引的區(qū)分度過低？
我們都知道當(dāng)索引的區(qū)分度過低時暖庄，sql優(yōu)化器會放棄走索引聊替，直接走全表掃描，但是CaseA中的第二條SQL同樣也走了索引培廓，并且在執(zhí)行下面的SQL惹悄，增加區(qū)分度后，第一條SQL還是走了全表掃描
insert into `test13` (`code`,`uid`,`env`) values (14,51,'env'),(14,101,'env'),(14,151,'env'),(14,152,'env'),(12,201,'env'),(12,203,'env');
選擇的數(shù)據(jù)有問題肩钠？
對比兩個case泣港，索引的區(qū)分度差不多，主要的區(qū)別在于條件的數(shù)據(jù)不同价匠，CaseA中的(5当纱，20)存在重復(fù)數(shù)據(jù)，CaseB中的(5,10)中沒有重復(fù)數(shù)據(jù)踩窖，那么我們換一批數(shù)據(jù)試試
當(dāng)查詢條件變成(5,51)的時候坡氯，這條SQL是正常走索引的。
把uid=20的重復(fù)數(shù)據(jù)刪除一條看看效果,發(fā)現(xiàn)SQL正常走索引
重新執(zhí)行CaseA的事務(wù)列表
| 時間軸 | 事務(wù)1 | 事務(wù)2 |
|---|---|---|
| T1 | begin | begin |
| T2 | update test13 set code = 15 where uid in (5,20); Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 |
|
| T3 | update test13 set code = 13 where uid = 15; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 |
|
| T4 | commit; | |
| T5 | commit; |
事務(wù)可以正常執(zhí)行毙石。
總結(jié)
當(dāng)我們分析數(shù)據(jù)庫加鎖分析的時候廉沮，一定通過實際操作來分析，不要犯經(jīng)驗注意錯誤
對Update語句的分析可以現(xiàn)轉(zhuǎn)化為對應(yīng)的Select語句查看查詢計劃徐矩，看看SQL的執(zhí)行情況
對于普通的二級非唯一索引滞时，當(dāng)我們修改數(shù)據(jù)的時候要注意重復(fù)的記錄，盡量通過ID來更新
