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

現場でちょっとはまった話。

InnoDBのトランザクション分離レベル

http://dev.mysql.com/doc/refman/5.1/ja/innodb-transaction-isolation.html

InnoDBはトランザクション分離レベルが選べますが、
デフォルトは「REPEATABLE READ」です。

Oracleは「READ COMMITTED」に近いとの事。（どこが違うんだろう。。）

じゃあこの2つどう違うかというと、

READ COMMITTED

セッション1

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

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

セッション2

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

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

セッション1

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

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

セッション2

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

セッション1

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

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

セッション2

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

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

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

セッション1がコミットした段階で、セッション2にも更新が反映されました。

REPEATABLE READ

セッション1

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

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

セッション2

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

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

セッション1

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

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

セッション2

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

セッション1

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

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

セッション2

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

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

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