PrismaとTypeScriptで実現する、PostgreSQLでのFOR UPDATEと集約関数との同時利用法

今回はTypeScriptとPrismaを用いた例を中心に、PostgreSQLでロックとデータの集約を同時に行う方法について解説します。

PostgreSQLでは、FOR UPDATEと集約関数を同時に使用することが許可されていません。

しかし、一時的なテーブルを作成することで、この問題を効果的に回避することが可能です。

その一つの方法として、Common Table Expression（CTE）を使用する手法をここでは紹介します。

集約関数とは？

集約関数は、複数のレコードを一つにまとめる関数です。

PostgreSQLでよく使われる集約関数には、例えばSUM（合計値を求める）やCOUNT（レコード数を数える）などがあります。

これらの関数を用いると、特定の条件を満たすレコードの合計値や数を手軽に計算することができます。

FOR UPDATEとは？

PostgreSQLではFOR UPDATEを使うことで、一連のトランザクションを行う際に、選択したレコードを他のトランザクションから隔離することができます。

この機能により、あるトランザクションがそのレコードを変更または削除するのを防ぎます。

しかし、PostgreSQLでは、FOR UPDATEと集約関数を直接組み合わせることは出来ません。

そのため、集約処理とロックを同時に行いたい場合には、別の手法を用いる必要があります。

Common Table Expression (CTE)とは？

Common Table Expression（CTE）はSQLの一部で、一時的な結果セットを生成します。

CTEは、名前をつけたサブクエリのようなものであり、その結果はその後のクエリの中で再利用することができます。

CTEを使うと、複雑なクエリをより読みやすく、管理しやすい形に分解することが可能になります。

Common Table Expression（CTE) を使用する例

まず、CTEを使用せずにFOR UPDATEと集約関数を用いるケースを考えてみます。

指定した商品IDに対して対象となる全レコードを選択しようとすると以下のようなコードになります。

const inventoryEntries = await tx.$queryRaw`
    SELECT * FROM "Inventory" WHERE "itemId" = ${id} FOR UPDATE;`;

これでレコード全体がロックされ、選択したレコードを他のトランザクションから隔離することができます。

ただし、この方法では全てのレコードを取得しているため、レコードの数が非常に多い場合には時間がかかることが懸念されます。

一方、CTEを使用すると選択したレコードの集約を効果的に行うことができます。

まず一つ目のクエリで目的の商品を抽出し、その結果を一時的なテーブルに保存します。

そして二つ目のクエリでそのテーブルのレコード数を数えます。

そのコードは以下の通りです。

const result = await tx.$queryRaw`
  WITH "temporaryTable" AS (
    SELECT "itemId" FROM "Inventory" WHERE "itemId" = ${id} FOR UPDATE
  ) 
  SELECT COUNT(*) FROM "temporaryTable";
`

上記のCTEを使用したアプローチにより、大量のレコードデータを一度に取得することなく、必要な数を効率的に得ることができます。

まとめ

アプリケーションのパフォーマンスは、必要なデータのみを効率的に操作することにより向上します。

この方法は、データ量が非常に大きい際に、データ抽出と集約処理を効率的に行うための有効な手段となります。