Controlling transaction isolation level

[nappa85]

Hello guys,
I need to be able to change transaction isolation level, but, since isolation level must be set before beginning transaction, and sea-orm takes a connection from the pool at every query, I can't simply execute a query before calling begin, since it could end up in a totally different connection.

I somehow remember already discussing that in the past, but can't find it...

MySQL and PostgreSQL have really similar syntax, while SQLite has a different syntax.

Isolation levels

	MySQL	PostgreSQL	SQLite
REPEATABLE READ	default	V
READ COMMITTED	V	default
READ UNCOMMITTED	V	V	V
SERIALIZABLE	V	V	default

Access mode

Transactions are read-write by default, but can be set as read-only.

Synthax

MySQL

MySQL doesn't accept SET TRANSACTION commands inside a transaction, but they will be applied only to next transaction

SET TRANSACTION ISOLATION LEVEL <level>;
SET TRANSACTION READ ONLY;
BEGIN;

MySQL accepts access mode also on START TRANSACTION command

START TRANSACTION READ ONLY;

PostgreSQL

PostgreSQL accepts SET TRASANCTION commands only inside an active transaction

BEGIN;
SET TRANSACTION ISOLATION LEVEL <level>;
SET TRANSACTION READ ONLY;

PostgreSQL accepts both isolation level and access mode also on START TRANSACTION command

START TRANSACTION ISOLATION LEVEL <level>;
START TRANSACTION READ ONLY;

PostgreSQL also have a DEFERRABLE mode that can be applied only when isolation level is SERIALIZABLE and access mode is READ ONLY

SET TRANSACTION DEFERRABLE;

SQLite

SQLite uses the PRAGMA command to change settings

PRAGMA read_uncommitted = boolean;l
PRAGMA query_only = boolean;

Settings must be undone manually and affects all connections.

SQLite have a mode that can be used inside the BEGIN TRANSACTION command

BEGIN EXCLUSIVE TRANSACTION;
BEGIN DEFERRED TRANSACTION;
BEGIN IMMEDIATE TRANSACTION;

Summing up

Since our begin transaction is done by sqlx, that, as far as I know, doesn't supports isolation level, we can't use START TRANSACTION alternatives.

That said, going to change global settings in SQLite seems quite risky to me, knowing we should undone them on transaction end, but in case of multiple transactions with different settings can become a mess.

My proposal is to add an enum for isolation level, like

pub enum IsolationLevel {
  RepeatableRead,
  ReadCommitted,
  ReadUncommitted,
  Serializable,
}

then adding a method to TransactionTrait, like

trait TransactionTrait {
  fn begin_with_config(&self, isolation_level: Option<IsolationLevel>, read_only: Option<bool>);
}

Then various implementations will defer the real implementation based on database engine, MySQL implementation will execute SET TRANSACTION commands before begin transaction, PostgreSQL will execute SET TRANSACTION commands after begin transaction, and for SQLite I would ignore the parameters simply starting a new transaction.

What do you think?

[billy1624]

Hey @nappa85, thanks for the detailed investigation and proposal!

Then various implementations will defer the real implementation based on database engine, MySQL implementation will execute SET TRANSACTION commands before begin transaction, PostgreSQL will execute SET TRANSACTION commands after begin transaction, and for SQLite I would ignore the parameters simply starting a new transaction.

I agree, actually it's mostly align with this SQLx PR on transaction options, launchbadge/sqlx#1924.

I think we can implement similar logic in SeaORM first, once SQLx released transaction options we can just dump our custom code and use SQLx's begin_with method to begin the transaction with isolation level.

CC @tyt2y3

[tyt2y3]

You are looking for #118

Then various implementations will defer the real implementation based on database engine, MySQL implementation will execute SET TRANSACTION commands before begin transaction, PostgreSQL will execute SET TRANSACTION commands after begin transaction, and for SQLite I would ignore the parameters simply starting a new transaction.

I think we can implement similar logic in SeaORM first, once SQLx released transaction options we can just dump our custom code and use SQLx's begin_with method to begin the transaction with isolation level.

I agree with all of the above.

Because SQLx 0.7 could be 3 to 6 months away.

[billy1624]

For anyone who is watching this, do checkout the amazing PR by @nappa85 as well:

• Transactions Isolation level and Access mode #1230