Efcore: SQLite: Use multi-threaded mode by default

[bricelam]

We should investigate using multi-threaded mode by default in Microsoft.Data.Sqlite.

SQLitePCLRaw's e_sqlite3 uses the default SQLITE_THREADSAFE of Serialized.

We don't want to affect other usages of SQLitePCLRaw, so we can't use SQLITE_CONFIG_MULTITHREAD.

We'd need to use SQLITE_OPEN_NOMUTEX when opening the connection.

In this mode, a connection cannot be used simultaneously on different threads, but I believe that is already a de facto limitation of ADO.NET, so we should be fine. We also need to be aware of any limitations this might impose on a connection pool (issue #13837).

This will avoid locks during Open and Execute.

[roji]

In this mode, a connection cannot be used simultaneously on different threads, but I believe that is already a de facto limitation of ADO.NET [...]

Agreed, I'm not aware of any other provider where the same connection instance can be used concurrently... It may be worth considering what happens if the user does do this - a concurrent usage exception here is much better than undefined behavior. Any synchronization here is likely to be very low-overhead because it's error-detection only (i.e. no contention), and reducing contention of global locks during Open/Execute sounds great.

We also need to be aware of any limitations this might impose on a connection pool

I think this should be orthogonal - other providers provide pooling while at the same time not supporting concurrent usage of the same instance...