そーだいなるらくがき帳

そーだいが自由気侭に更新します。

PostgreSQLのread committed時におけるUPDATEの挙動について

発端

　反復不能読み取り=ファジーリードがどのように作用するかって質問　

www.postgresql.jp

追記

　質問主が完璧な検証してた。 そっちの方が良いのでそっちを見ましょう。

qiita.com

結論

　PostgreSQLは該当のUPDATEは共有テーブルロックをとり、実行時に取得対象になった対象に対して順次SQLが実行される*1*2ので値がマイナスになることはない。

　PostgreSQLのロックについては澤田くんが詳しいのでこの記事を読みましょう。 PostgreSQLアンカンファレンスなどでも発表されたりしています。

masahikosawada.github.io

　とはいえ、後述のそーだい本にも書いてありますが、ツイート元のSQLのような値が不定のSQLはロックを取って実行しましょう。

ファジーリードとトランザクション分離レベル

　失敗から学ぶRDBの正しい歩き方って本に書いてある。

ロックの功罪

　失敗から学ぶRDBの正しい歩き方から引用。

ロストアップデートはトランザクション分離レベルによって振る舞いが変わります。repeatable readを設定しているときは図14.5のように後勝ちで、完全に上書きになります。read committedの場合は、 UPDATE 商品 SET 価格 = 価格+5 WHERE id =1; のようなSQLであればほかのトランザクションのCOMMIT後の値が参照できるため、足し算した結果が保存されます。 　このように、ロストアップデートの振る舞いはトランザクション分離レベルによって変わるため、アンチパターンの例のように運用途中で変更した場合は在庫の値がずれるなど、SQLの結果が意図せず変更されることがあります。 とくに、 repeatable readがデフォルトのMySQLとread committedがデフォルトのPostgreSQLを併用して運用する場合、よく起こるミスです。

質問への検証

-- トランザクション分離レベルの確認
SHOW TRANSACTION ISOLATION LEVEL;
read committed


-- テストテーブル
create table test
(
    id     bigserial
        constraint test_pk
            primary key,
    amount numeric default (round(((random() * ((0 - 10))::double precision))::numeric, 0) + (10)::numeric) not null
);

alter table test
    owner to femcle_user;

-- まず100件くらいデータを作る
INSERT INTO test DEFAULT VALUES;

-- 実行するたびレコードがポポポポ～ンと増える
INSERT INTO test
SELECT  FROM test;

-- 登録件数
SELECT count(*) FROM test;
207093760

-- 初期状態
SELECT count(*) FROM test WHERE amount > 0;
196740402

SELECT count(*) FROM test WHERE amount = 1;
20712095

SELECT count(*) FROM test WHERE amount = 2;
20709853

SELECT count(*) FROM test WHERE amount = 3;
20706995

SELECT count(*) FROM test WHERE amount = 4;
20710020

-- トランザクション A
UPDATE test SET amount = amount -1 WHERE amount > 0;
196,740,402 rows affected in 14 m 55 s 360 ms

-- トランザクション B
UPDATE test SET amount = amount -1 WHERE amount > 0;
176,028,307 rows affected in 1 h 2 m 25 s 172 ms

-- UPDATE 実行中
SELECT count(*) FROM test WHERE amount = 1;
20712095

SELECT count(*) FROM test WHERE amount = 2;
20709853

-- トランザクション A終了後
SELECT count(*) FROM test WHERE amount = 1;
20709853

SELECT count(*) FROM test WHERE amount = 2;
20706995

-- トランザクションB終了後
SELECT count(*) FROM test WHERE amount = 1;
20706995

SELECT count(*) FROM test WHERE amount = 2;
20710020

まとめ

　みんな失敗から学ぶRDBの正しい歩き方を読んでくれよな！

*1:PostgreSQLのロックマネージャはほぼFIFO

*2:しかし複数のトランザクションがロック待ちで且つ、お互いが競合しない場合はロックの取得順ではなく、開放時に先に処理を始めた方なので厳密ではない