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

[revmischa]

Bug description

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

I'm using 3.14.0
I increased timeouts and these queries generally take ~500ms though they can take longer under load

{
      maxWait: 20000, // default: 2000
      timeout: 60000, // default: 5000
    }

{"is_panic":false,"message":"Transaction API error: Transaction already closed: Transaction is no longer valid. Last state: 'Expired'.","meta":{"error":"Transaction already closed: Transaction is no longer valid. Last state: 'Expired'."},"error_code":"P2028","clientVersion":"3.14.0"} 

Slack thread: https://prisma.slack.com/archives/CCWDULGUW/p1654457710257189

How to reproduce

Do a bunch of complex interactive transactions in different lambda functions running in parallel

Expected behavior

No response

Prisma information

3.14.0, in a lambda layer
serverless stack

Environment & setup

• OS: Lambda
• Database: Postgres
• Node.js version: 14

Prisma Version

prisma                  : 3.14.0
@prisma/client          : 3.14.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 2b0c12756921c891fec4f68d9444e18c7d5d4a6a (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 2b0c12756921c891fec4f68d9444e18c7d5d4a6a (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core 2b0c12756921c891fec4f68d9444e18c7d5d4a6a (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt 2b0c12756921c891fec4f68d9444e18c7d5d4a6a (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : 2b0c12756921c891fec4f68d9444e18c7d5d4a6a
Studio                  : 0.460.0

[Abdulrahmanelheyb]

I get the same error. Any resolve ?

[revmischa]

This error is causing me a lot of trouble, I'm happy to try to get more information. Is there anything I can do to get some more useful data for debugging it? Would really like to get this resolved one way or another.

[Abdulrahmanelheyb]

I tried add max options is work fine and I changing the value based on NODE_ENV

[revmischa]

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.

[manojmpc91]

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 #9584 in version 3.9
I have version 3.14 but the issue still exists.

[cesarvspr]

Still exists on 4.0.0

[felipdc]

Any update on this?

[SevInf]

Excessive CPU usage was fixed in prisma/prisma-engines#3028.
This fix was released in 4.1.0. If you haven't updated yet, could you update to the latest prisma version and report if you are still seeing this?
If you can reproduce it on 4.1.0+ — can you share the schema and code that causes it?

[manojmpc91]

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

[janpio]

@cesarvspr @felipdc @Abdulrahmanelheyb @revmischa Any help with creating a reproduction of this? We would love to be able to understand this and fix it - but right now we just do not have any code and way to run it that would lead to this error state. Thanks!

[t3ned]

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

[danstarns]

Do a bunch of complex interactive transactions in different lambda functions running in parallel

Can you give us some sort of figures to work with? What does a 'bunch' mean?

At the moment we cannot reproduce this.

[w8ze-devel]

Hello,

Any update on this ?

We are experiencing this issue very often too.

What is done is a simple interactive transaction. Sometimes it success, sometimes not without the underlying any change on the underlying data.

Thanks a lot for any help

[bazzscript]

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

[bazzscript]

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

[w8ze-devel]

And how @bazzscript did you solde this while using a transaction ?

Thanks

[garrensmith]

@w8ze-devel could you look at increasing the timeout for your transaction. To me it sounds like your transaction is timing out before all your operations have completed.

[t3ned]

@garrensmith When I faced this issue 4 weeks ago, I was using loops of various complexities (which could have been improved overtime). I kept increasing the timeout, but that didn't fix the issue regardless of the timeout.

I'm also still unable to reproduce it again

[Pompedup]

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 ✌🏻

[hironow]

Thank you so much for proceeding!

my commented

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.

I have checked that the loop rewritten to while also reproduced the problem.

[aqrln]

@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.

[aqrln]

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!

[andrewgreenh]

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?

[bazzscript]

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?

Yes, definitely.

[aqrln]

@andrewgreenh

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?

We could add a link to the docs there, this is a nice idea. Sorry I didn't see your comment earlier.
Is the current error message good enough for now, or do you think it's not that helpful without a link to the docs?

Optimally we'd also need to add a subheader we could link to directly, as currently the nearest header we could link to is https://www.prisma.io/docs/concepts/components/prisma-client/transactions#interactive-transactions-in-preview, and one would need to scroll a bit until the transaction options.

[steven-tey]

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!

[Karl-EdwardFPJeanMehu]

I recently started to experience this. I confirm @steven-tey 's solutions works

[erdinc]

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.

[janpio]

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.

[patriciorasquetti]

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.

[sebmellen]

Is there any way to increase the timeout? This is a hard blocker for us right now.

[ByBogon]

@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

[chrisui]

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.

[notflip]

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

[shadoath]

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.

[m1212e]

In my case this error appeared because I fired but did not await a promise from inside the async transaction callback

[Eh-haque]

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 ✌🏻

[c-andrey]

I solved using tthe promise.all as @Eh-haque commented above

[matthewjerome]

$transaction

ran into the same issue - if you need to run transactions inside a loop, consider using a generator function.

[avarayr]

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,
              });
            }),
          );
        });
      }),
    );