前幾天在處理線上DB遷移時把原本的 MySQL 5.5.23 改成 Percona 5.5.32 ，結果原本程式每天五點時進行的批次作業出現了 DeadLock 問題，經過交叉測試後發現是 SELECT ... FOR UPDATE 語法中用到的 WHERE 條件沒有配合建立正確的索引造成的 ，而先前 MySQL 5.5.23 沒有問題也完全是運氣好的原因 XD 。
簡化測試如下
CREATE TABLE `test_1` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL,
`c2` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`),
KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
這張測試表中只針對 c1 & c2 各別建了單獨索引.
測試資料內容
測試方式
Transaction-1
START TRANSACTION;
SELECT id, c1, c2 FROM test_1 WHERE c1=1 AND c2=2 FOR UPDATE;
# 刻意不COMMIT 來產生 lock
Transaction-2
DELETE FROM test_1 WHERE c1=1 AND c2=1;
DELETE FROM test_1 WHERE c1=1 AND c2=2;
DELETE FROM test_1 WHERE c1=1 AND c2=3;
DELETE FROM test_1 WHERE c1=2 AND c2=1;
DELETE FROM test_1 WHERE c1=2 AND c2=2;
DELETE FROM test_1 WHERE c1=2 AND c2=3;
DELETE FROM test_1 WHERE c1=3 AND c2=1;
DELETE FROM test_1 WHERE c1=3 AND c2=2;
DELETE FROM test_1 WHERE c1=3 AND c2=3;
同時用 SHOW ENGINE INNODB STATUS \G 觀察 lock 的狀態
MySQL Server 5.5.23
一. 初始測試
Transaction-1START TRANSACTION;
SELECT id, c1, c2 FROM test_1 WHERE c1=1 AND c2=2 FOR UPDATE;
產生 lock 後
Transaction-2
mysql> DELETE FROM test_1 WHERE c1=1 AND c2=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=1 AND c2=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=1 AND c2=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=2 AND c2=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=2 AND c2=2;
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.01 sec)
mysql> DELETE FROM test_1 WHERE c1=3 AND c2=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=3 AND c2=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=3 AND c2=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
除了 c1=2,c2=2 與 c1=2,c2=3 沒被 lock 之外其他7行都被鎖了
二. 配合 WHERE 條件增加 c1,c2 之聚合索引
ALTER TABLE test_1 ADD INDEX idx_c1c2(c1,c2);
Transaction-1
START TRANSACTION;
SELECT id, c1, c2 FROM test_1 WHERE c1=1 AND c2=2 FOR UPDATE;
產生 lock 後
START TRANSACTION;
SELECT id, c1, c2 FROM test_1 WHERE c1=1 AND c2=2 FOR UPDATE;
產生 lock 後
Transaction-2
mysql> DELETE FROM test_1 WHERE c1=1 and c2=1;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=1 and c2=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=1 and c2=3;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=2 and c2=1;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=2 and c2=2;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=2 and c2=3;
Query OK, 1 row affected (0.01 sec)
mysql> DELETE FROM test_1 WHERE c1=3 and c2=1;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=3 and c2=2;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=3 and c2=3;
Query OK, 1 row affected (0.00 sec)
這個 idx_c1c2(c1,c2) INDEX 加上後就只鎖 c1=1,c2=2 那一行了
Percona Server 5.5.32
一. 初始測試
Transaction-1START TRANSACTION;
SELECT id, c1, c2 FROM test_1 WHERE c1=1 AND c2=2 FOR UPDATE;
產生 lock 後
Transaction-2
mysql> DELETE FROM test_1 WHERE c1=1 and c2=1;/div>
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=1 and c2=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=1 and c2=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=2 and c2=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=2 and c2=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=2 and c2=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=3 and c2=1;
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
mysql> DELETE FROM test_1 WHERE c1=3 and c2=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=3 and c2=3;
Query OK, 1 row affected (0.00 sec)
這跟前面的 MySQL 5.5.23 又有些不一樣。
二. 配合 WHERE 條件增加 c1,c2 之聚合索引
ALTER TABLE test_1 ADD INDEX idx_c1c2(c1,c2);
Transaction-1
START TRANSACTION;
SELECT id, c1, c2 FROM test_1 WHERE c1=1 AND c2=2 FOR UPDATE;
產生 lock 後
START TRANSACTION;
SELECT id, c1, c2 FROM test_1 WHERE c1=1 AND c2=2 FOR UPDATE;
產生 lock 後
Transaction-2
mysql> DELETE FROM test_1 WHERE c1=1 and c2=1;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=1 and c2=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> DELETE FROM test_1 WHERE c1=1 and c2=3;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=2 and c2=1;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=2 and c2=2;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=2 and c2=3;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=3 and c2=1;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=3 and c2=2;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM test_1 WHERE c1=3 and c2=3;
Query OK, 1 row affected (0.00 sec)
這個 idx_c1c2(c1,c2) INDEX 加上後就也只鎖 c1=1,c2=2 那一行了
結論：
正確的索引很重要 ...
發表文章
