Thanks you both. I'm already using a timeout of 1 minute.
My "Database is locked" errors only happen in the following situation : - I run an import thread, which does a lot of write - this thread uses a transaction : BEGIN at import start, COMMIT/ROLLBACK at import end - during the import (which is very long to execute, sometimes several hours), another thread execute reads on the same database (different connection) because of user actions on the main GUI I didn't understood the documentation about transactions and transactions mode very well, so thanks for clarifying the situation 4/ to me. So now I will : - use WAL mode - or don't use a transaction during import (by there is performances loss and other drawbacks) - or maybe import in a temporary database and the lock the destination database just the time of copying back imported data to it - or commit the import transaction periodically, maybe every "n" seconds or every "n" imported data I don't really know which strategy is the best, I will experiment (WAL mode seems the easier, but there is maybe other drawbacks) -----Message d'origine----- De?: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] De la part de R.Smith Envoy??: mardi 3 novembre 2015 19:43 ??: sqlite-users at mailinglists.sqlite.org Objet?: Re: [sqlite] Problem with locked database On 2015-11-03 06:31 PM, ALBERT Aur?lien wrote: > Hi, > > I'm often getting the "Database is locked" error message and I don't really > understand what can lead to this. > > I'm using these settings : > - sqlite3_config(SQLITE_CONFIG_MULTITHREAD) > - multiple threads > - only one sqlite connection per thread > - each thread have a different connection instance > > Could anyone clarify some things for me ? > > 1/ Can different threads perform read at the same time (I think yes) Yes - at least, SQLite will handle the requests in a way that seems simultaneous. > 2/ Can different threads perform write at the same time (I think no) Nothing can write simultaneously > 3/ Can a thread perform read while another single one is performing > write ? (I think yes) Depends on the transaction and Journaling mode. See: https://www.sqlite.org/lockingv3.html#writing https://www.sqlite.org/lockingv3.html#rollback In fact, that entire page should be of interest to you. Also, WAL journal mode is inherently good at this. See: https://www.sqlite.org/wal.html#concurrency > 4/ Can a thread perform read while another thread has started a > transaction ? (I think yes) Only if it isn't an exclusive transaction and has not started a write operation, or WAL mode is used, or it is the same connection with read_uncommitted turned on... actually, there are a lot of "if"s here. In general, it is best to view a "Transaction" as a locked state and plan accordingly, and if you absolutely need to read simultaneously from elsewhere, be prepared to take special steps in your code and read up on the modes. > My "Database is locked" problem seems to only appears in situation number 4, > may I am wrong on this one ? Maybe on others ? To put this shortly - you cannot do two things at the same time in a database. Most contentions can be (and are) resolved internally with simple rules, but as soon as a more prominent contention appears (and there are many ways this could happen) then one reader/writer thread will have to wait a little bit until a previous is finished. SQLite's "timeout" setting (as mentioned and linked by Simon already) should fix 99% of these without making you add any extra code. There may be one or two extreme cases or race conditions that requires special consideration, but mostly setting the timeout should suffice. If you still get locked problems - please post again. Cheers, Ryan _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
