feat: add support for nested transaction rollbacks via savepoints in sql by LucianBuzzo · Pull Request #21678 · prisma/prisma

LucianBuzzo · 2023-10-30T10:33:10Z

~~This adds a high level test that should pass if/when prisma/prisma-engines#4375 is merged.~~
Transactions are now handled inside this repository, so the update to prisma-engines is no longer required.

This change adds support for handling rollbacks in nested transactions
in SQL databases. Specifically, the inner transaction should be rolled
back if the outer transaction fails.

To do this we keep track of the transaction ID and transaction depth so we can
re-use an existing open transaction in the underlying engine. This change also
allows the use of the $transaction method on an interactive transaction client.

Summary by CodeRabbit

New Features
- Nested interactive transactions for SQL databases (Postgres, MySQL, SQLite, SQL Server) via savepoints; MongoDB remains without nested-transaction support.
Tests
- Added extensive nested-transaction tests covering multi-level nesting, savepoint behavior, rollback/commit propagation, concurrency and provider-specific syntax.
Documentation
- Updated runtime documentation to describe nested transaction behavior and provider-specific savepoint semantics.

CLAassistant · 2023-10-30T10:35:18Z

All committers have signed the CLA.

codspeed-hq · 2023-10-30T10:50:58Z

Merging this PR will not alter performance

✅ 17 untouched benchmarks
⏩ 30 skipped benchmarks¹

_{Comparing LucianBuzzo:lucianbuzzo/nested-rollbacks (64cdb92) with main (85c63e5)}

30 benchmarks were skipped, so the baseline results were used instead. If they were deleted from the codebase, click here and archive them to remove them from the performance reports. ↩

packages/client/tests/functional/interactive-transactions/tests.ts

LucianBuzzo · 2023-11-01T12:30:33Z

@Jolg42 Is it possible to verify that prisma/prisma-engines#4375 fixes the issue and gets the test to pass?

janpio · 2023-11-01T15:54:17Z

~~@prisma/engines-version@5.6.0-12.integration-sql-nested-transactions-a8af640da343c10e0f7421264c2838b302d3ce6d via https://github.com/prisma/prisma/pull/21735/files should work for that.~~

Update: That surfaced that the PR in prisma-engines is not up do date with main, which makes some tests fail. We are fixing that right now and then will have a new integration version to test here.

Update 2: @prisma/engines-version@5.6.0-14.integration-sql-nested-transactions-5a0f60fe78f2029f692e21ce8732a978466b9baf might be the one, via #21737

janpio · 2023-11-01T20:43:59Z

Ummm, seems it is not working @LucianBuzzo. Or I messed up royally. Asking some colleagues for advice.

LucianBuzzo · 2023-11-01T22:08:55Z

@janpio I'll look into it on my end - any idea how to run just the tests/functional/interactive-transactions/tests.ts file locally?

LucianBuzzo · 2023-11-01T22:58:52Z

@janpio I'm replicating the issue on my end - I think I might need to update the client code to maintain the same transaction ID in nested transactions 🤔 . I'm going to dig through and see what I can come up wiht.

docker/docker-compose.yml

janpio · 2023-11-30T14:41:25Z

Can be updated to use version from #22161 now

packages/client/package.json

packages/engines/package.json

packages/fetch-engine/package.json

packages/internals/package.json

packages/migrate/package.json

pnpm-lock.yaml

packages/client/src/runtime/getPrismaClient.ts

Summary: - Added per-transaction operation serialization in TransactionManager via operationQueue and lock helpers, so nested start/commit/rollback and timeout/cancel paths cannot interleave state mutations. - Routed nested start (newTxId) through the transaction lock and revalidated state under lock before mutating depth/savepoint stacks. - Updated nested rollback to keep state consistent even when rollback-to-savepoint or release-savepoint queries fail. - Added a regression test for concurrent nested starts where the first savepoint creation fails, verifying the second nested start remains consistent and rollback targets the correct savepoint. - Removed leftover RequestHandler interactiveTransaction payload mutation that injected new_tx_id. - Updated AGENTS.md touched line to use official Wasm capitalization. Validation: - pnpm --filter @prisma/client-engine-runtime test transaction-manager.test.ts - pnpm --filter @prisma/client-engine-runtime build

Changes: - Added getItxScopeContext() helper to read TX_ID/TX_SCOPE_ID/TX_SCOPE_STATE with a typed context object, removing repeated local any-casts. - Updated _transactionWithCallback() to use typed itx context values for nested transaction scope handling. - Pulled scopeStack.push(scopeId) out of the nested/non-nested if-else without changing behavior. - Replaced transaction start info type from any to Transaction.InteractiveTransactionInfo<unknown>. - Renamed rollbackAttempts to rollbackScopeCount for clearer intent in rollback loop. - Replaced scopeStack.splice(0, scopeStack.length) with scopeStack.length = 0 for direct stack reset. - Switched Mongo nested-transaction runtime guard to use typed itx context (txId presence) instead of direct symbol access.

packages/client/src/runtime/getPrismaClient.ts

Changes: - Standardized provider checks in client-generator-ts to use context.isSqlProvider() for interactive transaction deny-list generation. - Refined getPrismaClient transaction scope typing into coherent top-level vs nested states to avoid per-field optional handling. - Added helpers for scope context validation and nested scope id generation to simplify _transactionWithCallback flow. - Simplified nested-vs-top-level close order checks for readability while preserving existing runtime behavior and error messages. - Added debug logging for suppressed rollback failures in multi-rollback cleanup loop. - Updated Mongo nested transaction runtime check to use typed scope context kind.

