2021年は、皆様にとってどのような年だったでしょうか。
自分にとっては、殻を破るため冒険に出た激動の1年でした。

NewSQLのTiDBに触れ始め、ワクワクさせられながら1ヶ月経ちました。
今回は今年の最終回で、並行トランザクションの同時実行制御(デッドロック含)を検証してみます。
関連して、TiDBトランザクション分離レベルも少しみてみます。

検証環境は、前回TiDB 検証ライフ(9-分散トランザクションの検証1)の環境を続けて使用することにします。

同時実行制御の検証

同時実行制御って大事ですね、TiDBがどこまでやってくれるか気になります。
悩まず、とにかくやってみます。

検証シナリオ

僕がA銀行からB銀行へ、
妻がB銀行からA銀行へ、
同時に振替ボタンを押した、といったシナリオです。

(妻が怒っているように見えますが、フィクションです。とても仲よいです)

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

前回と比べ、端末2、端末3の用途が変わります。

端末2:　僕の振替用セッション
端末3:　妻の振替用セッション

端末2のセッションから、僕が操作開始

A、B銀行の口座残高を確認します

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

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

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

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のセッションから、妻が操作開始

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

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

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

A、B銀行の口座残高を確認します

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

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

トランザクション開始

start transaction;
START TRANSACTION

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

update b_bank.account set balance=balance-100000 where name='foo';
（待ち状態。。。。。。）
error: mysql: 1205: Lock wait timeout exceeded; try restarting transaction

すると、UPDATEがロック待ちに入り、タイムアウトで失敗しました。
悲観的モードなので、相手の更新中は、ロック待ちに陥ってしまいますね。

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

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

端末2のセッションから、僕が操作開始

一旦、今までの処理をロールバック

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のセッションから、妻が操作開始

一旦、今までの処理をロールバック

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: 430159871769051137
START_TIME: 2021-12-31T14:48:06.619+09:00
CURRENT_SQL_DIGEST: 
CURRENT_SQL_DIGEST_TEXT: 
STATE: Idle
WAITING_START_TIME: 
MEM_BUFFER_KEYS: 4
MEM_BUFFER_BYTES: 158
SESSION_ID: 7
USER: root
DB: b_bank
ALL_SQL_DIGESTS: ["f53534242526f5f17dd442bf4429823244510563047efdcc0679741ca5833ea6","df9f53eb4cb1672927cb6374dd27fa80a8124ec0186f390ea41cac113d8428ac","dc5f0aff8b925c386a79b6e33f64e79e814e2310a12f0d793fe9ab4c9c8c0eee","d7815f6adab7ceebe67882038b7d29c081675c0e94751110ff74213523b6998e"]

Idel状態になっている。

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

select * from information_schema.tidb_trx\G
*************************** 1. row ***************************
ID: 430159928314822659
START_TIME: 2021-12-31T14:51:42.324+09:00
CURRENT_SQL_DIGEST: 
CURRENT_SQL_DIGEST_TEXT: 
STATE: Idle
WAITING_START_TIME: 
MEM_BUFFER_KEYS: 4
MEM_BUFFER_BYTES: 158
SESSION_ID: 7
USER: root
DB: 
ALL_SQL_DIGESTS: ["f53534242526f5f17dd442bf4429823244510563047efdcc0679741ca5833ea6","8e412fff994ffcdc77c6516ab298cc58851c07c354eb766a283a16bc997845ab","05704c1de71e0adc626cf220a7fbae005553d5ef74ed6bf44dec053cd656d474","d7815f6adab7ceebe67882038b7d29c081675c0e94751110ff74213523b6998e"]

端末2同様、Idle状態。

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

commit;
COMMIT

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

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

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

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

commit;
error: mysql: 9007: Write conflict, txnStartTS=430159928314822659, conflictStartTS=430159871769051137, conflictCommitTS=430160050056593410, key={tableID=66, handle=1} primary={tableID=66, handle=1} [try again later]

書き込みで競合発生し失敗。
楽観的モードで、コミット時に競合チェックするのである意味当然か。

この楽観的モードは、大規模な分散データベースとして、TiDBが採用しているストレージエンジンRocksDBが提供している機能らしいです。

トランザクションの分離レベル

並行トランザクション競合時の挙動は、理論上トランザクション分離レベルに依存するようです。
分離レベルは、同時「読み取り/書き込み」で競合発生時、どこまでデータ整合性を担保できるかを示してくれます。

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

Read Uncommitted
Read Committed
Repeatable Read
Serializable

TiDBの分離レベルは何よ

MySQLとの互換性のためRepeatable Readとして知られているSnapshot Isolation(SI)レベルの整合性を実装しているようです。これは、ANSI Repeatable Readレベルとも、MySQL Repeatable Readレベルとも、異なるようでご注意を。

