Sort out transactions and IsolationLevel

[bricelam]

To support IsolationLevel.ReadCommitted, we'd issue the following.

PRAGMA read_uncommitted = 0;
BEGIN;

[divega]

Doesn't that make the isolation level effectively Serializable in SQLite? Is the idea that we would snap to the closest supported isolation level?

[bricelam]

I'm still trying to wrap my head around it, but I think it will seem like ReadCommitted until it needs to "elevate" to Serialized.

[bricelam]

Digging into this, apparently we already support it. It's the default behavior (or IsolationLevel.Unspecified). But SqliteTransaction.IsolationLevel will lie to you and tell you it's Serialized.

[bricelam]

It prevents dirty reads by locking the row while Serialized prevents all read phenomena by allowing only one transaction at a time.

[bricelam]

Nevermind. DEFERRED vs IMMEDIATE are completely orthogonal to isolation level. Only read uncommitted and serialized are supported. I don't think we should be using IMMEDIATE. It creates a write lock even if the transaction never writes.

That said, I don't think it's wrong to allow other isolation levels so long as we guarantee at least that level of isolation.

Reccommendation:

1. Stop using IMMEDIATE
2. Allow IsolationLevel values that may actually result in a higher level of isolation.

[ericsink]

When you speak of IMMEDIATE or DEFERRED, I assume you're talking about using that keyword in conjunction with BEGIN TRANSACTION.

If so, then your recommendation to "stop using IMMEDIATE" concerns me. For transactions that are intended to modify the database, I always recommend BEGIN IMMEDIATE. If it succeeds, you know you have the write lock, and you don't need to do any special handling of SQLITE_BUSY for any of the operations in that transaction.

OTOH, never use BEGIN IMMEDIATE for a transaction which is only going to be reading data.

OTOH, do use an explicit transaction when you are just reading data, because SQLite is much faster that way.

Also, it seems prudent to mention WAL mode. In that mode, writers do not block readers. Does EF Core's SQLite provider take a stance on whether WAL mode is used or not?

Regardless of whether WAL mode is being used or not, SQLite only supports one writer at a time.

[bricelam]

Additional context: We have built-in retry (up to CommandTimeout) for SQLITE_BUSY.

[bricelam]

We could consider providing an overload of BeginTransacion that lets you specify IMMEDIATE (or even EXCLUSIVE), but nobody has asked for this yet.

[ericsink]

The question is where does this built-in retry take place?

To clarify, I'm saying a retry loop for SQLITE_BUSY when executing BEGIN IMMEDIATE TRANSACTION is a really nice way to go. Once it has succeeded, you can ignore SQLITE_BUSY.

The alternative is usually a mess. If you just BEGIN TRANSACTION, then SQLite will not try to get the write lock until you do something that needs it. Which sounds good in principle, but in practice it means you have to have special busy-handling logic on every SQLite operation within the tx, to deal with the case where you need to grab the write lock but somebody else already grabbed it.

[bricelam]

It's done during the first sqlite3_step of every statement. Unfortunately, ADO.NET doesn't really have a concept for "begin a transaction with updates". Because we don't know if there'll be updates it seems more efficient to defer and always retry. The app will only actually see the busy error after we've reached the timeout (defaults to 30 seconds) which probably means your app has deadlocked.

[bricelam]

Idiomatically, I also think ADO.NET users expect command.Execute to fail with locking errors, but probably not connection.BeginTransaction. But I'll definitely discuss with the team our different options here.

[ericsink]

Yeah, I can see that. You've got a bit of an impedance mismatch. My preferred handling of SQLITE_BUSY is not necessarily the approach that best fits this situation.

I wonder how System.Data.SQLite (the EF6 ADO.NET provider maintained by the SQLite team) handles SQLITE_BUSY issues?

[bricelam]

I wonder how System.Data.SQLite handles SQLITE_BUSY issues?

Looks like they retry on every step. They overload BeginTransaction allowing you to choose IMMEDIATE, but DEFERRED is the default.

[bricelam]

Allow IsolationLevel values that may actually result in a higher level of isolation.

I couldn't find anything for ADO.NET, but the ADO documentation says:

If the level of isolation you request is unavailable, the provider may return the next greater level of isolation.