gotoshin

主に学んだ事の自分メモ用です。記事に書くまでも無いような事はhttps://scrapbox.io/study-diary/に書いてます。

【PostgreSQL】複数トランザクションを並列実行時の順序制限によるdeadlock回避

この記事について

前回の動画ではクエリの機能を活用しdeadlockを防いでいたが、今回は実行するクエリの順序を調整してデータロックを防ぐ

How to avoid deadlock in DB transaction? Queries order matters! www.youtube.com

事象

二つのトランザクション内で、同じレコードのデータを異なる順序で更新しようとすると、お互いのCommit待ちとなりdeadlockが発生してしまう

  • 以下の処理が同時に実行された場合、
    • 口座1⇨口座2へ10ドル送金
    • 口座2⇨口座1へ10ドル送金

① Transaction1内で口座1を10ドルプラス⇨成功

// Transaction1を開始
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 1 RETURNING *;

② Transaction2内で口座2を10ドルプラス⇨成功

// Transaction2を開始
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 2 RETURNING *;

③ Transaction1内で口座2を10ドルマイナス⇨口座2はTransaction2内で更新中のためロック

// Transaction1内
UPDATE accounts SET balance = balance - 10 WHERE id = 2 RETURNING *;

④ Transaction2内で口座1を10ドルマイナス⇨口座1はTransaction1内で更新中のためロック

// Transaction2内
UPDATE accounts SET balance = balance - 10 WHERE id = 1 RETURNING *;

deadlock発生!!!

解決策

更新する順序を揃えることで、片方のトランザクションのクエリがCommitされるまでもう片方のトランザクションのクエリは実行不可となり、deadlockは発生しない

① Transaction1内で口座1を10ドルプラス⇨成功

// Transaction1を開始
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 1 RETURNING *;

② Transaction2内で口座1を10ドルマイナス⇨口座1はTransaction1内で更新中のためロック

// Transaction2を開始
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 2 RETURNING *;

③ Transaction1内で口座2を10ドルマイナス⇨成功しTransaction1はCommitされる、その後中止されていた②の処理が動く

// Transaction1内
UPDATE accounts SET balance = balance - 10 WHERE id = 2 RETURNING *;

④ Transaction2内で口座2を10ドルプラス⇨成功

// Transaction2内
UPDATE accounts SET balance = balance - 10 WHERE id = 1 RETURNING *;