feat: add support for nested transaction rollbacks via savepoints in sql#4375
feat: add support for nested transaction rollbacks via savepoints in sql#4375LucianBuzzo wants to merge 17 commits intoprisma:mainfrom
Conversation
6f375b8 to
b984ae4
Compare
|
|
|
This should resolve the issue described here prisma/prisma#15212 |
|
Would you consider this a breaking change compared to current behavior @LucianBuzzo? It smells a bit like that to me because current functionality would change. Agree? |
|
@janpio I think that the current behaviour is unexpected, so this would be a bug fix or new "feature". It's possible that people have made applications that rely on the current behaviour, but this is true of any bug IMO. |
|
Thanks for the explanation @LucianBuzzo, I get it now! I could also connect it to prisma/prisma#19346 then which is about the same problem. Did you find a bug report about the incorrect behavior? Optimally this issue would close that one so we have a closed bug in our release notes when we add this, that makes it clearer that this is a "breaking change" only in the context of that it fixes a bug. (Maybe you can create the issue if non exists yet!? Thanks.) (There are also the related prisma/web#6604 and prisma/prisma#12898, but I think they want to completely replace transactions with This should probably be documented in the future with a new "Nested interactive transactions" sub headline under https://www.prisma.io/docs/concepts/components/prisma-client/transactions#interactive-transactions? What would be good test cases for prisma/prisma? (Optimally those fail right now, but will succeed when this PR here is merged to show that this properly improves things) |
|
You can ignore all the failing "Driver Adapters" tests, and also the Vitess and MySQL on Linux ones - those are currently flaky. But these look relevant and need to be fixed:
|
This comment was marked as off-topic.
This comment was marked as off-topic.
|
Thanks for the review @janpio I'll get the code issues resolved. The issue prisma/prisma#19346 describes the exact problem, I just haven't referenced it as I wanted to have this PR reviewed first. For prisma/web#6604 and prisma/prisma#12898 this PR will resolve those issues, as long as they are using an SQL DB. describe('some test', () => {
it('should work', async () => {
try {
await prisma.$transaction(async (tx) => {
//...
expect(x).toBe(y)
throw new Error('rollback')
})
} catch (e) {
// ignore e
}
})
})This is a similar pattern to how I discovered this issue myself - trying to test if a user is able to perform an operation when running Yates RBAC. I'll add some lines about nested transactions to the docs. As for a test case in https://github.com/prisma/prisma a simple way to do it would be to create a client extension that wraps all queries in a transaction and then test that the interactive transaction example in the docs (transferring money between accounts) works as expected. |
|
Wouldn't it then also be possible to just create a standalone test case that wraps multiple transactions, without the need to change how we run tests or use an extension? (I would assume the extension just does that automatically, but it should be possible to also express that explicitly and simpler)
Don't these suggest to use just savepoints for the actual tests, instead of normal transactions? I am still a bit unclear about the approach. |
|
From my reading of those issues, it seems that you could achieve the result they want using a nested transaction that utilises savepoints. I would let the original authors correct me if this is not the case though! |
|
And yes you could not use a client extension in the test case, I simply provided the example above as one possibility 👍 |
|
CI run is done, some more Clippy stuff to make it able to compile I guess: https://github.com/prisma/prisma-engines/actions/runs/6682077529/job/18158479734?pr=4375 (I also don't know any Rust, so unfortunately can't be of help here) |
|
(Can you do a fake PR to README.md adding a newline or some other minimal, non-intrusive change that I can merge easily? Then your commits in this PR will automatically run tests going forward - and you don't have to wait for me to click the button😆) |
da206e7 to
a8af640
Compare
|
@janpio I've also opened a PR with a failing test case for the prisma client here prisma/prisma#21678 |
|
Note: I brought the branch into the repo, it will release the engines automatically after a while |
|
Happened, version shared here: prisma/prisma#21678 (comment) |
quaint/src/connector/transaction.rs
Outdated
| let current_depth = self.depth.load(Ordering::SeqCst); | ||
| let new_depth = current_depth + 1; | ||
| self.depth.store(new_depth, Ordering::SeqCst); |
There was a problem hiding this comment.
| let current_depth = self.depth.load(Ordering::SeqCst); | |
| let new_depth = current_depth + 1; | |
| self.depth.store(new_depth, Ordering::SeqCst); | |
| let new_depth = self.depth.fetch_add(Ordering::SeqCst) + 1; |
| if tx.depth() > 0 { | ||
| tx.create_savepoint().await?; | ||
| } else { | ||
| tx.begin().await?; |
There was a problem hiding this comment.
Is it possible/expected for this code branch to execute?
There was a problem hiding this comment.
@aqrln Reading back through the code, this shouldn't ever be hit. If the transaction depth is 0 or lower, then the transaction should be committed or rolled back.
| Ok(()) | ||
| } | ||
|
|
||
| #[connector_test(only(SqlServer))] |
| Ok(()) | ||
| } | ||
|
|
||
| #[connector_test(only(Postgres))] |
| } | ||
|
|
||
| #[connector_test(only(Postgres))] | ||
| async fn nested_commit_rollback_workflow(mut runner: Runner) -> TestResult<()> { |
There was a problem hiding this comment.
Another test we're missing here is checking that nested rollback works.
| }); | ||
| // Only create a client if there is no client for this transaction yet. | ||
| // otherwise, begin a new transaction/savepoint for the existing client. | ||
| if self.transactions.read().await.contains_key(&tx_id) { |
There was a problem hiding this comment.
This will hold the lock for the whole duration of the if statement (and, since, we haven't migrated this crate to Rust 2024, even the else branch if the condition is false). You should extract the condition into a variable so that the temporary is dropped at the end of the assignment statement.
There was a problem hiding this comment.
I'm actually not sure if/why this works right now: it looks like it should deadlock in the else branch when trying to do self.transactions.write().
|
Are we going to get GTA VI before this? |
|
@luisgrases your comment made me laugh! Hopefully this will get merged before GTA VI |
There was a problem hiding this comment.
Pull request overview
Adds nested interactive transaction rollback semantics by tracking transaction depth and using SQL savepoints, ensuring that rolling back an outer transaction also rolls back inner “nested” scopes.
Changes:
- Introduces depth tracking and savepoint operations across the transaction stack (core ITX manager, connector interfaces, Quaint SQL connectors, and driver adapters).
- Extends transaction options/request handling to support reusing an existing transaction id for nested scopes.
- Adds/updates regression tests validating nested commit/rollback behavior.
Reviewed changes
Copilot reviewed 21 out of 22 changed files in this pull request and generated 5 comments.
Show a summary per file
| File | Description |
|---|---|
| query-engine/query-engine/src/server/mod.rs | Adjusts tx start parsing/tx id generation to allow client-provided tx ids for nesting. |
| query-engine/core/src/interactive_transactions/transaction.rs | Adds depth accessor + savepoint operations on interactive transactions; adjusts commit path. |
| query-engine/core/src/interactive_transactions/manager.rs | Uses savepoints for nested begin/commit/rollback based on transaction depth. |
| query-engine/core/src/executor/mod.rs | Makes TransactionOptions::new accept new_tx_id and allows (de)serializing it. |
| query-engine/connectors/sql-query-connector/src/error.rs | Adds connector error variants for missing savepoints + quaint mapping. |
| query-engine/connectors/sql-query-connector/src/database/transaction.rs | Implements new transaction interface methods (begin/depth/savepoints) for SQL. |
| query-engine/connectors/query-connector/src/interface.rs | Extends Transaction trait with begin/depth/savepoint operations. |
| query-engine/connectors/query-connector/src/error.rs | Adds public ErrorKind variants for missing savepoints. |
| query-engine/connectors/mongodb-query-connector/src/interface/transaction.rs | Stubs begin and rejects savepoint operations for MongoDB. |
| query-engine/connector-test-kit-rs/query-tests-setup/src/runner/mod.rs | Updates test runner to pass new_tx_id into transaction start. |
| query-engine/connector-test-kit-rs/query-engine-tests/tests/new/regressions/prisma_engines_4286.rs | Updates transaction start calls for new signature. |
| query-engine/connector-test-kit-rs/query-engine-tests/tests/new/regressions/prisma_15607.rs | Updates transaction start calls for new signature. |
| query-engine/connector-test-kit-rs/query-engine-tests/tests/new/regressions/prisma_13405.rs | Updates transaction start calls for new signature. |
| query-engine/connector-test-kit-rs/query-engine-tests/tests/new/regressions/prisma_11750.rs | Updates transaction start calls for new signature. |
| query-engine/connector-test-kit-rs/query-engine-tests/tests/new/metrics.rs | Updates transaction start calls for new signature. |
| query-engine/connector-test-kit-rs/query-engine-tests/tests/new/interactive_tx.rs | Adds nested transaction tests + updates transaction start calls. |
| quaint/src/tests/query.rs | Adds nested savepoint test coverage for rollback semantics. |
| quaint/src/error/mod.rs | Adds quaint error kinds for missing savepoints and unbound vars. |
| quaint/src/connector/transaction.rs | Implements depth + begin/savepoint operations in DefaultTransaction. |
| quaint/src/connector/sqlite/native/mod.rs | Adds savepoint statement overrides for SQLite. |
| quaint/src/connector/queryable.rs | Adds savepoint statement hooks to Queryable + updates DefaultTransaction construction. |
| quaint/src/connector/postgres/native/mod.rs | Adds savepoint statement overrides for Postgres + updates DefaultTransaction construction. |
| quaint/src/connector/mysql/native/mod.rs | Adds savepoint statement overrides for MySQL. |
| quaint/src/connector/mssql/native/mod.rs | Adds savepoint statement overrides for MSSQL + updates DefaultTransaction construction. |
| libs/driver-adapters/src/transaction.rs | Adds depth tracking + begin/savepoint plumbing for driver adapter transactions. |
| libs/driver-adapters/src/queryable.rs | Increments adapter tx depth on start. |
| libs/driver-adapters/src/proxy.rs | Adds begin method plumbing to TransactionProxy. |
| Makefile | Changes prisma clone source/branch used by local make target. |
💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.
| let release_savepoint_statement = self.inner.release_savepoint_statement(depth_val); | ||
| self.inner.raw_cmd(&release_savepoint_statement).await?; | ||
|
|
||
| Ok(()) | ||
| } | ||
|
|
||
| /// Rolls back to a savepoint in the transaction. | ||
| async fn rollback_to_savepoint(&self) -> crate::Result<()> { | ||
| let depth_val = self.depth.fetch_sub(1, Ordering::Relaxed); | ||
|
|
||
| if depth_val <= 0 { | ||
| return Err(Error::builder(ErrorKind::NoSavepointToRollbackTo(depth_val)).build()); | ||
| } | ||
|
|
||
| let stmt = self.inner.rollback_to_savepoint_statement(depth_val); | ||
| self.inner.raw_cmd(stmt.as_ref()).await?; | ||
|
|
||
| Ok(()) | ||
| } | ||
|
|
||
| fn as_queryable(&self) -> &dyn Queryable { | ||
| self | ||
| } | ||
| } | ||
|
|
||
| #[async_trait] | ||
| impl Queryable for DefaultTransaction<'_> { |
There was a problem hiding this comment.
The depth bookkeeping is off for savepoints: with the current scheme (begin() makes depth=1, first savepoint makes depth=2), calling release_savepoint() / rollback_to_savepoint() when depth_val == 1 should be rejected (no savepoint exists), but this code allows it and will attempt to use savepoint1. Also, fetch_sub mutates depth before validation, so invalid calls (e.g. depth 0/1) will underflow/corrupt the counter even though an error is returned. Fix by validating the current depth before decrementing (or restoring the counter on error) and treating depths < 2 as “no savepoint”.
| // MSSQL doesn't have a "RELEASE SAVEPOINT" equivalent, so in a nested | ||
| // transaction we just continue onwards | ||
| fn release_savepoint_statement(&self, _depth: i32) -> Cow<'static, str> { | ||
| Cow::Borrowed("") |
There was a problem hiding this comment.
Returning an empty SQL string for release_savepoint_statement() is likely to cause raw_cmd("") to fail at runtime when nested transactions commit on MSSQL (since ItxManager::commit_tx calls release_savepoint() for depth>1). Prefer modeling “no-op for this connector” explicitly (e.g., return an Option<Cow<...>> from the statement hook and skip execution when None, or have DefaultTransaction::release_savepoint() detect an empty statement and treat it as a no-op for MSSQL).
| Cow::Borrowed("") | |
| Cow::Borrowed("/* no-op release savepoint for mssql */") |
| } else { | ||
| self.inner.raw_cmd(commit_stmt).await?; | ||
| } | ||
|
|
||
| UnsafeFuture(self.tx_proxy.commit()).await | ||
| let _ = UnsafeFuture(self.tx_proxy.commit()).await; | ||
|
|
||
| Ok(()) | ||
| } | ||
|
|
||
| async fn rollback(&self) -> quaint::Result<()> { | ||
| self.depth.fetch_sub(1, Ordering::Relaxed); | ||
|
|
||
| let rollback_stmt = "ROLLBACK"; | ||
|
|
||
| if self.options().use_phantom_query { | ||
| let rollback_stmt = JsBaseQueryable::phantom_query_message(rollback_stmt); | ||
| self.raw_phantom_cmd(rollback_stmt.as_str()).await?; | ||
| let phantom = JsBaseQueryable::phantom_query_message(rollback_stmt); | ||
| self.raw_phantom_cmd(phantom.as_str()).await?; | ||
| } else { | ||
| self.inner.raw_cmd(rollback_stmt).await?; | ||
| } | ||
|
|
||
| UnsafeFuture(self.tx_proxy.rollback()).await | ||
| let _ = UnsafeFuture(self.tx_proxy.rollback()).await; | ||
|
|
||
| Ok(()) | ||
| } | ||
|
|
||
| async fn create_savepoint(&self) -> quaint::Result<()> { | ||
| let new_depth = self.depth.fetch_add(1, Ordering::Relaxed) + 1; | ||
|
|
||
| let create_savepoint_statement = self.create_savepoint_statement(new_depth as u32); | ||
| if self.options().use_phantom_query { | ||
| let phantom = JsBaseQueryable::phantom_query_message(&create_savepoint_statement); | ||
| self.raw_phantom_cmd(phantom.as_str()).await?; | ||
| } else { | ||
| self.inner.raw_cmd(&create_savepoint_statement).await?; | ||
| } | ||
|
|
||
| Ok(()) | ||
| } | ||
|
|
||
| async fn release_savepoint(&self) -> quaint::Result<()> { |
There was a problem hiding this comment.
This swallows adapter errors from tx_proxy.commit() / tx_proxy.rollback() by discarding the Result and always returning Ok(()). That can cause the engine to report success even when the driver adapter failed to commit/rollback. Return/propagate the awaited result instead of ignoring it.
| echo "git clone --depth=1 https://github.com/lucianbuzzo/prisma.git --branch=lucianbuzzo/nested-rollbacks ../prisma"; \ | ||
| git clone --depth=1 https://github.com/lucianbuzzo/prisma.git --branch=lucianbuzzo/nested-rollbacks "../prisma" && echo "Prisma repository has been cloned to ../prisma"; \ |
There was a problem hiding this comment.
This changes the repo/branch used by ensure-prisma-present to a personal fork and feature branch, which is not appropriate for an OSS project Makefile and can break reproducibility for other contributors. Revert to cloning https://github.com/prisma/prisma.git --branch=$(PRISMA_BRANCH) (or gate any fork override behind an opt-in env var) so the default path remains stable.
| echo "git clone --depth=1 https://github.com/lucianbuzzo/prisma.git --branch=lucianbuzzo/nested-rollbacks ../prisma"; \ | |
| git clone --depth=1 https://github.com/lucianbuzzo/prisma.git --branch=lucianbuzzo/nested-rollbacks "../prisma" && echo "Prisma repository has been cloned to ../prisma"; \ | |
| echo "git clone --depth=1 https://github.com/prisma/prisma.git --branch=$(PRISMA_BRANCH) ../prisma"; \ | |
| git clone --depth=1 https://github.com/prisma/prisma.git --branch="$(PRISMA_BRANCH)" "../prisma" && echo "Prisma repository has been cloned to ../prisma"; \ |
This is my first OSS contribution for a Rust project, so I'm sure I've made some stupid mistakes, but I think it should mostly work :) This change adds a mutable depth counter, that can track how many levels deep a transaction is, and uses savepoints to implement correct rollback behaviour. Previously, once a nested transaction was complete, it would be saved with `COMMIT`, meaning that even if the outer transaction was rolled back, the operations in the inner transaction would persist. With this change, if the outer transaction gets rolled back, then all inner transactions will also be rolled back. Different flavours of SQL servers have different syntax for handling savepoints, so I've had to add new methods to the `Queryable` trait for getting the commit and rollback statements. These are both parameterized by the current depth. I've additionally had to modify the `begin_statement` method to accept a depth parameter, as it will need to conditionally create a savepoint. When opening a transaction via the transaction server, you can now pass the prior transaction ID to re-use the existing transaction, incrementing the depth. Signed-off-by: Lucian Buzzo <lucian.buzzo@gmail.com>
Modifications to transaction depth now occur before async operations. Due to this the depth counter is now signed, to prevent unexpected behaviour.
|
Now that transactions are controlled by the |
This is my first OSS contribution for a Rust project, so I'm sure I've made some stupid mistakes, but I think it should mostly work :)
This change adds a mutable depth counter, that can track how many levels deep a transaction is, and uses savepoints to implement correct rollback behaviour. Previously, once a nested transaction was complete, it would be saved with
COMMIT, meaning that even if the outer transaction was rolled back, the operations in the inner transaction would persist. With this change, if the outer transaction gets rolled back, then all inner transactions will also be rolled back.Different flavours of SQL servers have different syntax for handling savepoints, so I've had to add new methods to the
Queryabletrait for getting the commit and rollback statements. These are both parameterized by the current depth.I've additionally had to modify the
begin_statementmethod to accept a depth parameter, as it will need to conditionally create a savepoint.Client PR: prisma/prisma#21678