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.