【PostgreSQL】トランザクションの中に別のトランザクションを入れる

はじめに

これは、PostgreSQL Advent Calendar 2024のシリーズ 2の24日目の記事となります。

2024/12/16 に1年以上かけて作成したアプリケーションをリリースしました。大きなトラブルはなく無事に本番稼働しています。
もともと Classic ASP(VB)で作成されていたアプリケーションを改善しながら10数年稼働させていましたが、.NET 8のBlazor(C#)で作り直しました。

複数台検証

ずっと1台で動かして開発してきましたが、実際に使用する環境は10数台が同時に動きます。
Webアプリケーション向けテスト自動化フレームワークPlaywrightを使用して自動で複数台検証をしたところ、たまに一意制約違反が発生していることが分かりました。

原因

アプリケーション独自の採番を使用しているのですが、タイミングによって同じ採番が取得されて登録するために、一意制約違反となってしまう。

筆者の中では採番は別のトランザクションで実行させているため、同じ採番が取得されることはないと思い込んでいたのです。しかし、実際には同じ採番が取得されてしまう。
それではと、リトライ処理を追加して一意制約違反となったら採番をやり直せばいいと思って実装してみるのですが、ロールバック無しでリトライすると下記エラーになってしまう。

current transaction is aborted commands ignored until end of transaction block

Copilotに聞く

PostgreSQLのNPGSQLにて、C#でトランザクションの中に別のトランザクションを入れて実行したい。どうすればいいですか

Copilotからの回答

PostgreSQL自体はネストされたトランザクションを直接サポートしていませんが、セーブポイントを使用することで、ネストされたトランザクションのような動作を実現できます。セーブポイントを使うと、トランザクション内で特定のポイントに戻ることができます。

そうか、PostgreSQL自体はネストされたトランザクションを直接サポートしていない。

対応

トランザクションの中に別のトランザクションを入れるってことは「トランザクションのネスト」のことでした。キーワードが分かれば検索がしやすくなります。
SAVEPOINTを使えば、トランザクション内で指定した位置までロールバックできることが分かりました。

リトライ処理を追加し一意制約違反エラーになった場合、SAVEPOINTの位置まで戻して採番をやり直します。ロールバックすることになるため、先程のtransaction is abortedエラーも出ません。

try
{
    bool isSuccess = false;

    for (int retry = 1; retry <= 3; retry++)
    {
        // シリアルを採番する
        serial = GetNewSerial(serial);

        manager.ExecuteNonQuery("SAVEPOINT retry_point");

        if (!Construction(serial, manager))
        {
            // 一意制約違反
            Logger.Debug($"Duplicate Serial={serial} Retry={retry}");
            manager.ExecuteNonQuery("ROLLBACK TO SAVEPOINT retry_point");
            continue;
        }
        isSuccess = true;
        break;
    }

    // 登録を行う
    if (isSuccess && !Registered(serial, manager))
        return false;

    return isSuccess;
}
catch (Exception e)
{
    return false;
}

別案

Copilotに聞く

メイントランザクションの中で、採番の値だけ自動コミットさせることは出来ますか

Copilotからの回答

PostgreSQLでは、メイントランザクションの中で特定の操作だけを自動コミットさせることは直接的にはできません。しかし、シーケンスを使用することで、採番の値をトランザクションの外で取得することが可能です。シーケンスはトランザクションに依存せずに動作するため、トランザクションの中でも外でも同じように動作します。

シーケンスを使えばトランザクションに依存しないとのことです。
アプリケーション独自の採番も8桁中の末尾5桁は連番となっているため、シーケンスを使う修正案も考えたのですが、上記対応で解決できたのでやめました。

最後に

生成AIのいいところは、「トランザクションの中に別のトランザクションを入れる」といった質問でも、「トランザクションのネスト」と解釈して回答をくれるところですね。
ネストと指摘されれば、そうなんですが、このキーワードが意外と思い付かないもんです。