☰

MySQL: トランザクションとテーブルのロック 質問する

tag-icon • tag-icon sql mysql transactions locking
MySQL: トランザクションとテーブルのロック 質問する

データベースの整合性を確保し、SELECT と UPDATE が同期されたままになり、他の接続が干渉しないようにするために、トランザクションとテーブルのロックのどちらを使用するかについて、少し混乱しています。次の操作が必要です。

SELECT * FROM table WHERE (...) LIMIT 1

if (condition passes) {
   // Update row I got from the select 
   UPDATE table SET column = "value" WHERE (...)

   ... other logic (including INSERT some data) ...
}

他のクエリが干渉して同じことSELECT(その接続が行の更新を完了する前に「古い値」を読み取ること) を実行しないようにする必要があります。

LOCK TABLES table一度に 1 つの接続のみがこれを実行していることを確認し、完了したらロックを解除するようにデフォルトで設定できることはわかっていますが、やりすぎのように思えます。これをトランザクションでラップすると、同じこと (別の接続が処理中に同じプロセスを試行しないこと) が実行されますか? またはSELECT ... FOR UPDATE、またはの方SELECT ... LOCK IN SHARE MODEが良いでしょうか?

ベストアンサー1

テーブルをロックすると、他の DB ユーザーがロックした行/テーブルに影響を与えることがなくなります。ただし、ロックだけでは、ロジックが一貫した状態で出力されることは保証されません。

銀行システムについて考えてみましょう。オンラインで請求書を支払う場合、その取引によって影響を受ける口座が少なくとも 2 つあります。お金が引き落とされるあなたの口座。お金が振り込まれる受取人の口座。そして、銀行が喜んで取引に課される手数料をすべて入金する銀行の口座です。銀行が極めて愚かであることを考えると (最近では誰もが知っていることですが)、銀行のシステムは次のように機能するとしましょう。

$balance = "GET BALANCE FROM your ACCOUNT";
if ($balance < $amount_being_paid) {
    charge_huge_overdraft_fees();
}
$balance = $balance - $amount_being paid;
UPDATE your ACCOUNT SET BALANCE = $balance;

$balance = "GET BALANCE FROM receiver ACCOUNT"
charge_insane_transaction_fee();
$balance = $balance + $amount_being_paid
UPDATE receiver ACCOUNT SET BALANCE = $balance

ロックもトランザクションもないため、このシステムはさまざまな競合状態に対して脆弱です。その最大のものは、複数の支払いがあなたのアカウントまたは受取人のアカウントで並行して実行されることです。あなたのコードが残高を取得して huge_overdraft_fees() などを実行している間に、他の支払いで同じタイプのコードが並行して実行される可能性は十分にあります。彼らはあなたの残高 (たとえば $100) を取得し、トランザクション (あなたが支払う $20 と彼らがあなたにだましている $30 を取り出す) を実行し、両方のコード パスに 2 つの異なる残高 ($80 と $70) が存在します。どちらが最後に終了するかによって、最終的にアカウントに残るはずだった $50 ($100 - $20 - $30) ではなく、その 2 つの残高のいずれかが残ります。この場合、「銀行のエラーはあなたに有利」です。

Now, let's say you use locks. Your bill payment ($20) hits the pipe first, so it wins and locks your account record. Now you've got exclusive use, and can deduct the $20 from the balance, and write the new balance back in peace... and your account ends up with $80 as is expected. But... uhoh... You try to go update the receiver's account, and it's locked, and locked longer than the code allows, timing out your transaction... We're dealing with stupid banks, so instead of having proper error handling, the code just pulls an exit(), and your $20 vanishes into a puff of electrons. Now you're out $20, and you still owe $20 to the receiver, and your telephone gets repossessed.

So... enter transactions. You start a transaction, you debit your account $20, you try to credit the receiver with $20... and something blows up again. But this time, instead of exit(), the code can just do rollback, and poof, your $20 is magically added back to your account.

In the end, it boils down to this:

Locks keep anyone else from interfering with any database records you're dealing with. Transactions keep any "later" errors from interfering with "earlier" things you've done. Neither alone can guarantee that things work out ok in the end. But together, they do.

in tomorrow's lesson: The Joy of Deadlocks.

おすすめ記事