Q1: 排他キーに時刻を使わないほうがいいのはなぜ？
A1: 以下のようなケースで楽観ロックが働かず更新が後勝ちになってしまうため。 https://qiita.com/NagaokaKenichi/items/73040df85b7bd4e9ecfc でいう「同一秒に複数の操作」というのがこれにあたる。
- 最初のレコードの排他キーは1:00:00
- Aさんが1:00:00にレコードを更新
- レコードの排他キーは1:00:00
- Bさんが1:00:00にレコードを取得
- 取得した排他キーは1:00:00
- Cさんが1:00:00にレコードを取得
- 取得した排他キーは1:00:00
- Bさんが1:00:00のうちにレコードを更新
- DBの排他キーは1:00:00、Bさんが持っている排他キーも1:00:00のままなので更新は成功する
- レコードは更新されたがDBの排他キーは1:00:00のまま
- Cさんが1:11:11にレコードを更新
- DBの排他キーは1:00:00、Cさんが持っている排他キーも1:00:00のままなので更新が成功してしまい、Bさんの更新がなかったことにされてしまう
Q2: 最終的にはCさんの更新したデータで正しくDBのレコードが更新されるんだから、排他キーに時刻を使っても問題なくない？
A2: アプリケーションの設計思想にもよるかもしれないが、大きい問題としては以下が考えられる。
- アプリケーションの動作ログとDBの状態とが一致しなくなるため、監査で問題となる。
- アプリケーションのログにはレコードをAさん→Bさん→Cさんという順で更新した記録が残っているが、DBを見るとAさん→Cさんの順に更新したように見えBさんが更新したはずの内容は消えている等が発生するため。
- バッチ処理でテーブル上のレコードを一括更新した際に、バッチ処理で更新したはずの内容がオンライン側の処理で上書きされて無くなってしまう（後述）。
Q3: 「同一秒に複数の操作」というけれど、現実的にそんなことできる人間っていなくない？
A3: 普通にWebブラウザから操作するだけでは難しいかもしれないが、以下のような場合に困るはず。
- オンライン業務中にバッチ処理による更新を行い、バッチ側でも排他キーを更新する場合。たとえば全レコードに対して法改正に合わせたチェックをバッチ処理で行い、問題ないとしたレコードは「法改正対応日付」カラムを更新する……とかの要件がある場合、このバッチ処理の結果が上書きされると「法改正チェックが行われていない（ように見える）レコード」ができてしまう。
- 高負荷時でもロックが正しく動作するか・不正更新が発生しないかを確認するため、JMeter等で高負荷をかけた上でアプリケーションログとDBのレコード上との整合性を確認する場合。
Q4: 別に「やりなおしが難しい」処理をする業務じゃないから、排他制御の方法は楽観ロックで大丈夫だよね？（2024/02/11 追記）
A4: 「やりなおしが難しい」処理の中には、他にも、DBのトランザクションと同期できない処理を実行して、その結果に応じてデータベースの更新をコミットするという場合が挙げられる。
たとえば「証明書は最大10枚まで印刷できる」という要件に対し、
- DBのトランザクションを開始する
- DBから印刷済枚数をSELECTし、10枚未満だったら証明書プリンタで印刷をする
- 正常に印刷できたら、排他キーのチェックを行ったうえで、印刷済枚数を+1するUPDATE文を実行するしてコミットする
- 印刷が失敗したらロールバックする
という実装にした場合、以下のような問題が発生する。
- 問題1: 2台の端末で同時に印刷を開始すると、両方の端末から計2枚の証明書が印刷されるが、DBの印刷済枚数は1枚しか増えない。
- 問題2: 9枚印刷した後に2台の端末から同時に印刷を実行すると、上限を超えて11枚目が印刷できてしまう。
電通大Tips
— ZЯQ (@PAQ_KN) 2024年2月9日
学割証を9枚印刷した状態で2台同時に発行することで上限突破して11枚目を出せる pic.twitter.com/uHR9r3QSUy
これは、プリンターで「証明書を印刷する」という処理自体はDBのトランザクションに合わせて「なかったこと」にできないのが原因。片方の端末では印刷ボタンを押したあと楽観ロックエラーが発生し、画面上には印刷エラーの表示がされるかもしれないが、プリンタでの印刷自体は開始してしまっているので、そのまま問題なければ印刷は完了してしまう*1。
印刷の他にも、たとえばWeb APIやローカルストレージ上のファイルなど、DBMS以外へのリソースに対する更新処理の実行でも同じようなことが言えるだろう。
このようなケースでは、以下のいずれかの対策が考えられる。
- 排他制御の方式を楽観ロックから変えたくなくい場合は、印刷処理の後ではなく、トランザクションの開始直後にSELECT FOR UPDATEで排他キーを取得しておく*2。DBMSの行ロックであれば処理が競合してもTOCTOUは発生しないため、後から処理を開始した端末を楽観排他エラーにできる。
- 排他制御の方式を変えられるのであれば、悲観ロックを使う。上記の例であれば、印刷ボタンを画面に出す前に悲観ロックを取得しておき、1台の端末で印刷しようとしている際は、他の端末では印刷ボタンを出す前にエラーを返す。
- 印刷済枚数をSELECTするときにダーティリードが可能なDBMSであれば、印刷処理を実行する前にUPDATE文を実行して印刷済枚数を+1してしまう方法も考えられる。ただしTOCTOUに注意が必要。
- LOCK TABLEなど、トランザクションの決着を待たずに効果が出るロックを獲得して処理をする方法もあるにはある*3。ただし処理の並行性は当然低下する。印刷という物理的に時間のかかる処理において、「端末とプリンターは複数用意しているのに、アプリケーションの実装のせいで複数人が同時に印刷できない」という状況が発生するわけで、これで問題ないかは十分に検討した方がよいだろう。
Q5: データの更新画面を表示するときには排他キーの情報は持ってきていない。この場合、更新ボタンを押してからSELECT文で排他キーの値を取得すればいいんだよね？（2024/02/11 追記）
A5: 更新ボタンを押してからSELECT文で排他キーの値を取得してはいけない。
楽観排他における排他キーは単に「他の人が同じレコードを更新しようとしていないか？」をチェックするためのものではない（そのような制御はDBMSが行っている）。
排他キーは「データの更新画面を出すときに取得したDBレコードのバージョンは何か？操作者は、どのバージョンの内容をもとにこの更新用データを作ったのか？」を意味している。そのため、更新ボタンを押してからSELECT文で排他キーの値を取得すると、「操作者が更新用データを作るときに画面で見ていた『変更前』のレコードのバージョンと、実際のDBにおける『UPDATE前』のレコードのバージョンが異なる」という状況になる*4。
楽観ロックでも悲観ロックでも、「二人が同時に操作を行おうとしたときに、ひとりずつ順々に操作を行ったのと同じ結果になる（同じ結果にするように利用者を制御する）」ことが目的のはず。なので、更新ボタンを押してからSELECT文で排他キーの値を取得すると、そもそもこの目的が達成できなくなってしまう。
Q6: GitLabのWikiページのRest API*5にもバージョン番号はないみたいだし、Rest APIでデータを更新するときは更新処理中にSELECT文で排他キーを取得するしかないよね？（2024/02/11 追記）
A6: これは「更新処理とは何か」「DBのレコードとは何か」の考えかたによるのでは。たとえば同じWiki機能でも、RedmineのWikiページのRest API*6ではバージョン番号を指定するようになっており、楽観排他エラーが発生した場合は409 Conflictが返るようだ。
- DBの1レコードはそのまま現実世界の状態をモデル化したもの
- システムが画面に表示したデータの内容は、DB上のレコードの最新状態と整合しているはず
- レコードを更新するということは（たとえ部分的な更新であったとしても）操作者はそのレコードの内容すべてに対するコミットメントを持つ
- レコードの内容が更新されるときは、必ず1つ前のバージョンのレコードをもとに更新作業を行っているはず
といった前提*7を置いているのであれば、Rest APIでも更新前の排他キーを渡すようにして、楽観排他を行うような実装が必要になるのでは。
逆に、このような前提は起かずに以下のような考えかたにしてしまうのであれば、楽観ロックは行わずに更新してしまう方法もあるだろう。
- DBは単なる便利なデータの入れ物*8
- 利用者が画面上でどのような操作を行った結果このようなデータができたのかは知らない*9
- 複数の利用者が同一レコードを更新したとして、どちらの利用者も「このデータが正しい」と思っているのだから最終更新者が責任を持てばよい。自分が更新したデータはどのみち誰かがさらに更新するかもしれないのだから、過去バージョンのデータに対する責任はない
- 複数の利用者が同じレコードの一部をそれぞれ別々に更新した結果、レコードが現実世界の状態と一致しなくなっても、それはユーザの操作を忠実に記録した結果だから問題ない
もちろん、「そのレコードを最後に更新した利用者は何をもとにしてこのようなレコードを作りあげたのだろう？」ということが分からなくなる。それはつまり、データ更新の経緯について監査をするための証跡がないということなので、セキュリティのデューデリジェンスから見てそれは大丈夫なんだろうか？
*1:証明書が結果的に11枚印刷されてしまっても、またDB上の印刷済枚数と実際の印刷済枚数が多少ずれても支障がない業務であればいいのかもしれないが……
*2:この時点で排他キーのチェックまでしておけば、WAITでもNOWAITでも機能するだろう。排他キーの突き合わせをUPDATE時にする場合、NOWAITであれ問題1も問題2も防げるだろうが、WAITだと問題1が防げなくなると思われる
*3:この場合、LOCK TABLEはWAITモードでもNOWAITモードでもよいと思われるが、WAITモードの場合はLOCK TABLEの解放を待った後に排他キーの突き合わせをしてからエラーとなるのに対し、NOWAITモードの場合はすぐにエラーが返されるので、ユーザの体感としてはNOWAITモードの方が良くなるだろう
*4:その結果として、「更新画面を表示したあとに他の人がデータを更新していても、その内容を上書きしてしまう」「更新したはずのデータが先祖返りする」という結果が起こる
*5:https://docs.gitlab.com/ee/api/wikis.html
*6:https://www.redmine.org/projects/redmine/wiki/Rest_WikiPages#Creating-or-updating-a-wiki-page
*7:アプリケーションで実装している入力データのバリデーションや、RDBMSの各種制約は、どれもこういった前提を満たすために用意されているのではないだろうか？
*8:たしかにRDBMSはポインタや物理レコード設計からプログラマを解放してくれるから間違いじゃないんだけど
*9:またはDBのレコード以外の場所で記録しているから気にしない、なのかも？
