SQLではACID特性を担保するために、トランザクションとロックが用意されています。トランザクションとロックは区別されずに説明されることが多いですが、実際には違うものです。この区別がないことによって、トランザクションの動作に誤解が生じることがあります。
よくある説明
銀行の預金管理システムを考えましょう。
ある口座をAさんとBさんの二人が共有している状況では、次にような状況が起こりえます。
12:00 Aさん : 口座を開設します。
12:01 Aさん : 通販の支払いのために1万円預け入れます。
12:03 Bさん : 昼ご飯代で5千円引き出します。
12:05 Aさん : 通販の支払いのために1万円振り込みます。
果たして、Aさんの振込は成功するでしょうか。Aさんの立場では、せっかく通販の支払いのために1万円預けたので、途中でBさんに引き出される事態は防ぎたいはずです。
このようなときに、トランザクションを使います。具体的には、Aさんの取引が終わるまでBさんの取引を待ってもらう(ロックする)ようにします。
12:00 Aさん : 口座を開設します。
12:01 Aさん : 通販の支払いのために1万円預け入れます。
12:03 Bさん : [locked] 昼ご飯代で5千円引き出します。
12:05 Aさん : 通販の支払いのために1万円振り込みます。
12:05 Bさん : [実行] 昼ご飯代で5千円引き出します。(fail)
よくある誤解
誤解の内容を説明する前に、次の口座取引がどのような順番で行われるべきか考えてみてください。
17:00 Aさん : 夕飯代を引き出したいので残高を確認します。(5千円)
17:03 Bさん : 飲み代で5千円引き出します。
17:05 Aさん : 夕飯代で3千円引き出します。
もし、トランザクションとロックが渾然一体なら、
17:00 Aさん : 夕飯代を引き出したいので残高を確認します。(5千円)
17:03 Bさん : [locked] 飲み代で5千円引き出します。
17:05 Aさん : 夕飯代で3千円引き出します。
17:05 Bさん : [実行] 飲み代で5千円引き出します。(fail)
となるはずです。しかし、MySQLの実装では、Bさんの引き出しが先に行われて、Aさんの引き出しが失敗します。
トランザクション分離レベル
二つのトランザクションが競合したときに、どのような動作になるかは、トランザクション分離レベルによって決定されます。分離レベルは、分離の度合いが小さい順に、
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
の四種類がANSI/ISO SQL標準で定められています。
MySQLではデフォルトではREPEATABLE READレベルでトランザクションが分離されています。具体的な動作としては、クエリがテーブルを参照すると、初めて参照した時のテーブル状態がスナップショットとして記録されて、以降のクエリ実行ではスナップショットがデータソースとして使われるようになります。
そのため、たとえばBさんが飲み代を引き出した後に、再度Aさんが残高を確認すると、Bさんの引き出しは反映されません。
17:00 Aさん : 夕飯代を引き出したいので残高を確認します。(5千円)
17:03 Bさん : 飲み代で5千円引き出します。
17:05 Aさん : 夕飯代を引き出したいので再度残高を確認します。(5千円)
ロック
これでは困ることも多いので、ここでロックが登場します。
ロックには、
- 共有ロック
- 占有(排他)ロック
の二種類があります。ロックによって、他のトランザクションのロック取得が制限(ブロック)されます。
| 共有ロック | 占有ロック | |
|---|---|---|
| 共有ロック | o | x |
| 占有ロック | x | x |
先ほどの例でBさんの飲み代の引き出しをロックしたい場合は、Aさんが初めに残高を確認した段階で共有ロックを取得しておく必要があります。
SELECT account.amount FROM account WHERE account.id = "xxx" LOCK IN SHARE MODE;
共有ロックを取得するには、SQLクエリの末尾にLOCK IN SHARE MODEを追加します。すると、口座テーブルがAさんのトランザクション用にロックされて、Bさんは口座テーブルの内容を変更できなくなります。
17:00 Aさん : 夕飯代を引き出したいので残高を確認します。(5千円)
17:03 Bさん : [locked] 飲み代で5千円引き出します。
17:05 Aさん : 夕飯代で3千円引き出します。
17:05 Bさん : [実行] 飲み代で5千円引き出します。(fail)
ちなみに、占有ロックを取得するには、SQLクエリの末尾にFOR UPDATEを追加します。また、SELECTでは明示しない限りロックは取得されませんが、UPDATEやINSERTでは自動的にロックが取得されます。
そのため、例えばLOCK IN SHARE MODEを指定したSELECTとUPDATEを別トランザクションで並列実行するとロック取得が制限されます。
ネクストキーロック
先ほどは、あたかも口座テーブルの全行がロックの対象になるかのような書き方をしましたが、実際にはケースバイケースでテーブルの一部の行のみロックの対象になる場合があります。
ロック対象はWHERE句の指定内容をもとに決められます。WHERE句の指定でインデックスされていないカラムが使われている場合は全行がロック対象になります。一方でインデックスされているカラムが使われている場合は、ネクストキーロックという仕組みによって一部の行のみがロック対象になります。
例えば、次の口座テーブルとSQLクエリの組み合わせで、どの行がロックされるのか考えます。
| id (indexed) | amount |
|---|---|
| 1 | 1万円 |
| 5 | 3万円 |
| 7 | 2万円 |
| ... | ... |
SELECT * FROM account WHERE account.id = 3 FOR UPDATE;
すると、口座情報が存在しないインデックスの隙間（ギャップ）と隙間の次の口座情報(id = 5)がロックされます。id = 3の口座情報を他のトランザクションから保護することで、競合を防いでいます。
本来であれば、id = 3の場合のみロックすれば事済みますが、実際にはid = 1, 2, 3, 4, 5の行がロックされます。これは、おそらくロック機構を実装するときに、実レコードのインデックスを基準にしてロックをかけた方が都合が良いからだとされています(噂)。