I?m a bit lost with the multi-threaded concurrent access errors I?m getting, and looking for an advise on the best solution. Basically, I have a desktop/mobile app (single user) that embeds a tiny local http server. The UI is done through an embedded browser, calling the server for pages and data. And this browser component can submit multiple requests, which will be processed simultaneously on the server, by different threads. Each of these requests can update the same SQLIte database, and this is were the troubles start. 1- Is it safe to share a single connection between all these threads (assuming serialized mode)? So far it seems to work, but what happens if a thread begins a transaction by calling ?BEGIN TRANSACTION"? Is this thread safe (the transaction bound to this thread), or will the statements from the other threads be mixed up in that transaction? Are there other known limitations, for example how does sqlite3_last_insert_row_id() behave? 2- If I need to create one connection per thread, what are then the best options to set on the connection? I tried many ones (journal mode=WAL, busy_timeout, ?) but I?m getting errors like database is locked or even schema is locked. 3- I tried to enable the shared cache, but I?m still getting database is locked (262). According to the doc, sqlite3_busy_handler does not help here. Is there a way to not get the error but simple have the thread wait until the lock is freed?
Or is there a better way to get this implemented using SQLite, beyond synchronizing my threads and making sure that only one is executing DB code at a time? I?m currently using 3.9.1, but can move to the latest version if it helps.