Added rationale comments above #withActiveTransactionLock and #runSerialized explaining that transaction operations are serialized per tx id to avoid savepoint/depth/status races across await boundaries.

- Add optional Transaction.savepoint(action, name) API in @prisma/driver-adapter-utils and re-export SavepointAction. - Update binder to forward wrapped savepoint methods for error-capturing adapters. - Refactor TransactionManager nested start/commit/rollback to prefer adapter-provided savepoint SQL, with provider-based fallback retained behind a TODO. - Implement savepoint methods for pg, neon, ppg, planetscale, mariadb, libsql, better-sqlite3, and mssql transaction implementations. - Extend transaction-manager tests to verify adapter-provided savepoint SQL and optional release behavior. - Validation: transaction-manager test suite passes; touched packages type-check with tsconfig.build.json using --emitDeclarationOnly false --noEmit.

packages/adapter-better-sqlite3/src/better-sqlite3.ts

packages/client-engine-runtime/src/transaction-manager/transaction-manager.ts

packages/adapter-better-sqlite3/src/better-sqlite3.ts

packages/client/src/runtime/getPrismaClient.ts

packages/client-engine-runtime/src/transaction-manager/transaction-manager.ts

- Replace Transaction savepoint(action, name) with async createSavepoint/rollbackToSavepoint/releaseSavepoint in driver-adapter-utils and binder. - Refactor TransactionManager nested transaction flow to call adapter savepoint methods and remove provider fallback SQL synthesis. - Migrate adapter savepoint implementations (pg, neon, ppg, planetscale, mariadb, libsql, better-sqlite3, mssql, d1-http, d1-worker) to execute async savepoint operations. - Update transaction-manager tests for method-based savepoint behavior and missing-method error cases. - Simplify nested ITX scope symbol storage in getPrismaClient to TX_SCOPE_CONTEXT. - Document the new Transaction savepoint API shape in AGENTS.md.

packages/client/src/runtime/getPrismaClient.ts

LucianBuzzo · 2026-02-18T16:14:13Z

@jacek-prisma let's go for the hatrick? 😅 🤞

janpio · 2026-02-19T19:43:02Z

Congratulations @LucianBuzzo!

LucianBuzzo · 2026-02-19T21:47:02Z

Haha thanks @janpio we got there in the end! 🎉

LucianBuzzo requested a review from a team as a code owner October 30, 2023 10:33

LucianBuzzo requested review from Druue and Jolg42 and removed request for a team October 30, 2023 10:33

LucianBuzzo mentioned this pull request Oct 30, 2023

feat: add support for nested transaction rollbacks via savepoints in sql prisma/prisma-engines#4375

Closed

janpio self-assigned this Oct 30, 2023

janpio changed the title ~~Add test for nested transaction rollback behaviour in SQL databases~~ feat: Add test for nested transaction rollback behaviour in SQL databases Oct 30, 2023

Jolg42 reviewed Oct 30, 2023

View reviewed changes

packages/client/tests/functional/interactive-transactions/tests.ts Outdated Show resolved Hide resolved

LucianBuzzo force-pushed the lucianbuzzo/nested-rollbacks branch from 155d16c to 5d80814 Compare November 1, 2023 12:29

LucianBuzzo mentioned this pull request Nov 9, 2023

feat: add support for nested transaction rollbacks via savepoints in sql prisma/prisma-engines#4405

Closed

Jolg42 reviewed Nov 9, 2023

View reviewed changes

docker/docker-compose.yml Show resolved Hide resolved

LucianBuzzo force-pushed the lucianbuzzo/nested-rollbacks branch from 69291d4 to 5ffdd3d Compare November 30, 2023 12:26

LucianBuzzo requested a review from a team as a code owner November 30, 2023 12:26

janpio closed this Nov 30, 2023

janpio reopened this Nov 30, 2023

janpio reviewed Dec 1, 2023

View reviewed changes

packages/client/package.json Outdated Show resolved Hide resolved

janpio reviewed Dec 1, 2023

View reviewed changes

packages/engines/package.json Outdated Show resolved Hide resolved

janpio reviewed Dec 1, 2023

View reviewed changes

packages/fetch-engine/package.json Outdated Show resolved Hide resolved

janpio reviewed Dec 1, 2023

View reviewed changes

packages/internals/package.json Outdated Show resolved Hide resolved

janpio reviewed Dec 1, 2023

View reviewed changes

packages/migrate/package.json Outdated Show resolved Hide resolved

janpio reviewed Dec 1, 2023

View reviewed changes

pnpm-lock.yaml Outdated Show resolved Hide resolved

janpio reviewed Dec 1, 2023

View reviewed changes

pnpm-lock.yaml Outdated Show resolved Hide resolved

jacek-prisma reviewed Feb 13, 2026

View reviewed changes

packages/client/src/runtime/getPrismaClient.ts Outdated Show resolved Hide resolved

jacek-prisma reviewed Feb 13, 2026

View reviewed changes

packages/client/src/runtime/getPrismaClient.ts Outdated Show resolved Hide resolved

jacek-prisma reviewed Feb 13, 2026

View reviewed changes

packages/client/src/runtime/getPrismaClient.ts Outdated Show resolved Hide resolved

LucianBuzzo added 2 commits February 14, 2026 16:56

jacek-prisma reviewed Feb 14, 2026

View reviewed changes

packages/client/src/runtime/getPrismaClient.ts Outdated Show resolved Hide resolved

LucianBuzzo added 3 commits February 14, 2026 20:04

docs: clarify per-transaction serialization guards

19587a7

Added rationale comments above #withActiveTransactionLock and #runSerialized explaining that transaction operations are serialized per tx id to avoid savepoint/depth/status races across await boundaries.