Open in app

Sign in

Write

Sign in

InnoDB のロック機構について

Hajime Terasawa
67 min readAug 7, 2019

--

n 番煎じネタですが、一度やってみたかったので手元で検証してみました。

特段新しいものは無いと思いますが、インテンションロックと各種ロックタイプを網羅的に挙動検証する記事は自分の観測範囲にはなかったはず？

図を用意できたら読みやすかったと思うのですが、怠慢したので innodb_status_output_locks の出力でご容赦ください

データセット

今回使うデータセットです

mysql> show global variables like ‘version’;
+ — — — — — — — -+ — — — — +
| Variable_name | Value |
+ — — — — — — — -+ — — — — +
| version | 5.7.26 |
+ — — — — — — — -+ — — — — +
1 row in set (0.00 sec)mysql> show create table tests;
+ — — — -+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — +
| Table | Create Table |
+ — — — -+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — +
| tests | CREATE TABLE `tests` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `value1` int(11) DEFAULT NULL,
 `value2` int(11) DEFAULT NULL,
 `value3` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `value1` (`value1`),
 KEY `value2` (`value2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
+ — — — -+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — +mysql> select * from tests;
+ — — + — — — — + — — — — + — — — — +
| id | value1 | value2 | value3 |
+ — — + — — — — + — — — — + — — — — +
| 10 | 10 | 10 | 10 |
| 20 | 20 | 20 | 20 |
| 30 | 30 | 30 | 30 |
+ — — + — — — — + — — — — + — — — — +
3 rows in set (0.00 sec)mysql> show global variables like ‘tx_isolation’;
+ — — — — — — — -+ — — — — — — — — -+
| Variable_name | Value |
+ — — — — — — — -+ — — — — — — — — -+
| tx_isolation | REPEATABLE-READ |
+ — — — — — — — -+ — — — — — — — — -+
1 row in set (0.02 sec)mysql> show session variables like ‘tx_isolation’;
+ — — — — — — — -+ — — — — — — — — -+
| Variable_name | Value |
+ — — — — — — — -+ — — — — — — — — -+
| tx_isolation | REPEATABLE-READ |
+ — — — — — — — -+ — — — — — — — — -+
1 row in set (0.01 sec)mysql> show global variables like ‘innodb_status_output_locks’;
+ — — — — — — — — — — — — — — + — — — -+
| Variable_name | Value |
+ — — — — — — — — — — — — — — + — — — -+
| innodb_status_output_locks | ON |
+ — — — — — — — — — — — — — — + — — — -+
1 row in set (0.01 sec)

排他・共有ロックについて

ロックタイプ互換性マトリクス

innodb では下記4種類にロックの種類が分類されている。

  • 共有ロック(S)
    ロックを保持するトランザクションによる行の読み取りが許可される
  • 排他ロック(X)
    ロックを保持するトランザクションによる行の更新または削除が許可される
  • インテンション共有ロック(IS)
    トランザクション T は意図的に、テーブル t 内の各行に S ロックを設定する
  • インテンション排他(IX)
    トランザクション T は意図的に、テーブル t 内の各行に X ロックを設定する

これらのロックはそれぞれ下記のように相互作用する。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-lock-modes.html

インテンションロックとはトランザクションがそのテーブル内の行で必要となるロックのタイプ (共有または排他) を示す InnoDB のテーブルロックである。

競合する場合にはロックが解放されるのを待ち、下記のシナリオをなぞる

  • innodb_lock_wait_timeout 内に TX1 のロックが解放され、TX2 がロックを取得
  • innodb_lock_wait_timeout 内に TX1 のロックが解放されないため TX2 が lock wait timeout
  • TX1 が TX2 の取得しているロックを取得しようとし、 TX2 がデッドロックエラー

排他・共有ロックの検証

X の場合
先んじて tests に IX Table lock が掛かり、その後 X Record lock が掛かっているのがわかる。

mysql> begin;
mysql> select * from tests where id=10 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712309, ACTIVE 4 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1374 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712309 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712309 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f0110; asc O ;; 
 3: len 4; hex 8000000a; asc ;; 
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;;

S の場合
先んじて tests に IS Table lock が掛かり、その後 S Record lock が掛かっているのがわかる。

基本的に innodb_status_output_locks で表示されるのは X でロックを取得した・しようとしている場合に限るようで、S を取得してるかを表示するために X を取得している。

mysql> begin;
mysql> select * from tests where id=10 lock in share mode;
mysql> select * from tests where id=10 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712310, ACTIVE 7 sec
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1379 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712310 lock mode IS
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712310 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f0110; asc O ;;
 3: len 4; hex 8000000a; asc ;;
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;;
 
TABLE LOCK table `testdb`.`tests` trx id 2712310 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712310 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f0110; asc O ;; 
 3: len 4; hex 8000000a; asc ;; 
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;;

競合・互換の確認（レコードロック編）

「S-*」 を実際に起こして競合するか検証。innodb_status_output_locks に出力するために select … for update を最後に実行する。

以下 TX1-TX2 で同時にロックが取れたかどうかをまとめた（ついでに確認できたテーブルロックも含む）

  • IS-IS：互換
  • IS-IX：互換
  • S-S：互換
  • S-X：競合
  • IX-IX：互換
TX1> begin;
TX1> select * from tests where id=10 lock in share mode;TX2> begin;
TX2> select * from tests where id=10 lock in share mode;TX1> select * from tests where id=10 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionTX2> select * from tests where id=10 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> show engine innodb status\G
 — -TRANSACTION 2712312, ACTIVE 164 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 44, OS thread handle 140479125415680, query id 1406 172.20.0.1 root
TABLE LOCK table `testdb`.`tests` trx id 2712312 lock mode IS
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712312 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f0110; asc O ;;
 3: len 4; hex 8000000a; asc ;;
 4: len 4; hex 8000000a; asc ;; 
 5: len 4; hex 8000000a; asc ;; 
 
TABLE LOCK table `testdb`.`tests` trx id 2712312 lock mode IX
 — -TRANSACTION 2712311, ACTIVE 172 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1407 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712311 lock mode IS
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712311 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f0110; asc O ;; 
 3: len 4; hex 8000000a; asc ;; 
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;; 
 
TABLE LOCK table `testdb`.`tests` trx id 2712311 lock mode IX

「X-*」 の検証はシンプル。

  • X-X：競合
TX1> begin;
TX1> select * from tests where id=10 for update;TX2> begin;
TX2> select * from tests where id=10 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712314, ACTIVE 7 sec starting index read
mysql tables in use 1, locked 1 
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 44, OS thread handle 140479125415680, query id 1413 172.20.0.1 root statistics
select * from tests where id=10 for update 
 — — — — TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712314 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f0110; asc O ;;
 3: len 4; hex 8000000a; asc ;;
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;;
 
 — — — — — — — — — 
TABLE LOCK table `testdb`.`tests` trx id 2712314 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712314 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;;
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f0110; asc O ;;
 3: len 4; hex 8000000a; asc ;; 
 4: len 4; hex 8000000a; asc ;; 
 5: len 4; hex 8000000a; asc ;;
 
 — -TRANSACTION 2712313, ACTIVE 16 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1414 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712313 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712313 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f0110; asc O ;; 
 3: len 4; hex 8000000a; asc ;; 
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;;
  • IX-IS；互換
  • X-S：競合
    innodb_status_output_locks で表示できないため show engine innodb status はなし。
TX1> begin;
TX1> select * from tests where id=10 for update;TX2> begin;
TX2> select * from tests where id=10 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionTX2> select * from tests where id=10 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> show engine innodb status\G
 — -TRANSACTION 2712316, ACTIVE 109 sec
2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 44, OS thread handle 140479125415680, query id 1422 172.20.0.1 root starting
show engine innodb status 
TABLE LOCK table `testdb`.`tests` trx id 2712316 lock mode IS 
TABLE LOCK table `testdb`.`tests` trx id 2712316 lock mode IX
 — -TRANSACTION 2712315, ACTIVE 114 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1419 172.20.0.1 root
TABLE LOCK table `testdb`.`tests` trx id 2712315 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712315 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f0110; asc O ;; 
 3: len 4; hex 8000000a; asc ;; 
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;;

競合・互換の確認（テーブルロック編）

  • X-IX：競合
TX1> lock tables tests write;
TX2> select * from tests where id=10 for update;
 — table metadata lock
  • X-IS：競合
TX1> lock tables tests write;
TX2> select * from tests where id=10 lock in share mode;
 — table metadata lock
  • S-IS：互換
  • S-IX：競合
TX1> lock tables tests read;
TX2> select * from tests where id=10 lock in share mode;
TX2> select * from tests where id=10 for update;
 — table metadata lock
  • IS-S：互換
  • IS-X：競合
TX1> select * from tests where id=10 lock in share mode;
TX2> lock tables tests read;
 — table metadata lock
  • IX-S：競合
  • IX-X：競合
TX1> select * from tests where id=10 for update;
TX2> lock tables tests read;
 — table metadata lockTX2> lock tables tests write;
 — table metadata lock

排他・共有ロックのまとめ

ロックタイプ互換性マトリクス通りの挙動が確認できた。
レコードロック時とテーブルロック時では process の state が異なる（当たり前だが）

mysql> show processlist;
+ — — + — — — + — — — — — — — — — + — — — — + — — — — -+ — — — + — — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — -+ 
| Id | User | Host | db | Command | Time | State | Info |
+ — — + — — — + — — — — — — — — — + — — — — + — — — — -+ — — — + — — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — -+
| 11 | root | 172.20.0.1:36240 | testdb | Query | 7 | Waiting for table metadata lock | lock tables tests write |
| 12 | root | 172.20.0.1:39716 | testdb | Query | 0 | starting | show processlist |
| 13 | root | 172.20.0.1:50006 | testdb | Sleep | 417 | | NULL |
+ — — + — — — + — — — — — — — — — + — — — — + — — — — -+ — — — + — — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — -+
3 rows in set (0.00 sec)mysql> show processlist;
+ — — + — — — + — — — — — — — — — + — — — — + — — — — -+ — — — + — — — — — — + — — — — — — — — — — — — — — — — — — — — — -+
| Id | User | Host | db | Command | Time | State | Info |
+ — — + — — — + — — — — — — — — — + — — — — + — — — — -+ — — — + — — — — — — + — — — — — — — — — — — — — — — — — — — — — -+
| 11 | root | 172.20.0.1:36240 | testdb | Query | 3 | statistics | select * from tests where id=10 for update |
| 12 | root | 172.20.0.1:39716 | testdb | Query | 0 | starting | show processlist |
| 13 | root | 172.20.0.1:50006 | testdb | Sleep | 628 | | NULL |
+ — — + — — — + — — — — — — — — — + — — — — + — — — — -+ — — — + — — — — — — + — — — — — — — — — — — — — — — — — — — — — -+
3 rows in set (0.00 sec)

脇道にそれるが、X でのテーブルロックが掛かっている場合、別セッションからの同テーブルへのクエリは全てテーブルロック（メタテーブルロック）によってブロックされる。現実世界では long transaction（IX-X）と online DDL（Xを取る）の組み合わせで意図せず発生する事が多い。
https://dev.mysql.com/doc/refman/5.6/ja/metadata-locking.html

その他ロックタイプについて

データセット

再掲するがデータセットは以下である

mysql> show create table tests;
+ — — — -+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — +
| Table | Create Table |
+ — — — -+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — +
| tests | CREATE TABLE `tests` (
 `id` int(11) NOT NULL,
 `value1` int(11) DEFAULT NULL,
 `value2` int(11) DEFAULT NULL,
 `value3` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `value1` (`value1`),
 KEY `value2` (`value2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+ — — — -+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — +mysql> select * from tests;
+ — — + — — — — + — — — — + — — — — +
| id | value1 | value2 | value3 |
+ — — + — — — — + — — — — + — — — — +
| 10 | 10 | 10 | 10 |
| 20 | 20 | 20 | 20 |
| 30 | 30 | 30 | 30 |
+ — — + — — — — + — — — — + — — — — +
3 rows in set (0.00 sec)

ロックタイプとその表示

innodb ロックモニターにおいて各ロックがどのように表示されるかをまとめておく。

  • レコードロック：lock_mode X locks rec but not gap
  • ギャップロック：lock_mode X locks gap before rec
  • ネクストキーロック：lock_mode X
  • インサートインテンションロック：lock_mode X locks gap before rec insert intention waiting

これらのロックがどのような条件で発生するか実際に見ていく。

レコードロック

主に等値検索によって掛けられるロック
インデックスタイプによる挙動の違い以下のよう。

primary key
primary key を検索して、そのままその1レコードをロック

mysql> begin;
mysql> select * from tests where id=20 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712318, ACTIVE 2 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1430 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712318 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712318 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f011e; asc O ;; 
 3: len 4; hex 80000014; asc ;; 
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;;

unique index
unique index を検索して primary key, unique indexの2レコードをロック

mysql> begin;
mysql> select * from tests where value1=20 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712319, ACTIVE 4 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1434 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712319 lock mode IX
RECORD LOCKS space id 12992 page no 4 n bits 72 index value1 of table `testdb`.`tests` trx id 2712319 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc ;;
 1: len 4; hex 80000014; asc ;;
 
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712319 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f011e; asc O ;; 
 3: len 4; hex 80000014; asc ;; 
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;;

non-unique index
index scan して 2つの index, primary key の3レコードをロック
20,30 とインデックスを走査し、そこで通過したレコードはロックされる
30 まで走査するのは non-unique index であるため、等値でないことを確かめるために次のインデックスまで読みに行くんだと思われる。

mysql> begin;
mysql> select * from tests where value2=20 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712320, ACTIVE 5 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1439 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712320 lock mode IX
RECORD LOCKS space id 12992 page no 5 n bits 72 index value2 of table `testdb`.`tests` trx id 2712320 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc ;; 
 1: len 4; hex 80000014; asc ;;
 
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712320 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;;
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f011e; asc O ;; 
 3: len 4; hex 80000014; asc ;; 
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;; 
 
RECORD LOCKS space id 12992 page no 5 n bits 72 index value2 of table `testdb`.`tests` trx id 2712320 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 0: len 4; hex 8000001e; asc ;;
 1: len 4; hex 8000001e; asc ;;

non-indexed column
テーブルスキャンして、スキャンしたレコード全てをロックしているように見える。テーブルスキャンは primary key のインデックススキャンに等しい

mysql> begin;
mysql> select * from tests where value3=20 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712321, ACTIVE 6 sec 
2 lock struct(s), heap size 1136, 4 row lock(s) 
MySQL thread id 43, OS thread handle 140479124064000, query id 1443 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712321 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712321 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f0110; asc O ;;
 3: len 4; hex 8000000a; asc ;;
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;; 
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;;
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f011e; asc O ;;
 3: len 4; hex 80000014; asc ;;
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;; 
 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000001e; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f012c; asc O ,;; 
 3: len 4; hex 8000001e; asc ;; 
 4: len 4; hex 8000001e; asc ;;
 5: len 4; hex 8000001e; asc ;;

書き込みしてみると、全レコードにロックが掛かっていることが分かる。

TX1> begin;
TX1> select * from tests where value3=20 for update;TX2> begin;
TX2> update tests set value2=100 where id=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction— 違う行に対してもロックが掛かる
TX2> update tests set value2=100 where id=30;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ギャップロック

等値検索がを空振ることで発生する。
idx1:10, idx2:20 のときに idx:15で空振ると idx:11~19 までがロックされる。

primary key
空振った値の属するインデックスギャップがロックされる（今回だとprimary keyの11~19）

mysql> begin;
mysql> select * from tests where id=15 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712323, ACTIVE 5 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1451 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712323 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712323 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f011e; asc O ;; 
 3: len 4; hex 80000014; asc ;; 
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;;

unique index
unique index の11~19がロックされる

mysql> begin;
mysql> select * from tests where value1=15 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712325, ACTIVE 4 sec 
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1461 172.20.0.1 root starting
show engine innodb status 
TABLE LOCK table `testdb`.`tests` trx id 2712325 lock mode IX
RECORD LOCKS space id 12992 page no 4 n bits 72 index value1 of table `testdb`.`tests` trx id 2712325 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 0: len 4; hex 80000014; asc ;;
 1: len 4; hex 80000014; asc ;;

non-unique index
こちらも non-unique index の11–19にギャップロックが掛かる

mysql> begin;
mysql> select * from tests where value2=15 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712326, ACTIVE 4 sec 
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 43, OS thread handle 140479124064000, query id 1465 172.20.0.1 root starting
show engine innodb status 
TABLE LOCK table `testdb`.`tests` trx id 2712326 lock mode IX
RECORD LOCKS space id 12992 page no 5 n bits 72 index value2 of table `testdb`.`tests` trx id 2712326 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 0: len 4; hex 80000014; asc ;;
 1: len 4; hex 80000014; asc ;;

non-indexed column
等値検索と同様にテーブルスキャンによる primary key の全レコードにレコードロックが掛かるためギャップロックは発生しない

mysql> begin;
mysql> select * from tests where value3=15 for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712327, ACTIVE 5 sec 
2 lock struct(s), heap size 1136, 4 row lock(s) 
MySQL thread id 43, OS thread handle 140479124064000, query id 1469 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712327 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712327 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f0110; asc O ;;
 3: len 4; hex 8000000a; asc ;;
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;; 
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;;
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f011e; asc O ;;
 3: len 4; hex 80000014; asc ;;
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;; 
 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000001e; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f012c; asc O ,;; 
 3: len 4; hex 8000001e; asc ;; 
 4: len 4; hex 8000001e; asc ;;
 5: len 4; hex 8000001e; asc ;;

ネクストキーロック

ギャップロック+レコードロック。
先程のインデックスギャップを用いると、ギャップ（11~19）の右隣のレコード（20）を含んだ範囲（11~20）にロックが掛かる
これは主に範囲検索が空振ったときに発生する。

primary key
ネクストキーロックが掛かる
desc 的にはレコードロックが掛かってるように見える。

TX1> begin;
TX1> select * from tests where id between 13 and 17 for update;TX1> show engine innodb status\G
 — -TRANSACTION 2712850, ACTIVE 4 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 7, OS thread handle 140655283296000, query id 56 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712850 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712850 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f011e; asc O ;; 
 3: len 4; hex 80000014; asc ;; 
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;;

ネクストキーがブロックされる

TX2> begin;
TX2> update tests set value3=200 where id=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionTX2> update tests set value3=200 where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

unique index
ネクストキーロックが掛かる

TX1> begin;
TX1> select * from tests where value1 between 13 and 17 for update;TX1> show engine innodb status\G
 — -TRANSACTION 2712852, ACTIVE 4 sec 
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 7, OS thread handle 140655283296000, query id 64 172.20.0.1 root starting
show engine innodb status 
TABLE LOCK table `testdb`.`tests` trx id 2712852 lock mode IX
RECORD LOCKS space id 12992 page no 4 n bits 72 index value1 of table `testdb`.`tests` trx id 2712852 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 0: len 4; hex 80000014; asc ;;
 1: len 4; hex 80000014; asc ;;

ネクストキーがブロックされる

TX2> begin;
TX2> update tests set value3=200 where value1=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionTX2> update tests set value3=200 where value1=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

non-unique index
ネクストキーロックが掛かる

TX1> begin;
TX1> select * from tests where value2 between 13 and 17 for update;TX1> show engine innodb status\G
 — -TRANSACTION 2712855, ACTIVE 6 sec 
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 7, OS thread handle 140655283296000, query id 75 172.20.0.1 root starting
show engine innodb status 
TABLE LOCK table `testdb`.`tests` trx id 2712855 lock mode IX
RECORD LOCKS space id 12992 page no 5 n bits 72 index value2 of table `testdb`.`tests` trx id 2712855 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 0: len 4; hex 80000014; asc ;;
 1: len 4; hex 80000014; asc ;;

ネクストレコード（20）がブロックされる

TX2> begin;
TX2> update tests set value3=200 where value2=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionTX2> update tests set value3=200 where value2=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

non-indexed column
ネクストキーロックはかからず全レコードにロックが掛かる

TX1> begin;
TX1> select * from tests where value3 between 13 and 17 for update;TX1> show engine innodb status\G
 — -TRANSACTION 2712856, ACTIVE 5 sec 
2 lock struct(s), heap size 1136, 4 row lock(s) 
MySQL thread id 7, OS thread handle 140655283296000, query id 79 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712856 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712856 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f0110; asc O ;;
 3: len 4; hex 8000000a; asc ;;
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;; 
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;;
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f011e; asc O ;;
 3: len 4; hex 80000014; asc ;;
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;; 
 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000001e; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f012c; asc O ,;; 
 3: len 4; hex 8000001e; asc ;; 
 4: len 4; hex 8000001e; asc ;;
 5: len 4; hex 8000001e; asc ;;

ネクストキー以外のレコードもブロックされている

TX2> begin;
TX2> update tests set value3=200 where value3=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionTX2> update tests set value3=200 where value3=10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

インサートインテンションロック

repeatble-read による insert はファントムレコードを避けるため IX ロックを伴う
そのため insert 同士が複数のトランザクションから行われても互換だが X が取られている状態で別トランザクションから insert を実行すると競合する

TX1> begin;
TX1> insert into tests values(15, 15, 15, 15);TX1> show engine innodb status\G
 — -TRANSACTION 2712863, ACTIVE 16 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 140655283296000, query id 101 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712863 lock mode IX

X が先に取られていると競合する。

TX1> begin;
TX1> select * from tests where id=15 for update;TX2> begin;
TX2> insert into tests values(17, 17, 17, 17);
-- ブロックされるTX1> show engine innodb status\G
 — -TRANSACTION 2712873, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1 
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 8, OS thread handle 140655283025664, query id 123 172.20.0.1 root update
insert into tests values(17, 17, 17, 17) 
 — — — — TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712873 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f011e; asc O ;;
 3: len 4; hex 80000014; asc ;;
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;;
 
 — — — — — — — — — 
TABLE LOCK table `testdb`.`tests` trx id 2712873 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712873 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;;
 1: len 6; hex 0000002962f0; asc )b ;;
 2: len 7; hex d60000124f011e; asc O ;;
 3: len 4; hex 80000014; asc ;; 
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;;
 
 — -TRANSACTION 2712872, ACTIVE 21 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 7, OS thread handle 140655283296000, query id 124 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712872 lock mode IX
