SqlLite concurrency

[tmds]

The documentation of the sqllite provider doesn't mention concurrency as a limitation: https://docs.efproject.net/en/latest/providers/sqlite/limitations.html

Is this an oversight or does ef manage concurrent access to sqllite in some way?

[Suchiman]

While it's not advisable to access the same database file from multiple processes, concurrency as in multithreading is fully supported by SQLite https://www.sqlite.org/threadsafe.html

[tmds]

@Suchiman cool! Does ef use "Multi-thread" or "Serialized"?

[bricelam]

It uses the default mode: serialized. See also aspnet/Microsoft.Data.Sqlite#228

[natemcmaster]

Does ef use "Multi-thread" or "Serialized"?

EF actually doesn't set this. This is controlled by the compilation options that produced sqlite3.dll (the native component EF invokes to use SQLite). See https://www.sqlite.org/compile.html#threadsafe. Note this document says "Serialized" is the default, but this varies per installation of SQLite. For example, the default "libsqlite3.dylib" library on OSX El Capitan is compiled with "THREADSAFE=2", or multithreaded ,and on Ubuntu 14 it is "THREADSAFE=1", or serialized.

Also note:

If single-thread mode is selected at compile-time, then critical mutexing logic is omitted from the build and it is impossible to enable either multi-thread or serialized modes at start-time or run-time.

You can check which compilation options where used by executing "pragma compile_options;".

using(var conn = new Microsoft.Data.Sqlite.SqliteConnection("Data Source=:memory:"))
{
    var cmd = conn.CreateCommand();
    cmd.CommandText = "PRAGMA compile_options";
    conn.Open();
    using(var reader = cmd.ExecuteReader())
    {
        while(reader.Read())
        {
            Console.WriteLine(reader.GetValue(0));
        }
    }
}   

Triage: we could consider adding API to change multi-threaded/serialized mode to https://github.com/aspnet/Microsoft.Data.Sqlite. This cannot be set via issuing a PRAGMA so it would need to be API added to our wrapper.

[tmds]

@bricelam @natemcmaster to be safely used in an asp.net core app, should the mode be set to serialized? Wouldn't it make sense to put this to the same value (e.g. serialized) on all platforms?

[rowanmiller]

@natemcmaster are we going to be compiling versions for OSX etc? Assuming we will use serialized if we are.

[natemcmaster]

@tmds we don't have complete control over this. For RC2, Linux and OSX rely on the system-installed version of SQLite. We are hoping to redistribute libsqlite3 via NuGet instead, but we are waiting on external partners for this.

@rowanmiller assuming the SQLite team accepts the package we proposed for aspnet/Microsoft.Data.Sqlite#237, then yes, this library on OSX and Linux will be compiled with default as "serialized".

[rowanmiller]

No action to be taken here - we'll be using the new SQLite package for RTM

[jaimestuardo]

How can I set Serialized mode in EF? I have System.Data.SQLite namespace.

I am using last SQLite version that is installed by NuGet package manager.