[解決済み] 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 の方が良いですか？

どのように解決するのですか？

テーブルをロックすることで、他の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つの残高のどちらかが残ることになります。この場合、quot;あなたに有利な銀行のエラー"。

次に、ロックを使用するとします。請求書の支払い($20)が先にパイプに到達したので、それが勝って、あなたの口座記録をロックします。これで、あなたは独占的に使用できるようになり、残高から20ドルを引き、新しい残高を安心して書き戻すことができます...そして、あなたの口座は予想通り80ドルになっています。しかし、受取人の口座を更新しようとしたら、ロックされていて、しかもコードの許容範囲を超えて長くロックされ、取引のタイミングを逃してしまう... 我々は愚かな銀行を相手にしているので、適切なエラー処理を行う代わりに、コードは単に exit() で、20ドルは電子の塊になって消えてしまいます。そして、あなたは20ドルを失い、受取人にまだ20ドルの借りがあり、あなたの電話は差し押さえられるのです。

そこで...トランザクションを入力します。取引を開始し、自分の口座から20ドル引き落とし、受取人に20ドルを入金しようとすると...また何かが吹っ飛ぶ。しかし今度は、代わりに exit() の代わりに、コードは rollback を実行すれば、20ドルは魔法のようにアカウントに戻されます。

結局のところ、これに尽きるのです。

ロックは、あなたが扱っているすべてのデータベースレコードに他の人が干渉しないようにします。トランザクションは、quot;later" エラーを、quot;earlier" の実行に干渉させないようにします。どちらも単独では、最終的に物事がうまくいくことを保証するものではありません。しかし、一緒になれば、うまくいくのです。

明日のレッスンでは、「デッドロックの楽しさ」を紹介します。