Efcore: Microsoft.Data.Sqlite: Add way to provide underlying SQLitePCL connection for SqliteConnection

[wanton7]

I was looking at SQLite docs and it seems single connection supports multithreading in serialized mode. From what I read here default mode for included SQLite is serialized. So you could just use single connection for lifetime of your app if you don't care about sharing PRAGMAs etc.
But current SqliteConnection implementation doesn't support providing your own underlying SQLite connection.

Add constructor that allows providing your own SQLitePCL connection.

[ajcvickers]

/cc @bricelam

[bricelam]

I don’t think SqliteConnection is thread safe (even if the underlying sqlite handle is). But I’d need to investigate.

[bricelam]

But yes, going from sqlite to SqliteConnection seems useful. (Same for sqlite_stmt and sqlite_blob)

[wanton7]

That's why it would be great if you could constuct new SqliteConnection from raw SQLite connection. This way SqliteConnection class wouldn't need to be thread safe.

[bricelam]

Although a static method like FromHandle might be better to avoid overwhelming the constructor

[KaloyanIT]

I'm interested in contributing. If I understand correctly, I should add a static method FromHandle, that can create a SqliteConnection from raw SQLite connection. Could someone give me more details, thank you!

[bricelam]

@KaloyanIT Yep. I imagine it would do something like this:

var connection = new SqliteConnection();
connection.ConnectionOptions = new SqliteConnectionStringBuilder
{
    DataSource = sqlite3_db_filename(handle),
    Mode = sqlite3_db_readonly(db, "main") == 0 ? default : SqliteOpenMode.ReadOnly,
    // Cache = ???
};
connection._connectionString = ConnectionOptions.ToString();
connection._db = handle;
connection._state = ConnectionState.Open;

return connection;

We might also need to avoid closing the handle when the SqliteConnection is disposed...

[bricelam]

Hmm, I don't see a way to tell if the database is using a shared cache. We may need to just trust the user if they try to use IsolationLevel.ReadUncommitted.

[groege]

@bricelam
I'm sure there is a reason why serialized instead of multithreaded mode is in use - can you give some information why this is?
As I see it https://sqlite.org/threadsafe.html

Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.

Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.
==> As I see it we should use this because it pretty much behaves the way I'd expect ef core to behave...
(so one connection for each db context)

Serialized. In serialized mode, SQLite can be safely used by multiple threads with no restriction.

Would it not be a lot better for performance to be able to use sqlite in multithreaded mode for read and write?
I was wondering why a simple statement took very long (22 rows in total and I only selected one), then realized it is because there is a background process that synchronized the data with the server...
So any app (in my case Android) that does some background sync with the db will strongly impact ui performance...

[bricelam]

@groege When I've thought about this in the past, I was a little concerned about creating the connection on one thread but executing commands on another. (The connection pool #13837 would do this.) I think this is ok so long as it's not used simultaneous on different threads.

Can you submit a new issue? Performance is one of the themes for 6.0, and it would be good to investigate whether we can safely pass SQLITE_OPEN_NOMUTEX during SqliteConnection.Open()

[groege]

@groege When I've thought about this in the past, I was a little concerned about creating the connection on one thread but executing commands on another. (The connection pool #13837 would do this.) I think this is ok so long as it's not used _simultaneous_ on different threads.

Can you submit a new issue? Performance is one of the themes for 6.0, and it would be good to investigate whether we can safely pass SQLITE_OPEN_NOMUTEX during SqliteConnection.Open()

@bricelam Do you mean a general perf issue or about making sqlite multithreaded? (maybe compile it with all options enabled so the dev can decide which mode to use via modelbuilder, etc...)

[bricelam]

Filed #22330.