RECORD LOCKS space id 12992 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712872 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;; 
 1: len 6; hex 0000002962f0; asc )b ;; 
 2: len 7; hex d60000124f011e; asc O ;; 
 3: len 4; hex 80000014; asc ;; 
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;;

オプティマイザによるテーブルスキャンへのフォールバック

基本的に in 句によるフィルターは複数の等値検索にフォールバックされ、ヒットすればレコードロック、空振ればギャップロックになるが、インデックスのカーディナリティが低い等で選択感度が低い場合はオプティマイザがテーブルスキャンを選択することもある。
その場合 in 句によるフィルターが全レコードをレコードロックする。

通常の等値検索

mysql> begin;
mysql> select * from tests where value1 in (30) for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712936, ACTIVE 4 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 140655283296000, query id 209 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712936 lock mode IX
RECORD LOCKS space id 12994 page no 4 n bits 72 index value1 of table `testdb`.`tests` trx id 2712936 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000001e; asc ;;
 1: len 4; hex 8000001e; asc ;;
 
RECORD LOCKS space id 12994 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712936 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000001e; asc ;; 
 1: len 6; hex 00000029654a; asc )eJ;; 
 2: len 7; hex ba0000011f012c; asc ,;; 
 3: len 4; hex 8000001e; asc ;; 
 4: len 4; hex 8000001e; asc ;;
 5: len 4; hex 8000001e; asc ;;

