prisma: Transaction API error: Transaction already closed: Transaction is no longer valid. Last state: 'Expired' P2028

I had the same issue and I solved it with a Promise.all I work on a local postgre. I was doing

const documentsToArchive: DocumentAttributes[] = [...]

await prisma.$transaction(async (trx) => {
  for (const documentToArchive of documentsToArchive) {
    await trx.documents.updateMany({ data: { status: 'ARCHIVED'}, where: documentToArchive })
  }
})

Now I do

const documentsToDelete: DocumentAttributes[] = [...]

await prisma.$transaction(async (trx) => {
  await Promise.all(documentsToArchive.map((documentToArchive) =>
    trx.documents.updateMany({ data: { status: 'ARCHIVED'}, where: documentToArchive })))
})

Hope it can help to reproduce & to solve for some people ✌🏻

Hey everyone!

This is a complex issue in a sense that people run into it under different circumstances, and whether it is easily reproducible or only occurs intermittently or even once also differs for various people.

From what I can see, in most cases where some kind of reproduction was provided, the error looks very plausible and doesn’t seem to be an issue in Prisma looking at the code alone without specific numbers.

I believe that in those cases where we couldn’t see a reproduction, even where the error was very elusive and hard to reproduce again for the reporter, the error was still technically correct and the transactions timed out. However, there is still an open question of whether it is expected for the operations inside the transaction to take as much time as they took for everyone who reported it — as well as what exactly the numbers were. I think some people might just be facing network issues that make their queries take longer than usual to get to the database (the original report, for example, mentions serverless functions, which implies remote database), but there could theoretically be a possibility that we might have a performance issue somewhere which makes some operation slower when used inside a transaction, although I haven’t seen an indication of it so far.

It also looks like some people were just confused by the error itself and thought it was some internal error and not a user-facing error indicating that the transaction has timed out.

To alleviate these problems, we would like to improve the error messages related to interactive transactions to make them more informative and user-friendly, re-add the information that was lost since version 4.1.0, as well as provide new information and more visibility into the expired transactions.

Aside from the reason why the transaction was closed, expired transactions will return the transaction timeout and the actual time that has passed since the start of the transaction as a part of the error message: Transaction API error: Transaction already closed: A query cannot be executed on an expired transaction. The timeout for this transaction was X ms, however Y ms passed since the start of the transaction. Consider increasing the interactive transaction timeout or doing less work in the transaction.

Hopefully this should be less confusing and more helpful for those who are doing a lot of work in the transaction callback and run into this error as expected.

For those who don’t believe they should be running into this error, it should provide more context about your circumstances that you could share with us — and I would suggest opening new, more specific, issues instead of reusing this very general and ambiguous one. Any reports about this functionality not working as you would expect are still very welcome and wanted!

While the new error message will provide some basic visibility into the timing of an interactive transaction (which is already immensely more useful than nothing), I would suggest using tracing for those who want more advanced visibility and to learn why exactly a transaction is taking as much time as it does. We would really appreciate if you include this information in your bug reports btw!

