Prisma•4mo ago

Prisma Interactive transactions not waiting on await statement

Prisma version: 5.19.1 Database Engine: Postgres I am running the following code structure inside an interactive transaction

async function doStuff() {
  await prisma.$transaction(async (txn) => {
    await writeToStuff1(txn)
    await writeToStuff2(txn)

    await writeToStuff3WDependentOnStuff1(txn)
    await writeToStuff4WDependentOnStuff2(txn)
  })
}

async function doStuff() {
  await prisma.$transaction(async (txn) => {
    await writeToStuff1(txn)
    await writeToStuff2(txn)

    await writeToStuff3WDependentOnStuff1(txn)
    await writeToStuff4WDependentOnStuff2(txn)
  })
}

I noticed that the code inside $transaction is not waiting for writeToStuff1(txn) to complete, even though await keyword is used. The code execution is moving to writeToStuff3WDependentOnStuff1, before row is add to database for Stuff 1 (writeToStuff1). This is causing error and rollbacks as writing to stuff3 requires writing to stuff1 is complete and a row exists. When I ran the code without $transaction block, its respecting the await keyword again, and waiting for writeToStuff1() to finish before moving to writeToStuff3WDependentOnStuff1() Could someone confirm if await keyword, Prisma Interactive transactions, doesn't guarantee wait until completion of statement? If yes, any suggestions to achieve the above using $transaction but guaranteeing statement order? Thanks

1 Reply

auditt800OP•4mo ago

Using isolationLevel: Prisma.TransactionIsolationLevel.Serializable seems to have solved the problem. The new issue after that was Transaction failed due to a write conflict or a deadlock. Please retry your transaction I added the retry logic, but the issue didn't resolve. So I made all three createMany calls, in my code, serial and the issue was gone. Initially I was collecting promises of the three createMany calls, then resolving it using Promise.all(promises); This parallel calls seems to be causing write conflict or a deadlock. Let me know if there is a better approach to solve this. And also if the current solution is reliable Solution:

while (retries < MAX_RETRIES) {
    try {
      await client.$transaction(
        async (tx) => {
          await writeToStuff1(txn)
          await writeToStuff2(txn)
      
          await writeToStuff3_DependentOnStuff1(txn)
          await writeToStuff4_DependentOnStuff2(txn)

          await createManyStuff5_DependentOnStuff2(txn)
          await createManyStuff6_DependentOnStuff2(txn)
          await createManyStuff7_DependentOnStuff2(txn)
      },
      {
        maxWait: 5000, // default: 2000
        timeout: 10000, // default: 5000
        isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
      }
      );
      break;
    } catch (error: any) {
      if (error.code === "P2034") {
        retries++;
        continue;
      }
      throw error;
    }
}

while (retries < MAX_RETRIES) {
    try {
      await client.$transaction(
        async (tx) => {
          await writeToStuff1(txn)
          await writeToStuff2(txn)
      
          await writeToStuff3_DependentOnStuff1(txn)
          await writeToStuff4_DependentOnStuff2(txn)

          await createManyStuff5_DependentOnStuff2(txn)
          await createManyStuff6_DependentOnStuff2(txn)
          await createManyStuff7_DependentOnStuff2(txn)
      },
      {
        maxWait: 5000, // default: 2000
        timeout: 10000, // default: 5000
        isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
      }
      );
      break;
    } catch (error: any) {
      if (error.code === "P2034") {
        retries++;
        continue;
      }
      throw error;
    }
}

Gaming

Programming

Prisma Interactive transactions not waiting on await statement