【MySQL】InnoDBの select for update のロックの動作を確認する

Tweet

Category MySQL 2015/01/28

問題

select ～ for update したら、単純に行ロックがかかっているだけではなさそうなんだけど？

答え

where句によって、単純な行ロックになったり、ならなかったりするので動きを見てみる。

MySQL5.5 InnoDBのテーブルにて。

準備

こんなテーブルがあるとする。

mysql> create table t1 (
    ->   id int primary key,
    ->   txt text
    -> );

データを追加する。

mysql> insert into t1 (id, txt) values (1, 'aaa');
mysql> insert into t1 (id, txt) values (2, 'bbb');
mysql> insert into t1 (id, txt) values (3, 'ccc');

mysql> select * from t1;
+----+------+
| id | txt  |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
+----+------+

実験

Aさんが select ～ for update で行ロックを取得する。

A> start transaction;
A> select * from t1 where id = 2 for update;

ちょうどそのとき、Bさんも select ～ for update すると？

B> start transaction;
B> select * from t1 where txt = 'ddd' for update;

ここで、Bさんはロックがかかってしまう。

Aさん作業継続、終了。

A> update t1 set txt = 'eee' where id = 2;
A> commit;

ここで、Bさんのロックは解除される。Bさん操作可能になる。

そしてAさんまた t1 に対して操作をしようとすると

A> insert into t1 (id, txt) values (4, 'fff');

と、今度はここで、Aさんロックがかかってしまう。

Bさんが commit すると、Aさんの insert が実行できるようになる。

結果

主キー、ユニークキーで select ～ for update しないと、テーブル全体でロックになったり、ある程度の範囲でロックがかかったりする。

上の実験では、Bさんがプライマリキーでないカラムで条件を指定したのが問題。

InnoDBはそういう動きをするらしい。

http://walf443.hatenablog.com/entry/20130829/1377788440

http://blog.kamipo.net/entry/2013/12/03/235900