【PostgreSQL】 並列実行時のトランザクション管理の注意が必要なパターンと解決策

この記事について

• 以下Youtube動画を参考にGo×PostgreSQLで簡易銀行APIを作成している
• その中でトランザクションの部分が少し複雑だったため、自分の頭の整理がてらまとめてみる www.youtube.com

前提

• ある口座から別の口座へ金額を移すAPIを2つのゴールチンでテストして、トランザクションの正当性を確認している

同じ時点のデータを2ルーチンが同時に更新してしまうパターン

事象

• 更新前
  • 送金元:380
  • 送金先:390
    • 送金元から送金先へ10ドル送付するという処理を2ルーチンで動かす
• 更新後
  • ルーチン1
    • 送金元:370（380-10）
    • 送金先:400（390+10）
      • 想定通り
  • ルーチン2
    • 送金元370 ※想定360
    • 送金先410（400+10）
      • 送金元が想定と異なる！

原因

• select時に2ルーチンで同じ時点（値が370時点）の送信元データを取得してしまっているため

解決策

• SELECT文にFOR UPDATEを追加することで、あるルーチンでデータ取得している際は、そのルーチンが COMMIT か ROLLBACK するまでブロックされ処理が止まる

SELECT * FROM accounts 
WHERE id = $1 LIMIT 1
FOR UPDATE;

外部キー制約が原因でクエリの実行がブロックされるパターン

事象

• 更新用にaccounts取得のクエリを実行したところ、transfers tableのレコード 挿入が原因でクエリの実行がブロックされる

// ルーチン2で実行し、ブロックされたクエリ
SELECT * FROM accounts 
WHERE id = $1 LIMIT 1
FOR UPDATE;

// ルーチン1で実行し、ブロック原因になっているクエリ
INSERT INTO transfers (
    from_account_id,
    to_account_id,
    amount
) VALUES (
    $1,
    $2,
    $3
) RETURNING *;

原因

• 前提として、transfers tableのfrom_account_idと to_account_idは、accountstableの外部キーとなっている。
• transfers tableの更新が行われるトランザクション実行中に、万が一accounts tableのidが書き換えられてしまうと、不整合なtrasferレコードを作成してしまうことになる。
• それを防ぐためtransfers tableの実行中はaccountstableの更新ができない
• 動画ではルーチン1が更に SELECT * FROM accounts・・・を発行し、ルーチン2でそれ以前に実行されたINSERT INTO transfersによりブロック、一方でルーチン2はルーチン1によってブロックされているという状態つまりdeadlock状態となっていた

解決策

• SELECT文にFOR NO KEY UPDATE を追加することで、キーの更新が行われないことをPostgreSQLへ伝える事ができ、ブロックされなくなる

SELECT * FROM accounts 
WHERE id = $1 LIMIT 1
FOR NO KEY UPDATE;

後日追記

• 上記のやり方は排他制御の中でもいわゆる悲観ロックと呼ばれるやり方で、実装コストが高い
  • 例えばSELECT文発行後にロックした状態でそっちのルーチンに何らか問題が発生した場合、他が一切処理出来ない状態となる
• 排他制御（楽観ロック・悲観ロック）の基礎 - Qiita