今回はSQL Server環境でトランザクションを掛けてロック中の行に対して、SELECTやUPDATEをするやり方を紹介します。
それにともなって、トランザクション管理やロックの必要性、トランザクション分離レベルについても解説します。
ご注意ください。
その為、前半の説明は不要だよと言う方は目次のリンクでまるっと飛ばしちゃって下さい。
そもそもトランザクションやロックって何
以前にトランザクション管理について記事を書いています。
少し前の記事で我ながら分かりにくい文章だなと思いますが、トランザクション管理とは、「データベースにおいてデータ更新をする場合に、対象行の整合性を担保するための行ロック管理の仕組み」と言えます。
じゃあ「ロック」って何？
上に「行ロック管理」とありますが、「ロック」とはなんでしょうか？
簡単に言えば、「データベースの更新中の行にカギを掛けて、他の誰にもその行を更新させないし、見ることすら出来なくする仕組み」です。
なぜ他から更新させないようにする必要があるの？
それを理解するには以下の想定をする必要があります。
- 同じ行に対して複数人が同時に更新を掛ける可能性がある。
- 更新処理に時間が掛かる場合がある。
使用者が１人といった極小規模なシステムであれば考慮しなくてもあまり支障は無いですが、仕事で使われる業務システムでは、大抵上記条件に当てはまるはずです。
上記の条件を前提とした場合、同じ行に同時に更新をされると不都合があります。
同時に更新を許してはいけない例
まず以下の二つのプロセスがあると仮定します。
| プロセス名 | 更新行 | 処理時間 | 処理内容 |
|---|---|---|---|
| A | 受注番号1～100(複数行) | 5秒 | 列[受注金額]の値を一斉に更新する。 |
| B | 受注番号1(単一行) | 1秒 | 列[受注金額]の値を訂正するために受注番号を指定して更新する。 |
更新行に対してロックを掛けない場合の処理の流れは以下です。
- プロセスAが何らかの条件を元に受注番号1を含む100行の列[受注金額]に対して値を更新する処理を開始。※この処理は完了に5秒必要
- プロセスBが受注番号1の行の列[受注金額]に対して値を更新する処理を開始し1秒後に完了。
- プロセスAが実行していた受注番号1を含んだ行に対する列[受注金額]への更新処理が完了。
さて、この場合最終的に受注番号1の行の列[受注金額]に反映される値はプロセスAの更新内容でしょうか？
それともプロセスBの更新内容でしょうか？
例えばプロセスAがプロセスBより先に受注番号1の行に更新を掛けていれば、後から更新を掛けるプロセスBの内容に上書かれますし、プロセスAが更新対象の100行の最後に受注番号1の行に更新を掛けた場合は、プロセスBの更新した値をプロセスAが更新する値で上書くことになります。
上記の更新処理における在るべき姿としては「先に更新処理を開始したプロセスを優先して、後から開始した更新処理は前の更新処理が終わるまで待機して前の処理が終わったら自身の更新処理を開始する」です。(※そもそもプロセスBを実行する人はプロセスAがしようとしている更新内容を知らない可能性が高いので、本当にプロセスBが更新を掛けて良いのかという論理的な問題が有りますがそれは無視します…。)
それを実現するのがデータベースにおける「ロック制御」です。
上記のプロセスAとプロセスBの例で言えば以下の流れになります。
- プロセスAが更新処理を開始し、更新対象になる100行に対して予めロックを掛けて占有します。
- プロセスBが受注番号1の行に対して更新処理を開始しようとするが、対象行はロックされているためロックが解放されるまで待機します。
- プロセスAの更新処理が完了したら、待機していたプロセスBの更新処理を再開します。
この処理の流れを実現するためには「行ロック」という仕組みが不可欠です。
なぜ他から見ることすらできないようにする必要があるの？
更新中行にロックを掛けて同時更新をさせてはいけない理由はわかりました。
では、そもそも更新中を行を見せることさえいけない理由は何でしょうか？
見るだけなら制限を掛けなくても良さそうにも思えます。
コミットとロールバックとは？
前述のプロセスの例で言えば、「在るべき姿」の処理の流れのなかで、項番1のプロセスAが更新処理を開始する際に「今からトランザクションを開始します！」と宣言をします。
そうすると更新処理を実行した場合に仮更新のような状態になり、更新結果の実データへの反映は保留状態になります。
実データへの更新結果の反映は、上記項番3のプロセスAの更新処理が終わったあとに「これまでのトランザクションを確定します！」と宣言することで、保留になっていた更新結果が実データに反映されます。これを「コミット」と呼びます。
また、前述した「トランザクション開始宣言」後に、更新処理を実行して保留状態になってる実データへの反映(コミット)を取り止める事ができます。
その場合は「これまでのトランザクションをロールバックします！」と宣言すると、トランザクションを開始してからそれまでの更新結果を巻き戻して無かったことにします。
このロールバック処理は通常プログラム内のエラー発生時の処理内に組み込んでおきます。
データベースのデータに対して更新を掛ける場合に、上記の「コミット」と「ロールバック」という仕組みが存在し、更新中のデータは「コミット」をされるまでは変更は確定しておらず、「ロールバック」されることでデータが巻き戻る可能性があるということを理解してください。
因みに、トランザクション中の行に対してSQLのSELECT文等で参照しようとした場合はそのトランザクションがコミットかロールバックで終了するまで参照できず、待たされることになります。
更新中の行を見せてはいけない例
前述したプロセスAとBの例で説明します。
プロセスAが更新処理を開始し、プロセスBが受注番号1に対して更新を掛ける前にSELECTしてデータ内容を確認するとします。
もしトランザクション中の行がプロセスBから見える場合、参照したタイミングによって更新前の値が見えたり更新後の値が見えたりします。
更新前の値であれば参照した直後に変わる可能性があるため見せてはいけない値ですし、更新後の値であれば参照した直後にロールバックされて更新前の値に巻き戻る可能性があるため見せてはいけない値です。
よって、そんな不確実な値であれば、トランザクション中の行はSELECTなどの参照もできないようにしておかないと、データベースが返すデータの信頼性が無くなります。
これらの理由により、トランザクション中の行に対しては、本来更新も参照もさせてはいけないのです。
Oracleでは更新前の行をUNDOデータとして保持しており、ロック中の行に対する参照要求が発生した場合には、そのUNDOデータを返します。
それにより参照時のロック待ちは発生しません。
ただ、更新される前の古いデータを読んでしまう前述のリスクはあると言えます。
トランザクション分離レベル
ロック中の行に対して参照や更新を許すということは、「トランザクション分離レベルを下げてSQLを実行する」という処理になります。
トランザクション分離レベルの予備知識
タイトルにも書かれている「ダーティリード」についても補足します。
このダーティリードとは、ロック中の行に対して参照(具体的には更新済み且つ未確定の行の参照)を許すことを言います。このように、ロック中の行に対する制限の強さによって、他にもファントムリード、ファジーリード又はリピータブルリードという三種類の状態があります。
何やらどれも格好良い名前ですが、この三種類の制限の緩さで並べると以下の様になります。
ダーティリードが最も制限が緩い場合でのみ発生し、ファントムリードは比較的制限を厳しめにしても発生します。これらの具体的な違いは以下です。
ダーティリード
コミット前のトランザクション中の行への参照を許してしまうケース。
前述したとおり、コミット前のトランザクション中の行は値が変わる可能性があるので、その値を参照してはいけません。
ファジーリード・ノンリピータブルリード
あるプロセスが同一行を複数回に渡って参照する際に、その途中で別のプロセスがその対象行の値を更新してしまうケース。
この場合、複数回参照をしているプロセスでは、ある時から急に対象行の値がこれまで読んできた値と異なる値が取得されることになります。
ファントムリード
あるプロセスが同一範囲のデータを複数回に渡って参照する際に、その途中で別のプロセスがその範囲内にある値を持つ行をINSERTしたり、DELETEしてしまうケース。
この場合、複数回同一範囲を参照しているプロセスでは、ある参照時から急にそれまで存在しなかった行が出現したり、逆にそれまで存在していた行が消えたいるといった事象が発生します。
まさにファントム(幻や幽霊)ですね。
上記のダーティリードは説明もしていますし、ダーティリードを許してしまう場合のデメリットはわかりやすいのですが、ファジーリードやファントムリードは何の問題があるのかピンと来ない人も多いかと思います。
例えばファントムリードの場合はあるプロセスが番号1～100までを条件にあるテーブルをSELECTします。その後別のプロセスが1から100までの値のうち空いている番号をセットして新規行をINSERTします。SELECTしていたプロセスは再度番号1～100を条件にSELECTしたところ、前回のSELECT時には存在しなかった行が1行増えていることになります。
これって普通じゃん・・・って思ったかも知れませんが、この流れは限りなく短時間で行われる処理であり、SELECTで番号1～100までの参照を繰り返す場合に、その取得行は同一であると担保したいケースはやはり存在します。
この三種類の状態は、トランザクション処理時に定義する「トランザクション分離レベル」によって発生の有無が異なります。
| 分離レベル | ダーティリード | ファジーリード | ファントムリード |
|---|---|---|---|
| READ UNCOMMITTED | ○ | ○ | ○ |
| READ COMMITTED | × | ○ | ○ |
| REPEATABLE READ | × | × | ○ |
| SERIALIZABLE | × | × | × |
分離レベルの内容は名前の通りで、コミット前のREADを許す、コミット後のREADを許す、繰り返し参照中のREADを許す、直列化または逐次化しREADを許さないという4種類です。
SERIALIZABLEだとどの読み取り状態も発生しません。
であればトランザクション分離レベルは常にSERIALIZABLEを設定しておけば良いと思ってしまいますが、トランザクション分離レベルをSERIALIZABLEにした場合、ロック管理が厳密過ぎて、ことあるごとにプロセスはロック解放待ちで待たされることになり、システムのレスポンスが大きく落ちます。
実際にOracleやSQL Serverなどの主要なデータベース製品では、トランザクション分離レベルを明示的に指定しない場合に適用される設定はREAD COMMITTEDなどの分離レベルであり、SERIALIZABLEは明示的に指定しないと適用されません。
それを理解した上でダーティリードを許してロック中の行に対してSELECTやUPDATEをする方法を紹介していきます。
ダーティリードを許す具体的な実装方法
前述の説明により、トランザクション中の行に対して更新や参照をすることは良くないことだと説明しておきながら、そのやり方を紹介するのもおかしな話ですが、当ブログは比較的初心者向けに書いているという趣旨があるので、あくまでイレギュラーな実装だと認識して頂くために長々と説明を入れました。
システム設計上ダーティリードを許さないといけない状況にある場合、その設計は見直しが必要というサインです。ただ、どうしても設計の見直しが難しい場合はあるので、リスクを承知の上で実装してもらえればと思います。
やり方は非常に簡単で、プログラム内でSELECT文やUPDATE文等を実行する際に、SQL文字列の先頭に以下の文字列を付与して下さい。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
実際のSQL文の例だと以下です。
--SELECT文実行時の例 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM テーブル名 WHERE 参照条件 --UPDATE文実行時の例 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED UPDATE テーブル名 SET 更新内容 WHERE 更新条件
このようにSQL文を作成して実行するだけで、ロック中の行に対して参照や更新が可能です。
尚、当処理はロック中の行を参照される側のプロセスで実施するのではなく、ロック行を参照しに行く側のプロセスで実施します。
お間違え無いようにご注意下さい。
それでは、今回も長々とお付き合い頂きましてありがとうございました。