On Tuesday, 8 March, 2016 22:00 +07:00, Philippe Riand <phil at riand.com> said:
> 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)? Yes. The purpose of serialized mode is to ensure that only one thread per connection is active at any given time, thus ensuring the concurrent entry prohibitions for multiple threads on a single connection. > So far it seems to work, but what happens if a > thread begins a transaction by calling ?BEGIN TRANSACTION"? Transactions are an artifact of the connection. So a thread executing a "begin transaction" begins a transaction for the connection. Any subsequent calls made on that connection (or statements on that connection) execute within the context of that 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? Yes, it is thread safe (if you are in serialized mode which will prevent multiple concurrent entry into the SQLite library from different threads on the same connection -- which is the only concurrency limitation for multiple threads). No, the transaction has nothing to do with the thread -- a transaction is an artifact of the connection. sqlite3_last_insert_row_id() will return the rowid of the last insert performed on the connection (without regard to which thread did what to the connection). If you need isolation between threads then you need a connection per thread. > 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. You did not say what OS you are using. What is the result of trying to put the database in WAL mode? If you then ask what the journal_mode is, does it tell you WAL? > 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? Shared Cache is for resource constrained devices. You have not said what device/OS you are using. Unless you really really need shared cache, you should probably not use it as it significantly alters the behaviour of SQLite. > 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. Serialized threading mode will do this for you automatically. If you have a fixed thread pool you probably want one connection per thread. If WAL is supported, then you can have concurrent readers with one writer. If WAL is not supported, then readers will block writers and writers will block readers -- which setting a busy timeout will help with as long as you make sure to perform your transactions (which includes implicit read transactions) transactions expeditiously.
