I am going to have to do some testing. Right now, we lock on a single SQLiteConnection instance because of the fact that insert & id retrieval are 2 separate invocations. If we can get away with just a single ExecuteQueryAsync<long>(MyUpdateSql, MyObject) call and it's thread safe, then we could drop a ton of locking abstractions at the application level.
> If we can get away with just a single ExecuteQueryAsync<long>(MyUpdateSql, MyObject) call and it's thread safe, then we could drop a ton of locking abstractions at the application level.
Not only would you have to ensure you're always setting the db to SQLITE_OPEN_FULLMUTEX, https://sqlite.org/c3ref/errcode.html and https://sqlite.org/c3ref/changes.html are still not coherent in multithreaded contexts. Calling them won't corrupt anything, but they'll return garbage. The latter probably doesn't matter much, but the former seems quite relevant if you want to handle errors beyond "this failed".
Correct - We open our databases in serialized mode and access a single connection from multiple threads.
We actually are not concerned with handling detailed error information. Our usage of these databases is very well bounded. All of the SQL that will ever execute against them is contained in 1 series of constant declarations, and the mappers ensure request data is well-formed before attempting inserts & updates. SQLite is really just there to provide identity and persistence for our business objects.
We are still able to use these fields effectively in local development and unit testing, since in these contexts there is only ever 1 thread hitting the database at a time.
Now only MySQL is missing `RETURNING` from the relational databases that still matter. Postgres had it for ages, SQL Server has it with OUTPUT (with a caveat of breaking when using triggers) and SQLite finally added them on this release.
Using `RETURNING` is so much nicer than spending four round-trips to `BEGIN`, `INSERT`, `SELECT` and `COMMIT` otherwise...
> Especially since last_insert_rowid() can never be free of race condition
I think that's overstating it. It's per-connection, not per-database. If you don't share connections between threads, it's fine. If you keep the connection locked / checked out for the span of the two statement, it's fine. You're probably doing the latter anyway if you use transactions.
Returning looks nice, though! Particularly that it can return "one result row for each database row that is deleted, inserted, or updated" rather than just one.
> Returning looks nice, though! Particularly that it can return "one result row for each database row that is deleted, inserted, or updated" rather than just one.
That… seems normal? Returning just one row would make no sense, how would even a trivial `RETURNING id` work otherwise?
Ah I see, I was a bit confused because I'm used to RETURNING from pg and I've been waiting for sqlite to add it for a very long time. Especially since ON CONFLICT was added as it made the issue even worse.
It's not part of any standard, so they'd need to pick what to implement. They docs say they're modeling it on postgres's, which is a perfectly reasonable extension.
True, though SQL isn't much of a standard anyway. I literally don't know of a single SQL database that implements the SQL standard to spec with no fiddling. Or more precisely, the vast majority of SQL databases out of the box behave differently, so anything more complex than a basically SELECT + JOIN generally isn't very cross-compatible.
