Threading & transactions & async

[trevyn]

I’m leaning toward the best approach being thread_local! database connections, with SQLite handling concurrency at its level. This gives us some advantages:

• async for atomic SQL statements is just tokio::task::spawn_blocking(|| { ... }); this will then use a shared database connection from the thread pool.
• Transactions could also be handled as just async { thread::spawn(|| { ... }).join() }, and then just write blocking imperative code. Because it’s our own thread, we guarantee that we have exclusive access to that DB connection.
• We don’t have to worry about locks or serializing requests at all; SQLite handles this. (In the correct threading mode.)

Outstanding questions:

• If a thread starts something that creates a SQLite internal lock (e.g. a database write), and then the thread is killed, how does SQLite know that the lock should be dropped?
• We can’t be moved from the thread that spawn_blocking puts us on, right? It’s ours until completion, that’s the nature of synchronous code. Which means that we could share that DB connection too. But there might be some benefit from a “fresh” DB connection, too.
• What is the correct SQLite threading mode, and are we in it?
• Can we return data easily from a spawn_blocking closure?

Next steps:

• Try thread_local! and dbg! the database connections on create/etc., and run some simulated loads.

[trevyn]

related: rusqlite/rusqlite#697

[trevyn]

https://youtu.be/4QZ0-vIIFug?t=2568

Explains that the best thing to do for async filesystem access (SQLite is filesystem access, basically) is to use a thread pool and just glue it into a Future.

[trevyn]

Ok, thinking about transactions and threading constraints:

• SQLite supports multi-threaded access to a single database file, and we should take advantage of this for multiprocessing and read concurrency.
• With concurrent access to a database file, SQLite will occasionally throw SQLITE_BUSY errors that will need to be retried. This should happen automatically and transparently.
• Async-everywhere has a lot of ergonomics advantages, and potentially significant performance advantages later.
• Using a transaction should be easy and safe; you shouldn't have to think about what's happening underneath.
• When you don't need a full transaction, it should be simple with no boilerplate.
• In a full async program, you're already in an async block and using awaits everywhere. We are deliberately no longer targeting non-async programs.
• All in on Tokio for the first pass.

[trevyn]

Do automatic retry on SQLITE_BUSY always, with some exponential backoff. (Unless there's some way to be notified by SQLite when a command should be retried.)

[trevyn]

Closed circa f30413b. For more details, see https://github.com/trevyn/turbosql#transactions-and-async