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

Transcript

1. MySQLのロックについて
   〜基本編〜
   2022.03.25
   

   View Slide

2. 自己紹介
   • 三谷　智史（@mita2）
   • 仕事その１　とある企業のMySQL チームの中間管理職
   • 仕事その２　ANDPADのデータベース技術顧問
   • 月に２日、お邪魔してます
   • 　
   • http://mita2db.hateblo.jp/
   

   View Slide

3. 本日のゴール
   ロック関連のエラーの解析ができるようになる
   

   View Slide

4. 目次
   1. ロックが必要な理由
   2. ロックの範囲
   • MySQLのレコードロック
   • 外部キーとロック
   3. ロックに関するエラーと対処方法
   • ロック待ちタイムアウト
   • デッドロック
   4. 質疑応答
   今日取り上げないこと
   ・DDLのロック、metadatalock
   ・ギャップロック/ネクストキーロック
   ・シャドーロック
   ・TX分離レベルがREPEATABLE-READ以外のロックの挙
   動
   

   View Slide

5. 目次
   1. ロックが必要な理由
   2. ロックの範囲
   • MySQLのレコードロック
   • 外部キーとロック
   3. ロックに関するエラーと対処方法
   • ロック待ちタイムアウト
   • デッドロック
   4. 質疑応答
   

   View Slide

6. なぜ、ロックが必要か？
   複数のトランザクションを安全に並列実行するため
   

   View Slide

7. ロックのない世界
   UPDATE zaiko_tbl SET 在庫 = 在庫-10 UPDATE zaiko_tbl SET 在庫 = 在庫-20
   在庫を読み出す
   在庫を読み出す
   書き込むべき値を計算
   （100 – 20)
   値 (80) を書き込む
   書き込むべき値を計算
   （100 – 10)
   値 (90) を書き込む
   在庫
   100
   在庫
   90
   在庫
   80
   不整合！
   

   View Slide

8. ロックのある世界
   在庫
   100
   UPDATE zaiko_tbl SET 在庫 = 在庫-10 UPDATE zaiko_tbl SET 在庫 = 在庫-20
   在庫を読み出す
   在庫を読み出す
   書き込むべき値を計算
   （90 – 20)
   値 (70) を書き込む
   書き込むべき値を計算
   （100 – 10)
   値 (90) を書き込む
   ロック解放待
   ち
   在庫
   90
   在庫
   70
   ロック
   中
   ロック
   中
   

   View Slide

9. なぜ、ロックが必要か？
   • 高い性能(並列性) と安全性(データの整合性)の両立のため
   • 互いに影響しない処理は並行に実行
   • 互いに影響しあう処理はロックによりシリアルに実行
   

   View Slide

10. 目次
    1. ロックが必要な理由
    2. ロックの範囲
    • MySQLのレコードロック
    • 外部キーとロック
    3. ロックに関するエラーと対処方法
    • ロック待ちタイムアウト
    • デッドロック
    4. 質疑応答
    

    View Slide

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;
    

    View Slide

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)
    

    View Slide

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;
    

    View Slide

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)
    インデックス追加で
    ロックの粒度が変わる
    

    View Slide

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)
    

    View Slide

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)
    

    View Slide

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)
    フルスキャン
    

    View Slide

18. InnoDBのロック
    • 「条件に合致した行」に対するロックではなく、「スキャンしたインデック
    ス」に対するロック
    • ロック範囲は実行計画に依存する
    • 適切にチューニングされていないクエリは余計な行もロックする
    

    View Slide

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 |
    +-------+-------+---------------+
    

    View Slide

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 |
    +-------+-------+---------------+
    

    View Slide

21. 目次
    1. ロックが必要な理由
    2. ロックの範囲
    • MySQLのレコードロック
    • 外部キーとロック
    3. ロックに関するエラーと対処方法
    • ロック待ちタイムアウト
    • デッドロック
    4. 質疑応答
    

    View Slide

22. 外部キー
    • 子テーブルへINSERT、UPDATEした際、親テーブルの共有ロックを取る
    • 子テーブルのトランザクションが完了するまで、親テーブルの該当行が更新でき
    なくなる
    

    View Slide

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
    

    View Slide

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'); ロックされる
    

    View Slide

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;
    ロックされる
    

    View Slide

