【PostgreSQL】トランザクション分離レベル毎の競合時の動作を比較する

マネックス・ラボ DB

turtle

こんにちは、エンジニアの田代です。
昨年はインフラのプロジェクトが中心だった為書く記事もコンテナ関連ばかりでしたが、最近は開発に携わっている事の方が多く、
その中で改めて考える必要があったPostgreSQLのトランザクション分離レベルについて書こうと思います。

始めに

Isolation Level Dirty Reads Non-repeatable Reads Phantom Reads
READ UNCOMMITTED 起こり得る 起こり得る 起こり得る
READ COMMITTED 安全 起こり得る 起こり得る
REPEATABLE READ 安全 安全 起こり得る
SERIALIZABLE 安全 安全 安全

RDBMSのトランザクション分離レベルについて調べると、上記のようなマトリクス表を見る機会が多いと思います。
しかし、この表が知識として頭に入っていたとしても、実際に業務ロジックを考える際は試してみないと動作に確証が持てなかったりするのではないでしょうか。
そこで今回は、出金や残高更新と言った具体的なビジネスロジックを例に取り、各レベルでの競合時の動作を検証してみたいと思います。

出金に伴う残高テーブルのUPDATE

  1. トランザクション開始
  2. 残高テーブルから現在の残高(amount)を取得
  3. 残高が出金額(100)以上かをチェック
  4. 出金額(100)の分amountを減算しコミット

先ずは上記のロジックについて、競合時の動作を検証してみます。

-- テーブル作成

CREATE TABLE IF NOT EXISTS public.balance
(
    id bigint NOT NULL DEFAULT nextval('balance_id_seq'::regclass),
    amount bigint NOT NULL,
    CONSTRAINT balance_pkey PRIMARY KEY (id)
)

-- 残高レコードを現在のamount=100でINSERT

INSERT INTO public.balance(
    id, amount)
    VALUES (1, 100);
Transaction A Transaction B
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT amount FROM balance WHERE id = 1;
-- アプリケーション側でamount >= 100かどうかチェック
SELECT amount FROM balance WHERE id = 1;
-- アプリケーション側でamount >= 100かどうかチェック
UPDATE balance SET amount = amount - 100 WHERE id = 1;
COMMIT;
UPDATE balance SET amount = amount - 100 WHERE id = 1;
COMMIT;
SELECT amount FROM balance WHERE id = 1;
-- -100
SELECT amount FROM balance WHERE id = 1;
-- -100

Transaction Bが残高チェックを行なった後でTransaction Aが-100減算コミットした結果、
本来Transaction Bは残高不足として失敗させるべきですが実際の残高以上の出金が行われてしまい、
残高-100の不整合の状態となってしまいました。
トランザクション分離レベルをREPEATABLE READ以上にするとどうなるでしょうか。

Transaction A Transaction B
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT amount FROM balance WHERE id = 1;
-- アプリケーション側でamount >= 100かどうかチェック
SELECT amount FROM balance WHERE id = 1;
-- アプリケーション側でamount >= 100かどうかチェック
UPDATE balance SET amount = amount - 100 WHERE id = 1;
COMMIT;
UPDATE balance SET amount = amount - 100 WHERE id = 1;
COMMIT;
-- ERROR: could not serialize access due to concurrent update
SELECT amount FROM balance WHERE id = 1;
-- 0

トランザクション分離レベルをREPEATBLE READに設定した場合は、
Transaction Aによりレコードが更新された事を検知し、Transaction Bがエラーとなりました。
上記の通り、このようなビジネスロジックで不整合を回避する為にはREPEATBLE READ以上に設定するか、
SELECT FOR UPDATEによる悲観ロック、或いは楽観ロックをアプリケーション側で実装する必要がある事が分かります。

出金に伴う取引レコードのINSERT

  1. トランザクション開始
  2. 取引テーブルの各レコードのamountをSUM()で総計し、残高を算出
  3. 残高が出金額(100)以上かをチェック
  4. 出金レコード(-100)を取引テーブルにINSERTしコミット

次に上記のロジックについて、REPEATABLE READとSERIALIZABLEで動作を検証してみます。

-- テーブル作成

CREATE TABLE IF NOT EXISTS public.transactions
(
    amount bigint NOT NULL,
    created_at timestamp with time zone NOT NULL
)

-- +100の入金レコードをINSERT

INSERT INTO public.transactions(
    amount, created_at)
    VALUES (100, NOW());
Transaction A Transaction B
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(amount) FROM transactions;
-- アプリケーション側でamount >= 100かどうかチェック
SELECT SUM(amount) FROM transactions;
-- アプリケーション側でamount >= 100かどうかチェック
INSERT INTO transactions (amount, created_at) VALUES (-100, NOW());
COMMIT;
INSERT INTO transactions (amount, created_at) VALUES (-100, NOW());
COMMIT;
SELECT SUM(amount) FROM transactions;
-- -100
SELECT SUM(amount) FROM transactions;
-- -100

Transaction Bが残高の算出を行なった後でTransaction Aが出金レコードをINSERTした結果、
実際の残高以上の出金が行われてしまい、残高-100の不整合の状態となってしまいました。
次に、トランザクション分離レベルをSERIALIZABLEに設定してみます。

Transaction A Transaction B
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(amount) FROM transactions;
-- アプリケーション側でamount >= 100かどうかチェック
SELECT SUM(amount) FROM transactions;
-- アプリケーション側でamount >= 100かどうかチェック
INSERT INTO transactions (amount, created_at) VALUES (-100, NOW());
COMMIT;
INSERT INTO transactions (amount, created_at) VALUES (-100, NOW());
-- ERROR: could not serialize access due to read/write dependencies among transactions
SELECT SUM(amount) FROM transactions;
-- 0

今度は、Transaction B中にTransaction AがレコードをINSERTした事を検知し、
エラーを出力して残高以上の出金を回避する事が出来ました。
このようなケースでは、不整合を避ける為にはトランザクション分離レベルをSERIALIZABLEに設定するか、
アプリケーション側でルートエンティティにロックを掛ける等実装を工夫する必要がある事が分かりました。

まとめ

今回紹介したビジネスロジックは一例であり、
実際には複数のテーブルを跨ぐトランザクションや、パフォーマンスの観点での並行性を考慮した設計等も必要になって来ると思いますが、
当記事が少しでも何かのヒントになれば幸いです。

田代 侑大システム開発部 マネックス・ラボ
人材募集