MySQL vs. TiDB-分散トランザクションの比較検証(2): MySQL XA

##### [前回] [MySQL vs. TiDB-分散トランザクションの比較検証(1): トランザクションの基本](https://qiita.com/mingchun_zhao/items/0eca47a4f0f8ee43c358)

## はじめに

今回は、MySQLの分散トランザクションについて検証します。

## XA(eXtended Architecture)のおさらい
MySQL 5.0からグローバルトランザクションのXAがサポートされました。
構成要素です。
- RM(リソースマネージャー)
    - トランザクションリソースへのアクセスを提供
    - MySQLサーバーが、RMとして動作
- TM(トランザクションマネージャー)
    - グローバルトランザクション内の個々のトランザクションを調整
    - アプリケーション(クライアント)が、TMとして動作
        - XAを実装しMySQLサーバーに接続
### XAは2フェーズコミット(2相コミット)プロトコルを使用
グローバルトランザクションは、以下2フェーズで実行される
- 第1フェーズ: コミットの要求
    - TMは、グローバルトランザクションに参加するすべてのブランチに対しコミットメッセージを準備
    - RMは、アクション結果をストレージに記録し、コミットの準備が確認できたら、第2フェーズを実行
- 第2フェーズ: 実際のコミット
    - TMは、関係するすべてのブランチから受信したレスポンスが
        - すべてOKの場合、すべてのブランチにコミットを指示
        - いずれかがNOの場合、すべてのブランチにロールバックを指示

### XAトランザクションの処理フロー

TMとRMを入れたXAのシーケンス図を描いてみました。
QiitaがPlantUMLに対応していました、すごい。

```plantuml
@startuml
entity "TM(アプリケーション)" as app #lightskyblue
entity "RM1(データベース1)" as db1 #hotpink
entity "RM2(データベース2)" as db2 #hotpink

== 第1フェーズ ==
app -> db1 : XA START
app -> db2 : XA START
app --> db1 : 更新DML(INSERT/UPDATE/DELETE)
app --> db2 : 更新DML(INSERT/UPDATE/DELETE)
app -> db1 : XA END
app -> db2 : XA END
app -> db1 : XA PREPARE
app -> db2 : XA PREPARE
app --> db1 : XA RECOVER
app --> db2 : XA RECOVER
app <- db1 : 応答(OK/NG)
app <- db2 : 応答(OK/NG)
== 第2フェーズ ==
app -> db1 : XA COMMIT/ROLLBACK
app -> db2 : XA COMMIT/ROLLBACK
@enduml
```

- TMは複数RMとコミュニケーションし、グローバルトランザクション内の個々のトランザクション/ブランチを処理

### XAトランザクションのステートメント
- ステートメントのフォーマット
    - XAキーワード
    - 実行するアクション
    - XAトランザクションの一意識別子
        - 文字列を用いたグローバルトランザクション識別子
        - ブランチ識別子とフォーマットIDも指定可能
            - ブランチ識別子は、ローカルトランザクションを識別する
            - フォーマットIDは、グローバルトランザクション識別子およびブランチ識別子で使用されるフォーマットを識別するための数値
- 各ステートメントの説明
    - `XA START/BEGIN`
        - トランザクションを開始し、ACTIVE状態に
            - グローバルトランザクション識別子を定義
    - `XA END`
        - トランザクションをIDLE状態に
    - `XA PREPARE`
        - トランザクションを準備し、PREPARED状態に
    - `XA COMMIT [ONE PHASE]`
        - 準備されたトランザクションをコミットし、トランザクションを終了
        - `ONE PHASE`オプションの場合
            - `PREPARE`と`COMMIT`は1ステップで実行され
            - トランザクションを終了
    - `XA ROLLBACK`
        - ロールバックし、トランザクションを終了
    - `XA RECOVER`
        - PREPARED状態のXAトランザクションをすべてリスト表示

## いよいよ、XAの検証です

### 検証環境

```
CPU: Intel(R) Core(TM) 3.30GHz(4コア8スレッド)
メモリ: 16 GB
OS: Ubuntu 20.04 LTS
DB: MySQL 8.0.28
```

ちなみに、Ubuntuは、Windows 10で以下のように管理者権限で導入しました。

```
> wsl --install -d Ubuntu-20.04
```

### 事前準備

操作用端末を開きます。