テーブルスキャンへのフォールバック

mysql> begin;
mysql> select * from tests where value1 in (10, 30) for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712938, ACTIVE 2 sec 
2 lock struct(s), heap size 1136, 4 row lock(s) 
MySQL thread id 7, OS thread handle 140655283296000, query id 217 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712938 lock mode IX
RECORD LOCKS space id 12994 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712938 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;; 
 1: len 6; hex 00000029654a; asc )eJ;;
 2: len 7; hex ba0000011f0110; asc ;;
 3: len 4; hex 8000000a; asc ;;
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;; 
 
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;;
 1: len 6; hex 00000029654a; asc )eJ;;
 2: len 7; hex ba0000011f011e; asc ;;
 3: len 4; hex 80000014; asc ;;
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;; 
 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000001e; asc ;; 
 1: len 6; hex 00000029654a; asc )eJ;; 
 2: len 7; hex ba0000011f012c; asc ,;; 
 3: len 4; hex 8000001e; asc ;; 
 4: len 4; hex 8000001e; asc ;;
 5: len 4; hex 8000001e; asc ;;

当然だが、これは non-primary key に限った挙動であり、primary key による絞り込みは primary key 全件スキャンよりも高速であるので等値検索が走る。
2件のレコードロックが発生しているのがわかる。

