Please support sharing a SqliteConnection among threads/tasks

[joshtriplett]

By default, current versions of sqlite handle concurrent use of the same connection from multiple threads. Would it be possible, given an sqlite compiled to support that, for SqlitePool to just hand out the same connection to every thread rather than opening new connections?

[mehcode]

We definitely need a specialized pool for SQLite in SQLx.

Connection handle re-use across threads (if enabled and supported) is one thing it could support.

Additionally, it could support a reader/writer split pool. 1 connection shared concurrently for writes and M connections for reads.

---

A work-around for any locking issues though is to set the max_connections of the pool to 1. This is basically what the serialized threading level would do in the C API.

[joshtriplett]

A work-around for any locking issues though is to set the max_connections of the pool to 1. This is basically what the serialized threading level would do in the C API.

Good point, and thanks!

The other reason I'd like this is so I can set PRAGMA locking_mode=EXCLUSIVE, to avoid needing the -shm file.

[markazmierczak]

Thread-safe here means that no SQLite's internal structure will be corrupted,
but it doesn't mean it will do what you want it to do.

sqlite3_exec/sqlite3_step might still be interleaved with other calls like sqlite3_last_insert_rowid, e.g. consider following piece of code:

char* sql = "INSERT INTO friends(name) VALUES ('Josh');";
sqlite3_exec(db, sql, 0, 0, 0);
int last_id = sqlite3_last_insert_rowid(db);

If you run this code from multiple threads (with different name set on each thread in real example) on the same db connection, you cannot predict what will happen - what ID you get on which thread.

And this is only a very simple example of many other issues like running INSERTs from Thread A while Thread B started a transaction - those INSERTs will be executed inside this transaction, they won't be queued or whatever people might assume to happen or should happen.

As I understand it "serialized" mode was introduced simply because people misused the library too often. SQLITE_THREADSAFE has absolutely no impact on anything other than protecting the connection data against multiple concurrent modifications (where data means in-memory structures associated with db handle).
There are use-cases for that. But you certainly don't want to share the same connection between multiple threads as if it was a pool, because the above problems apply (even though SQLite "supports" it).

Having that said, the following:

A work-around for any locking issues though is to set the max_connections of the pool to 1. This is basically what the serialized threading level would do in the C API.

is not quite true.

You might have a look at #853 - it might be useful for you.