MySQLのトランザクションとロック

毎回同じこと調べてる気がするので、メモ。

MySQL5.0.77、InnoDB、デフォルトのAUTOCOMMIT=1

tableテーブル
id, paramカラム


■関係ない行

・A
START TRANSACTION;
INSERT INTO `table` VALUES('1', '1');

・B
SELECT * FROM `table`
// 正常終了（取得結果0件）

・A
COMMIT;

・B
SELECT * FROM `table`
// 正常終了（取得結果1件）


■更新中の行

・A
START TRANSACTION;
UPDATE `table` SET `param` = 'hoge' WHERE `id` = 1;

・B
SELECT * FROM `table`
// 正常終了（取得結果1件、更新前の値）

・A
COMMIT;

・B
SELECT * FROM `table`
// 正常終了（取得結果1件、更新後の値）


■両方更新

・A
START TRANSACTION;
UPDATE `table` SET `param` = 'A' WHERE `id` = 1;
// 正常終了（更新結果1件）

・B
UPDATE `table` SET `param` = 'B' WHERE `id` = 1;
// 待ち状態

・A
COMMIT;
// BプロセスのUPDATEが終了

・B
SELECT * FROM `table`
// 正常終了（取得結果、param='B'）


■ロック（READロック）

・A
LOCK TABLES `table` READ;
// 更新処理はエラー

・B
SELECT * FROM `table`
// 正常終了（取得結果1件）
// 更新系クエリは待ち

・A
// 更新処理はエラー
UNLOCK TABLES;
// BプロセスのUPDATEが終了

■ロック（WRITEロック）

・A
LOCK TABLES `table` WRITE;
UPDATE `table` SET `param` = 'A' WHERE `id` = 1;
// 正常終了（更新結果1件）

・B
SELECT * FROM `table`
// 待ち
// 更新系クエリも待ち

・A
UNLOCK TABLES;
// BプロセスのSELECTが終了
// BプロセスのUPDATEが終了


■ロックとトランザクション（ロックしてからトランザクション開始）

・A
LOCK TABLES `table` WRITE;

・B
SELECT * FROM `table`
// 待ち

・A
START TRANSACTION;
// START TRANSACTIONする前のアクティブなトランザクションは閉じられる。
// 1回目のLOCK TABLESが開放されて、BプロセスのSELECTが終了


■ロックとトランザクション（トランザクション発行してからREADロック）

・A
START TRANSACTION;
UPDATE `table` SET `param` = 'A' WHERE `id` = 1;

・B
SELECT * FROM `table`
// 正常終了（取得結果1件、更新前）

・A
LOCK TABLES `table` READ;
// 開始していたトランザクションが閉じられる

・B
SELECT * FROM `table`
// 正常終了（取得結果1件、更新後）

■ロックとaoutocommit

・AUTOCOMMITを無効にしていてもUNLOCK時にはコミットされる
SET AUTOCOMMIT=0;
LOCK TABLES `table` WRITE;
UPDATE `table` SET `param` = 'hoge5' WHERE `id` = 1;

UNLOCK TABLES;
// hoge5

・ROLLBACKすればOK
SET AUTOCOMMIT=0;
LOCK TABLES `table` WRITE;
UPDATE `table` SET `param` = 'hoge6' WHERE `id` = 1;

ROLLBACK;
UNLOCK TABLES;
// hoge5

・でもAUTOCOMMITが有効なら意味無い
SET AUTOCOMMIT=1;
LOCK TABLES `table` WRITE;
UPDATE `table` SET `param` = 'hoge6' WHERE `id` = 1;

ROLLBACK;
UNLOCK TABLES;
// hoge6

■まとめ
・トランザクション（ロック）の発行はそれまでのロック（トランザクション）が閉じられるので1シーケンスの中にトランザクションとロックの発行を混在させないこと。
・トランザクションが終わるまで他のプロセスに値がreadされるとまずい場合、行ロックやテーブルロック。
・ロック時にはAUTOCOMMITを無効にして明示的にCOMMITすること。
・readされるとまずい場合は行ロックでは辛いので現実的にはテーブルロック