mysql> begin;
mysql> select * from tests where id in (10, 30) for update;mysql> show engine innodb status\G
 — -TRANSACTION 2712940, ACTIVE 4 sec 
2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 140655283296000, query id 225 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712940 lock mode IX
RECORD LOCKS space id 12994 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712940 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000000a; asc ;;
 1: len 6; hex 00000029654a; asc )eJ;;
 2: len 7; hex ba0000011f0110; asc ;;
 3: len 4; hex 8000000a; asc ;;
 4: len 4; hex 8000000a; asc ;;
 5: len 4; hex 8000000a; asc ;; 
 
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000001e; asc ;; 
 1: len 6; hex 00000029654a; asc )eJ;; 
 2: len 7; hex ba0000011f012c; asc ,;; 
 3: len 4; hex 8000001e; asc ;; 
 4: len 4; hex 8000001e; asc ;;
 5: len 4; hex 8000001e; asc ;;

Delete/Update による排他ロック

ここまで基本的に select … for update によるロック挙動を確認してきたが、Delete/Update では挙動が微妙に異なる。
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

select … for update ではスキャンしたレコードに対するレコードロックが掛かるのに対し、Delete/Update ではネクストキーロックが掛かる。ただし等値検索は例外で、これは普通にレコードロックが掛かる

