DB: Selectする時の異常状況

Selectする時の異常状況

1. Dirty Read

Session 1	Session 2
SELECT v FROM tt WHERE id = 1;//v = 5
BEGIN; UPDATE tt SET v = 100 WHERE id = 1;//v = 100
SELECT v FROM tt WHERE id = 1;//v = 100
//v = 100 (Dirty Read!)	ROLLBACK;//v = 5

あるトランザクションが更新されている最中に、他のトランザクションからデータを読み出すことができてしまう現象です。

2. Non-repeatablbe read

Session 1	Session 2
SELECT v FROM tt WHERE id = 1;//v = 5
BEGIN; UPDATE tt SET v = 100 WHERE id = 1;//v = 100
COMMIT; //v = 100
SELECT v FROM tt WHERE id = 1; //v = 100
//v = 5? v=100?

同じデータを読み込むたびに値が他のトランザクションから変わってしまう現象です。

3. Phantom read

Session 1	Session 2
SELECT * FROM tt WHERE id = 1;//id = 1, v=5
BEGIN; INSERT INTO tt VALUES ( 2, 8);
COMMIT;
SELECT * FROM tt WHERE id = 1; //d = 1, v=5 id = 2, v=8

並行して動作する他のトランザクションが追加したり削除したデータが途中で見えてしまうため、処理の結果が変わってしまう現象です。

また通常の場合SelectはSlaveから取るので、Isolation levelがREAD COMMITTEDと同じくdirty readsのことから防ぎます。だが、Non-repeatablbe readやPhantom readのことは防げません。ここは注意すべきなことです。

補足ですが、innodbのTransactionのisolation levelはRepeatable readにdefaultされます。同じtransaction以内のselectは他のtransactionのupdateに影響されません。それで、Non-repeatablbe readのことが防ぎます。

しかし、それでもphantom readのことを避けられる保障がありません。通常のやり方はNext-key lockingという方法で解決します。または、isolation levelをSERIALIZABLEにする方法です。だが、この方法には全てのselectもSelect ...lock in share modeにするので、Lockについて実装する必要性を検討します。

Select for Update

データを挿入または更新する時、ほかのトランザクションで同じ行のデータを先に更新するまたは削除することもできます。

Session 1	Session 2
SELECT v FROM tt WHERE id = 1;//v = 5
v = v - 1; //v = 4	SELECT v FROM tt WHERE id = 1;//v = 5
UPDATE tt SET v = v - 1 WHERE id = 1;//v = 4	v = v - 1; //v = 4
UPDATE tt SET v = v - 1 WHERE id = 1;//v = 4

例えば、これはお金の取引でttは >0の状況しかお金払えます(v--)。こういう場合は、session 1でお金が０になったでもSession 2では続き払えます。それで、select for updateが必要です（commitまで、デーダを他のtransactionからロックます）。

Dead Lockの異常状況

Dead Lockとは、２つ以上のプログラムが資源の解放を待っていることです。通常はタイムアウトで処理をやり直します(my.cnfでinnodb_lock_wait_timeout = n の待ってる時間を設定する)。

DBの設計にはロックの順番を統一する方法もあります。テーブルの間がまたがってる場合、テーブル順をソートします。参照と更新処理をまぜないようにします。