[InnoDB] 非ユニークキーによる行ロックの範囲についてちょっと調べた

バージョン

% mysql --version
mysql  Ver 14.14 Distrib 5.5.29, for debian-linux-gnu (x86_64) using readline 6.2

調査方法

CREATE TABLE innodb_lock_monitor(a int) ENGINE=InnoDB
BEGIN
ロックを獲得するクエリを実行
SHOW ENGINE INNODB STATUSのTRANSACTIONSを読む
ROLLBACK
1. に戻ってクエリ実行を繰り返す

利用したテーブル

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`b`),
  KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

mysql> SELECT * FROM t;
+-----+---+
| a   | b |
+-----+---+
|  16 | 1 |
|  32 | 2 |
|  48 | 3 |
|  64 | 4 |
|  80 | 5 |
+-----+---+

結果

ALL

（おそらく主キーの全ノードに対してネクストキーロックがかかりそう）

index

SELECT * FROM t FOR UPDATE
- a の 16, 32, 48, 64, 80, sup にネクストキーロック
- PRIMARY の 1, 2, 3, 4, 5 にレコードロック

range (ASC)

SELECT * FROM t WHERE a IN (16, 32) FOR UPDATE
- a の 16, 32 にレコードロック
- PRIMARY の 1, 2 にレコードロック
SELECT * FROM t WHERE a < 48 FOR UPDATE
- a の 16, 32, 48 にネクストキーロック
- PRIMARY の 1, 2, 3 にレコードロック
SELECT * FROM t WHERE a <= 48 FOR UPDATE
- a の 16, 32, 48, 64 にネクストキーロック
- PRIMARY の 1, 2, 3, 4 にレコードロック
SELECT * FROM t WHERE a >= 48 FOR UPDATE
- a の 48 にレコードロック
- a の 64, 80, sup にネクストキーロック
- PRIMARY の 3, 4, 5 にレコードロック
SELECT * FROM t WHERE a > 48 FOR UPDATE
- a の 64, 80, sup にネクストキーロック
- PRIMARY の 1, 2, 3 にレコードロック

range (DESC)

SELECT * FROM t WHERE a < 32 ORDER BY a DESC FOR UPDATE
- a の 16 にネクストキーロック
- a の 32 の直前にギャップロック
- PRIMARY の 1 にレコードロック
SELECT * FROM t WHERE a <= 32 ORDER BY a DESC FOR UPDATE
- a の 16, 32 にネクストキーロック
- a の 48 の直前にギャップロック
- PRIMARY の 1, 2 にレコードロック
SELECT * FROM t WHERE a >= 64 ORDER BY a DESC FOR UPDATE
- a の 48, 64, 80, sup にネクストキーロック
- PRIMARY の 3, 4, 5 にレコードロック
SELECT * FROM t WHERE a > 64 ORDER BY a DESC FOR UPDATE
- a の 64, 80, sup にネクストキーロック
- PRIMARY の 4, 5 にレコードロック

ref

SELECT * FROM t WHERE a = 16 FOR UPDATE
- a の 16 にネクストキーロック
- a の 32 の直前にギャップロック
- PRIMARY の 1 にレコードロック
SELECT * FROM t WHERE a = 0 FOR UPDATE
- a の 16 の直前にギャップロック
SELECT * FROM t WHERE a = 20 FOR UPDATE
- a の 32 の直前にギャップロック
SELECT * FROM t WHERE a = 100 FOR UPDATE
- a の sup にネクストキーロック

eq_ref

（ユニークキーによる行ロックのみ）

const

（ユニークキーによる行ロックのみ）

SELECT * FROM t WHERE b = 1 FOR UPDATE
- PRIMARY の 1 にレコードロック

さらに調べないといけなそうなこと

複合キー
LIMIT
JOIN
サブクエリ

etheriqa/innodb-lock.md

Select an option

No results found

Select an option

No results found

[InnoDB] 非ユニークキーによる行ロックの範囲についてちょっと調べた

バージョン

調査方法

利用したテーブル

結果

ALL

index

range (ASC)

range (DESC)

ref

eq_ref

const

さらに調べないといけなそうなこと

参考文献