Prisma Transaction Already closed

[Sheyereyesh]

Question

using postgresql & prisma version 4.14.0
this function updates the order column with the given value and update other rows order

I got this error

Transaction API error: Transaction already closed: Could not perform operation.

it works but throws this error periodically

How to reproduce (optional)

function updateOrder(
      id: number,
      order: number,
      currentPosition: number
    ) {
     return this.prisma.$transaction(async tx => {
          if (currentPosition < order) {
            await tx.items.updateMany(
              {
                where: {
                  id: {
                    not: id,
                  },
                  order: {
                    gt: order,
                  },
                },
                data: {
                  order: {
                    increment: 1,
                  },
                },
              },
              tx.items
            );
            await tx.items.update(
              {
                where: {
                  id,
                },
                data: {
                  order: order + 1,
                },
              },
              tx.items
            );
          } else {
            await tx.items.updateMany(
              {
                where: {
                  id: {
                    not: id,
                  },
                  order: {
                    gte: order,
                  },
                },
                data: {
                  order: {
                    increment: 1,
                  },
                },
              },
              tx.items
            );
            await tx.items.update(
              {
                where: {
                  id,
                },
                data: {
                  order: order,
                },
              },
              tx.items
            );
          }
        });
    }

[ludralph]

Hi @Sheyereyesh 👋

Thank you for raising this question.

Can you increase the timeout for your transaction?

for example:

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

You may also want to take a look at this closed issue to see how other users resolved the same error.

[Sheyereyesh]

@ludralph thanks for your advice
this might work,
but i'd like to understand why i have to increase wait and time out cause this also occurs on my stage environment that don't have to handle high traffic

and is there a way to set timeout & maxWait globally or maybe to unlimited
i have been facing situation where i have to set these value because transaction running longer than default value, but in such case error message is clear where it log something like "unable to start a transaction in the given time" or "transaction time limit is 5s but to 8s". where as it only says "transaction already closed".

also i m getting errors like "Timed out fetching a new connection from the connection pool" or "connection limit exceeded"
i m struggling with these errors

are those error related?

[raymondKelly]

@Sheyereyesh Out of interest are you running async calls in parallel with both of them updating the database at some point in their call cycle? A suspicion I have for why I see this issue is that I am using Promise.all([doAction1, doAction2]) and at some point action1 is timing out but action2 has not yet operated on the database. When it attempts to then operate on the database it tries to use the same transaction as action1 but action1 closed that transaction after it hit the timeout error.

[LJITDPDF]

im dealing with the same problem, my function was working well and i was using the function for two months to help with the development of other functions, suddently the function stopted working with this error:

Transaction API error: Transaction already closed: Could not perform operation

if it is just to increase the time of the transaction, why the function was working and just now it stops like that?

[raymondKelly]

@LJITDPDF that's impossible to answer without more context. For example did the function change, did your database change, did your transaction pattern change, did the data change.