postgresqlselecttransactionsisolation-levelacid

Does using a transaction for read queries provide benefits under Read Committed Isolation Level in PostgreSQL?


I'm using Prisma with PostgreSQL. As I read about the transaction isolations in PostgreSQL, I wondered if I am using transactions without any benefits or if I understood the subject correctly.

async getProductCounts(categoryIds: string[]): Promise<any> {
  const stats = await this.prisma.$transaction(async (tx) => {
    const productsWithPrice = await tx.product.findMany({
      where: {
        categoryId: {
          in: categoryIds,
        },
        price: {
          not: null,
        },
      },
    });

    const productsWithoutPrice = await tx.product.findMany({
      where: {
        categoryId: {
          in: categoryIds,
        },
        price: null,
      },
    });

    return {
      productsWithPrice,
      productsWithoutPrice,
    };
  });

  return stats;
}

Given that the Read Committed isolation level is used, does wrapping these read-only findMany operations in a transaction provide any benefits?

I understand that Read Committed will make the 2 read queries see different snapshots anyways, so why would one use transaction here?

I am asking this to understand the transaction subject in PostgreSQL better.

Would this be any different if the second query was an update? Thank you for your answers.


Solution

  • There is no good reasons to put two read-only statements in a single transaction on the read committed isolation level.

    If only the second statement modifies data, there is still no good reason.