Transactions

[pimeys]

We need a higher-level system for managing transactions. It is possible to trigger transactions with the normal BEGIN/COMMIT/ROLLBACK mechanism, but an abstraction that handles proper cleanup, committing, rolling back and so on, and offers the same query mechanisms as the Client would be useful.

The transaction should be started from the Client. When started, Rust's ownership system should prevent the usage of Client until the Transaction is dropped. The Transaction should provide at least the basics: execute and query, and additionally rollback and commit methods.

When the Transaction is dropped, it should know did the user finish it properly using the rollback or commit method, and if not, message the client to trigger a rollback before allowing more queries to be handled.

The last part can be done in many ways, and the tricky thing here is if wanting to set the ROLLBACK query in the Drop trait, which is not by design asynchronous. Transaction should have a mutable reference to the Client, so one way of doing this would be to set a flag in the Client about a dirty state, and the client (or Connection) takes care of triggering the ROLLBACK call before executing the next query.

[pimeys]

So let's write the possibilities here:

Calling method

The transaction API could work in two ways. First is to return a transaction object:

let mut tx = client.start_transaction().await?
tx.query(..).await?;

This is quite straightforward and easier to abstract. Has some problems with transaction lifetimes we'll come back to later.

The second would be a closure-based approach:

client.with_transaction(|tx| async move {
    tx.query(..).await
}).await?;

This makes it easier to manage the transaction lifetime, but the API is harder for situations, where one wants to store the transaction somewhere or create an abstraction over the way transactions are created (nobody else uses this approach).

Transaction API

The Transaction should have the same methods as a Client does for querying. On top of these the normal commit and rollback facilities.

It should not be possible to use a Client when a Transaction is open and in the scope, meaning it should hold a &mut Client inside so the compiler would catch any bad uses.

Nesting should be possible by calling start_transaction on an existing Transaction. This then requires a trait already implemented by the Client and a Transaction so we can nest the transactions, holding a reference either to a client or the parent transaction.

Transaction lifetime

In a perfect world the user always calls either ROLLBACK or COMMIT at the end of the transaction. In real life there can be all kinds of errors that prevents us from cleaning up.

Traditionally in synchronous Rust it has been quite easy to just implement Drop for the transaction, where we'd block and execute ROLLBACK if the transaction is in an open state.

With asynchronous Rust, there needs to be a polling Drop for this to be easy, or we need to design the clean-up in a different way. Some options:

• mysql_async stores a reference to a connection and sets a flag if there is an open transaction. If the transaction is not being closed properly, during the next request it'll trigger a ROLLBACK before doing anything else. This works fine without a direct dependency to a runtime, but what if after opening the transaction you never use the connection anymore, but also keep it in the memory?
• tokio-postgres has a channel between the client and the connection, and on Drop sends the ROLLBACK through the channel to be executed in another thread. This requires the connection future to be spawned when creating a new client for the system to work. It's a valid design, but requires this exact design for the whole crate for the Drop to work.
• If going with the closure calling method, the function could just catch unwinding panics and errors and trigger a ROLLBACK before returning to the call site.
• Another possibility is to store Arc<Mutex<Client>> to the Transaction, and on Drop use the runtime's spawn function to run the ROLLBACK, cloning the client Arc which is Send and the Mutex giving you Sync. You need some feature flags and abstraction over different runtimes in the crate for this option.

Of course having an async destructor would make all of this much easier. Boats wrote a good article about the issue: https://boats.gitlab.io/blog/post/poll-drop/

[tomhoule]

This makes it easier to manage the transaction lifetime, but the API is harder for situations, where one wants to store the transaction somewhere or create an abstraction over the way transactions are created (nobody else uses this approach).

I think diesel does. Of course it's a different story with async APIs, since async closures aren't a thing yet.

[pimeys]

This pull request enables the basis for spawning transactions: #55

On top of that change it should be possible to build a higher level transaction API.

[bbigras]

Any progress on this?

[pimeys]

Is there an actual need for transaction API in Tiberius? In the current state we can't guarantee an automatic ROLLBACK or COMMIT due to Rust not having a PollDrop trait, and Tiberius being runtime-independent would make it quite ugly to implement.

It's not that tricky to make it work by yourself:

client.simple_query("BEGIN TRAN").await?;
/// deal with your business, do not panic or error out
match result {
  Ok(_) => client.simple_query("COMMIT").await?,
  Err(_) => client.simple_query("ROLLBACK").await?,
}

Now adding higher level functionality on top of what we have now in Rust, especially automatic rollbacks is kind of cumbersome and if we'd not guarantee on doing a COMMIT or ROLLBACK, the API would be kind of dangerous to use.

I'm ready for suggestions how this could be done now in Tiberius, and I'm also happy if some higher level crate (such as Diesel) would implement SQL Server support with Tiberius, and would also solve the COMMIT/ROLLBACK problem with a smart pooling solution.

For now, yes transactions work and yes, you need to structure your code a bit to make them safe.

[bbigras]

Correct me if I'm wrong, but won't a transaction get a rollback if the connection is closed? If so couldn't I just use BEGIN TRAN and COMMIT and if there's an error the connection with drop and the transaction will get a rollback?

[pimeys]

Yes. But typically you want to reuse the connection between requests, so you either hold it in a mutex, or use a connection pool. Not triggering a ROLLBACK might lead to leaking the transaction to other requests.

[ArunGust]

How about this ?

let res = client.execute(
    format!(
        r#"
        BEGIN TRAN
        BEGIN TRY
            INSERT INTO ARCHIVE
            SELECT * FROM XXX WHERE id = '##{}' and status = 'completed' and updateddate  < dateadd(day,-30,GETDATE());            
            DELETE FROM XX WHERE id = '1234' and status = 'completed' and updateddate  < dateadd(day,-30,GETDATE());            
            COMMIT TRAN;
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE();
            ROLLBACK TRAN
        END CATCH
        "#, "1234"),
    &[],
).await?;
println!("{:?}", res);

[pimeys]

This example is quite nice for the throughput due to not opening any long-running transactions. The downside is you must write your logic in T-SQL, losing some of the compile-time checks from Rust. But, throughput-wise I'd prefer this approach for sure...

[pimeys]

@esheppa has experimental branch for long-running transaction api in this pull request. Please take a look if interested and maybe get the ball rolling again, if you'd think the api works for you.