※ PingCAP社のマニュアルから引用: https://docs.pingcap.com/tidb/stable/transaction-isolation-levels

デッドロックの検証

トランザクション間でデッドロックが起きたら、厄介ですね。
通常は外部からロック解除してあげないといけませんが、TiDBはどう対処するでしょうか。

検証シナリオ

僕と妻が、同じA銀行口座に対し、以下のように交互に行レベル操作を行う、といったシナリオです。

僕がfooレコードから1万円引く
妻がbarレコードから1万円引く
僕がbarレコードに1万円足す
妻がfooレコードに1万円足す

操作の肝は、

異なるトランザクションから
同じテーブルの複数行に対し
交互に同じ行の値を更新

で、金額を引いたか足したかは、どうでもよいです。

検証スタート

1. 端末2のセッションから、僕が操作開始

一旦、今までの処理をロールバック

rollback;
ROLLBACK

悲観的モードに切り替え

set @@tidb_txn_mode='pessimistic';
SET

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

A銀行口座にbarレコードを追加

insert into a_bank.account values('bar',800000);
INSERT 1

select * from a_bank.account;
 name |  balance  
------+-----------
 bar  | 800000.00 
 foo  | 300000.00 
(2 rows)

これで、A銀行口座に、foo、barの2レコード存在します。

トランザクション開始

start transaction;
START TRANSACTION

A銀行口座のfooから1万円引く

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

2. 端末3のセッションから、妻が操作開始

一旦、今までの処理をロールバック

rollback;
ROLLBACK

悲観的モードに切り替え

set @@tidb_txn_mode='pessimistic';
SET

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

トランザクション開始

start transaction;
START TRANSACTION

A銀行口座のbarから1万円引く

update a_bank.account set balance=balance-10000 where name='bar';
UPDATE 1

3. 端末2のセッションから、僕が操作

A銀行口座のbarに1万円足す

update a_bank.account set balance=balance+10000 where name='bar';
UPDATE 1

4. 端末3のセッションから、妻が操作

A銀行口座のfooに1万円足す

update a_bank.account set balance=balance+10000 where name='foo';
error: mysql: 1213: Deadlock found when trying to get lock; try restarting transaction

ここで、すぐデッドロックが検知され、処理中断されました。
延々にロック待ちに陥るようなことはなさそうです、すごい!

DEADLOCKS表から詳細情報を確認

select * from information_schema.deadlocks\G
*************************** 1. row ***************************
DEADLOCK_ID: 1
OCCUR_TIME: 2021-12-31T16:48:06.575191+09:00
RETRYABLE: 0
TRY_LOCK_TRX_ID: 430161722650394625
CURRENT_SQL_DIGEST: 05704c1de71e0adc626cf220a7fbae005553d5ef74ed6bf44dec053cd656d474
CURRENT_SQL_DIGEST_TEXT: update `a_bank` . `account` set `balance` = `balance` + ? where `name` = ? ;
KEY: 7480000000000000455F698000000000000001016261720000000000FA
KEY_INFO: {"db_id":57,"db_name":"a_bank","table_id":69,"table_name":"account","index_id":1,"index_name":"PRIMARY","index_values":["bar"]}
TRX_HOLDING_LOCK: 430161741511655425
*************************** 2. row ***************************
DEADLOCK_ID: 1
OCCUR_TIME: 2021-12-31T16:48:06.575191+09:00
RETRYABLE: 0
TRY_LOCK_TRX_ID: 430161741511655425
CURRENT_SQL_DIGEST: 05704c1de71e0adc626cf220a7fbae005553d5ef74ed6bf44dec053cd656d474
CURRENT_SQL_DIGEST_TEXT: update `a_bank` . `account` set `balance` = `balance` + ? where `name` = ? ;
KEY: 7480000000000000455F69800000000000000101666F6F0000000000FA
KEY_INFO: {"db_id":57,"db_name":"a_bank","table_id":69,"table_name":"account","index_id":1,"index_name":"PRIMARY","index_values":["foo"]}
TRX_HOLDING_LOCK: 430161722650394625

デッドロックの詳細情報を確認できます。

発生時間
ロック(待たせる側、待たされる側)
SQL文
レコードのキー

これで、原因調査を行い、再発防止策を立てられそうです。

終わりに

なんとか年越す前に、トランザクション検証を終えることができました。
TiDBの魅力に徐々に引き込まれていきそうです。

2022年は、技術とメンタル両面で自分を磨き上げ強くなりたい、
と心に決めながら、今年の検証ライフを締めさせていただきます。

ご多用中お付き合いいただき、ありがとうございました。
良いお年をお迎えください、来年もどうぞよろしくお願いいたします。