わりと珍しそうなデッドロックに遭遇しました。
- BEGIN/COMMIT/ROLLBACK などのトランザクション関連のコマンドは使っていない
- 2接続で
SELECT ~ FOR UPDATE
だけでデッドロック - 外部キー制約やトリガは使っていない
- 実行計画によってはデッドロックしないこともある
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 status
の LATEST 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
すると実際にロックされる行が予測できなくなるのでやらない方がいいかもしれない。
Oracle や PostgreSQL だと FOR UPDATE OF table_name
のように INNER JOIN
のある SELECT ~ FOR UPDATE
でどのテーブルをロックするかを指定出来るらしい。