interactiveTransactions feature breaks long running transactions

[livthomas]

Bug description

When you enable the new interactiveTransactions preview feature, it will break all your existing long running transactions.

The following error will be printed to the console:

[0] prisma:info Starting a sqlite pool with 9 connections.
[0] PrismaClientKnownRequestError: 
[0] Invalid `prisma_1.prisma.contract.create()` invocation in
[0] /home/livthomas/Projects/livthomas/pawnshop-next/build/main/legacy/contract/importLegacyContractRecords.js:15:41
[0] 
[0]   12 const userPasswordsMap = await (0, createUserHashedPasswordsMap_1.createUserHashedPasswordsMap)(userNames);
[0]   13 const operations = results.map(result => {
[0]   14     const data = (0, convertLegacyRecordToContract_1.convertLegacyRecordToContract)(result, userPasswordsMap, archived);
[0] → 15     return prisma_1.prisma.contract.create(
[0]   Transaction API error: Transaction already closed: Transaction is no longer valid. Last state: 'Expired'.
[0]     at Object.request (/home/livthomas/Projects/livthomas/pawnshop-next/node_modules/@prisma/client/runtime/index.js:39068:15)
[0]     at async PrismaClient._request (/home/livthomas/Projects/livthomas/pawnshop-next/node_modules/@prisma/client/runtime/index.js:40851:18)
[0]     at async Promise.all (index 0)
[0]     at async Proxy._transactionWithCallback (/home/livthomas/Projects/livthomas/pawnshop-next/node_modules/@prisma/client/runtime/index.js:40807:18)
[0]     at async importLegacyContractRecords (/home/livthomas/Projects/livthomas/pawnshop-next/build/main/legacy/contract/importLegacyContractRecords.js:17:5)
[0]     at async reportLegacyTableImportStatus (/home/livthomas/Projects/livthomas/pawnshop-next/build/main/legacy/reportLegacyTableImportStatus.js:34:9)
[0]     at async importLegacyDatabase (/home/livthomas/Projects/livthomas/pawnshop-next/build/main/legacy/importLegacyDatabase.js:27:20)
[0]     at async node:electron/js2c/browser_init:193:563 {
[0]   code: 'P2028',
[0]   clientVersion: '3.9.0',
[0]   meta: {
[0]     error: "Transaction already closed: Transaction is no longer valid. Last state: 'Expired'."
[0]   }
[0] }

I thought this would get resolved once #9533 is fixed but nothing really changed with the 3.9.0 release.

How to reproduce

1. Create prisma.schema for an SQLite database
2. Enable interactiveTransactions preview feature in prisma.schema
3. Run a transaction which takes around 30 seconds to execute using the original API:

   prisma.$transaction([operation1, operation2]);

4. Wait for errors

Expected behavior

This new interactiveTransactions preview feature should not affect the existing transactions in any way.

Prisma information

I cannot really share this. But there is nothing special, just 20000 create queries on a single table with a few connectOrCreate fields.

Environment & setup

• OS: Linux 4.19.225-1-MANJARO
• Database: SQLite
• Node.js version: v17.3.0

Prisma Version

prisma                  : 3.9.0
@prisma/client          : 3.9.0
Current platform        : debian-openssl-1.1.x
Query Engine (Node-API) : libquery-engine bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-1.1.x.so.node)
Migration Engine        : migration-engine-cli bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine    : introspection-core bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary           : prisma-fmt bcc2ff906db47790ee902e7bbc76d7ffb1893009 (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Default Engines Hash    : bcc2ff906db47790ee902e7bbc76d7ffb1893009
Studio                  : 0.457.0
Preview Features        : interactiveTransactions

[garrensmith]

Quick update on this. We know where the issue is and will fix it in the next release. As a quick fix for now you can also do this:

prisma.$transaction(tx => {
   await operation1();
   await operation2();
}, {timeout: A_LONG_TIMEOUT_VALUE});

[arcticmatt]

Is this supposed to be fixed now? I'm still running into it on 3.12.0

[millsp]

Hey @arcticmatt, are you getting timeouts on regular (array) transactions or interactive (callback) transactions?

[sivadinesh1]

Happens for us also for interactive (callback) transactions. The database call happens with network call and a batch of 20 plus records, consistently fails around 16 th loop. EDIT: issue happens when transaction crosses 5 seconds, going thru more docs helped.
does not happen when db & server are in same server. Had to increase the timeout but not sure of the impact in db performance.

[livthomas]

Guys, keep in mind that this issue is about the normal transactions (those without callback) which got broken when I turned on interactiveTransactions feature. I haven't really used the new interactive (callback) transactions. If you have problems with those, you should either find a more appropriate open issue and leave a comment there or create a new issue.

[revmischa]

I'm getting the same error on 3.14.0: #13713

[shtse8]

I have the same issue of timeout. what is the default timeout now? Is any way to set the default timeout longer? I don't want to set it per transaction, it's a pain.

[millsp]

Hey @shtse8, feel free to check if a feature request is already open and upvote it, if not you can just open one. Thanks!