初めてのデッドロック ~SQLクライアントを利用したハンズオンで解る~
はじめに
本記事では、デッドロックをメインテーマにしてデータベースのロックの仕組みを少しでも理解を深められるようにハンズオン形式で紹介していきます。
対象の方は、排他ロックや共有ロックと何となく概念を理解しているが、正しく理解出来ていない方を想定しております。
実際に手元でデッドロックを再現させることで、ふわっとした知識を具体的なイメージに変えていただくことを目的としています。
また、SQLクライアントを使うことで、複雑な設定や環境構築を必要とせず、簡単にデッドロックを再現して確認していきます。
使用したソフトウェア
データベース：MySQL-5.7.42
SQLクライアント：SequelAce-4.0.7
他の記事紹介
より実践的な情報を提供して頂いてる方々がいらっしゃいますので、是非とも参考にしてみてください。
Keywords
排他ロック
共有ロック
トランザクション
コミット・ロールバック
ロックについて
排他ロック・共有ロックとは
共有 (S) ロックでは、ロックを保持するトランザクションによる行の読み取りが許可されます。
排他 (X) ロックでは、ロックを保持するトランザクションによる行の更新または削除が許可されます。
MySQL5.7でのロック取得のSQL
共有 (S) ロック
SELECT * FROM projects WHERE id = 1 LOCK IN SHARE MODE;
排他 (X) ロック
SELECT * FROM projects WHERE id = 1 FOR UPDATE;
複数トランザクション間でのロックの影響
トランザクションが複数ある場合、特定のトランザクションのロックが他のトランザクションへ影響を及ぼします。
ハンズオンで実際の動きを後述しますが、簡単に共有ロック・排他ロックの動きを紹介します。（トランザクションをT1・T2・・・で表現）
ケース１：
複数トランザクション間で、同じレコードに対する共有 (S) ロックを保持することが可能
ケース２：
排他 (X) ロック中のレコードに対して、共有 (S) ロックを保持することは不可
排他 (X) ロックが解放されるまで、共有 (S) ロックは待機の状態となる
ケース３：
同じレコードに対する共有 (S) ロックを保持している場合、T1で排他 (X) ロックを取得しようとすると待機となる。
T2でも、排他 (X) ロックを取得しようとすると強制的にRollbackされる（デッドロックが発生する）
既にロックを利用している？
共有ロック・排他ロックを使用する際には、LOCK IN SHARE MODE・FOR UPDATEを利用してロックを取得する必要がありました。
しかし、明示的に指定せずとも、ロックを取得した上で処理しているケースがあります。
UPDATE・DELETEを利用した場合には、データベースが更新されるまで排他 (X) ロックを利用します。
※UPDATE・DELETEが宣言された場合には、抽出条件によってロックの種類が異なります。
※UDPATE・DELETE時のロックのパターンは：https://dev.mysql.com/doc/refman/8.0/ja/innodb-locks-set.html
今回は、単純な排他 (X) ロックがされるものとしてハンズオンを進めていきます。
ハンズオン形式でロック・デッドロックの理解促進へ
それでは、実際にロックの動きを確認してみたいと思います。
ロックが掛かっても問題無いテスト用のデータベース・SQLクライアントを用意してください。
データベースの状態を把握するためのクエリ
はじめに、データベースの状態を確認する方法を把握しておきましょう。
ロックをかけたり、デッドロックが発生しても、データベースがどういった状態だったのかを把握出来ていなければ、理解が深まりません。
トランザクションの状態を把握するクエリ
INFORMATION_SCHEMA.INNODB_TRX テーブル
今回はカラムを絞って、下記クエリにて抽出します。
SELECT trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started, trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
ロックの状態を把握するクエリ
INFORMATION_SCHEMA.INNODB_LOCKS テーブル
トランザクションがリクエストしたがまだ取得していない各ロックと、別のトランザクションをブロックしているトランザクションが保持している各ロックに関する情報が表示されます。
今回はカラムを絞って、下記クエリにて抽出します。
SELECT lock_id, lock_trx_id, lock_mode, lock_type, lock_index FROM INFORMATION_SCHEMA.INNODB_LOCKS;
それでは、これらのクエリで状態を確認しつつ、ロックを取得するクエリを実行していきます。
ケース１：複数トランザクション間で、同じレコードに対する共有 (S) ロックを保持することが可能
まずは、複数トランザクション間で、同じレコードに対する共有 (S) ロックを保持することが可能であることを確認していきます。
T1：
START TRANSACTION;
SELECT * FROM projects WHERE id = 1 LOCK IN SHARE MODE;
| id | name |
|---|---|
| 1 | AAAA |
T2：
START TRANSACTION;
SELECT * FROM projects WHERE id = 1 LOCK IN SHARE MODE;
| id | name |
|---|---|
| 1 | AAAA |
トランザクションを確認
SELECT trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started, trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_isolation_level |
|---|---|---|---|---|---|
| 281750215547224 | RUNNING | 2024-03-03 13:53:58 | NULL | NULL | REPEATABLE READ |
| 281750215542624 | RUNNING | 2024-03-03 13:53:54 | NULL | NULL | REPEATABLE READ |
ロックの状態を確認
SELECT lock_id, lock_trx_id, lock_mode, lock_type, lock_index FROM INFORMATION_SCHEMA.INNODB_LOCKS;
0 record
T1・T2双方でデータを取得することは出来ました。
また、2つのトランザクションは、RUNNINGの状態ですが。ロック待機の状態は発生していません。
ケース２：排他 (X) ロック中のレコードに対して、共有 (S) ロックを保持することは不可・排他 (X) ロックが解放されるまで、共有 (S) ロックは待機の状態となる
次に、排他ロックを実行していきます。排他ロックによってロックされているレコードは、共有ロックで参照することが出来ません。そして、ロックが完了（COMMIT OR ROLLBACK）するまでロック待機の状態となります。
T1：
START TRANSACTION;
SELECT * FROM projects WHERE id = 1 FOR UPDATE;
| id | name |
|---|---|
| 1 | AAAA |
T2：
START TRANSACTION;
SELECT * FROM projects WHERE id = 1 LOCK IN SHARE MODE;
待機
トランザクションを確認
SELECT trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started, trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_isolation_level |
|---|---|---|---|---|---|
| 370718 | RUNNING | 2024-03-03 14:03:23 | NULL | NULL | REPEATABLE READ |
| 281750215547224 | LOCK WAIT | 2024-03-03 14:03:41 | 281750215547224:1164:3:2 | 2024-03-03 14:03:41 | REPEATABLE READ |
ロックの状態を確認
SELECT lock_id, lock_trx_id, lock_mode, lock_type, lock_index FROM INFORMATION_SCHEMA.INNODB_LOCKS;
| lock_id | lock_trx_id | lock_mode | lock_type | lock_index |
|---|---|---|---|---|
| 281750215547224:1164:3:2 | 281750215547224 | S | RECORD | PRIMARY |
| 370718:1164:3:2 | 370718 | X | RECORD | PRIMARY |
上記のように、(T1)排他ロックされているレコードに対して、(T2)共有ロックを実行したところ、T2はロック待機の状態となりました。
INNODB_LOCKSテーブルでも、別のトランザクションをブロックしているトランザクションが発生したので、ロックに関するレコードが抽出されました。
デッドロックを発生させる
デッドロックを発生させてみます。
同じレコードに対する共有 (S) ロックを保持している状態で、T2で排他 (X) ロックを取得しようとすると待機となる。
その状態で、T1でも、排他 (X) ロックを取得しようとデッドロックが発生し、強制的にRollbackされます。
T1：
START TRANSACTION;
SELECT * FROM projects WHERE id = 1 LOCK IN SHARE MODE;
| id | name |
|---|---|
| 1 | AAAA |
T2：
START TRANSACTION;
SELECT * FROM projects WHERE id = 1 LOCK IN SHARE MODE;
| id | name |
|---|---|
| 1 | AAAA |
トランザクションを確認
SELECT trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started, trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_isolation_level |
|---|---|---|---|---|---|
| 281750215542624 | RUNNING | 2024-03-03 14:35:26 | NULL | NULL | REPEATABLE READ |
| 281750215547224 | RUNNING | 2024-03-03 14:35:28 | NULL | NULL | REPEATABLE READ |
T1：
UPDATE projects SET name = 'BBBB' WHERE id = 1;
待機
トランザクションを確認
SELECT trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started, trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_isolation_level |
|---|---|---|---|---|---|
| 370959 | LOCK WAIT | 2024-03-03 14:35:26 | 370959:1164:3:2 | 2024-03-03 14:35:48 | REPEATABLE READ |
| 281750215547224 | RUNNING | 2024-03-03 14:35:28 | NULL | NULL | REPEATABLE READ |
T2：
UPDATE projects SET name = 'BBBB' WHERE id = 1;
Deadlockが発生!!
トランザクションを確認
SELECT trx_id, trx_state, trx_started, trx_requested_lock_id, trx_wait_started, trx_isolation_level FROM INFORMATION_SCHEMA.INNODB_TRX;
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_isolation_level |
|---|---|---|---|---|---|
| 370959 | RUNNING | 2024-03-03 14:35:26 | NULL | NULL | REPEATABLE READ |
共有ロックを保持している状態で、それぞれが排他ロックを実行した場合、ロック待機とはならずデッドロックが発生しました。
また、デッドロックを発生させたトランザクションはロールバックされてトランザクションが削除されています。
そして、先に排他ロックを実行した（UPDATE実行した）トランザクションのトランザクションは維持されています。
おわりに
如何でしたでしょうか。実際に手を動かしてロックの状態を確認しながら進めることによって、イメージを深堀ることは出来ましたか。
今回は簡単なケースをSQLクライアントにて実行しました。本当はもっと複雑なケースやロックの種類もそまざまです。
公式ドキュメントや他の方の記事などを参照しながら、理解を深めてみてはいかがでしょうか。
わたしもまだまだまだまだ勉強途中です。何か不備などがあれば教えて頂けますと幸いです。
Discussion