#### MySQLをインストール

```
$ sudo apt install mysql-server
$ sudo service mysql start
$ sudo mysql_secure_installation
```

#### MySQLユーザーにXA_RECOVER_ADMIN権限を付与

MySQL 8.0で、 `XA RECOVER`は`XA_RECOVER_ADMIN`権限を持つユーザーのみ許可されます。
ユーザー`user`に`XA RECOVER`ステートメントの実行を許可するコマンド例:
```
mysql> GRANT XA_RECOVER_ADMIN ON *.* TO 'user'@'localhost';
```
#### データベースとテーブルを作成

- mysqlコマンドからMySQL接続
```
$ mysql -u user -p
```

- データベース作成
```
mysql> CREATE DATABASE test;
mysql> USE test
```

- テーブル作成
```
mysql> CREATE TABLE theater (
  seat_id INT PRIMARY KEY,
  ordered ENUM('YES', 'NO') DEFAULT 'NO'
);
```
```
mysql> COMMIT;
```

### 検証1: 通常パターン
- mysqlコマンドでMySQL接続し、データベース指定
```
$ mysql -u user -p
mysql> USE test;
```
- XAトランザクションを開始
`myxa`は、XAトランザクションの一意識別子
```
mysql> XA START 'myxa';
```
- 1行INSERT
```
mysql> INSERT INTO theater VALUES (1, 'NO');
```
- `XA END`実行し、トランザクションをIDLE状態に
```
mysql> XA END 'myxa';
```
- `XA PREPARE`実行し、トランザクションを準備
```
mysql> XA PREPARE 'myxa';
```
- `XA RECOVER`実行し、PREPARED状態のXAトランザクションを確認
```
mysql> XA RECOVER;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
|        1 |            4 |            0 | myxa |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)
```

- `XA COMMIT`実行し、トランザクションをコミット
```
mysql> XA COMMIT 'myxa';
```
- 再度`XA RECOVER`実行し、PREPARED状態のXAトランザクションを確認
```
mysql> XA RECOVER;
Empty set (0.00 sec)
```
コミットされたので、PREPARED状態のXAトランザクションは存在しません。
- SELECT実行し、検索
```
mysql> SELECT * FROM theater;
+---------+---------+
| seat_id | ordered |
+---------+---------+
|       1 | NO      |
+---------+---------+
1 row in set (0.00 sec)
```

結果、期待とおり1レコードヒットしました。

### 検証2: 異常パターン
操作途中でクライアントが異常終了した、といったシナリオです。

- mysqlコマンドでMySQL接続
```
$ mysql -u user -p
mysql> USE test;
```
- XAトランザクションを開始
```
mysql> XA START 'myxa';
```
- 1行INSERT
```
mysql> INSERT INTO theater VALUES (2, 'NO');
```

- `XA END`実行し、IDLE状態に
```
mysql> XA END 'myxa';
```

- `XA PREPARE`実行し、トランザクションを準備
```
mysql> XA PREPARE 'myxa';
```
- ここで、Ctrl+Dでセッションを強制終了します

- 再度、mysqlコマンドでMySQL接続
```
$ mysql -u user -p
mysql> USE test;
```

- `XA RECOVER`実行し、PREPARED状態のXAトランザクションを確認

```
mysql> XA RECOVER;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
|        1 |            4 |            0 | myxa |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)
```

- `XA COMMIT`実行し、PREPARED状態のトランザクションをコミット
```
mysql> XA COMMIT 'myxa';
```

- SELECT実行し、seat_id=2を検索
```
mysql> SELECT * FROM theater WHERE seat_id=2;
+---------+---------+
| seat_id | ordered |
+---------+---------+
|       2 | NO      |
+---------+---------+
1 row in set (0.00 sec)
```

結果、期待とおり1レコードヒットしました。
第1フェーズの`XA PREPARE`後、クライアント側でセッション異常終了しても、
第2フェーズで、別セッションから`XA COMMIT`続行できました、すごい。

## おわりに

MySQL分散トランザクションの基本機能を検証しました。
次回は、もう少し複雑なパターンで検証してみます。
お楽しみに。

##### [次回] [MySQL vs. TiDB-分散トランザクションの比較検証(3): MySQL内部XA](https://qiita.com/mingchun_zhao/items/12008d82af315c2cd163)