MySQLのロックについて（基本編）

Transcript

1. MySQLのロックについて 〜基本編〜 2022.03.25
2. 自己紹介 • 三谷　智史（@mita2） • 仕事その１　とある企業のMySQL チームの中間管理職 • 仕事その２　ANDPADのデータベース技術顧問 • 月に２日、お邪魔してます • 　 • http://mita2db.hateblo.jp/
3. 本日のゴール ロック関連のエラーの解析ができるようになる
4. 目次 1. ロックが必要な理由 2. ロックの範囲 • MySQLのレコードロック • 外部キーとロック 3. ロックに関するエラーと対処方法 • ロック待ちタイムアウト • デッドロック 4. 質疑応答 今日取り上げないこと ・DDLのロック、metadatalock ・ギャップロック/ネクストキーロック ・シャドーロック ・TX分離レベルがREPEATABLE-READ以外のロックの挙 動
5. 目次 1. ロックが必要な理由 2. ロックの範囲 • MySQLのレコードロック • 外部キーとロック 3. ロックに関するエラーと対処方法 • ロック待ちタイムアウト • デッドロック 4. 質疑応答
6. なぜ、ロックが必要か？ 複数のトランザクションを安全に並列実行するため
7. ロックのない世界 UPDATE zaiko_tbl SET 在庫 = 在庫-10 UPDATE zaiko_tbl SET 在庫 = 在庫-20 在庫を読み出す 在庫を読み出す 書き込むべき値を計算 （100 – 20) 値 (80) を書き込む 書き込むべき値を計算 （100 – 10) 値 (90) を書き込む 在庫 100 在庫 90 在庫 80 不整合！
8. ロックのある世界 在庫 100 UPDATE zaiko_tbl SET 在庫 = 在庫-10 UPDATE zaiko_tbl SET 在庫 = 在庫-20 在庫を読み出す 在庫を読み出す 書き込むべき値を計算 （90 – 20) 値 (70) を書き込む 書き込むべき値を計算 （100 – 10) 値 (90) を書き込む ロック解放待 ち 在庫 90 在庫 70 ロック 中 ロック 中
9. なぜ、ロックが必要か？ • 高い性能(並列性) と安全性(データの整合性)の両立のため • 互いに影響しない処理は並行に実行 • 互いに影響しあう処理はロックによりシリアルに実行
10. 目次 1. ロックが必要な理由 2. ロックの範囲 • MySQLのレコードロック • 外部キーとロック 3. ロックに関するエラーと対処方法 • ロック待ちタイムアウト • デッドロック 4. 質疑応答
11. レコードロック ▪データ mysql> select * from lockt; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec) ▪クエリー mysql> UPDATE lockt SET favorite = ‘Banana' WHERE favorite = 'Orange' AND addr = ‘Tokyo'; どの行がロックされ る？ ▪テーブル定義 CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`)) ENGINE=InnoDB;
12. レコードロック ▪テーブル定義 CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`)) ENGINE=InnoDB; ▪ロック mysql> UPDATE lockt SET favorite = ‘Banana' WHERE favorite = 'Orange' AND addr = ‘Tokyo'; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec)
13. レコードロック ▪ロック mysql> UPDATE lockt SET favorite = ‘Banana' WHERE favorite = 'Orange' AND addr = ‘Tokyo'; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec) ▪テーブル定義 CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `idx_addr` (`addr`)) ENGINE=InnoDB;
14. レコードロック ▪テーブル定義 CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `idx_addr` (`addr`)) ENGINE=InnoDB; ▪ロック mysql> UPDATE lockt SET favorite = ‘Banana' WHERE favorite = 'Orange' AND addr = ‘Tokyo'; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec) インデックス追加で ロックの粒度が変わる
15. レコードロック ▪テーブル定義 CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `idx_fav_addr` (`favorite`,`addr`) ) ENGINE=InnoDB; ▪ロック mysql> UPDATE lockt SET favorite = ‘Banana' WHERE favorite = 'Orange' AND addr = ‘Tokyo'; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec)
16. レコードロック ▪テーブル定義 CREATE TABLE `lockt` ( `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `favorite` varchar(255) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `idx_fav_addr` (`favorite`,`addr`) ) ENGINE=InnoDB; ▪ロック mysql> UPDATE lockt SET favorite = ‘Banana' WHERE favorite = 'Orange' AND addr = ‘Tokyo'; +----+-------+----------+---------+ | pk | name | favorite | addr | +----+-------+----------+---------+ | 1 | Taro | Apple | Tokyo | | 2 | Jiro | Orange | Tokyo | | 3 | Alice | Orange | London | | 4 | John | Pine | NewYork | +----+-------+----------+---------+ 4 rows in set (0.00 sec)
17. 実行計画 ▪インデックスあり mysql> EXPLAIN UPDATE lockt SET favorite = 'Banana' WHERE favorite = 'Orange' AND addr = 'Tokyo'; +----+-------------+-------+-------+-------------------+-------------+------+ | id | select_type | table | type | key | ref | rows | +----+-------------+-------+-------+-------------------+-------------+------+ | 1 | SIMPLE | lockt | range | idx_favorite_addr | const,const | 1 | +----+-------------+-------+-------+-------------------+-------------+------+ 1 row in set (0.00 sec) ▪インデックスなし mysql> EXPLAIN UPDATE lockt SET favorite = 'Banana' WHERE favorite = 'Orange' AND addr = 'Tokyo'; +----+-------------+-------+-------+---------+------+------+ | id | select_type | table | type | key | ref | rows | +----+-------------+-------+-------+---------+------+------+ | 1 | SIMPLE | lockt | index | PRIMARY | NULL | 4 | +----+-------------+-------+-------+---------+------+------+ 1 row in set (0.00 sec) フルスキャン
18. InnoDBのロック • 「条件に合致した行」に対するロックではなく、「スキャンしたインデック ス」に対するロック • ロック範囲は実行計画に依存する • 適切にチューニングされていないクエリは余計な行もロックする
19. TIPS:大量DELETE時のロック回避 • 普通にDELETE • 広範囲のレコードを長時間にわたってロック mysql> DELETE FROM table WHERE created_at < ‘2021-12-01’; +-------+-------+---------------+ | pk | name + created_at | +-------+-------+---------------+ | 1 | xxxx + 2021-10-03 | | 2 | xxxx + 2021-11-14 | ： | 10000 | xxxx + 2021-11-30 | ： | 20000 | xxxx + 2021-12-05 | +-------+-------+---------------+
20. TIPS:大量DELETE時のロック回避 • まず、対象をSELECT • 小分けにしてDELETE mysql> SELECT id FROM table WHERE created_at < ‘2021-12-01’; mysql> DELETE FROM table WHERE id IN (1,2,3,4….) +-------+-------+---------------+ | pk | name + created_at | +-------+-------+---------------+ | 1 | xxxx + 2021-10-03 | | 2 | xxxx + 2021-11-14 | ： | 10000 | xxxx + 2021-11-30 | ： | 20000 | xxxx + 2021-12-05 | +-------+-------+---------------+
21. 目次 1. ロックが必要な理由 2. ロックの範囲 • MySQLのレコードロック • 外部キーとロック 3. ロックに関するエラーと対処方法 • ロック待ちタイムアウト • デッドロック 4. 質疑応答
22. 外部キー • 子テーブルへINSERT、UPDATEした際、親テーブルの共有ロックを取る • 子テーブルのトランザクションが完了するまで、親テーブルの該当行が更新でき なくなる
23. 外部キー ▪userテーブル +----+-----------+ | pk | user_name | +----+-----------+ | 1 | user1 | | 2 | user2 | | 3 | user3 | +----+-----------+ ▪user_itemテーブル +----+---------+-----------+ | pk | user_id | item_name | +----+---------+-----------+ | 1 | 1 | item1 | | 2 | 2 | item2 | +----+---------+-----------+ CREATE TABLE `user_item` ( `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `item_name` varchar(255) NOT NULL, PRIMARY KEY (`pk`), UNIQUE KEY `pk` (`pk`), KEY `user_id` (`user_id`), CONSTRAINT `user_item_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`pk`) ) ENGINE=InnoDB
24. 外部キー ▪userテーブル +----+-----------+ | pk | user_name | +----+-----------+ | 1 | user1 | | 2 | user2 | | 3 | user3 | +----+-----------+ 3 rows in set (0.00 sec) mysql> BEGIN; mysql> INSERT INTO user_item 　　　　(pk, user_id, item_name) VALUES(3, 3, 'item3'); ロックされる
25. 外部キー ▪userテーブル +----+-----------+ | pk | user_name | +----+-----------+ | 1 | user1 | | 2 | user2 | | 3 | user3 | +----+-----------+ 3 rows in set (0.00 sec) mysql> BEGIN; mysql> UPDATE user_item SET user_id = 1 WHERE pk = 2; ロックされる
26. 目次 1. ロックが必要な理由 2. ロックの範囲 • MySQLのレコードロック • 外部キーとロック 3. ロックに関するエラーと対処方法 • ロック待ちタイムアウト • デッドロック 4. 質疑応答
27. ロックに関するエラー • ERROR 1205 (HY000): Lock wait timeout exceeded • これはロック待ちでタイムアウト（デッドロックではない） • ERROR 1213 (40001): Deadlock found when trying to get lock • これはデッドロック
28. ロックに関するエラー • ERROR 1205 (HY000): Lock wait timeout exceeded • これはロック待ちでタイムアウト（デッドロックではない） • ERROR 1213 (40001): Deadlock found when trying to get lock • これはデッドロック
29. Lock wait timeout exceeded • 先行するトランザクションが保持しているロックの解放を待っている時間が innodb_lock_wait_timeout パラメータの値を超えるとタイムアウト • デフォルト値は innodb_lock_wait_timeout = 50 • デッドロックよりユーザ体験を損ねる • タイムアウトは50秒まってエラー • デッドロックは即時エラー
30. • 赤矢印部分の時間が innodb_lock_wait_timeout を超えるとT2はエラー Lock wait timeout exceeded TRANSACTION 1 TRANSACTION 2 BEGIN UPDATE 行A BEGIN UPDATE 行A 行A 時間 COMMIT ロック解放待 ち ロック保 持
31. Rails サンプルコード (1) def cleanup id = params[:id] histories = History.find([1, 10000000]) histories.destroy_all # すごい時間のかかるDELETE respond_to do |format| format.html { redirect_to ‘/tasks’, notice: ‘削除しました。’ } format.json { render :show, status: :created, location: task } end end • DELETE対象に対するロックで長時間待たされる
32. Rails サンプルコード (2) def update id = params[:id] task = Task.find(id) <snip> Task.transaction do # BEGIN task.save # UPDATE sleep 60 #時間のかかる（SQLではない）処理 end # COMMIT <snip> end • ロック保持期間 • 󰢃 クエリの実行中 • 󰢏 トランザクションの実行中
33. ロックタイムアウトの原因調査方法 • 基本的にはパフォーマンス問題 • innodb_lock_wait_timeout を超えるトランザクションが存在する
34. 手がかりの見つけ方 1. Bugsnag でエラー発生時間と処理内容を確認する 2. Datadog APM でエラーを食らったSQLを確認する • 該当テーブル or 該当テーブルの子テーブル（FKありの場合）に対して、長時間の トランザクションが実行中であったはず 3. Cloud Watch Logs を利用して、スロークエリログから生SQLを入手する
35. Bugsnag • いつ発生したか？どんな処理・テーブルでエラーを食らったか？ 社外非公開
36. Bugsnag • Bugsnag から Datadog の trace id を取る 社外非公開 社外非公開
37. Datadog • https://app.datadoghq.com/apm/trace/<trace_id> • 実際のSQLが確認可能 • ここで確認できるのは「タイムアウトを食らったSQL」（被害者） • 原因は別 社外非公開
38. Datadog • APMで該当時間前後のアクセスログを Duration (実行時間) でソートする • エラー発生時刻以降に、50秒+αで正常終了している処理が原因の可能性が高い 原因っぽい 社外非公開
39. Cloud Watch Logs • 生ログが確認できる。顧客依存の問題の場合の手がかりに。 • スロークエリログにはエラーになったSQLも記録される • 手順 • 社内ドキュメントURL
40. ロックに関するエラー • ERROR 1205 (HY000): Lock wait timeout exceeded • これはロック待ちでタイムアウト（デッドロックではない） • ERROR 1213 (40001): Deadlock found when trying to get lock • これはデッドロック
41. デッドロックとは • 同じリソース（行）のロックを互いに取り合っている状態 • データベースの不具合ではない TRANSACTION 1 TRANSACTION 2 BEGIN UPDATE 行A UPDATE 行B BEGIN UPDATE 行B UPDATE 行A 行A 行B ロック獲得待ち ロック獲得待ち
42. デッドロックとは • 同じリソース（行）のロックを取り合っている状態 • データベースの不具合ではない TRANSACTION 1 TRANSACTION 2 BEGIN UPDATE 行A UPDATE 行B BEGIN UPDATE 行B UPDATE 行A 行A 行B デッドロック状態に陥った場合、 MySQLは片方のトランザクションを エラー（ロールバック）にし、もう片方を救う
43. デッドロックの解析方法
44. デッドロック解析手順 1. Bugsnag でデッドロックのログを確認する 2. 該当時間のDBのエラーログを Cloud Watch Logs から取得する 3. Bugsnag の情報とDBエラーログを突き合わせて、原因を特定する
45. Bugsnag • 「Deadlock found 〜」のログを検索する 社外非公開
46. Bugsnag • いつ発生したか？どんな処理・テーブルでエラーを食らったか？ • ここでわかるのは 強制ロールバックされた側の情報。救われた側はエラーにはならない 社外非公開
47. Cloud Watch Logs • MySQL のエラーログを見る • Bugsnagでエラーが検知された時間帯にログが出ているはず ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-06-18 15:32:07 7f76dd7c1700 *** (1) TRANSACTION: TRANSACTION 2464, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 6, OS thread handle 0x7f76dd77f700, query id 150 localhost root updating update lockt2 set name = 'hogehoge' where pk = 2 <snip>
48. DBのエラーログの読み方(1) TRANSACTION 1 TRANSACTION 2 BEGIN クエリ? クエリ? 行? 行? クエリ? クエリ? • ログから競合したトランザクションの内容とロックの状況を特定する • 「強制ロールバックを食らった側」の処理は Bugsnag も手がかりになる ERR BEGIN COMMIT ？ ？ ？ ？ 社外非公開
49. DBのエラーログの読み方(2) ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-06-18 15:32:07 7f76dd7c1700 *** (1) TRANSACTION: TRANSACTION 2464, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 6, OS thread handle 0x7f76dd77f700, query id 150 localhost root updating update lockt2 set name = 'hogehoge' where pk = 2 <snip> *** (2) TRANSACTION: TRANSACTION 2465, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f76dd7c1700, query id 151 localhost root updating update lockt1 set name = 'hogehoge' where pk = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2465 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; <snip> *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt1` trx id 2465 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000208; asc ;; 1: len 6; hex 00000000099b; asc ;; 2: len 7; hex ef000001c20110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** WE ROLL BACK TRANSACTION (2) ・トランザクションの最後のクエリと対象テーブルを読みとく ・トランザクションの最後のクエリ以外は推測するしかない！ TRANSACTION 1 TRANSACTION 2 BEGIN クエリ? UPDATE lockt2 lockt1 pk=2 lockt2 pk=2 クエリ? UPDATE lockt1 ERR BEGIN COMMIT ？ ？ ？ ？
50. DBのエラーログの読み方(3) ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-06-18 15:32:07 7f76dd7c1700 *** (1) TRANSACTION: TRANSACTION 2464, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 6, OS thread handle 0x7f76dd77f700, query id 150 localhost root updating update lockt2 set name = 'hogehoge' where pk = 2 <snip> *** (2) TRANSACTION: TRANSACTION 2465, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f76dd7c1700, query id 151 localhost root updating update lockt1 set name = 'hogehoge' where pk = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2465 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; <snip> *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt1` trx id 2465 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000208; asc ;; 1: len 6; hex 00000000099b; asc ;; 2: len 7; hex ef000001c20110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** WE ROLL BACK TRANSACTION (2) ・どちらのトランザクションが強制ロールバックされたか TRANSACTION 1 TRANSACTION 2 BEGIN クエリ? UPDATE lockt2 lockt1 pk=2 lockt2 pk=2 クエリ? UPDATE lockt1 ERR BEGIN COMMIT ？ ？ ？ ？
51. DBのエラーログの読み方(4) ------------------------ LATEST DETECTED DEADLOCK ------------------------ <snip> *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2464 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000992; asc ;; 2: len 7; hex e8000001bd0110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** (2) TRANSACTION: TRANSACTION 2465, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f76dd7c1700, query id 151 localhost root updating update lockt1 set name = 'hogehoge' where pk = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2465 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; <snip> *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt1` trx id 2465 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000208; asc ;; 1: len 6; hex 00000000099b; asc ;; 2: len 7; hex ef000001c20110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** WE ROLL BACK TRANSACTION (2) ・どのテーブルのロックを待っているか ・複数のテーブルを扱っている場合や外部キーを利用している場合 にテーブルを絞り込むのに役立つ TRANSACTION 1 TRANSACTION 2 BEGIN クエリ? UPDATE lockt2 lockt1 pk=2 lockt2 pk=2 クエリ? UPDATE lockt1 ERR BEGIN COMMIT ？ 待ち ？ 待ち
52. DBのエラーログの読み方(5) ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-06-18 15:32:07 7f76dd7c1700 *** (1) TRANSACTION: TRANSACTION 2464, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 6, OS thread handle 0x7f76dd77f700, query id 150 localhost root updating update lockt2 set name = 'hogehoge' where pk = 2 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2464 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000204; asc ;; 1: len 6; hex 000000000992; asc ;; 2: len 7; hex e8000001bd0110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; *** (2) TRANSACTION: TRANSACTION 2465, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f76dd7c1700, query id 151 localhost root updating update lockt1 set name = 'hogehoge' where pk = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 22 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt2` trx id 2465 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; <snip> *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`lockt1` trx id 2465 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0 0: len 6; hex 000000000208; asc ;; 1: len 6; hex 00000000099b; asc ;; 2: len 7; hex ef000001c20110; asc ;; 3: len 8; hex 0000000000000001; asc ;; 4: len 4; hex 5461726f; asc Taro;; 5: len 5; hex 4170706c65; asc Apple;; 6: len 5; hex 546f6b796f; asc Tokyo;; ・HOLDS THE LOCK(S) はTRANSACTION2側しか表示してくれ ない TRANSACTION 1 TRANSACTION 2 BEGIN クエリ? UPDATE lockt2 lockt1 pk=2 lockt2 pk=2 クエリ? UPDATE lockt1 ERR BEGIN COMMIT ？ 待ち 待ち ロック
53. 残りのクエリ推測するしかない • A) エラーになっているトランザク ション。アプリのログから辿れる。 テーブル lockt2 を含んでいるは ず（もしくは、lockt2 の子テーブ ル）。 • B) テーブル lockt1 を含んでいる はず（もしくは、lockt1 の子テー ブル）。 • C) T2が待っている lockt1 の ロックを保持 TRANSACTION 1 TRANSACTION 2 BEGIN クエリB UPDATE lockt2 lockt1 pk=2 lockt2 pk=2 クエリA UPDATE lockt1 ERR BEGIN COMMIT (C) 待ち 待ち ロック
54. 答え TRANSACTION 1 TRANSACTION 2 BEGIN UPDATE lockt1 WHERE pk=2 UPDATE lockt2 WHERE pk=2 BEGIN UPDATE lockt2 WHERE pk=2 UPDATE lockt1 WHERE pk=2 lockt1 pk=2 lockt2 pk=2 ERR COMMIT
55. ANDPADでのdeadlock 例 • 1時間に１〜２件発生していた users.id = 320208 TRANSACTION (A) TRANSACTION (B) BEGIN UPDATE users BEGIN INSERT or UPDATE usersの子テーブル 外部キーによる 共有ロック UPDATE users 排他ロックが 取れない 排他ロックが 取れない
56. デッドロックの対処方法 1. トランザクションを再実行する（リトライ） • お手軽 • FE→API→DB なら、FEからAPIにリトライすると楽 2. ロック時間を短くする • SQLチューニング • BEGIN〜COMMIT の間で時間のかかる処理をしない • トランザクションを小さく（こまめにCOMMIT）する 3. ロックの取り合いを防ぐ • 更新する順序を揃える
57. まとめ
58. まとめ • MySQL (InnoDB) のロック範囲は実行計画に依存する • 外部キーを持つテーブルへの更新は親テーブルのロックを取る • ロックタイムアウトとデッドロックは別物 • デッドロックはエラーログから解析する • こまったら、Slack で 相談しよう！ • #dev_dbパフォーマンスチューニング
59. 参考資料 • MySQL SQLチューニング 勉強会 • 社内ドキュメント • MySQL ロック勉強会（DDL編） • https://tech.andpad.co.jp/entry/2020/08/18/132130 • Datadog APM を使ったパフォーマンス分析 勉強会 • https://tech.andpad.co.jp/entry/2021/02/10/170000 • データベース利用規約／SQLレビュー観点 • 社内ドキュメント
60. Thanks