InnoDBはMySQLのデータベースエンジンの1つです。InnoDBの行ロックには共有ロック(Sロック)と排他ロック（占有ロック、Xロック）の2種類があります。1
今回は共有ロックと排他ロックの概要と、ロック時の挙動について紹介していきます。
共有ロックについて
共有ロックはレコードの更新を禁止する際に利用するロックです。共有ロックを利用することで参照先のレコードの書き換えが防げます。
共有ロックはMySQL 8以前のSELECT ... LOCK IN SHARE MODE、MySQL 8のSELECT ... FOR SHARE、重複キーエラーによるINSERT失敗時に実行されます。2 3
トランザクション内で作成された共有ロックはトランザクション終了後に解除されます。
排他ロックについて
排他ロックはレコードの更新と参照を禁止する際に利用するロックです。排他ロックを利用することで参照先のレコードを独占できます。
排他ロックはSELECT ... FOR UPDATE、INSERT、UPDATE、DELETEで実行されます。2
トランザクション内で作成された排他ロックはトランザクション終了後に解除されます。
排他制御の手法には楽観ロック（楽観的排他制御）と悲観ロック（悲観的排他制御）の2種類があります。排他ロックを利用した排他制御は悲観ロックに該当します。
ロックの互換性について
ロックの互換性がある場合、同一レコードに対して同時にロックができます。
共有ロックどうしはロックの互換性があるため、共有ロックであれば同時に同一のレコードにアクセスできます。
一方、排他ロックの場合は排他ロックが終わるまで当該レコードにアクセスできません。
ロックの組み合わせとロックの互換性の関係をまとめると以下のようになります。
| ロックの組み合わせ | ロックの互換性 |
|---|---|
| 共有ロック x 共有ロック | あり |
| 共有ロック x 排他ロック | なし |
| 排他ロック x 排他ロック | なし |
共有ロックと排他ロックの挙動確認
以下のようなusersテーブルを作成し、共有ロックと排他ロックの挙動を確認してみます。
> SELECT * FROM users \G;
*************************** 1. row ***************************
id: 1
last_name: Franecki
first_name: Cherri
age: 22
*************************** 2. row ***************************
id: 2
last_name: Conroy
first_name: Columbus
age: 18
2 rows in set (0.00 sec)
共有ロック実行中の場合
トランザクション内で共有ロックを作成するSELECT ... FOR SHAREを実行します。
共有ロックのSQLが実行されているターミナルを『セッションA』と呼ぶことにします。
セッションA
-- トランザクション開始
-- 『BEGING』のかわりに『START TRANSACTION』でもOK
> BEGIN;
Query OK, 0 rows affected (0.01 sec)
> SELECT * FROM users WHERE id = 1 FOR SHARE \G;
*************************** 1. row ***************************
id: 1
last_name: Franecki
first_name: Cherri
age: 21
1 row in set (0.00 sec)
『セッションA』が実行中の場合、別ターミナル『セッションB』の挙動がどうなるか以下で検証していきます。
SELECTの場合、当該レコードにアクセスできる
通常のSELECTは行ロックが不要なので共有ロック中のレコードにアクセスできます。
セッションB
> SELECT * FROM users WHERE id = 1 \G;
*************************** 1. row ***************************
id: 1
last_name: Franecki
first_name: Cherri
age: 21
1 row in set (0.00 sec)
共有ロックの場合、当該レコードにアクセスできる
共有ロックどうしはロックの互換性があります。
ですので共有ロックを実行するSQLは共有ロック中のレコードにアクセスできます。
セッションB
> SELECT * FROM users WHERE id = 1 FOR SHARE \G;
*************************** 1. row ***************************
id: 1
last_name: Franecki
first_name: Cherri
age: 21
1 row in set (0.00 sec)
排他ロックの場合、当該レコードにアクセスするとロック待ちになる
共有ロックと排他ロックはロックの互換性がありません。
そのため、共有ロック中のレコードに対して排他ロックを実行するとロック待ちが発生します。
セッションB
> SELECT * FROM users WHERE id = 1 FOR UPDATE \G;
-- 結果が返ってこない
以下のように、セッションAのトランザクションを終了して共有ロックを解除することでセッションBの結果が返ってきます。
セッションA
-- トランザクション終了
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
セッションB
> SELECT * FROM users WHERE id = 1 FOR UPDATE \G;
-- セッションAでCOMMIT(もしくはROLLBACK)が実行されたタイミングで結果が返ってくる
*************************** 1. row ***************************
id: 1
last_name: Franecki
first_name: Cherri
age: 21
1 row in set (18.03 sec)
任意のSQLは別レコードにアクセスできる
行ロックはレコード単位のロックですので、排他ロックだとしても別レコードであればロック待ちは発生しません。
セッションB
-- id=1は共有ロック中だが、別レコードであれば排他ロックでもアクセスできる
> SELECT * FROM users WHERE id = 2 FOR UPDATE \G;
*************************** 1. row ***************************
id: 2
last_name: Conroy
first_name: Columbus
age: 18
排他ロック実行中の場合
トランザクション内で排他ロックを作成するUPDATEを実行します。
排他ロックのSQLが実行されているターミナルを『セッションA』と呼ぶことにします。
セッションA
-- トランザクション開始
> BEGIN;
Query OK, 0 rows affected (0.01 sec)
-- トランザクション内でid=1のageを21から22に更新する
> UPDATE users SET age = 22 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
『セッションA』が実行中の場合、別ターミナル『セッションB』の挙動がどうなるか以下で検証していきます。
SELECTの場合、当該レコードにアクセスできる
通常のSELECTは行ロックが不要なので排他ロック中のレコードにアクセスできます。
ただし、このとき取得されるデータはトランザクションの内容が反映されていない値（age = 21）です。
セッションB
> SELECT * FROM users WHERE id = 1;
*************************** 1. row ***************************
id: 1
last_name: Franecki
first_name: Cherri
age: 22
1 row in set (0.00 sec)
共有ロックの場合、当該レコードにアクセスするとロック待ちになる
共有ロックと排他ロックはロックの互換性がありません。
そのため、排他ロック中のレコードに対して共有ロックを実行するとロック待ちが発生します。
共有ロックと排他ロックはロックの順番にかかわらず、同時に同一のレコードに対してロックをかけられません。
セッションB
> SELECT * FROM users WHERE id = 1 FOR SHARE \G;
-- 結果が返ってこない
以下のように、セッションAのトランザクションを終了して排他ロックを解除することでセッションBの結果が返ってきます。
なお、このとき取得されるデータはトランザクションの内容が反映された値（age = 22）です。
セッションA
-- トランザクション終了
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
セッションB
> SELECT * FROM users WHERE id = 1 FOR UPDATE \G;
-- セッションAでCOMMIT(もしくはROLLBACK)が実行されたタイミングで結果が返ってくる
*************************** 1. row ***************************
id: 1
last_name: Franecki
first_name: Cherri
age: 22
1 row in set (6.16 sec)
排他ロックの場合、当該レコードにアクセスするとロック待ちになる
排他ロックどうしもロックの互換性がないためロック待ちになります。
セッションB
> SELECT * FROM users WHERE id = 1 FOR UPDATE \G;
-- 結果が返ってこない
セッションAのロックを解除すると、トランザクションの内容が反映された値(age = 22)が取得できます。
セッションA
> COMMIT;
Query OK, 0 rows affected (0.01 sec)
セッションB
> SELECT * FROM users WHERE id = 1 FOR UPDATE \G;
-- セッションAでCOMMIT(もしくはROLLBACK)が実行されたタイミングで結果が返ってくる
*************************** 1. row ***************************
id: 1
last_name: Franecki
first_name: Cherri
age: 22
1 row in set (18.03 sec)
任意のSQLは別レコードにアクセスできる
行ロックはレコード単位のロックですので、排他ロックだとしても別レコードであればロック待ちは発生しません。
セッションB
-- id=1は排他ロック中だが、別レコードであれば排他ロックでもアクセスできる
> SELECT * FROM users WHERE id = 2 FOR UPDATE \G;
*************************** 1. row ***************************
id: 2
last_name: Conroy
first_name: Columbus
age: 18
まとめ
- 共有ロックはレコードの更新を禁止するロック
- 排他ロックはレコードの参照と更新を禁止するロック
- 『共有ロックどうし』はロックの互換性があるため、同時に同一レコードへアクセスできる
- 『排他ロックどうし』『排他ロックと共有ロック』はロックの互換性がないため、同時に同一レコードへアクセスできない
Twitter(@nishina555)やってます。フォローしてもらえるとうれしいです！
