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: #481 (comment)

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