ドキュメント的には unique index の等値検索に限ってレコードロックが掛かるとあるが、non-unique index に対する等値検索でもレコードロックが掛かった。

unique-index
通常のレコードロック

TX1> begin;
TX1> delete from tests where id=15;TX1> show engine innodb status\G
 — -TRANSACTION 2712941, ACTIVE 8 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 7, OS thread handle 140655283296000, query id 229 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712941 lock mode IX
RECORD LOCKS space id 12994 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712941 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;; 
 1: len 6; hex 00000029654a; asc )eJ;; 
 2: len 7; hex ba0000011f011e; asc ;; 
 3: len 4; hex 80000014; asc ;; 
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;;

ギャップロックなのでネクストキーへの X はブロックされない

TX2> begin;
TX2> delete from tests where id=20;

範囲検索によるネクストキーロック

TX1> begin;
TX1> delete from tests where id between 13 and 15;TX1> show engine innodb status\G
 — -TRANSACTION 2712952, ACTIVE 4 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 7, OS thread handle 140655283296000, query id 248 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712952 lock mode IX
RECORD LOCKS space id 12994 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712952 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;; 
 1: len 6; hex 00000029654a; asc )eJ;; 
 2: len 7; hex ba0000011f011e; asc ;; 
 3: len 4; hex 80000014; asc ;; 
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;;

