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

##### [前回] [MySQL vs. TiDB-分散トランザクションの比較検証(9): TiDBで銀行振込みのシミュレーション](https://qiita.com/mingchun_zhao/items/d0f9179ddb65e4f9e225)

## はじめに

今回から、分散トランザクションの同時実行制御の検証です。
まずは、TiDBの検証から。

## 同時実行制御(排他制御)とは

- 複数トランザクションが同時実行中の場合、データ整合性(一貫性)を保つための機能
- 複数トランザクションからデータベースにアクセスがあった場合、ロックにより同時実行制御(排他制御)を実現

## 検証シナリオ


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2523098/ef63445b-3cf9-3fbf-4dcf-0397e69a7469.png)

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

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

## 検証準備

- 端末を三つ用意します
    - 端末1: TiDBクラスタを起動
    - 端末2: トランザクションAを実行
    - 端末3: トランザクションBを実行

### 端末1から、TiDBクラスタを起動

```
$ tiup playground --db 2 --pd 3 --kv 3
```

### 端末2から、データベースとテーブルを作成

- TiDBに接続

```
$ tiup client
```

Enterを押すと、コマンドプロンプトが表示されます。

```
my:root@127.0.0.1:4000=>
```

- 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);
```

- 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);
```

- A、B銀行残高を確認

```
select * from a_bank.account;
 name |  balance  
------+-----------
 foo  | 500000.00 
(1 row)

select * from b_bank.account;
 name |  balance  
------+-----------
 foo  | 500000.00 
(1 row)
```

## 検証スタート(悲観的トランザクション)

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

- トランザクションモードを確認

```
select @@tidb_txn_mode;
 @@tidb_txn_mode 
-----------------
 pessimistic 
(1 row)
```

デフォルトの悲観的モードになっています。

- トランザクション開始

```
start transaction;
START TRANSACTION
```

- A銀行から10万円引き出す

```
update a_bank.account set balance=balance-100000 where name='foo';
UPDATE 1
```

- B銀行に10万円振込む

```
update b_bank.account set balance=balance+100000 where name='foo';
UPDATE 1
```

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

- TiDBに接続

```
$ tiup client
```

Enterを押すと、コマンドプロンプトが表示されます。

```
my:root@127.0.0.1:4000=>
```

- トランザクションモードを確認

```
select @@tidb_txn_mode;
 @@tidb_txn_mode 
-----------------
 pessimistic 
(1 row)
```

デフォルトの悲観的モードになっています。

- A、B銀行残高を確認

```
select * from a_bank.account;
 name |  balance  
------+-----------
 foo  | 500000.00 
(1 row)

select * from b_bank.account;
 name |  balance  
------+-----------
 foo  | 500000.00 
(1 row)
```

- トランザクション開始

```
start transaction;
START TRANSACTION
```

- B銀行から10万円引き出す

```
update b_bank.account set balance=balance-100000 where name='foo';
# 1分程、待ち状態。。。
error: mysql: 1205: Lock wait timeout exceeded; try restarting transaction
```

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

では、楽観的トランザクションではどうなるでしょうか。

## 検証やり直し(楽観的トランザクション)

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

- 今までの処理をロールバック

```
rollback;
ROLLBACK
```

- 楽観的モードに切り替え

```
set @@tidb_txn_mode='optimistic';
SET

select @@tidb_txn_mode;
 @@tidb_txn_mode 
-----------------
 optimistic 
(1 row)
```

- トランザクション開始

```
start transaction;
START TRANSACTION
```

- A銀行から10万円引き出す

```
update a_bank.account set balance=balance-100000 where name='foo';
UPDATE 1
```

- B銀行に10万円振り込む

```
update b_bank.account set balance=balance+100000 where name='foo';
UPDATE 1
```

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

- 今までの処理をロールバック

```
rollback;
ROLLBACK
```

- 楽観的モードに切り替え

```
set @@tidb_txn_mode='optimistic';
SET

select @@tidb_txn_mode;
 @@tidb_txn_mode 
-----------------
 optimistic 
(1 row)
```

- トランザクション開始

```
start transaction;
START TRANSACTION
```

- B銀行から10万円引き出す

```
update b_bank.account set balance=balance-100000 where name='foo';
UPDATE 1
```

`UPDATE`がロック待機せず、一瞬で終わりました。
悲観的モードとは挙動が異なりますね。

- A銀行に10万円振込む

```
update a_bank.account set balance=balance+100000 where name='foo';
UPDATE 1
```

ここも一瞬で終了。

### 端末2から、トランザクションのステータスを確認

