Isolation level support

[tuxzz]

There are four transaction isolation levels in SQL language and most of database support them. But I can't find how to set isolation level for transactions in sqlx.
Is it a missing feature?

[mehcode]

It is currently missing, yea. I'm open to API ideas.

// would rename existing ::transaction to ::with_transaction
let mut tx = conn.transaction() // -> TransactionOptions
    .read_only()
    .isolation_level(IsolationLevel::RepeatableRead)
    .begin()
    .await?;

let mut tx = TransactionOptions::new(&mut conn)
    .isolation_level(IsolationLevel::Snapshot)
    .begin()
    .await?;

[pythoneer]

What are the pros and cons of the different approaches? Just from the first look at it i have no strong feelings one way or the other. The first one looks just fine and is maybe more discoverable?

[LucasPickering]

Is there any workaround for this currently?

[abonander]

I was wondering if we could start with something like .begin_with("BEGIN READ ONLY, READ COMMITTED").await (on the Connection trait and Pool) which returns a Transaction since that would cover most use cases without needing to figure out database-specifics.

For sanity, we can assert that the connection was put into a transaction if it wasn't already. Both Postgres and MySQL tell us after executing a command whether the connection is in a transaction, and SQLite has get_autocommit().

[ivan]

@LucasPickering if you're using PostgreSQL, I believe you can do

use sqlx::postgres::PgPoolOptions;

let pg_pool = PgPoolOptions::new()
    .after_connect(|conn| Box::pin(async move {
        conn.execute("SET default_transaction_isolation TO 'repeatable read'").await?;
        Ok(())
    }))
    .connect(&uri).await?;

[pythoneer]

@ivan And for different isolation levels create different pools and use the pool that is "holding" the right isolation level? Or is this global?

[Diggsey]

I like the begin_with approach.

[LukeMathWalker]

I'd be happy to pick this up if we landed on an API design - is there a consensus among sqlx's maintainers at this point? @abonander

[pythoneer]

By choosing the "first" approach we would change the current one from

conn.transaction(|conn|Box::pin(async move {
    query("select * from ..").fetch_all(conn).await
})).await

into

conn.with_transaction(|conn|Box::pin(async move {
    query("select * from ..").fetch_all(conn).await
}), IsolationLevel::RepeatableRead).await

right?

[patrick-gu]

You can set the isolation level by executing SET TRANSACTION after creating the transaction:

let mut tx = conn.begin().await?;
tx.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;")
    .await?;

Documentation for SET TRANSACTION:

• PostgreSQL
• MySQL
• Microsoft SQL Server

SQLite does not have SET TRANSACTION, and uses SERIALIZABLE isolation except with PRAGMA read_uncommitted turned on, as documented.

It may still be necessary to add a custom BEGIN as with SQLite's BEGIN IMMEDIATE as mentioned in #1182.

Hope this helps!

[abonander]

@LukeMathWalker I think my proposal for .begin_with("<BEGIN statement with flags>") would be a good start: https://github.com/launchbadge/sqlx/issues/481#issuecomment-726991429

If nothing else it's something we can build on if it's not satisfactory.

[LukeMathWalker]

I've started to work on this 👍🏻

[LukeMathWalker]

Question: what should happen when the user tries to invoke begin_with within the context of an existing transaction? Right now sqlx determines, based on the current depth, if it should issue a BEGIN or a SAVEPOINT statement. Do we want to keep the same behaviour? Or do we want to error, given that this is probably not what the user expects? @abonander

[saward]

Question: what should happen when the user tries to invoke begin_with within the context of an existing transaction? Right now sqlx determines, based on the current depth, if it should issue a BEGIN or a SAVEPOINT statement. Do we want to keep the same behaviour? Or do we want to error, given that this is probably not what the user expects? @abonander

Maybe it's too tricky given the way sqlx is written, but would this be a good place for a typestate pattern (http://cliffle.com/blog/rust-typestate/) so that calling begin_with after a transaction has begun is a compile time error?

