概要

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 内部ロック方法

• 行レベルロック
  • テーブル内の個々の行を対象としたロック
  • ロック対象が狭いのでロックの競合、ロールバックする変更が少なくなる
  • 1つの行を長時間ロック可能
• テーブルレベルロック
  • テーブルを対象としたロック
  • 必要になるメモリーが比較的に少ない（行ロックはロックされた行また行のグループごとにメモリーが必要）
  • 単一のロックだけが必要となるため、テーブルの大部分を対象に使用する場合は高速
  • データの大部分を対象にGROUP BYを頻繁に実行する場合やテーブル全体を頻繁にスキャンする場合は高速

InnoDBロック

cf. dev.mysql.com - 15.7.1 InnoDB ロック

共有（READ）ロック

共有ロックは、データのREADは可能だが、WRITEはできないロック。Shared lock（IS）。

検証

1. TX1でトランザクションを開始、共有ロックをかける

// TX1
mysql> INSERT INTO users(name) VALUES('foo'); // 初期データ投入
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

2. TX2でトランザクションを開始、WRITEを行う

// TX2
mysql> START TRANSACTION;
mysql> UPDATE users SET name = 'bar' WHERE id = 1;

TX1がCOMMITするまでTX2の更新はロックされる。

占有（排他・WRITE）ロック

排他ロックは、データのREADもWRITEもできないロック。Exclusive lock（IX）。

検証

1. TX1にてトランザクションを開始、占有ロックをかける

// TX1
mysql> INSERT INTO users(name) VALUES('foo'); // 初期データ投入
mysql> START TRANSACTION;
mysql> SELECT * FROM users WHERE id = 1 FOR UPDATE;

2. TX2でトランザクションを開始、READ、WRITEを行う

// 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のロックについて公式ドキュメントを読みながら動作検証してみた〜行レベルロック: インテンションロック〜

レコードロック

インデックスレコードのロック。インデックスレコードとはクラスタインデックスとセカンダリインデックスのこと。スキャンしたインデックスに対してロックする。

検証

データベースの内部的な動作であるため割愛。

ギャップロック

インデックスレコード間のギャップのロック。または、インデックスレコードの前または後ろのギャップのロック。

検証

1. TX1でトランザクション開始、READを行う

// 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;

2. TX2でトランザクション開始、WRITEを行う

// TX2
mysql> START TRANSACTION;
mysql> INSERT INTO users(id, name) VALUES(3, 'baz');

行単位のロックかと思いきや、範囲でロックされているのが確認できる。

ネクストキーロック

インデックスレコードのレコードロックとインデックスレコードの前のギャップのギャップロックの組み合わせ。

検証

1. TX1でトランザクション開始、READを行う

// 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;

2. TX2でトランザクション開始、WRITEを行う

// TX2
mysql> START TRANSACTION;
mysql> INSERT INTO users(id, name) VALUES(5, 'quux');

idが5未満の行だけでなく、末尾のインデックス値を持つ行の後のギャップもロックされることが確認できる。

インテンションロックの挿入

行の挿入前のINSERTによって設定されるギャップロックのタイプ。INSERTのインテンションロック。

検証

データベースの内部的な動作であるため割愛。

こちらの記事で検証されているので参照。 cf. MySQLのロックについて公式ドキュメントを読みながら動作検証してみた〜レコードロック / ギャップロック / ネクストキーロック / 他〜

AUTO-INCロック

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には明示的・暗黙的にロックされるパターンがある。

何が（行なのかテーブルなのか）対象なのか、範囲はどこまでなのかといったことにまずは目を向けると良さそう。

参照

• dev.mysql.com - 8.11.1 内部ロック方法
• dev.mysql.com - 15.7.1 InnoDB ロック
• zenn.dev - データベースのロックの基礎からデッドロックまで
• qiita.com - MySQLのロックについて公式ドキュメントを読みながら動作検証してみた〜レコードロック / ギャップロック / ネクストキーロック / 他〜
• qiita.com - MySQL のロックについて補足（注：すでに語りつくされている内容です）
• qiita.com - MySQLのロックについて公式ドキュメントを読みながら動作検証してみた〜行レベルロック: 共有ロック(S) / 排他ロック(X) 〜
• qiita.com - MySQLのロックについて公式ドキュメントを読みながら動作検証してみた〜行レベルロック: インテンションロック〜
• techblog.cartaholdings.co.jp - DBのロックについてあまり意識したことがない人に向けた実は覚えておきたいロックについての知識
• www.wakuwakubank.com - 占有ロック(FOR UPDATE)と共有ロック(LOCK IN SHARE MODE)
• saekis.hatenablog.com - MySQLの排他ロックの挙動を確認する
• bizstation.hatenablog.com - MySQL/MariaDBとTransactdのInnoDBロック制御詳細　その1
• devsakaso.com - 【MySQL】ロックとデッドロックについて
• nishinatoshiharu.com - 【MySQL】InnoDBの共有ロックと排他ロックの概要と挙動検証
• www.wakuwakubank.com - 占有ロック(FOR UPDATE)と共有ロック(LOCK IN SHARE MODE)
• free-engineer.life - MySQL(InnoDB)共有ロックと排他ロックとインテンションロック（テーブルロック）
• free-engineer.life - MySQL(InnoDB)の行ロック
• github.com - MySQLのInnoDBのロック挙動調査
• github.com - スレッドIDを利用したMySQLのデッドロック解析手法