MySQLのデッドロックについて

この記事は MySQL Casual Advent Calendar 2017 の7日目です。最近、デッドロックを真剣に対応する機会があったのでその時のメモです。

デッドロックとは？

デッドロックとは「複数のトランザクションが互いがロックの開放を待ち、結果として、どの処理も先に進めなくなってしまった状態」を指す。単にロック待ちが長時間になっているケースはデッドロックとは呼ばない。

時間	TRANSACTION(1)	TRANSACTION(2)
1	BEGIN
2		BEGIN
3	UPDATE t1 SET col1 = 'session1' WHERE pk = 1;
4		UPDATE t2 SET col1 = 'session2' WHERE pk = 1;
5	UPDATE t2 SET col1 = 'session1' WHERE pk = 1; ※ TRANSACTION 2 がt2のロックを持ってるので進めない
6		UPDATE t1 SET col1 = 'session2' WHERE pk = 1; ※ TRANSACTION 1 がt1のロックを持ってるので進めない

MySQL (InnoDB) はデッドロック状態になった場合、片方のトランザクションを強制ロールバックし、もう片方のトランザクションに必要なロックを獲得させ、処理を進める。その際、強制ロールバックされたクエリは以下のエラーが発生する。


mysql> UPDATE t1 SET col1 = 'session2' WHERE pk = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

デッドロックの対処方法

1. リトライ

根本的な対応ではないが、良くとられる解決策。タイミング悪く更新が重なったときに起こる程度であれば、リトライで十分。

2. クエリを修正し、ロックを獲得する順番を揃える

上記の例でいうと、片方のトランザクションがt1→t2 という順でロックしているのに対し、もう片方はt2→t1という順でロックしているため、互いにロックを取り合う構造になっている。ロックを取る順番を揃えれば、デッドロックにはならない（後続のクエリは待つ）。

3. ロックを取る範囲を小さくする

MySQL は条件に一致した行ではなく、スキャンした行やインデックスをロックする。例えば、以下のクエリは条件にマッチする行が１つもないが、全部の行をロックしてしまう。

mysql> SELECT * FROM t1;
+----+------+
| pk | col1 |
+----+------+
|  1 | t1-1 |
|  2 | t1-2 |
|  3 | t1-3 |
|  4 | t1-4 |
|  5 | t1-5 |
+----+------+
5 rows in set (0.00 sec)

mysql> BEGIN;

-- mysql> EXPLAIN SELECT * FROM t1 WHERE col1 = 'INVALID' FOR UPDATE;
-- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
-- | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
-- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
-- |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
-- +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+


-- col1 にインデックスがないのでフルスキャン
mysql> SELECT * FROM t1 WHERE col1 = 'INVALID' FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)

適切なインデックスを追加し、スキャンする範囲を小さくする、もしくは、
SELECTしたあとに主キーで対象の行のみを狙って更新する方法が有効。yoku0825さんの MySQLから大量のレコードをちまちま削除するメモはその例。

SHOW ENGINE INNODB STATUS から情報を得る

SHOW ENGINE INNODB STATUS の LATEST DETECTED DEADLOCK にデッドロックの詳細が記録される。
どのような経緯でデッドロックに到ったか確認する手助けになる。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-11-30 17:21:52 0x7fd26c11d700
*** (1) TRANSACTION:
TRANSACTION 1383, ACTIVE 25 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140541732701952, query id 84 localhost root updating
UPDATE t2 SET col1 = 'session1' WHERE pk = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 3 n bits 80 index PRIMARY of table `lockt`.`t2` trx id 1383 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000568; asc      h;;
 2: len 7; hex 46000001530110; asc F   S  ;;
 3: len 8; hex 73657373696f6e32; asc session2;;

*** (2) TRANSACTION:
TRANSACTION 1384, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 140541732968192, query id 85 localhost root updating
UPDATE t1 SET col1 = 'session2' WHERE pk = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 28 page no 3 n bits 72 index PRIMARY of table `lockt`.`t2` trx id 1384 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000568; asc      h;;
 2: len 7; hex 46000001530110; asc F   S  ;;
 3: len 8; hex 73657373696f6e32; asc session2;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `lockt`.`t1` trx id 1384 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000567; asc      g;;
 2: len 7; hex 45000001520110; asc E   R  ;;
 3: len 8; hex 73657373696f6e31; asc session1;;

*** WE ROLL BACK TRANSACTION (2)

1. 取得するタイミングに注意

LATEST DETECTED DEADLOCK とあるとおり、「最後に発生したデッドロック」の情報になる。複数のデッドロックが発生している場合、自分が狙ってる情報が取れるよう、必要に応じて定期的にSHOW ENGINE INNODB STATUSを実行するなどする。

2. 最後のクエリしか記載されない

SHOW ENGINE INNODB STATUSの情報にはトランザクションの最後のクエリしか表示されない。例でいうと
TRANSACTION(1) の UPDATE t1 SET col1 = 'session1' WHERE pk = 1;
TRANSACTION(2) の UPDATE t2 SET col1 = 'session2' WHERE pk = 1;
が表示されてない。

どういうクエリが原因でデッドロックが発生したか特定するには、LATEST DETECTED DEADLOCKに記載されているクエリを手がかりにソースコードなどからトランザクション全体のクエリを把握する必要がある（SHOW ENGINE INNODB STATUSだけでは全体の流れはわからない）。

3. HOLDS THE LOCKS はTRANSACTION(2)しか表示されない

HOLDS THE LOKCS がTRANSACTION(1)に表示されてないがロックは取ってる。 X row lock(s) に注目したほうが良い。

mita2 database life

主にMySQLに関するメモです