```
select * from information_schema.tidb_trx\G
*************************** 1. row ***************************
ID: 433218843158773761
START_TIME: 2022-05-15T16:12:16.415+09:00
CURRENT_SQL_DIGEST:
CURRENT_SQL_DIGEST_TEXT:
STATE: Idle
WAITING_START_TIME:
MEM_BUFFER_KEYS: 4
MEM_BUFFER_BYTES: 184
SESSION_ID: 407
USER: root
DB: b_bank
ALL_SQL_DIGESTS: ["f53534242526f5f17dd442bf4429823244510563047efdcc0679741ca5833ea6","df9f53eb4cb1672927cb6374dd27fa80a8124ec0186f390ea41cac113d8428ac","dc5f0aff8b925c386a79b6e33f64e79e814e2310a12f0d793fe9ab4c9c8c0eee","d7815f6adab7ceebe67882038b7d29c081675c0e94751110ff74213523b6998e"]
*************************** 2. row ***************************
ID: 433218876739420161
START_TIME: 2022-05-15T16:14:24.515+09:00
CURRENT_SQL_DIGEST:
CURRENT_SQL_DIGEST_TEXT:
STATE: Idle
WAITING_START_TIME:
MEM_BUFFER_KEYS: 4
MEM_BUFFER_BYTES: 184
SESSION_ID: 409
USER: root
DB:
ALL_SQL_DIGESTS: ["f53534242526f5f17dd442bf4429823244510563047efdcc0679741ca5833ea6","8e412fff994ffcdc77c6516ab298cc58851c07c354eb766a283a16bc997845ab","05704c1de71e0adc626cf220a7fbae005553d5ef74ed6bf44dec053cd656d474"]
```

トランザクションはIdle状態。

#### 端末3から、トランザクションのステータスを確認

```
select * from information_schema.tidb_trx\G
*************************** 1. row ***************************
ID: 433218876739420161
START_TIME: 2022-05-15T16:14:24.515+09:00
CURRENT_SQL_DIGEST:
CURRENT_SQL_DIGEST_TEXT:
STATE: Idle
WAITING_START_TIME:
MEM_BUFFER_KEYS: 4
MEM_BUFFER_BYTES: 184
SESSION_ID: 409
USER: root
DB:
ALL_SQL_DIGESTS: ["f53534242526f5f17dd442bf4429823244510563047efdcc0679741ca5833ea6","8e412fff994ffcdc77c6516ab298cc58851c07c354eb766a283a16bc997845ab","05704c1de71e0adc626cf220a7fbae005553d5ef74ed6bf44dec053cd656d474","d7815f6adab7ceebe67882038b7d29c081675c0e94751110ff74213523b6998e"]
*************************** 2. row ***************************
ID: 433218843158773761
START_TIME: 2022-05-15T16:12:16.415+09:00
CURRENT_SQL_DIGEST:
CURRENT_SQL_DIGEST_TEXT:
STATE: Idle
WAITING_START_TIME:
MEM_BUFFER_KEYS: 4
MEM_BUFFER_BYTES: 184
SESSION_ID: 407
USER: root
DB: b_bank
ALL_SQL_DIGESTS: ["f53534242526f5f17dd442bf4429823244510563047efdcc0679741ca5833ea6","df9f53eb4cb1672927cb6374dd27fa80a8124ec0186f390ea41cac113d8428ac","dc5f0aff8b925c386a79b6e33f64e79e814e2310a12f0d793fe9ab4c9c8c0eee","d7815f6adab7ceebe67882038b7d29c081675c0e94751110ff74213523b6998e"]
```

同じく、トランザクションはIdle状態。

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

```
commit;
COMMIT
```

コミット成功し、A、B銀行の残高が正常に更新されている。

```
select * from a_bank.account;
 name |  balance  
------+-----------
 foo  | 400000.00 
(1 row)

select * from b_bank.account;
 name |  balance  
------+-----------
 foo  | 600000.00 
(1 row)
```

### 端末3から、トランザクションをコミット

```
commit;
error: mysql: 9007: Write conflict, txnStartTS=433218876739420161, conflictStartTS=433218843158773761, conflictCommitTS=433218979342581764, key={tableID=67, handle=1} primary={tableID=67, handle=1} [try again later]
```

書込み競合によりコミット失敗しました。
楽観的モードでは、コミット時に競合チェックするためです(DML実行時ではなく)。

ちなみに、TiDBトランザクションの楽観的モードは、
分散データベースとして採用している、ストレージエンジン[RocksDB](https://rocksdb.org/)が提供する機能とのことです。

## 終わりに

TiDB分散トランザクションの同時実行制御を検証しました。
トランザクションの悲観的モードと楽観的モードの違いも確認しました。
次回は、比較のためMySQL XAの同時実行制御を検証します。
お楽しみに。

##### [次回] [MySQL vs. TiDB-分散トランザクションの比較検証(11): 同時実行制御(MySQL)](https://qiita.com/mingchun_zhao/items/c8b0cac8fa9d7d647c45)