I know it’s late, but in case you’re using prisma transaction inside an arr.foreach() it works if you not use the transaction refence, I mean this: instead of this (watch the tx inside the loop): const result = await prisma.$transaction(async (tx) => { deal.activities.forEach(async (activity) => { const dealActivity = await tx.crmDealActivity.create() } }

use this ( watch the prisma inside the loop, maybe tx is not recognised inside the scope) const result = await prisma.$transaction(async (tx) => { deal.activities.forEach(async (activity) => { const dealActivity = await prisma.crmDealActivity.create() } }

I know we’re missing the point of the transaction, but maybe you can use it anyway in an specific part of a bigger transaction.

it worked for me. Regards.

Yes this bug keeps troubling and blocks the development. Have tried killing idle processes, disconnecting all open connections, stopping database server but nothing works. Somehow it finds that some transaction was not closed properly.

This issue was closed with https://github.com/prisma/prisma/issues/9584 in version 3.9 I have version 3.14 but the issue still exists.

Any update on this?

I think most people are encountering this issue through running concurrent interactive transactions in a .map() and .forEach() - it is desireable to be able to do this so would welcome an extension to the documentation to better describe how to achieve concurrent running interactive transactions.

after reading provided solutions and comments, I didn’t understand why it works with good old for loop but not with Array.forEach or Array.map and also some people mentioned that using Promise.all solved the issue for their use cases. Can someone clarify the reason and work around for that case? it will be nice for people coming afterwards.

one feedback, I would expect to see more comments from Prisma team. It will give a bit more relief to people using Prisma on their production environment as database layer libraries are one of the core and fundamental parts of most web/server based applications.

Ran into this issue today as well – turns out the fix was to convert my arr.forEach(...) loop into a good old for (let i = 0; i < arr.length; i++) and everything worked like a charm!

I also encountered this issue in ver 4.5.0 and it is happening under high load in implementations where the only way is to use a for loop.

Still Experiencing this issue, happens when I make a request to the DB in immediate succession, that is if there is more than one request at the same time

Solved this, seems to happen whenever there is a for each loop inside a prisma.$transaction

A simple example would be:

const upsertIssue = await prisma.$transaction(async (prismaTransaction) => {
   await upsertApplicationData(prismaTransaction, application, issue);
   //the transaction would be closed due to the for loop in the upsertApplicationWithIssueData function so any subsequent upsert function would fail with the error
   const upsertIssue = await upsertIssue(prismaTransaction, application, issue);
 }
 
 async function upsertApplicationData(prismaTransaction, application, issue) {
  for (const releaseVersion of Issue.ReleaseVersions) {
    const application = await prismaTransaction.application.upsert({
      where: {
          application_id: application.id,
        },
      },
      create: {
        application_id: upsertApplication.application_id,
        jira_release_version: issue.releaseVersion,
      },
      update: {
        jira_release_version: issue.releaseVersion,
      },
    });
  }
}

The upsertApplicationData function called within the transaction is causing this transaction to close with the error:

Transaction API error: Transaction already closed: A query cannot be executed on a closed transaction..

Hi, I’m also experiencing this issue. I will attempt to reproduce it in a simplified environment later on today

UPDATE: unable to reproduce today, will continue through the week UPDATE (10/23): still unable to reproduce

If you can still reproduce some part of this problem, please open a new issue and provide all the information the bug issue template asks for - and optimally a reproduction we can run ourselves. Thanks.

For us the issue was closed by improving the error message, as there was no clear reproduction of what was going on. We unfortunately can not possibly respond to all not directly related comments in all our issues. That would make things a lot more confusing to everyone (and impossible for us to manage). Hope you understand.

Just a thought regarding the proposed error message: Would it be good to include a link or a hint how and where to increase the transaction timeout?

I also found that this error would come up when I had a transaction that called an awaited promise to Prisma that was NOT using the transaction.

@sebmellen

await prisma.$transaction( async (tx) => { // Code running in a transaction... }, { maxWait: 5000, // default: 2000 timeout: 10000, // default: 5000 isolationLevel: Prisma.TransactionIsolationLevel.Serializable, // optional, default defined by database configuration } )

This is from the official document. https://www.prisma.io/docs/concepts/components/prisma-client/transactions

For anyone trying add a LOT of data (for example, during seeding, more than 10,000 rows, Prisma will still timeout even with Promise.all) In that case, you can split up the data into chunks of transactions

✅ Works under 500ms to add over 10k rows…

    const chunkSize = 100; // I found 100 to work the fastest
    const chunks = [];
    for (let i = 0; i < seed_data.length; i += chunkSize) {
      chunks.push(seed_data.slice(i, i + chunkSize));
    }

    await Promise.all(
      chunks.map(async (chunk) => {
        await db.$transaction(async (tx) => {
          await Promise.all(
            chunk.map((chunkData) => {
              return tx.YOUR_TABLE_HERE.create({
                data: chunkData,
              });
            }),
          );
        });
      }),
    );

I had the same issue and I solved it with a Promise.all I work on a local postgre. I was doing

const documentsToArchive: DocumentAttributes[] = [...]

await prisma.$transaction(async (trx) => {
  for (const documentToArchive of documentsToArchive) {
    await trx.documents.updateMany({ data: { status: 'ARCHIVED'}, where: documentToArchive })
  }
})

Now I do

const documentsToDelete: DocumentAttributes[] = [...]

await prisma.$transaction(async (trx) => {
  await Promise.all(documentsToArchive.map((documentToArchive) =>
    trx.documents.updateMany({ data: { status: 'ARCHIVED'}, where: documentToArchive })))
})

Hope it can help to reproduce & to solve for some people ✌🏻

Ran into this issue today as well – turns out the fix was to convert my arr.forEach(...) loop into a good old for (let i = 0; i < arr.length; i++) and everything worked like a charm!

Why does this work? Man I’ve been breaking my head about this issue, it’s also only an issue on a Windows machine, on my mac it’s working fine using for…of

Can you please open a new issue for this @BCsabaEngine, we want to take a look at this behavior. Thanks.

Issue is not issue. Transation timeout occurs problem. Solved.

@Pompedup your example also hints at the transaction actually timing out, since the problem was solved by rewriting a slow sequential algorithm to a faster concurrent one.

Not taking the specific of your query into the account and just talking about the fact that you don’t have any dependencies between your queries, I think a slightly better option would’ve been using a batch transaction instead of an interactive transaction:

await prisma.$transaction(documentToArchive.map((documentToArchive) =>
  prisma.documents.updateMany({ data: { status: 'ARCHIVED'}, where: documentToArchive }))

However, regardless of the type of transaction, the big problem here is updateMany inside the loop, since you are probably scanning the whole table on every iteration.

The best option would be getting rid of both the transaction and the loop altogether and replacing it with a single updateMany call if possible — this could be especially cheap if you know the IDs of the documents.

@heymartinadams:

you can keep the for loop if you use for await (const ....

I don’t think this is correct. An array is not an async iterable and doesn’t require for await, and using for await wouldn’t make anything faster here since it is sequential too.

Unfortunately two months back we had to shift to pg (node-postgres) from prisma to get rid of the issue.

I wonder if this could be related. Could prisma be keeping the transaction open when this error is triggered? I don’t know why there are so many processes idle in transaction.