SELECT ... FOR UPDATEとロックの挙動

mysql

kamipoさんが補足を書いてくれたので、参照するとよいです。


基礎的だけど、SELECT ... FOR UPDATEをちゃんと理解できてない気がするな、ということで実際にコンソールで打ちながら挙動を確認してみた。

今回確認した環境は、

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.6.12 |
+---------------+--------+
1 row in set (0.00 sec)

今回利用するテーブルはこんな感じ

> CREATE TABLE `select_for_update` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` int(10) unsigned NOT NULL,
  `col2` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> insert into select_for_update (col1) values (1),(2),(3),(4),(5),(6),(7),(8),(9);

主キー指定でロックした場合

client1> begin;
client1> select * from select_for_update where id = 1 for update;

この状態で、別のクライアントから、

client2> select * from select_for_update where id = 1;

とすると、書き込みロックなので、読み取りは何も問題なく行なえる。

まず主キーのときの挙動

client2> select * from select_for_update where id = 1 for update;

とすると、ロック待ちになる。この状態でclient1でcommitすると、client2も実行結果が返ってくる。

client2> update select_for_update set col1 = 1 where id = 1;

とすると、これも更新のクエリなのでロック待ちになり、client1がcommitするまで返ってこない。

ここまでは、まぁ当然の挙動。

client2> select * from select_for_update where id < 3 order by id desc limit 1 for update

はブロックされない。

client2> update select_for_update set col1 = 2 where id < 3 order by id desc limit 1

はブロックされない。

次に、セカンダリインデックスのときの挙動。

client2> update select_for_update set col1 = 2 where col1 < 3 order by id desc limit 1
client2> select * from select_for_update FORCE INDEX (col1) where col1 < 3 order by id desc limit 1 for update

はusing indexだが、using filesortになるため行の読みこみが発生し、ブロックされる。

client2> select * from select_for_update FORCE INDEX (primary) where col1 < 3 order by id desc limit 1 for update

primary keyをINDEXとして使うようにすると、ブロックされないようだ。これは、5.6のtop sort最適化のためっぽい?

client2>  select * from select_for_update force index (col1) where col1 < 3 order by col1 desc limit 1 for update
client2>  select * from select_for_update force index (col1) where col1 < 3 order by col1 desc, id desc limit 1 for update;

はcol1が使われ、using indexのみでブロックされない。

rangeでロックした場合

client1> begin;
client1> select * from select_for_update where id < 2 for update
client2> select * from select_for_update where id = 2 for update

はid=1の行のみがブロックされると期待してしまうが、実際にはid=2の行もロックされているため、ブロックされてしまう。これはInnoDBのネクストキーロック問題と呼ばれているそうな。

client2> select * from select_for_update where id = 3 for update

はブロックされない。

では、主キーではなく、セカンダリインデックスを使うとどうなるか、

client2> select * from select_for_update where col1 = 2 for update

これもブロックされるようだ。

client2>  select * from select_for_update where id < 3 order by id desc limit 1 for update

など、結果がid=2になるようなクエリはブロックされる。

select * from select_for_update where id < 4 order by id desc limit 1 for update

はブロックされない。

他の結果については、id=3になるように調整して打った結果、同様の結果になった。

client1> begin;
client1> select * from select_for_update FORCE INDEX (primary) where col1 < 2 order by id desc limit 1 for update

でロックしてみる。

select * from select_for_update FORCE INDEX (primary) where col1 < 3 order by id desc limit 1 for update
select * from select_for_update FORCE INDEX (primary) where col1 < 4 order by id desc limit 1 for update
select * from select_for_update FORCE INDEX (col1) where col1 < 3 order by id desc limit 1 for update
select * from select_for_update FORCE INDEX (col1) where col1 < 4 order by id desc limit 1 for update

さきほどはうまくいった、force index (primary)が、今度はブロックされてしまう。

client1> begin;
client1> select * from select_for_update FORCE INDEX (col1) where col1 < 2 order by id desc limit 1 for update;

であれば、

client2> select * from select_for_update FORCE INDEX (primary) where col1 < 3 order by id desc limit 1 for update

はブロックされず、

client2>  select * from select_for_update FORCE INDEX (col1) where col1 < 3 order by id desc limit 1 for update

はブロックされる。

いまいち何故こうなるのかはまだ理解できてない。

追記:

とのことですが、まだ詳しくは確認できてないので、あとで別エントリで補足します。