MySQLのロックについてまとめる。 MySQLのバージョンは8系を想定する。
検証に使う環境はdocker-composeで用意した。（1コンテナだけなのでcomposeを使わなくも良いのだが..）
.
├── docker-compose.yml
└── initdb.d
└── 1_schema.sql
docker-compose.yml
version: '3'
services:
mysql:
image: mysql:8.0.33
environment:
MYSQL_ROOT_PASSWORD: password
MYSQL_DATABASE: example
TZ: "Asia/Tokyo"
command: mysqld
ports:
- 3306:3306
volumes:
- ./initdb.d:/docker-entrypoint-initdb.d
1_schema.sql
CREATE DATABASE IF NOT EXISTS example;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(255) NOT NULL UNIQUE
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
docker compose upでお手元にMySQL8系のコンテナが用意できる。
MySQLにおける排他制御の手法としては、行レベルロックとテーブルレベルロックがある。
cf. dev.mysql.com - 8.11.1 内部ロック方法
cf. dev.mysql.com - 15.7.1 InnoDB ロック
共有ロックは、データのREADは可能だが、WRITEはできないロック。Shared lock（IS）。
// TX1
mysql> INSERT INTO users(name) VALUES('foo'); // 初期データ投入
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
// TX2
mysql> START TRANSACTION;
mysql> UPDATE users SET name = 'bar' WHERE id = 1;
TX1がCOMMITするまでTX2の更新はロックされる。
排他ロックは、データのREADもWRITEもできないロック。Exclusive lock（IX）。
// TX1
mysql> INSERT INTO users(name) VALUES('foo'); // 初期データ投入
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE id = 1 FOR UPDATE;
// TX2
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE id = 1; // これは許容されるが
mysql> SELECT * FROM users WHERE id = 1 FOR UPDATE; // 許容されない
mysql> UPDATE users SET name = 'bar' WHERE id = 1; // 許容されない
TX1のロックが解放されるまでTX2ではREAD（単純なSELECT以外）やWRITEができないことが確認できる。
トランザクションがテーブルの行に必要とするロックタイプ（共有または排他）を示すテーブルレベルのロック。 行ロックとテーブルロックの共存をサポートするために用意されている。
インテンションロックには、
の2つがある。
SQLで明示的に操作できるものではなく、基本的にはデータベース内部で管理されるものであるので、検証は割愛。
いくつか検証パターンがあるが、以下の記事で色々と検証されている。
cf. qiita.com - MySQLのロックについて公式ドキュメントを読みながら動作検証してみた〜行レベルロック: インテンションロック〜
インデックスレコードのロック。インデックスレコードとはクラスタインデックスとセカンダリインデックスのこと。スキャンしたインデックスに対してロックする。
データベースの内部的な動作であるため割愛。
インデックスレコード間のギャップのロック。または、インデックスレコードの前または後ろのギャップのロック。
// TX1
mysql> INSERT INTO users(id, name) VALUES(1, 'foo'), (2, 'bar'), (4, 'qux'), (5, 'quux'), (6, 'corge'); // 初期データ投入
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE ID between 1 AND 5 FOR UPDATE;
// TX2
mysql> START TRANSACTION;
mysql> INSERT INTO users(id, name) VALUES(3, 'baz');
行単位のロックかと思いきや、範囲でロックされているのが確認できる。
インデックスレコードのレコードロックとインデックスレコードの前のギャップのギャップロックの組み合わせ。
// TX1
mysql> INSERT INTO users(id, name) VALUES(1, 'foo'), (2, 'bar'), (3, 'baz'), (4, 'qux'); // 初期データ投入
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE ID < 5 FOR UPDATE;
// TX2
mysql> START TRANSACTION;
mysql> INSERT INTO users(id, name) VALUES(5, 'quux');
idが5未満の行だけでなく、末尾のインデックス値を持つ行の後のギャップもロックされることが確認できる。
行の挿入前のINSERTによって設定されるギャップロックのタイプ。INSERTのインテンションロック。
データベースの内部的な動作であるため割愛。
こちらの記事で検証されているので参照。 cf. MySQLのロックについて公式ドキュメントを読みながら動作検証してみた〜レコードロック / ギャップロック / ネクストキーロック / 他〜
AUTO_INCREMENTカラムを含むテーブルに挿入されるトランザクションによって取得されるテーブルロック。 TX1でのトランザクションでINSERTするためにAUTO_INCREMENTの値を取得している間はTX2でのAUTO_INCREMENTの値を取得できないようするロック。
内部的な動作である&再現方法が分からなかったので割愛。
これはドキュメント参照。（空間インデックスに触りなれていないのものあってイマイチ分からなかった。。。）
cf. 空間インデックスの述語ロック
ロックは以下のクエリで確認することができる。
// ロックの状態確認
SELECT * FROM performance_schema.data_locks;
// ロック件数確認+スレッドID
SHOW ENGINE INNODB STATUS;
// ロック件数確認
SELECT trx_id, trx_rows_locked, trx_mysql_thread_id FROM information_schema.INNODB_TRX;
デッドロックを確認するには、SHOW ENGINE INNODB STATUSを実行し、LATEST DETECTED DEADLOCKと記載されている部分を確認する。
MySQLには明示的・暗黙的にロックされるパターンがある。
何が（行なのかテーブルなのか）対象なのか、範囲はどこまでなのかといったことにまずは目を向けると良さそう。