Microsoft.Data.Sqlite: Support deferred transactions #20786

What problem are you trying to solve?

I would like to execute multiple read transactions in parallel, while each transaction sees a consistent snapshot of the data (no read uncommited) and the transactions do not block each other. This would be a huge improvement and could be implemented by changing one line of code in SqliteTransaction.cs.

Describe the solution you'd like

At the moment SqliteConnection.BeginTransaction() supports isolation levels “ReadUncommitted” and “Serializable”.

Serializable provides a consistent data snapshot but blocks other serializable transactions. One reader would have to wait until the other reader has finished his transaction, which is quite bad for performance, if you have multiple long running transactions.

ReadUncommited requires SqliteCacheMode.Shared and suffers from dirty reads, non repeatable reads and phantoms.

Solution: Allow IsolationLevel.Snapshot by changing line 43 in SqliteTransaction.cs, so that it doesn’t throw an exception. The remaining code will then automatically start a deferred transaction, which starts a SQLite read transaction on the first select statement.

SQLite read transactions provide a consistent data snapshot without suffering from dirty reads, non repeatable reads and phantoms and without blocking other read transactions (see https://www.sqlite.org/lang_transaction.html ).

Additonal information

In SQL Server the isolation level “Snapshot” specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction (see https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15 ).

The proposed implementation is different in that data read will be the transactionally consistent version of the data that existed at the first select statement – not at the start of the transactions. I tried to execute a “select 1;” statement after the “begin;” statement, but SQLite still started the underlying read transaction only at the first “real” select statement, that accessed a table in the database.

The System.Data.SQLite provider requires you to specify IsolationLevel.ReadCommitted instead of IsolationLevel.Snapshot to start a read transaction, but I think that Snapshot is more correct, because SQLite read transactions do not suffer from non repeatable reads and phantoms as ReadCommited does (see isolation level table on this page: https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-isolation-levels?view=sql-server-ver15 ).