26. 目次
    1. ロックが必要な理由
    2. ロックの範囲
    • MySQLのレコードロック
    • 外部キーとロック
    3. ロックに関するエラーと対処方法
    • ロック待ちタイムアウト
    • デッドロック
    4. 質疑応答
    

    View Slide

27. ロックに関するエラー
    • ERROR 1205 (HY000): Lock wait timeout exceeded
    • これはロック待ちでタイムアウト（デッドロックではない）
    • ERROR 1213 (40001): Deadlock found when trying to get lock
    • これはデッドロック
    

    View Slide

28. ロックに関するエラー
    • ERROR 1205 (HY000): Lock wait timeout exceeded
    • これはロック待ちでタイムアウト（デッドロックではない）
    • ERROR 1213 (40001): Deadlock found when trying to get lock
    • これはデッドロック
    

    View Slide

29. Lock wait timeout exceeded
    • 先行するトランザクションが保持しているロックの解放を待っている時間が
    innodb_lock_wait_timeout パラメータの値を超えるとタイムアウト
    • デフォルト値は innodb_lock_wait_timeout = 50
    • デッドロックよりユーザ体験を損ねる
    • タイムアウトは50秒まってエラー
    • デッドロックは即時エラー
    

    View Slide

30. • 赤矢印部分の時間が innodb_lock_wait_timeout を超えるとT2はエラー
    Lock wait timeout exceeded
    TRANSACTION 1 TRANSACTION 2
    BEGIN
    UPDATE 行A BEGIN
    UPDATE 行A
    行A
    時間
    COMMIT
    ロック解放待
    ち
    ロック保
    持
    

    View Slide

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対象に対するロックで長時間待たされる
    

    View Slide

32. Rails サンプルコード (2)
    def update
    id = params[:id]
    task = Task.find(id)
    
    Task.transaction do # BEGIN
    task.save # UPDATE
    sleep 60 #時間のかかる（SQLではない）処理
    end # COMMIT
    
    end
    • ロック保持期間
    • 󰢃 クエリの実行中
    • 󰢏 トランザクションの実行中
    

    View Slide

33. ロックタイムアウトの原因調査方法
    • 基本的にはパフォーマンス問題
    • innodb_lock_wait_timeout を超えるトランザクションが存在する
    

    View Slide

34. 手がかりの見つけ方
    1. Bugsnag でエラー発生時間と処理内容を確認する
    2. Datadog APM でエラーを食らったSQLを確認する
    • 該当テーブル or 該当テーブルの子テーブル（FKありの場合）に対して、長時間の
    トランザクションが実行中であったはず
    3. Cloud Watch Logs を利用して、スロークエリログから生SQLを入手する
    

    View Slide

35. Bugsnag
    • いつ発生したか？どんな処理・テーブルでエラーを食らったか？
    社外非公開
    

    View Slide

36. Bugsnag
    • Bugsnag から Datadog の trace id を取る
    社外非公開
    社外非公開
    

    View Slide

37. Datadog
    • https://app.datadoghq.com/apm/trace/
    • 実際のSQLが確認可能
    • ここで確認できるのは「タイムアウトを食らったSQL」（被害者）
    • 原因は別
    社外非公開
    

    View Slide

38. Datadog
    • APMで該当時間前後のアクセスログを Duration (実行時間) でソートする
    • エラー発生時刻以降に、50秒+αで正常終了している処理が原因の可能性が高い
    原因っぽい
    社外非公開
    

    View Slide

39. Cloud Watch Logs
    • 生ログが確認できる。顧客依存の問題の場合の手がかりに。
    • スロークエリログにはエラーになったSQLも記録される
    • 手順
    • 社内ドキュメントURL
    

    View Slide

40. ロックに関するエラー
    • ERROR 1205 (HY000): Lock wait timeout exceeded
    • これはロック待ちでタイムアウト（デッドロックではない）
    • ERROR 1213 (40001): Deadlock found when trying to get lock
    • これはデッドロック
    

    View Slide

41. デッドロックとは
    • 同じリソース（行）のロックを互いに取り合っている状態
    • データベースの不具合ではない
    TRANSACTION 1
    TRANSACTION 2
    BEGIN
    UPDATE 行A
    UPDATE 行B
    BEGIN
    UPDATE 行B
    UPDATE 行A
    行A
    行B
    ロック獲得待ち
    ロック獲得待ち
    

    View Slide

42. デッドロックとは
    • 同じリソース（行）のロックを取り合っている状態
    • データベースの不具合ではない
    TRANSACTION 1
    TRANSACTION 2
    BEGIN
    UPDATE 行A
    UPDATE 行B
    BEGIN
    UPDATE 行B
    UPDATE 行A
    行A
    行B
    デッドロック状態に陥った場合、
    MySQLは片方のトランザクションを
    エラー（ロールバック）にし、もう片方を救う
    

    View Slide

43. デッドロックの解析方法
    

    View Slide

44. デッドロック解析手順
    1. Bugsnag でデッドロックのログを確認する
    2. 該当時間のDBのエラーログを Cloud Watch Logs から取得する
    3. Bugsnag の情報とDBエラーログを突き合わせて、原因を特定する
    

    View Slide

45. Bugsnag
    • 「Deadlock found 〜」のログを検索する
    社外非公開
    

    View Slide

46. Bugsnag
    • いつ発生したか？どんな処理・テーブルでエラーを食らったか？
    • ここでわかるのは 強制ロールバックされた側の情報。救われた側はエラーにはならない
    社外非公開
    

    View Slide

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
    
    

    View Slide

48. DBのエラーログの読み方(1)
    TRANSACTION 1 TRANSACTION 2
    BEGIN
    クエリ?
    クエリ?
    行?
    行?
    クエリ?
    クエリ?
    • ログから競合したトランザクションの内容とロックの状況を特定する
    • 「強制ロールバックを食らった側」の処理は Bugsnag も手がかりになる
    ERR
    BEGIN
    COMMIT
    ？
    ？
    ？
    ？
    社外非公開
    

    View Slide

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
    
    *** (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;;
    
    *** (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
    ？
    ？
    ？
    ？
    

    View Slide

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
    
    *** (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;;
    
    *** (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
    ？
    ？
    ？
    ？
    

    View Slide

51. DBのエラーログの読み方(4)
    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    
    *** (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;;
    
    *** (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
    ？
    待ち
    ？
    待ち
    

    View Slide

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;;
    
    *** (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
    ？
    待ち 待ち
    ロック
    

    View Slide

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)
    待ち 待ち
    ロック
    

    View Slide

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
    

    View Slide

55. ANDPADでのdeadlock 例
    • 1時間に１〜２件発生していた
    users.id = 320208
    TRANSACTION (A) TRANSACTION (B)
    BEGIN
    UPDATE users
    BEGIN
    INSERT or UPDATE
    usersの子テーブル
    外部キーによる
    共有ロック
    UPDATE users
    排他ロックが
    取れない
    排他ロックが
    取れない
    

    View Slide

56. デッドロックの対処方法
    1. トランザクションを再実行する（リトライ）
    • お手軽
    • FE→API→DB なら、FEからAPIにリトライすると楽
    2. ロック時間を短くする
    • SQLチューニング
    • BEGIN〜COMMIT の間で時間のかかる処理をしない
    • トランザクションを小さく（こまめにCOMMIT）する
    3. ロックの取り合いを防ぐ
    • 更新する順序を揃える
    

    View Slide

57. まとめ
    

    View Slide

58. まとめ
    • MySQL (InnoDB) のロック範囲は実行計画に依存する
    • 外部キーを持つテーブルへの更新は親テーブルのロックを取る
    • ロックタイムアウトとデッドロックは別物
    • デッドロックはエラーログから解析する
    • こまったら、Slack で 相談しよう！
    • #dev_dbパフォーマンスチューニング
    

    View Slide

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レビュー観点
    • 社内ドキュメント
    

    View Slide

60. Thanks
    

    View Slide