LoginSignup
1
0

More than 1 year has passed since last update.

@mingchun_zhao(趙 明春)inＡＲアドバンストテクノロジ株式会社（ARI）

MySQL vs. TiDB-分散トランザクションの比較検証(11): 同時実行制御(MySQL)

Last updated at Posted at 2022-05-16
[前回] MySQL vs. TiDB-分散トランザクションの比較検証(10): 同時実行制御(TiDB)

はじめに

今回はMySQLに対し、XA分散トランザクションの同時実行制御検証です。

まずは、トランザクション分離レベルの理解から

複数トランザクションから同時にデータ更新が行われ競合発生した場合、
分離レベルにより、どこまでデータ整合性が担保されるかが決まるようです。

  • Wikipediaから、トランザクション分離レベルとは

    • データベース管理システム(RDBMS)上で、一括処理(トランザクション)が複数同時に行われた場合
    • どれほどの一貫性、正確性で実行するかを4段階で定義したもの
    • 隔離レベル、独立性レベルとも呼ばれる
    • トランザクションを定義づけるACID特性のうち、I(Isolation、分離性, 独立性)に関する概念である

  • ANSI SQL-92標準で定義されて四つの分離レベル

    • Read Uncommitted
    • Read Committed
    • Repeatable Read
    • Serializable

  • MySQLの分離レベル

    • InnoDBのデフォルト分離レベルはREPEATABLE READ
    • SQL-92標準に記載された4つのトランザクション分離レベルがすべて提供される

  • TiDBの分離レベル

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

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

  • トランザクションAは、A銀行口座からB銀行口座へ10万円振込む
  • トランザクションBは、B銀行口座からA銀行口座へ10万円振込む

検証環境

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

検証準備

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

端末1から、MySQLを起動

  • MySQLインストールおよびXAに必要な設定
    以前の関連記事XAの検証をご参照ください。
  • MySQLを起動
$ sudo service mysql start

データベースとテーブルを作成

※ 前提: MySQLの作業ユーザ(今回はuser)に、データベース作成などの権限が必要です。
今回は検証のため、便宜上すべての権限を付与:

mysql> GRANT ALL PRIVILEGES ON * . * TO 'user'@'localhost';
  • MySQLに接続
$ sudo mysql -u user -p
  • トランザクション分離レベルを確認
mysql> SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
+--------------------------------+-------------------------+
| @@GLOBAL.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

デフォルトのREPEATABLE-READになっています。

  • A銀行のデータベースと口座テーブルを作成
    銀行データベース: a_bank
    口座テーブル: account
    を作成し、レコードをINSERT。
    ユーザー: foo
    残高: 50万円
CREATE DATABASE a_bank;
USE a_bank;
CREATE TABLE account(name VARCHAR(10) PRIMARY KEY, balance DECIMAL(10,2));
INSERT INTO account VALUES('foo', 500000);
COMMIT;
  • B銀行のデータベースと口座テーブルを作成
    銀行データベース: b_bank
    口座テーブル: account
    を作成し、レコードをINSERT。
    ユーザー: foo
    残高: 50万円
CREATE DATABASE b_bank;
USE b_bank;
CREATE TABLE account(name VARCHAR(10) PRIMARY KEY, balance DECIMAL(10,2));
INSERT INTO account VALUES('foo', 500000);
COMMIT;
  • 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に接続し、データベースa_bankを指定
$ sudo mysql -u user -p
mysql> USE a_bank;
  • トランザクション開始
mysql> XA START 'a_bank';
  • A銀行から10万円引き出す
mysql> update a_bank.account set balance=balance-100000 where name='foo';
Rows matched: 1  Changed: 1  Warnings: 0
  • B銀行に10万円振込む
mysql> update b_bank.account set balance=balance+100000 where name='foo';

ここで、端末3のセッションから、トランザクションBの振込み開始

  • MySQLに接続し、データベースb_bankを指定
$ sudo mysql -u user -p
mysql> USE b_bank;
  • トランザクション開始
mysql> XA START 'b_bank';
  • B銀行から10万円引き出す
mysql> update b_bank.account set balance=balance-100000 where name='foo';
# 1分程、待ち状態。。。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

UPDATEがロック待ち状態となり、1分後ぐらい経つとタイムアウトします。
相手(トランザクションA)の更新中は、ロック待ちに陥りますね。

端末2のセッションから、トランザクションAをIDLE状態に

  • XA END実行し、トランザクションをIDLE状態に
mysql> XA END 'a_bank';

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

  • B銀行から10万円引き出す
mysql> update b_bank.account set balance=balance-100000 where name='foo';
# 1分程、待ち状態。。。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

ロック待ちの状況は変わりません。

端末2のセッションから、トランザクションAをPREPARED状態に

  • XA PREPARE実行し、トランザクションを準備
mysql> XA PREPARE 'a_bank';
Query OK, 0 rows affected (0.02 sec)
  • XA RECOVER実行し、PREPARED状態のXAトランザクションを確認
mysql> XA RECOVER;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data   |
+----------+--------------+--------------+--------+
|        1 |            6 |            0 | a_bank |
+----------+--------------+--------------+--------+
1 row in set (0.00 sec)

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

  • B銀行から10万円引き出す
mysql> update b_bank.account set balance=balance-100000 where name='foo';
# 1分程、待ち状態。。。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

今度も、ロック待ちの状況は変わりません。

端末2のセッションから、トランザクションAをコミット

  • XA COMMIT実行し、トランザクションをコミット
mysql> XA COMMIT 'a_bank';
  • 再度XA RECOVER実行し、PREPARED状態のXAトランザクションを確認
mysql> XA RECOVER;
Empty set (0.00 sec)

コミットされたので、PREPARED状態のXAトランザクションは存在しません。

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

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

A銀行からB銀行へ10万円の振込み成功しています。

再度、端末3のセッションから、トランザクション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

別のトランザクションAがコミットされた後ですので、
問題なくUPDATE実行できました。

  • A銀行に10万円振込む
mysql> update a_bank.account set balance=balance+100000 where name='foo';
  • XA END実行し、トランザクションをIDLE状態に
mysql> XA END 'b_bank';
  • XA PREPARE実行し、トランザクションを準備
mysql> XA PREPARE 'b_bank';
  • XA RECOVER実行し、PREPARED状態のXAトランザクションを確認
mysql> XA RECOVER;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data   |
+----------+--------------+--------------+--------+
|        1 |            6 |            0 | b_bank |
+----------+--------------+--------------+--------+
1 row in set (0.00 sec)
  • XA COMMIT実行し、トランザクションをコミット
mysql> XA COMMIT 'b_bank';
Query OK, 0 rows affected (0.02 sec)
  • 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)

銀行Bから銀行Aへ10万円の振込み成功しています。
二つのトランザクション実行結果、
A、B銀行残高ともに元とおりに戻ってしまいましたけど。

終わりに

MySQLに対して、XA分散トランザクションの同時実行制御を検証しました。
トランザクション分離レベルも理解しました。
次回は、分散トランザクションのデッドロック(deadlock)を検証します。
お楽しみに。

[次回] MySQL vs. TiDB-分散トランザクションの比較検証(12): デッドロック(TiDB)
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0