MySQL と互換 DB のロック入門第９回の今回はデッドロックについてです。
デッドロックとは別々にリソースを確保した２つ以上のトランザクションが、互いに互いのリソースを必要としている手詰まりの状態を言い、データベース以外の分野でも使われる用語です。
User1 User2
+-----+ +-----+
| 1 |<---------+ _ |
| | | |
| ___ +--------->| 100 |
+-----+ +-----+
User1 は 1, User2 は 100 取得済み
相互に追加で取りたいデッドロック
お互い既に排他ロックしている行を、さらに互いに必要とし合うパターンでのデッドロックは直感的にもわかりやすいため、現実の開発においてはこのタイプの単純なデッドロックは避けられている事が比較的多いです。
デッドロックのもう一つのパターンとして、共有ロックを使っている場合があります。排他ロックにくらべて直感では「軽いロック」とイメージされやすい共有ロックが原因となっているためか、アプリケーションサーバーのロジックを考える段階で見逃される事があり、現実のデッドロックではよく見る形態の一つとなっています。
今回はこの共有ロックから発生するデッドロックについてロックの状態を実際に観察しながら解説していきます。
★ 第１回 トランザクション分離レベル
★ 第２回 ロックモニターの読み方
★ 第３回 ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うロック読取り
★ 第６回 performance_schema
★ 第７回ギャップロックと消えるロック
★ 第８回 ネクストキーロックと降順インデックス
★ 第９回 共有ロックでデッドロック
★ 第１０回 ロック読み取りは READ COMMITTED
★ 第１１回 AWS Aurora と MySQL の違い
★ 第１２回 PingCAP TiDB と MySQL の違い
排他の持ち合いで発生する基本的なデッドロック
まず排他ロックの持ち合いで発生する基本的なデッドロックについて説明します。
今回もこちらの t1 テーブルを使います。
+-----+------+
| k | v |
+-----+------+
| 1 | 1 |
| 10 | 10 |
| 100 | 100 |
+-----+------+
最初の端末でトランザクションを開始し k = 1 で排他ロックします。同様に２枚目の端末でもトランザクションを開始し k = 10 で排他ロックします。
[1]> SELECT * FROM t1 WHERE k = 1 FOR UPDATE;
+---+------+
| k | v |
+---+------+
| 1 | 0 |
+---+------+
1 row in set (0.00 sec)
[2]> SELECT * FROM t1 WHERE k = 10 FOR UPDATE;
+----+------+
| k | v |
+----+------+
| 10 | 0 |
+----+------+
1 row in set (0.00 sec)
次に互いに先にロックされている行のロックを試みます。
[1]> SELECT * FROM t1 WHERE k = 10 FOR UPDATE;
１つめの端末が追加で排他ロックを取得しようとした段階ではまだロック待ちでデッドロックではありませんが・・・・
[2]> SELECT * FROM t1 WHERE k = 1 FOR UPDATE;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
２つ目の端末で試みた瞬間にデッドロックが検知されました。１つ目の端末に戻ると、ハングしていたクエリが、
[1]> SELECT * FROM t1 WHERE k = 10 FOR UPDATE;
+----+------+
| k | v |
+----+------+
| 10 | 0 |
+----+------+
1 row in set (25.66 sec)
25秒かかっていますが終了していました。SHOW ENGINE INNODB STATUS でデッドロックの内容を確認します。
[1]> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
省略
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-07-24 00:45:42 129621799548608
*** (1) TRANSACTION:
TRANSACTION 20254, ACTIVE 48 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 8, OS thread handle 129622116079296, query id 37 localhost root statistics
SELECT * FROM t1 WHERE k = 10 FOR UPDATE
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t1` trx id 20254 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
0: len 4; hex 80000001; asc ;; <------- k = 1のロック取得済み
1: len 6; hex 000000004f19; asc O ;;
2: len 7; hex 02000001200a56; asc V;;
3: len 4; hex 80000000; asc ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t1` trx id 20254 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
0: len 4; hex 8000000a; asc ;; <------ k = 10 のロック取得待ち
1: len 6; hex 000000004f19; asc O ;;
2: len 7; hex 02000001200a78; asc x;;
3: len 4; hex 80000000; asc ;;
*** (2) TRANSACTION:
TRANSACTION 20255, ACTIVE 37 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 9, OS thread handle 129621782763200, query id 38 localhost root statistics
SELECT * FROM t1 WHERE k = 1 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t1` trx id 20255 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
0: len 4; hex 8000000a; asc ;; <----- k = 10 のロック取得済み
1: len 6; hex 000000004f19; asc O ;;
2: len 7; hex 02000001200a78; asc x;;
3: len 4; hex 80000000; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 72 index PRIMARY of table `learning_mysql`.`t1` trx id 20255 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 64
0: len 4; hex 80000001; asc ;; <------ k = 1 のロック取得待ち
1: len 6; hex 000000004f19; asc O ;;
2: len 7; hex 02000001200a56; asc V;;
3: len 4; hex 80000000; asc ;;
*** WE ROLL BACK TRANSACTION (2)
互いに排他のレコードロック lock_mode X locks rec but not gap を一つ HOLD し、
追加のロックを WAITING FOR THIS LOCK TO BE GRANTED で取得出来るのを待っている状態で、
(2) の方のトランザクション、trx id の値が大きな後で実行した方をロールバックしてこのデッドロックを解決したということがわかります。
共有ロックによるデッドロック
とある API で
１．自分の処理が終わるまである範囲（今回はわかりやすく全行とする）の変更はして欲しくない、読むのは OK
２．リクエスト毎に異なるパラメーター（例：ユーザーID）に応じてその範囲の中の１行のデータを変更する
という処理があり、これが多数同時に実行されているとします。自然な実装で
１．SELECT * FROM table LOCK IN SHARE MODE
２．UPDATE table SET value = new_value WHERE key = user_id
のようなクエリにしたと仮定し、２つの端末で同様のクエリを実行してみます。
[1]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
[1]> SELECT * FROM t1 LOCK IN SHARE MODE;
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 100 | 0 |
+-----+------+
3 rows in set (0.00 sec)
[2]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
[2]> SELECT * FROM t1 LOCK IN SHARE MODE;
+-----+------+
| k | v |
+-----+------+
| 1 | 0 |
| 10 | 0 |
| 100 | 0 |
+-----+------+
3 rows in set (0.00 sec)
この状態で両方の端末からそれぞれ異なる k に対して v = 1 となるように更新します。
１つ目の端末で k = 1 で実行するとブロックされます。
[1]> UPDATE t1 SET v = 1 WHERE k = 1;
２つ目の端末で k = 100 で実行すると
[2]> UPDATE t1 SET v = 1 WHERE k = 100;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
デッドロックが発生しました。一方１つ目の端末では 25秒かかって成功しました。
[1]> UPDATE t1 SET v = 1 WHERE k = 1;
Query OK, 1 row affected (24.77 sec)
Rows matched: 1 Changed: 1 Warnings: 0
S S
+-----+ X +-----+
| 1 +--------->| 1 |
| 10 | | 10 |
| 100 |<---------+ 100 |
+-----+ X +-----+
S: 取得済み共有ロック
X: UPDATE に欲しい排他ロック
この例では発生しやすい様に全行に対して共有ロックをかけましたが、先行する共有ロックの領域が小さめで衝突確率が下がった場合には、同時実行の数が少ないとデッドロックそのものが発生せず、同時実行数の増加にしたがってデッドロックが増えて初めて気がつく、という症状を示すようになります。
第９回まとめ
今回解説したように共有ロックを原因としたデッドロックは
１．同時実行数が増えてくるとデッドロックのエラーが出るようになる
２．成功／失敗した API に本質的な違いが見当たらない（実際成功失敗は確率だけなので）
３．成功している API も、次第に遅くなっている
４．MySQL の負荷増だけではなく、デッドロック判定までの待ち時間が足される事で API の性能が悪化しているが、混在していて原因になかなか気が付けない
という特徴があります。
共有ロックはこのようなリスクがあるとはいえ、API 毎に広い範囲を排他ロックするとデッドロックは防げる一方でテーブルロックのような状態になり同時実行が難しくなるのも事実です。共有ロックは必要悪、諸刃の剣としてデッドロックの発生確率を低く維持出来るよう、範囲を工夫する等してご活用ください。
次回 MySQL 編の最終回となる第１０回は、ロック読取りが実は READ COMMITTED になる件について解説します！ トランザクション分離レベルに頼らず手動でロック読取りをすることでロック制御するのが実戦でのクエリですが、ロック読取りを使うと実は REPEATABLE READ の特徴から外れているという、MySQL の特徴の真骨頂とも言えるところですのでご期待ください！