[davidbnk]

Using this in MySQL:

let mut tx = conn.begin().await?;
tx.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;")
    .await?;

Gives:

1568 (25001): Transaction characteristics can't be changed while a transaction is in progress

Is there another way?

[jclulow]

Is there any plan to finish even a limited SQLite-specific version of this? It is, as far as I can tell, not possible to do safe read-modify-write in SQLite without the ability to use BEGIN IMMEDIATE.

[jvliwanag]

Using this in MySQL:

let mut tx = conn.begin().await?;
tx.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;")
    .await?;

Gives:

1568 (25001): Transaction characteristics can't be changed while a transaction is in progress

Is there another way?

For MySQL, the SET TRANSACTION LEVEL should be done prior to starting a transaction. The workaround I have for now is:

use sqlx::Executor;

let mut con = self.pool.acquire().await?;

con.execute("SET TRANSACTION ISOLATION LEVEL READ_COMMITED");
let mut tx =
    sqlx_core::transaction::Transaction::begin(MaybePoolConnection::PoolConnection(con))
        .await?;

Note that for this to work, you'd have to add sqlx-core to your cargo dependencies.

[Reknij]

Is there any plan to finish even a limited SQLite-specific version of this? It is, as far as I can tell, not possible to do safe read-modify-write in SQLite without the ability to use BEGIN IMMEDIATE.

Hi, have you found a solution to use BEGIN IMMEDIATE?

[jclulow]

Is there any plan to finish even a limited SQLite-specific version of this? It is, as far as I can tell, not possible to do safe read-modify-write in SQLite without the ability to use BEGIN IMMEDIATE.

Hi, have you found a solution to use BEGIN IMMEDIATE?

Yes! I now use rusqlite and sea-query, where I have proper control over transactions.

[glebpom]

I have created the PR, which supports transactions with custom SQL. PR link. This generally work for my usecase (MariaDB), but probably requires more testing and it's unclear how to handle this for sqlite. Please comment on the PR if something specific should be addressed.

[lucasyvas]

API wise, the feature is more useful if it's generalized to not just be about isolation level. For example, I also set session variables per transaction and the workflow for this is cumbersome.

Unrelated, but PoolConnection can be owned, yet Connection is a mutable borrow. So it's challenging to actually pass that tx around besides by explicit parameter to a function call. I found a way around all this but it involves accessing the underlying connection and basically not using the native transaction capability in sqlx at all. If creating a transaction could have it take ownership of the underlying executable connection that would be amazing for my use case (If I understand the API properly). Anyway...

tldr; more flexibility in creating the transaction, whatever it may involve, is appreciated. Isolation level is a great start because it is pretty common.

[Crazytieguy]

Just wanted to post my workaround for sqlite in case it's helpful. Basically I have a dummy table called "acquire_write_lock" with one row that I populate on pool creation and I write to it before making my reads.

let mut transaction = pool.begin().await?;
sqlx::query!("UPDATE acquire_write_lock SET lock = TRUE WHERE id = 1")
    .execute(transaction.as_mut())
    .await?;

[Reknij]

Just wanted to post my workaround for sqlite in case it's helpful. Basically I have a dummy table called "acquire_write_lock" with one row that I populate on pool creation and I write to it before making my reads.

let mut transaction = pool.begin().await?;
sqlx::query!("UPDATE acquire_write_lock SET lock = TRUE WHERE id = 1")
    .execute(transaction.as_mut())
    .await?;

Haha, I also do like this.

[ckampfe]

For anyone else finding this issue because you're looking for SQLite's BEGIN_IMMEDIATE transaction support in sqlx, I made this little hack that so far seems to be working. I'm sure it's limited (and possibly broken) in plenty of ways but feel free to take it if you find it useful: https://gist.github.com/ckampfe/cfa85d409874e487c5117b6f242d9a07