もやぶろ

moyashidaisukeのブログだからもやぶろ。フリーランスのエンジニアのダイスケです。QOLあげて色々楽しくチャレンジして良く生きたい。プログラム関連とかギター関連とか旅行関連とか色々。

InnoDBとREPEATABLE READとSELECT FOR UPDATEと楽観ロックその2

前回の話に排他(行ロック)を絡めてみる。


Versionカラムを使った楽観ロックをしてみます。

■ストーリー
1.セッション1 トランザクション開始
2.セッション2 トランザクション開始
3.セッション1 行情報取得→versionカラムを使って楽観ロックをした更新→commit or rollback
4.セッション2 行情報取得→versionカラムを使って楽観ロックをした更新→commit or rollback

楽観ロックなので、1行も更新できなかった場合はrollbackする想定です。

長いので1,2は省略しちゃいます。

READ COMMITTED

セッション1

mysql> select * from Version;
+----+------+---------+
| id | text | version |
+----+------+---------+
|  1 | init |       1 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> update Version set text = 'updatedFromSession1' , version = version + 1 where id = 1 and version = 1; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

セッション2

mysql> update Version set text = 'updatedFromSession2' where id = 1 and version = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


通ってしまいます。

REPEATABLE READ

セッション1

mysql> select * from Version;
+----+------+---------+
| id | text | version |
+----+------+---------+
|  1 | init |       1 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> update Version set text = 'updatedFromSession1' , version = version + 1 where id = 1 and version = 1; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

セッション2

mysql> select * from Version;
+----+---------------------+---------+
| id | text                | version |
+----+---------------------+---------+
|  1 | updatedFromSession1 |       2 |
+----+---------------------+---------+
1 row in set (0.00 sec)

mysql> update Version set text = 'updatedFromSession2' where id = 1 and version = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


こちらも通ってしまいます。


ここでストーリーを少し変えて、

■ストーリー
1.セッション1 トランザクション開始、tempテーブルselect
2.セッション2 トランザクション開始、tempテーブルselect
3.セッション1 行情報取得→versionカラムを使って楽観ロックをした更新→commit or rollback
4.セッション2 行情報取得→versionカラムを使って楽観ロックをした更新→commit or rollback

tempテーブルのselectを入れただけです。(for updateは無し)


READ COMMITTED

セッション1

mysql> select * from Version;
+----+------+---------+
| id | text | version |
+----+------+---------+
|  1 | init |       1 |
+----+------+---------+
1 row in set (0.01 sec)

mysql> update Version set text = 'updatedFromSession1' , version = version + 1 where id = 1 and version = 1; 
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

セッション2

mysql> select * from Version;
+----+---------------------+---------+
| id | text                | version |
+----+---------------------+---------+
|  1 | updatedFromSession1 |       2 |
+----+---------------------+---------+
1 row in set (0.00 sec)

mysql> update Version set text = 'updatedFromSession2' where id = 1 and version = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

変更前と同じ。


REPEATABLE READ

セッション1

mysql> select * from Version;
+----+------+---------+
| id | text | version |
+----+------+---------+
|  1 | init |       1 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> update Version set text = 'updatedFromSession1' , version = version + 1 where id = 1 and version = 1; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

セッション2

mysql> select * from Version;
+----+------+---------+
| id | text | version |
+----+------+---------+
|  1 | init |       1 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> update Version set text = 'updatedFromSession2' where id = 1 and version = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)


なんと結果が変わります。
REPEATABLE READの場合、最初のselectのタイミングで取得するデータのスナップショットが決まるらしいです。
http://dev.mysql.com/doc/refman/5.1/ja/innodb-consistent-read.html

今回だと、2でtempをselectした時点でセッション2中でselectの結果として返ってくる値が全テーブルで確定するようです。

というわけで、1,2の処理にinsertやらが入っていても、REPEATABLE READなら排他がばっちり決まります。



またストーリーを少し変えてみます。

■ストーリー
1.セッション1 トランザクション開始、tempテーブルselect
2.セッション2 トランザクション開始、tempテーブルselect
3.セッション1 行情報取得(for update)
4.セッション2 行情報取得(for update)※トランザクション1のコミット待ち
5.セッション1 versionカラムを使って楽観ロックをした更新→commit or rollback
6.セッション2 versionカラムを使って楽観ロックをした更新→commit or rollback


楽観ロックがかかってるのに、for updateで悲観ロックをかけたとします。
で、コミットのタイミングを変えました。


READ COMMITTED

セッション1

mysql> select * from Version where id = 1 for update;
+----+------+---------+
| id | text | version |
+----+------+---------+
|  1 | init |       1 |
+----+------+---------+
1 row in set (0.00 sec)

セッション2

mysql> select * from Version where id = 1 for update;

ロック開放待ち。

セッション1

mysql> update Version set text = 'updatedFromSession1' , version = version + 1 where id = 1 and version = 1; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

セッション2

+----+---------------------+---------+
| id | text                | version |
+----+---------------------+---------+
|  1 | updatedFromSession1 |       2 |
+----+---------------------+---------+
1 row in set (8.33 sec)

mysql> update Version set text = 'updatedFromSession2' where id = 1 and version = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


REPEATABLE READ

セッション1

mysql> select * from Version where id = 1 for update;
+----+------+---------+
| id | text | version |
+----+------+---------+
|  1 | init |       1 |
+----+------+---------+
1 row in set (0.00 sec)

セッション2

mysql> select * from Version where id = 1 for update;

ロック開放待ち。

セッション1

mysql> update Version set text = 'updatedFromSession1' , version = version + 1 where id = 1 and version = 1; 
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

セッション2

+----+---------------------+---------+
| id | text                | version |
+----+---------------------+---------+
|  1 | updatedFromSession1 |       2 |
+----+---------------------+---------+
1 row in set (7.38 sec)

mysql> update Version set text = 'updatedFromSession2' where id = 1 and version = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

なんと、通ってしまいました。
REPEATABLE READでトランザクション開始後に1度selectしていても、select for update をかけると他トランザクションの影響を受けてしまうようです。

まとめ

REPEATABLE READの場合、READ COMMITTEDに比べて楽観ロックがシビアに効くこと場合がある。
ただし、select for updateで悲観ロックするとREAD COMMITTEDの場合と同じになる。

既存処理で、楽観ロックだけどシビアに排他が効いてる処理を、
何気なく悲観ロック追加すると挙動が変わる場合があるので注意する。


InnoDBのトランザクションは、ネクストキーロックとかもあって、
ハマるポイントが多いですね。。