データベースの不整合（ロストアップデート）と戦おう！
こんにちは。WEBチームの[twitter:@k2w4t4h]です。
最近ではプロジェクトのチェック役としてのお仕事も多くなってきました。
ガラパゴスには4KBもあるSQL文を平然と書いてくる人もいれば、そんなことはExcelでやればいいのにと思うことをDBでやっている人もいます。
そう、ガラパゴスは、OSSのDBだけでも、MySQL、PostgreSQL、Firebird、Memcachedなどを使用しており、DBが大好きな会社なんです。
DBでアルバイトが宗教戦争している会社も珍しいような（笑）
しかし、そんなガラパゴスでも、トランザクションを使用していても起こってしまうDBの不整合は見落とされがちです。
そこで、トランザクション使用時に起こるデータベースの不整合を防ぐ方法について、社内研修を兼ねて、まとめてみようかと思います。
準備
こんなテーブルがあったとします。
CREATE TALE item_list (
name VARCHAR(63),
stock INTEGER NOT NULL
);
問題を簡単にするために、超シンプルなテーブルにしてあります。
本当はInnoDBはPRIMARY KEYを使わないと行レベルロックではなく、テーブルロックになってしまいます。そこら辺は適当に対応されているとします。
そして、
name = '鉛筆' , stock = 1
というレコードが存在する状態であるとします。
普通にコードを書く
begin;
select stock from item_list where name = '鉛筆';
（１が返ります）
update item_list set stock = stock - 1 where name = '鉛筆';
（在庫数は０になります）
commit;
ところが、これでは不具合が起こる可能性があります。
AさんとBさんの２人が同じ商品をほぼ同じタイミングで購入してしまったような場合です。
先頭の数字は実際にクエリが走った順番です。
Aさん
１．begin;
３．select stock from item_list where name = '鉛筆';
（１が返ります）
５．update item_list set stock = stock - 1 where name = '鉛筆';
（在庫数は０になります）
７．commit;
Bさん
２．begin;
４．select stock from item_list where name = '鉛筆';
（１が返ります）
６．update item_list set stock = stock - 1 where name = '鉛筆';
（在庫数は−１になります）
８．commit;
こうなると、DBに不整合が発生してしまいます。このような不整合のことをロストアップデートといいます。
DBのクエリなんてすぐに終わるよ？と思うかもしれませんが、実際にはトランザクションの途中で外部の課金サーバーと通信するとか、色々する場合があって、好ましくないながらもトランザクションが長くなってしまうことは割とよくあります。
ネットショップ等で、特売の商品に購入が殺到し、在庫数の不一致を起こして、クチコミサイトなどで不評をかっていることがありますが、このようなことが原因かもしれません。
これらにはいくつかの対策があります。
for updateを使う
MySQLにはselect for updateという構文があり、updateするために、読み取りをした行をupdateするまでロックする（更新系のクエリのみ）という機能があります。
Aさん
１．begin;
３．select stock from item_list where name = '鉛筆' for update;
（１が返り、ロックがセットされます）
５．update item_list set stock = stock - 1 where name = '鉛筆';
（４のロックによりロックされます）
Bさん
２．begin;
４．select stock from item_list where name = '鉛筆' for update;
（１が返り、ロックがセットされます）
６．update item_list set stock = stock - 1 where name = '鉛筆';
（３のロックによりロックされます）
こうしてデッドロックが発生し、先にトランザクションをはじめたAさんのクエリが優先されます。
これで、ロストアップデートは防ぐことが出来ます。
実はfor updateでは、ファントムリードという別の問題が発生することがあるのですが、あまり起こらないのでここでは無視します。
（残念ながら、今回紹介する全ての対策のどれをもってしてもファントムリードは防げません）
しかし、この方法はちゃんと使えばよいのですが、
select * from item_list for update;
という風にしてしまうと、かなり広い範囲に対してロック（というか実質的にテーブルロック）してしまうため、パフォーマンスが常に出るとは限りません。
このようなロック方式のことを悲観的ロックと言います。
versionカラムを追加する
先の悲観的なロックでは、デッドロックに到達してしまいました。
そこで、今度は楽観的ロックという手法で検討してみます。
テーブルの構造をこのようにします。
CREATE TALE item_list (
name VARCHAR(63),
stock INTEGER NOT NULL,
version INTEGER NOT NULL
);
そこに入っているデータもこのようなものであるとします。
name = '鉛筆' , stock = 1, version = 1
そこでこのようなクエリを走らせます。
Aさん
１．begin;
３．select stock,version from item_list where name = '鉛筆';
（バージョン、在庫数ともに１が返ります）
５．update item_list set stock = stock - 1, version = version + 1 where name = '鉛筆' and version = 1;
（在庫数は０、バージョンは２になります）
７．commit;
Bさん
２．begin;
４．select stock,version from item_list where name = '鉛筆';
（バージョン、在庫数ともに１が返ります）
６．update item_list set stock = stock - 1, version = version + 1 where name = '鉛筆' and version = 1;
（一致する行がないの何も起こりません）
更新した行数より、BさんをロールバックすればOK。
where句を使う
最も簡単な対応方法に、where句を使うものがあります。
Aさん
１．begin;
３．select stock from item_list where name = '鉛筆';
（１が返ります）
５．update item_list set stock = stock - 1 where name = '鉛筆' and stock = 1;
（在庫数は０になります）
７．commit;
Bさん
２．begin;
４．select stock from item_list where name = '鉛筆';
（１が返ります）
６．update item_list set stock = stock - 1 where name = '鉛筆' and stock = 1;
（一致する行がないの何も起こりません）
更新した行数より、BさんをロールバックすればOK。
なら、where句を使えばいいじゃないかなのか、という話なのですが、このような場合にversionを利用したほうがより細かい制御が出来ます。
例えば、Aさんが購入しようとし、Bさんが返品する場合です。
Aさん
１．begin;
３．select stock from item_list where name = '鉛筆';
（１が返ります）
５．update item_list set stock = stock - 1 where name = '鉛筆' and stock = 1;
（在庫数は１になります）
７．commit;
Bさん
２．begin;
４．update item_list set stock = stock + 1 where name = '鉛筆';
（在庫数は２になります）
６．commit;
このような状況にversionなら対応できますがwhere句では対応できません。
無論、このようなことを許すべきでないという考え方もありだと思います。
恐らく、デフォルトでロストアップデートを検知してくれるFirebidrdなどは許さない実装になっていると思います。
