MySQL ロックのまとめ
MySQLにおけるDBのロックについてまとめる
前提知識
ACID
システムがデータの整合性を維持するために持つべき性質のこと
トランザクション
一連の操作を1つに束ねてDBに反映する仕組み
ACIDを実現しているのがこのトランザクション
コミット
一連の操作を確定させる（データを更新する）
ロールバック
一連の操作を操作開始時点に戻す
トランザクション分離レベル
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
MySQLのデフォルト
SERIALIZABLE
共有(S)ロック
他のトランザクションでロックされたデータを読み取りのみ許可
書き込み不可
排他(S)ロック
他のトランザクションが同じ行をロックをするのを回避
読み取り可、書き込み不可
インデックス
テーブルの行を高速でルックアップする機能を提供するデータ構造
ロックの種類
こちらを見るとクエリごとのロックの種類が書かれている トランザクション分離レベルがSERIALIZABLEになっていなければ、SELECT ... FROMの場合はスナップショットを読み取るのでロックは掛からない（一貫性非ロック読み取り）
ユーザレベルのロック
SELECT GET_LOCK(str, timeout)でユーザレベルのロックを取得可能、詳細はを参照 ロックを取得できた場合は1
ロックを取得できなかった場合は0が返る
timeoutは秒指定で0秒にした場合は得座に値が返ってくるので、待ちたくない場合は0にする
復数のアプリケーションで1つのDBを使う際は、strをapp.name.operationといった感じでアプリ間が被らないようにしたほうが良い
セッションごとにロックが掛かるので、同じセッションで何度GET_LOCK()をしても1が返る
SELECT RELEASE_LOCK(str)はロックを開放する
開放できた場合は1、できなかった場合はnullが返る
ロックをしてセッションから開放する必要がある
IS_FREE_LOCK(str)
strのロックが空いているかを確認
ロックできる場合は1、できない場合は0が返る
IS_USED_LOCK(str)
strのロックが使われているかを確認
もどり値はクライアントの接続識別子
注意点
ユーザレベルのロックも交差するとDeadLockが発生する
片方のロックは解除されないので、必要あれば手動でリリースする必要がある
例）
code:sql
client1>select get_lock("lock1", 0);
+----------------------+
| get_lock("lock1", 0) |
+----------------------+
| 1 |
+----------------------+
1 row in set
Time: 0.015s
client2>select get_lock("lock2", 0);
+----------------------+
| get_lock("lock2", 0) |
+----------------------+
| 1 |
+----------------------+
1 row in set
Time: 0.012s
client1>select get_lock("lock2", 100);
client2>select get_lock("lock1", 100);
(3058, 'Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks
and restarting lock acquisition.')
テーブルロック
テーブルごとロック
REPEATABLE READトランザクション分離レベルの場合、インデックスを使わない更新処理はテーブルロックになる
インデックスを貼っていない状態でfor updateを使用すると、レコードが存在しない場合はテーブルロックになる
レコードロック（行ロック）
インデックスレコードのロック
インデックスが定義されていなくても、非表示クラスタインデックスが使われる
ギャップロック（範囲ロック）
REPEATABLE READトランザクション分離レベルで動作する
インデックスレコードの間にあるギャップのロック
ギャップロックの確認
code:sql
SHOW CREATE TABLE users\G
*************************** 1. row *************************** Table | users
Create Table | CREATE TABLE users (
id int NOT NULL,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
A > BEGIN;
B > BEGIN;
A > SELECT * FROM users WHERE id < 10 FOR UPDATE;
+----+---------+
| id | name |
+----+---------+
| 1 | gorilla |
+----+---------+
A > SELECT LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks; -- idが1~9の間はギャップロックされる
+-----------+-----------+-----------+
| LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+-----------+-----------+
| TABLE | IX | <null> |
| RECORD | X | 1 |
| RECORD | X,GAP | 10 |
+-----------+-----------+-----------+
B > SET session innodb_lock_wait_timeout = 1;
B > INSERT INTO users VALUES (3, "bird"); -- ギャップロックにINSERTしようとしてロック取得待ち
(1205, 'Lock wait timeout exceeded; try restarting transaction')
B > INSERT INTO users VALUES (11, "bird"); -- ギャップロック範囲外にINSERTなので成功
Query OK, 1 row affected
Time: 0.021s
B > SELECT * FROM users;
+----+---------+
| id | name |
+----+---------+
| 1 | gorilla |
| 10 | dog |
| 11 | bird |
| 15 | cat |
+----+---------+
A > ROLLBACK;
A > SELECT LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks; -- ROLLBACKしてロックが解除される
+-----------+-----------+-----------+
| LOCK_TYPE | LOCK_MODE | LOCK_DATA |
+-----------+-----------+-----------+
ネクストキーロック
ロックの対象はインデックスレコードと先頭のインデックスレコードの前や末尾のインデックスレコードのあとにあるギャップのロック
https://gyazo.com/1187f9aac98028ed36dbec7f330f4ec9
INSERT操作でのロック
INSERT 操作では行の挿入前に、挿入インテンションギャップロックと呼ばれる一種のギャップロックが設定される
復数のユーザが同じギャップロックを取得可能で、そのギャップ範囲内に同一レコードにINSERTしなければINSERT可能
ロックエラー
DeadlockエラーとLock wait time outエラーがある
Deadlockエラーの例
AとBのロックが交差すると発生する
code:sql
A BEGIN;
B BEGIN;
A SELECT * FROM sample WHERE id = "x" FOR UPDATE;
B SELECT * FROM sample WHERE id = "x" FOR UPDATE;
A UPDATE sample SET col = "value"; --- 条件がないため、全レコードをロックしようとするためデッドロックが発生
B (1213, 'Deadlock found when trying to get lock; try restarting transaction')
ロックの解析
MySQL8だとSELECT * FROM performance_schema.data_locks\Gでレコード単位のロック状態が見れる
code:sql
SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140229171137104:1061:140229071664344
ENGINE_TRANSACTION_ID: 3593
THREAD_ID: 47
EVENT_ID: 13
OBJECT_SCHEMA: test
OBJECT_NAME: tu
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140229071664344
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140229171137104:4:4:7:140229071661352
ENGINE_TRANSACTION_ID: 3593
THREAD_ID: 47
EVENT_ID: 13
OBJECT_SCHEMA: test
OBJECT_NAME: tu
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140229071661352
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 3
2 rows in set (0.00 sec)
それ以前だと↓で競合したロックを表示できる
code:sql
select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
現在のロックの情報
code:sql
select * from information_schema.INNODB_TRX;
ロックに関するいろいろな情報
code:sql
SHOW ENGINE INNODB STATUS;
ロックの競合パターン表
table:ロック競合のパターン
X IX S IS
X 競合 競合 競合 競合
IX 競合 互換 競合 互換
S 競合 競合 互換 互換
IS 競合 互換 互換 互換
参考文献