SELECT ... LOCK IN SHARE MODEとSELECT ... FOR UPDATEの違い

MySQLドキュメントの14.2.5 ロック読み取り (SELECT ... FOR UPDATE および SELECT ... LOCK IN SHARE MODE)の記述は、以下のように読める。

• SELECT ... LOCK IN SHARE MODEは共有ロックを取得する
• SELECT ... FOR UPDATEは排他ロックを取得する

今回はこれをinnodb_lock_monitorを使って確認する。
innodb_lock_monitorの読み方は過去に調べた通り。

（※追記）検証環境はMySQLのデフォルトであるREPEATABLE READ。

準備

CREATE DATABASE TIL;
USE TIL;
CREATE TABLE test (
  id INTEGER NOT NULL DEFAULT 0,
  a INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);
INSERT INTO test (id, a) VALUES (1, 10);

/* enable innodb_lock_monitor */
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

※以下の検証中、各トランザクションはinnodb_lock_monitorデータ取得後にROLLBACKしている。

SELECT ... LOCK IN SHARE MODE

mysql> START TRANSACTION;
mysql> SELECT * FROM test WHERE id = 1 LOCK IN SHARE MODE;

TRANSACTIONのみ抜粋

---TRANSACTION 31830058, ACTIVE 4 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 1, OS thread handle 0xde4, query id 19 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31830058 lock mode IS
RECORD LOCKS space id 42754 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31830058 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5b021; asc      !;;
 2: len 7; hex 98000001580110; asc     X  ;;
 3: len 4; hex 8000000a; asc     ;;

lock mode S locks rec but not gap なので、共有ロックを取得している。

SELECT ... FOR UPDATE

mysql> START TRANSACTION;
mysql> SELECT * FROM test WHERE id = 1 FOR UPDATE;

TRANSACTIONのみ抜粋

---TRANSACTION 31830059, ACTIVE 12 sec
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 1, OS thread handle 0xde4, query id 22 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31830059 lock mode IX
RECORD LOCKS space id 42754 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31830059 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5b021; asc      !;;
 2: len 7; hex 98000001580110; asc     X  ;;
 3: len 4; hex 8000000a; asc     ;;

lock_mode X locks rec but not gapなので、排他ロックを取得している。

非ロック読み取り

mysql> START TRANSACTION;
mysql> SELECT * FROM test WHERE id = 1;

TRANSACTIONのみ抜粋

---TRANSACTION 31830057, ACTIVE 20 sec
MySQL thread id 1, OS thread handle 0xde4, query id 16 localhost ::1 root cleaning up
Trx read view will not see trx with id >= 31830058, sees < 31830058

ロックは取得していない。

書き込み

mysql> START TRANSACTION;
mysql> UPDATE test SET a = 110 WHERE id = 1;

TRANSACTIONのみ抜粋

---TRANSACTION 31830060, ACTIVE 8 sec
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0xde4, query id 25 localhost ::1 root cleaning up
TABLE LOCK table `til`.`test` trx id 31830060 lock mode IX
RECORD LOCKS space id 42754 page no 3 n bits 72 index `PRIMARY` of table `til`.`test` trx id 31830060 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001e5b02c; asc      ,;;
 2: len 7; hex 220000029208d1; asc "      ;;
 3: len 4; hex 8000006e; asc    n;;

lock_mode X locks rec but not gapなので、排他ロックを取得している。

まとめ

（※追記）REPEATABLE READ でのまとめ

操作		取得するロック
非ロック読み取り（Non-Locking Read）		なし
ロック読み取り（Locking Read）	SELECT ... LOCK IN SHARE MODE	共有ロック（S）
	SELECT ... FOR UPDATE	排他ロック（X）
書き込み		排他ロック（X）

感想

innodb_lock_monitorを使うと動作がしっかり確認できてよい。