Skip to content

feat: add support for nested transaction rollbacks via savepoints in sql#4375

Closed
LucianBuzzo wants to merge 17 commits intoprisma:mainfrom
LucianBuzzo:lucianbuzzo/sql-nested-transactions
Closed

feat: add support for nested transaction rollbacks via savepoints in sql#4375
LucianBuzzo wants to merge 17 commits intoprisma:mainfrom
LucianBuzzo:lucianbuzzo/sql-nested-transactions

Conversation

@LucianBuzzo
Copy link
Copy Markdown
Contributor

@LucianBuzzo LucianBuzzo commented Oct 17, 2023

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.

Client PR: prisma/prisma#21678

@LucianBuzzo LucianBuzzo requested a review from a team as a code owner October 17, 2023 21:59
@LucianBuzzo LucianBuzzo requested review from Weakky and miguelff and removed request for a team October 17, 2023 21:59
@LucianBuzzo LucianBuzzo force-pushed the lucianbuzzo/sql-nested-transactions branch from 6f375b8 to b984ae4 Compare October 17, 2023 22:00
@CLAassistant
Copy link
Copy Markdown

CLAassistant commented Oct 17, 2023

CLA assistant check
Thank you for your submission! We really appreciate it. Like many open source projects, we ask that you sign our Contributor License Agreement before we can accept your contribution.
You have signed the CLA already but the status is still pending? Let us recheck it.

@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

This should resolve the issue described here prisma/prisma#15212

@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

@miguelff @Weakky If you have a moment could you take a look a this? If it works as I think it does ( 🤞 ) it's going to fix a lot of headaches for me and my team! TIA 🙏

@janpio
Copy link
Copy Markdown
Contributor

janpio commented Oct 27, 2023

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?

@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

@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.
As an example of how I consider this a bug: the documentation for transactions shows an example where a transfer between two accounts I rolled back, but it's not explained that this will not work if the transaction is nested. If you try to make behaviour like this and couple it with something like the RLS example from the client extensions, it will fail and the user has to do a lot of debugging to find out why.

@janpio
Copy link
Copy Markdown
Contributor

janpio commented Oct 28, 2023

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 BEGIN/COMMIT with savepoints - which this PR does not do. Do you agree that there is no overlap?)

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)

@janpio
Copy link
Copy Markdown
Contributor

janpio commented Oct 28, 2023

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:

@janpio janpio self-assigned this Oct 28, 2023
@codspeed-hq

This comment was marked as off-topic.

@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

LucianBuzzo commented Oct 29, 2023

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.
To get integration tests in an isolated transaction, you would simply need to run your test cases inside an interactive transaction:

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.

@janpio
Copy link
Copy Markdown
Contributor

janpio commented Oct 29, 2023

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)

For prisma/docs#6604 and prisma/prisma#12898 this PR will resolve those issues, as long as they are using an SQL DB.

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.

@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

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!

@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

And yes you could not use a client extension in the test case, I simply provided the example above as one possibility 👍

@janpio
Copy link
Copy Markdown
Contributor

janpio commented Oct 29, 2023

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)

@janpio
Copy link
Copy Markdown
Contributor

janpio commented Oct 29, 2023

(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😆)

@janpio janpio removed request for Weakky and miguelff October 29, 2023 16:19
@LucianBuzzo LucianBuzzo force-pushed the lucianbuzzo/sql-nested-transactions branch from da206e7 to a8af640 Compare October 30, 2023 10:16
@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

@janpio Here's a PR for the Quaint README #4399

@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

@janpio I've also opened a PR with a failing test case for the prisma client here prisma/prisma#21678

@Jolg42
Copy link
Copy Markdown
Contributor

Jolg42 commented Nov 1, 2023

Note: I brought the branch into the repo, it will release the engines automatically after a while
https://github.com/prisma/prisma-engines/tree/integration/sql-nested-transactions
https://buildkite.com/prisma/test-prisma-engines/builds/21364

@janpio
Copy link
Copy Markdown
Contributor

janpio commented Nov 1, 2023

Happened, version shared here: prisma/prisma#21678 (comment)

@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

@janpio @Jolg42 I think that this commit 5d2cc0a should allow us to send an existing transaction ID to the engine in the case of a nested transaction. I'm not sure how to test this behaviour in this repo - any tips on how to do this, or an existing test I could use as an example?

Comment on lines +168 to +170
let current_depth = self.depth.load(Ordering::SeqCst);
let new_depth = current_depth + 1;
self.depth.store(new_depth, Ordering::SeqCst);
Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Suggested change
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;

jacek-prisma
jacek-prisma previously approved these changes Jan 9, 2025
if tx.depth() > 0 {
tx.create_savepoint().await?;
} else {
tx.begin().await?;
Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Is it possible/expected for this code branch to execute?

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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

Copy link
Copy Markdown
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Resolved 👍

@aqrln aqrln mentioned this pull request Feb 17, 2025
Ok(())
}

#[connector_test(only(SqlServer))]
Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Why only SQL Server?

Ok(())
}

#[connector_test(only(Postgres))]
Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Why only Postgres?

}

#[connector_test(only(Postgres))]
async fn nested_commit_rollback_workflow(mut runner: Runner) -> TestResult<()> {
Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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) {
Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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.

Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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().

@luisgrases
Copy link
Copy Markdown

Are we going to get GTA VI before this?

@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

@luisgrases your comment made me laugh! Hopefully this will get merged before GTA VI
The engines side is looking good, and what's remaining is to cover a number of edge cases with test cases in the corresponding prisma/prisma PR. I've been very busy with my day job and personal life, so this has had to take a back seat. I will hopefully make some time to work on it in the next few weeks 👍

Copy link
Copy Markdown
Contributor

Copilot AI left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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.

Comment on lines +175 to 201
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<'_> {
Copy link

Copilot AI Feb 9, 2026

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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

Copilot uses AI. Check for mistakes.
// 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("")
Copy link

Copilot AI Feb 9, 2026

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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

Suggested change
Cow::Borrowed("")
Cow::Borrowed("/* no-op release savepoint for mssql */")

Copilot uses AI. Check for mistakes.
Comment on lines 78 to +118
} 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<()> {
Copy link

Copilot AI Feb 9, 2026

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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.

Copilot uses AI. Check for mistakes.
Comment on lines +475 to +476
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"; \
Copy link

Copilot AI Feb 9, 2026

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

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.

Suggested change
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"; \

Copilot uses AI. Check for mistakes.
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.
@LucianBuzzo
Copy link
Copy Markdown
Contributor Author

Now that transactions are controlled by the prisma/prisma code, I'm closing this PR, and the important parts of this work have been moved to prisma/prisma#21678

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

PR: Feature A PR That introduces a new feature

Projects

None yet

Development

Successfully merging this pull request may close these issues.