2013-07-27 MySQL InnoDB SELECT ... FOR UPDATE 的 Lock 測試

前幾天在處理線上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-1 

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;

  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)

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;

  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 後

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-1 

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;/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)

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)

除了 c1=3,c2=1 與 c1=3,c2=3 沒被 lock 之外其他7行都被鎖了，

這跟前面的 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 後

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 那一行了

結論：

正確的索引很重要 ...