I'm planning to write a multi-threaded application that uses SQLite 3 to communicate with a database. It is planned that the database is concurrently read and written by multiple threads.
One of the things I want to do is executing a series of statements in a transaction. This series of statements cannot be combined into a single query as my application requires to execute application logic in between statements to determine what statement to execute next. It is crucial that the changes done in the transaction are visible to other threads only in an atomic fashion, i.e. either the whole transaction or none of it is visible at any time. Some of these transactions only perform SELECT statements, others also perform UPDATE or INSERT statements.
Right now my plan is to have a single sqlite3 object concurrently used by all threads. Whenever a thread needs to do a transaction, it grabs a set of prepared statements from somewhere and executes them as needed. I make sure that no prepared statement is used by more than one thread at a time.
What I found is that neither the sqlite_prepare, not the sqlite_step family of functions allow me to specify a context that identifies the transaction in which the statement is run. How does (if at all) SQLite identify which statements belong to which transaction? The documentation has been inconclusive on this matter.
If there is no way to identify a transaction to run a statement in, I believe I have to use multiple connections to the same database as at least the connection context identifies what transaction my statements are executing in. This would require a somewhat different program design, which is why I haven't started writing the database code and can't show any of it (because it doesn't exist).
A connection can have only a single transaction at a time.
So if you want to isolate multiple threads from each other, you have to use one connection for each of them, or serialize the threads outside of the database.