ネクストキーロックによってブロックされる

TX2> begin;
TX2> delete from tests where id=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

non-unique index
通常のレコードロック

TX1> begin;
TX1> delete from tests where value2=15;TX1> show engine innodb status\G
 — -TRANSACTION 2712954, ACTIVE 3 sec 
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 7, OS thread handle 140655283296000, query id 255 172.20.0.1 root starting
show engine innodb status 
TABLE LOCK table `testdb`.`tests` trx id 2712954 lock mode IX
RECORD LOCKS space id 12994 page no 5 n bits 72 index value2 of table `testdb`.`tests` trx id 2712954 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 0: len 4; hex 80000014; asc ;;
 1: len 4; hex 80000014; asc ;;

ギャップロックなのでネクストキーへの X はブロックされない

TX2> begin;
TX2> delete from tests where value2=20;

範囲検索によるネクストキーロック

TX1> begin;
TX1> delete from tests where value2 between 13 and 15;TX1> show engine innodb status\G
 — -TRANSACTION 2712963, ACTIVE 3 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 140655283296000, query id 268 172.20.0.1 root starting
show engine innodb status
TABLE LOCK table `testdb`.`tests` trx id 2712963 lock mode IX
RECORD LOCKS space id 12994 page no 5 n bits 72 index value2 of table `testdb`.`tests` trx id 2712963 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc ;;
 1: len 4; hex 80000014; asc ;;
 
RECORD LOCKS space id 12994 page no 3 n bits 72 index PRIMARY of table `testdb`.`tests` trx id 2712963 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc ;; 
 1: len 6; hex 00000029654a; asc )eJ;; 
 2: len 7; hex ba0000011f011e; asc ;; 
 3: len 4; hex 80000014; asc ;; 
 4: len 4; hex 80000014; asc ;;
 5: len 4; hex 80000014; asc ;;

ネクストキーロックによってブロックされる

TX2> begin;
TX2> delete from tests where value2=20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

まとめ

挙動は MySQL のバージョンで結構変わるので、あくまで手元の5.7系ではこのような挙動でしたというに留まります。とはいえクエリを見るだけで確度高く挙動が仮定できるのは安心感がある。

よしなに

MySQL

--

--

Hajime Terasawa

Written by Hajime Terasawa

8 Followers

Freelance SRE in Tokyo. Please contact me by DM on https://twitter.com/terakoya76.

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams