MySQL vs. TiDB-分散トランザクションの比較検証(13(最終回)): デッドロック(MySQL)

##### [前回] [MySQL vs. TiDB-分散トランザクションの比較検証(12): デッドロック(TiDB)](https://qiita.com/mingchun_zhao/items/ccbbd39955b6b762ab07)

## はじめに
今回は、MySQL XAのデッドロック検証です。

## 検証シナリオ(前回のTiDBと同じ)

二つの分散トランザクションから同時に振込みボタンが押された、
といったシナリオです。

|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2523098/18f6d8a5-40a3-08fa-68fa-e10f1f1037cf.png)|
|--|

デッドロックが起きる条件のおさらいです。
※ 更新処理の順序と対象が肝

```plantuml
@startuml
actor "トランザクションA" as TA #lightskyblue
database "A銀行残高レコード" as R1 #hotpink
database "B銀行残高レコード" as R2 #hotpink
actor "トランザクションB" as TB #lightskyblue

TA -> R1 : 「更新1」開始、ロック成功
activate R1
TB -> R2 : 「更新2」開始、ロック成功
activate R2
TA ->x R2 : 「更新3」開始、ロック失敗、「更新2」のロック解除待ち
TB ->x R1 : 「更新4」開始、ロック失敗、「更新1」のロック解除待ち
@enduml
```

- 更新1: トランザクションAにより
    - A銀行残高レコードから10万円引く
    - レコードがロックされた状態
- 更新2: トランザクションBにより
    - B銀行残高レコードから10万円引く
    - レコードがロックされた状態
- 更新3: トランザクションAにより
    - B銀行残高レコードに10万円足す
    - `更新2`によりレコードロック中のため、その解除を待つ。。。
- 更新4: トランザクションBにより
    - A銀行残高レコードに10万円足す
    - `更新1`によりレコードロック中のため、その解除を待つ。。。

お互い延々に待ち続ける状態に。。。

## 検証準備

前々回の[検証準備](https://qiita.com/mingchun_zhao/items/c8b0cac8fa9d7d647c45.md)をご参照ください。

- 端末を三つ用意します
    - 端末1: MySQL起動と確認作業
    - 端末2: トランザクションAを実行
    - 端末3: トランザクションBを実行

## 端末1から、A、B銀行残高を確認

- MySQLを起動

```
$ sudo service mysql start
```

- MySQLに接続
```
$ sudo mysql -u user -p
```

- A、B銀行残高を確認
```
mysql> select * from a_bank.account;
+------+-----------+
| name | balance   |
+------+-----------+
| foo  | 500000.00 |
+------+-----------+
1 row in set (0.00 sec)

mysql> select * from b_bank.account;
+------+-----------+
| name | balance   |
+------+-----------+
| foo  | 500000.00 |
+------+-----------+
1 row in set (0.00 sec)
```

## 端末2のセッションから、トランザクションAを開始

- MySQLに接続

```
$ sudo mysql -u user -p
```

- トランザクション開始

```
mysql> XA START 'A';
```

### A銀行残高レコードから10万円引く

```
mysql> update a_bank.account set balance=balance-100000 where name='foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```

## 端末3のセッションから、トランザクションBを開始

- MySQLに接続

```
$ sudo mysql -u user -p
```

- トランザクション開始

```
mysql> XA START 'B';
```

### B銀行残高レコードから10万円引く

```
mysql> update b_bank.account set balance=balance-100000 where name='foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```

## 端末1のセッションから、トランザクション状態確認

- `information_schema.innodb_trx`テーブルから、InnoDB内で実行中のトランザクション状態を確認

```
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 42006
                 trx_state: RUNNING
               trx_started: 2022-05-24 07:39:12
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 15
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
*************************** 2. row ***************************
                    trx_id: 42005
                 trx_state: RUNNING
               trx_started: 2022-05-24 07:38:35
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 14
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
2 rows in set (0.01 sec)
```

トランザクションA、Bともに、RUNNING状態です。

## 端末2のセッションから、トランザクションAを続行

### B銀行残高レコードに10万円足す

```
mysql> update b_bank.account set balance=balance+100000 where name='foo';
待ち状態。。。
```

トランザクションBにより、B銀行残高レコードがロック中のため、
そのロック解除待ち状態。。。

ロック待ちがタイムアウトなる前に、素早く次の処理を行います。
さもなければ、以下のエラーでupdate中断され、Deadlockは再現できません。
```
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```

## 端末3のセッションから、トランザクションBを続行

### A銀行残高レコードに10万円足す

```
mysql> update a_bank.account set balance=balance+100000 where name='foo';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
```

トランザクションAにより、A銀行残高レコードがロック中のため、
そのロック解除待ちに陥るかと思いきや、
すぐDeadlockが検出され、処理中断されました。

MySQLも、Deadlockにより待ち続けることなく、
すぐエラーハンドリングしてくれました、すごい!

## 端末2のセッションを確認

ロック待ち状態だったupdateが正常終了しています。

```
mysql> update b_bank.account set balance=balance+100000 where name='foo';
Query OK, 1 row affected (5.53 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```

## 端末1のセッションから、トランザクション状態確認

- `information_schema.innodb_trx`テーブルから、InnoDB内で実行中のトランザクション状態を確認

```
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 42005
                 trx_state: RUNNING
               trx_started: 2022-05-24 07:38:35
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 6
       trx_mysql_thread_id: 14
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 2
          trx_lock_structs: 4
     trx_lock_memory_bytes: 1128
           trx_rows_locked: 2
         trx_rows_modified: 2
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.00 sec)
```

トランザクションAしか、残っていません。
トランザクションBはどこへ?

- `performance_schema.data_locks`テーブルから、各保留ロックの行と、保留ロックの解放を待機中でブロックされているロックリクエストを確認

```
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140213739495424:1146:140213636645256
ENGINE_TRANSACTION_ID: 42005
            THREAD_ID: 54
             EVENT_ID: 6
        OBJECT_SCHEMA: b_bank
          OBJECT_NAME: account
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140213636645256
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140213739495424:1145:140213636645168
ENGINE_TRANSACTION_ID: 42005
            THREAD_ID: 54
             EVENT_ID: 5
        OBJECT_SCHEMA: a_bank
          OBJECT_NAME: account
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140213636645168
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140213739495424:72:4:2:140213636642256
ENGINE_TRANSACTION_ID: 42005
            THREAD_ID: 54
             EVENT_ID: 5
        OBJECT_SCHEMA: a_bank
          OBJECT_NAME: account
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140213636642256
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'foo'
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140213739495424:73:4:2:140213636642600
ENGINE_TRANSACTION_ID: 42005
            THREAD_ID: 54
             EVENT_ID: 6
        OBJECT_SCHEMA: b_bank
          OBJECT_NAME: account
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140213636642600
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 'foo'
4 rows in set (0.00 sec)
```

ロックが四つ確認できました。

|OBJECT|LOCK_TYPE|LOCK_MODE|
|:--:|:--:|:--:|
|b_bank.account|TABLE|IX|
|a_bank.account|TABLE|IX|
|a_bank.account|RECORD|X,REC_NOT_GAP|
|b_bank.account|RECORD|X,REC_NOT_GAP|

- ロックモードの説明
    - `IX`
        - 排他インテンションロックで、InnoDBのテーブルロックの一種
        - トランザクションが、テーブル内の行レベルで排他ロックが必要であることを示す 
    - `X,REC_NOT_GAP`
        - 排他レコードロック
        - 行レベルの排他制御に必要

- `performance_schema.data_lock_waits`テーブルから、特定ロックを待機しているトランザクション、または特定トランザクションが待機しているロックを確認

``` 
mysql> select * from performance_schema.data_lock_waits\G
Empty set (0.00 sec)
```

ロック待ちは既に存在しません。


## 端末3のセッションから、トランザクションBを確認

- トランザクションBをIDLE状態にしてみる

```
mysql> XA END 'B';
ERROR 1614 (XA102): XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
```

デッドロックにより、既にロールバックされていますね。

## 端末2のセッションから、トランザクションAをロールバック

- トランザクションAをIDLE状態に

```
mysql> XA END 'A';
Query OK, 0 rows affected (0.00 sec)
```

- トランザクションAをロールバック

```
mysql> XA ROLLBACK 'A';
Query OK, 0 rows affected (0.01 sec)
```

これで、MySQL XAのデッドロック検証が終わりました。

## おわりに

13回にわたる長編となってしまいました、今回が最終回です。

MySQLとTiDBの分散トランザクションを、いくつかの側面から比較しました。
それぞれの特徴を加味し、ユースケースに応じて利用したほうがよさそうです。

拙い文章を読んでいただき誠にありがとうございました。