MySQL で実行計画によってデッドロックしたりしなかったりするクエリ

わりと珍しそうなデッドロックに遭遇しました。


MySQL のバージョンとか。

mysql> select @@version, @@tx_isolation, @@autocommit;
/*----------+----------------+--------------+
| @@version | @@tx_isolation | @@autocommit |
+-----------+----------------+--------------+
| 5.5.31    | READ-COMMITTED |            1 |
+-----------+----------------+-------------*/

次の通りにテーブルを作ります。

create table ss
(
  id int not null primary key
);

create table tt
(
  id int not null primary key,
  no int not null,
  ss int,
  unique (no)
);

insert into ss (id) values (1), (2);
insert into tt (id, no) values (1, 1), (2, 2);

コンソールを2つ開いて下記をそれぞれ実行します。test の部分は↑でテーブルを作ったデータベース名で置き換えてください。

$ while :; do echo 'select * from ss inner join tt using (id) where no = 1 for update;'; done | mysql test > /dev/null
$ while :; do echo 'select * from ss inner join tt using (id) where no > 1 for update;'; done | mysql test > /dev/null

しばらくすると片方がデッドロックで止まります。

$ while :; do echo 'select * from ss inner join tt using (id) where no > 1 for update;'; done | mysql test > /dev/null
ERROR 1213 (40001) at line 846: Deadlock found when trying to get lock; try restarting transaction

明示的に BEGIN しない場合はクエリごとに暗黙的に BEGIN → COMMIT されているのと同じなので、 1回のクエリで複数のレコードがロックされるのであればデッドロックする可能性もあります(外部キー制約による INSERT 時の暗黙の共有ロックとかありがち?)。

が、↑の2つのクエリは同じレコードを参照しないはずなのでデッドロックもしないように思うのですが・・・

原因

show engine innodb statusLATEST DETECTED DEADLOCK に次のように出ていました。

*** (1) TRANSACTION:
TRANSACTION 1500F7, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock(s)
MySQL thread id 182, OS thread handle 0x7ffa14228700, query id 1376328 localhost root statistics
select * from ss inner join tt using (id) where no = 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 134 page no 3 n bits 72 index `PRIMARY` of table `test`.`ss` trx id 1500F7 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000014f2f2; asc       ;;
 2: len 7; hex 95000001460110; asc     F  ;;

*** (2) TRANSACTION:
TRANSACTION 1500F6, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 183, OS thread handle 0x7ffa14269700, query id 1376327 localhost root Sending data
select * from ss inner join tt using (id) where no > 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 134 page no 3 n bits 72 index `PRIMARY` of table `test`.`ss` trx id 1500F6 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000014f2f2; asc       ;;
 2: len 7; hex 95000001460110; asc     F  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 135 page no 3 n bits 72 index `PRIMARY` of table `test`.`tt` trx id 1500F6 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000014f2f3; asc       ;;
 2: len 7; hex 96000001470110; asc     G  ;;
 3: len 4; hex 80000001; asc     ;;
 4: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)

これの見かたはよく判っていないのですが、それぞれの explain も一緒に見てみると・・・

mysql> explain select * from ss inner join tt using (id) where no = 1 for update;
/*---+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | tt    | const | PRIMARY,no    | no      | 4       | const |    1 |             |
|  1 | SIMPLE      | ss    | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------*/

mysql> explain select * from ss inner join tt using (id) where no > 1 for update;
/*---+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | ss    | index  | PRIMARY       | PRIMARY | 4       | NULL       |    2 | Using index |
|  1 | SIMPLE      | tt    | eq_ref | PRIMARY,no    | PRIMARY | 4       | test.ss.id |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+------------*/

1つめのクエリは tt 駆動で no のインデックスを検索、2つめのクエリは ss 駆動でインデックススキャンになっています。なので・・・

トランザクション (1) が要求するロック

tt.no = 1
tt.id = 1 [A]
ss.id = 1 [B]

トランザクション (2) が要求するロック

ss.id = 1 [B]
tt.id = 1 [A]
ss.id = 2
tt.id = 2
tt.id = 2
tt.no = 2

よって [A] と [B] でデッドロックします。

トランザクション (2) が ss のインデックススキャンにならないように ss を適当に太らすと・・・

mysql> insert into ss values (3),(4),(5),(6),(7),(8),(9);
mysql> explain select * from ss inner join tt using (id) where no > 1 for update;

/*---+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | tt    | range  | PRIMARY,no    | no      | 4       | NULL       |    1 | Using where |
|  1 | SIMPLE      | ss    | eq_ref | PRIMARY       | PRIMARY | 4       | test.tt.id |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+------------*/

同じクエリでもデッドロックしなくなります。

$ while :; do echo 'select * from ss inner join tt using (id) where no = 1 for update;'; done | mysql test > /dev/null
$ while :; do echo 'select * from ss inner join tt using (id) where no > 1 for update;'; done | mysql test > /dev/null

まとめ

テーブルを結合するようなクエリで FOR UPDATE すると実際にロックされる行が予測できなくなるのでやらない方がいいかもしれない。


OraclePostgreSQL だと FOR UPDATE OF table_name のように INNER JOIN のある SELECT ~ FOR UPDATE でどのテーブルをロックするかを指定出来るらしい。