下記ツイートを見て改めて考えてみました。
更新系APIでDBトランザクション張る時、対象のリソース群の親となるリソース（大抵はUser）を無条件で最初にロックする手法が当たり前だと思っていたけど、もしかして世間ではそうではないのかな...デッドロックリスクを忘れられる大きなメリットがあると考えていたんだけど。
— やまでぃ🤗 | YOUTRUST (@aiueo4u) 2021年2月16日
並行に書き込みをどのように処理するかという点も含めて整理してみました。
並行性の問題一覧
| 名称 | 内容 |
|---|---|
| ダーティリード | あるクライアントが他のクライアントのまだコミットされていない書き込みを見ることができる。 read commited分離レベル以上では生じない。 |
| ダーティライト | あるクライアントが他のクライアントのまだコミットされていない書き込みを上書きできる。 |
| ノンリピータブルリード | トランザクション内でクライアントが異なる時刻で異なるデータを取得してしまう(他のトランザクションでデータが変更されるとその内容が反映されてしまう)。 スナップショット分離で回避 |
| ファントムリード | あるトランザクションでの書き込みが、他のトランザクションの検索クエリの結果を変化させる(行のINSERT、DELETE)。本来ファントムリードはREPEATABLE-READでは防がないが、MySQL InnoDBのREPEATABLE-READの分離レベルでは防ぐことが可能。 |
スナップショット分離(リピータブルリード)
それぞれのトランザクションがデータベースの一貫性のあるスナップショットから 読み取り(Read) を行うものです。
スナップショットに対して読み取りをすることで矛盾なく読み取りが出来ることを一貫性読み取り (Consistent Read) と言います。
MVCC
スナップショット分離(リピータブルリード)では、トランザクションに一貫したデータを参照させるために過去のバージョンのデータを見せる必要があります。
データベースはあるデータ(オブジェクト)について複数のバージョンを管理すること、これがMVCC(multi-version concurrency control)と呼ばれる手法です。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.12 InnoDB マルチバージョン
MySQL InnoDBにおけるスナップショット
スナップショット(過去のバージョンのデータ)は、UNDOログにコピー(退避)されています。
MySQL :: MySQL 5.7 Reference Manual :: 14.6.7 Undo Logs
それぞれの行にはローバックポインタがあり、 退避したUNDOレコードを指すようになっています。
さらに、UNDOレコードもさらに古いUNDOレコードのポインタを持っており、このローバックポインタをたどれば過去のバージョンのデータがわかるようになっています。
UNDOレコードの確認
関連しているトランザクションが全て終了しているUNDOレコードは削除できます。
削除(パージ)されていないUNDOレコードは、 SHOW ENGINE INNODB STATUS; の History list length で確認できます。
------------ TRANSACTIONS ------------ Trx id counter 334129 Purge done for trx's n:o < 334128 undo n:o < 0 state: running but idle History list length 18 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421690295200296, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421690295199440, not started 0 lock struct(s), heap size 1136, 0 row lock(s)
日々の覚書: InnoDBのHistory list lengthの監視と原因スレッドの特定と
SHOW ENGINE INNODB STATUS の History list length - ablog
MySQLのmetricに関する話 | エンジニアブログ | GREE Engineering
MySQL InnoDBにおけるスナップショット分離(REPEATABLE-READの分離レベル)
スナップショット分離の分離レベルでは、 ダーティーリード、ノンリピータブルリードは防ぐ必要がありますが、ファントムリードを防ぐかはその実装依存となります。
SQLの仕様ではREPEATABLE-READでは特にファントムは防がなくてもよいとなっています。
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt ANSI SQL 92
A Critique of ANSI SQL Isolation Levels読んだ - tom__bo’s Blog
MySQL InnoDBでは binlog_format=STATEMENT でのレプリケーションの整合性を保証するため、独自のロックのかけ方(ギャップロックとネクストキーロック)でそれを実現しています。
これは、STATEMENTの場合、sourceは並行で処理しているがreplicaはバイナリログを直列で処理しているため、sourceでファントムが起こる状態(並行で処理している)であってもreplicaではファントムが起こらないためデータの整合性が合わないことがあるためです。
MySQL InnoDBではREAD-COMMITEDの分離レベルで binlog_format=STATEMENT にするとエラーになります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.2.4.2 バイナリログ形式の設定
MySQLのレプリケーション設定で起きたトラブルの原因とその解決策 - Yahoo! JAPAN Tech Blog
この独自のロックのかけ方が、ファントムを防止しています。
ファントムとかギャップロックとネクストキーロック
すでにまとまっている資料がたくさんあるので割愛
スナップショットの取得
BEGIN句によるトランザクション開始
BEGIN句からトランザクションを開始すると、そのタイミングではスナップショットの作成されません。
スナップショット取得が行われるのは最初の SELECTのタイミングになります。
mysql> SHOW CREATE TABLE t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int unsigned NOT NULL,
`b` int NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql> SELECT @@SESSION.transaction_isolation \G
*************************** 1. row ***************************
@@SESSION.transaction_isolation: REPEATABLE-READ
1 row in set (0.00 sec)
スナップショットの取るタイミングの確認その1
| No. | session1 | session2 |
|---|---|---|
| 1 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | +---+---+ 1 row in set (0.00 sec) |
|
| 2 | mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) |
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) |
| 3 | mysql> INSERT INTO t VALUES(2, 1); Query OK, 1 row affected (0.01 sec) |
|
| 4 | mysql> COMMIT; Query OK, 0 rows affected (0.01 sec) |
|
| 5 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec) (このタイミングでスナップショットの生成が行われるので、ファントムぽく見えるけどこれは正しい挙動) |
スナップショットの取るタイミングの確認その2
| No. | session1 | session2 |
|---|---|---|
| 1 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.01 sec) |
|
| 2 | mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) |
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) |
| 3 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.01 sec) ここでスナップショット取得 |
mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec) |
| 4 | mysql> INSERT INTO t VALUES(3, 1); Query OK, 1 row affected (0.01 sec) |
|
| 5 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec) ファントムが防がれている |
mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec) |
| mysql> COMMIT; Query OK, 0 rows affected (0.01 sec) |
||
| mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.00 sec) ファントムが防がれている |
mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec) |
START TRANSACTION WITH CONSISTENT SNAPSHOTでトランザクションが開始とスナップショットの作成を同時に行う
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.1 START TRANSACTION、COMMIT、および ROLLBACK 構文
| No. | session1 | session2 |
|---|---|---|
| 1 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec) |
|
| 2 | mysql> START TRANSACTION WITH CONSISTENT SNAPSHOT; Query OK, 0 rows affected (0.01 sec) |
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) |
| 3 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.01 sec) |
|
| 4 | mysql> INSERT INTO t VALUES(4, 1); Query OK, 1 row affected (0.00 sec) 多分ここでスナップショットができていると予想 |
|
| 5 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | +---+---+ 4 rows in set (0.00 sec) |
|
| mysql> COMMIT; Query OK, 0 rows affected (0.01 sec) |
||
| mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec) ファントムが防がれている |
SELECT FOR UPDATE
SELECT FOR UPDATEは最新のスナップショットの取得をし、それを使うために行および関連付けられたすべてのエントリに排他ロックをかけます。
この動作は、これらの行に UPDATE ステートメントを発行した場合と同じ挙動になります。
MySQL InnoDBにおけるスナップショット分離(REPEATABLE-READの分離レベル)でも起き得ること
更新ロスト(Lost Update)
アプリケーションが何らかの値を読み取り、その値を変更して書き戻す( read-modify-write )の場合に生じることがあります。
ひとことでいうと最後の書き込みを勝たせる(last write wins LWW)ことです。
PostgreSQLのリピータブルリードだと更新ロストが発生したことを自動的に検出して問題のトランザクションを中断してくれるとのことですが、MySQLのInnoDBでは更新のロストの検出はしてくれません。
更新ロストを防ぐ方法として、更新対象のデータを明示的にロックをする方法が挙げられます。
なにかしら更新処理を行う最初にSELECT FOR UPDATEで対象となる行をロックし、更新中は他のトランザクションが並行に読みだそうとしても先行するトランザクションが完了するまで待たされるようにします。
この場合、アプリケーションロジックの中で必要なロックの取得を忘れてしまうと簡単にレース条件が発生するの注意が必要です。
また、トランザクション内で単純なSELECTとSELECT FOR UPDATEを使ったデータ取得に差異がでることがあるので、ここも要注意です。
(単純なSELECTだと過去のスナップショットを参照するが、SELECT FOR UPDATEは必ず最新のスナップショットを見る)
漢(オトコ)のコンピュータ道: InnoDBのREPEATABLE READにおけるLocking Readについての注意点
MySQL :: MySQL 5.6 Reference Manual :: MySQL Glossary
書き込みスキュー
2つのトランザクションが同じデータ群から読み取りを行い、それらのいくつかを更新する(特にトランザクションごとに更新するデータが異なっている)場合に生じるものです。
複数のデータが関わっているので、単一のデータに対するアトミックな操作(ロック)では解決できません。
これも、複数のデータに対して明示的にロックを行うことで解消できます。
ただし、特定の条件を満たす行が存在しないことをチェックし書き込みによってその行が追加される場合、ロックする対象となる行が存在しないためロックがかけられないという問題があります。
これを回避するためには、衝突の実体化(materializing conflicts)を行う必要があります。
https://dsf.berkeley.edu/cs286/papers/ssi-tods2005.pdf
これらの回避するために
これらは分離レベルをSERIALIZABLEにすることで解消することがほとんどですが、SERIALIZABLEによって並行に処理できなくなることが多く(事実上1個の処理だけが動くような排他制御が行われる)パフォーマンスに大きな影響を出すことがほとんどです。
ほとんどの場合において書き込みスキューを考慮しなくてよい場合においては、一部分だけ直列にできるようなしくみがあればよさそうです。
そこで、アプリケーション全体でロックを行う対象を決め、それらを順序を守ってロックをかけることで直列に処理を行うようにします。
ロックを行う対象は、必ず存在するデータに対して行います。(そうでないとギャップロックがかかるので)
そのため、自分の大体のケースでUsersテーブルを使って対象となるユーザーの行に対してロックをかけるということになります。 (UsersテーブルにはユーザーIDと作成日くらいしかないことを想定しています)
ロックを掛ける際は、デッドロックを防ぐためUsersテーブルのプライマリキーで ソートしてから ロックをかけます。
例えば、PvPを考えると
- ユーザーAの攻撃でユーザーBのHPを減らし、ユーザーAに経験値を追加する
- ユーザーBの攻撃でユーザーAのHPを減らす、ユーザーBに経験値を追加する
のようにの相互の更新が同時に起こりうるので、ユーザーAとユーザーBのUsersテーブルにロックを掛ける場合ロックを取るときはロックを取る順番がどちらも同じになるようにします。
SELECT id FROM users WHERE a IN ("A", "B") ORDER BY a FOR UPDATE;
実際にどうなるかを簡単なテーブルで試した例も記載しておきます。
デッドロックになる例
| No. | session1 | session2 |
|---|---|---|
| 1 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec) |
|
| 2 | mysql> START TRANSACTION WITH CONSISTENT SNAPSHOT; Query OK, 0 rows affected (0.01 sec) |
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) |
| 3 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.01 sec) |
|
| 4 | mysql> INSERT INTO t VALUES(4, 1); Query OK, 1 row affected (0.00 sec) 多分ここでスナップショットができていると予想 |
|
| 5 | mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | +---+---+ 4 rows in set (0.00 sec) |
|
| mysql> COMMIT; Query OK, 0 rows affected (0.01 sec) |
||
| mysql> SELECT * FROM t; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +---+---+ 3 rows in set (0.00 sec) ファントムが防がれている |
Lock Waitになる例
| No. | session1 | session2 |
|---|---|---|
| mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) |
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) |
|
| mysql> SELECT * FROM t WHERE a IN (2, 1) ORDER BY a FOR UPDATE; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.01 sec) |
mysql> SELECT * FROM t WHERE a IN (2, 1) ORDER BY a FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
しかしこの方法では並行性の制御の仕組み(ロックを掛ける順番やロックを行うデータの選択)がアプリケーションのデータモデルに漏れ出していてアプリケーション側で意識することが増えてしまい、またルールで縛るため間違いも起こりやすくなります。
なので、本来であれば、分離レベルをSERIALIZABLEにするのがよいのかなと思います。。。
ただMySQLのSERIALIZABLEは容易にロックがかかります。
- SERIALIZABLEでロックが掛かる例
| No. | session1 | session2 |
|---|---|---|
| mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) |
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) |
|
| mysql> SELECT * FROM t WHERE a IN (2, 1) ORDER BY a FOR UPDATE; +---+---+ | a | b | +---+---+ | 1 | 1 | | 2 | 1 | +---+---+ 2 rows in set (0.01 sec) |
mysql> SELECT * FROM t WHERE a IN (2, 1) ORDER BY a FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
PostgreSQLのSERIALIZABLEの分離レベルはSSIがだとどうなるかは気になるところです。
参考
InnoDBの分離レベルによるMySQLのパフォーマンスへの影響 | Yakst
漢(オトコ)のコンピュータ道: InnoDBのREPEATABLE READにおけるLocking Readについての注意点
Rails Developers Meetup 2018 で「MySQL/InnoDB の裏側」を発表しました - あらびき日記
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.7 ネクストキーロックによるファントム問題の回避
トランザクション技術とリカバリとInnoDBパラメータを調べた - たにしきんぐダム
MySQL InnoDB で大きなトランザクションを見つける - ablog
InnoDBのロールバックがあとどれくらいかかるかをなんとなく見積もる | GMOメディア エンジニアブログ
https://15445.courses.cs.cmu.edu/fall2017/slides/18-indexconcurrency.pdf
MySQL の Repeatable Read と RocksDB の楽観的トランザクション解説 | 株式会社インフィニットループ技術ブログ
