Simon Slavin wrote: > On 14 Oct 2009, at 1:21am, priimak wrote: > > >> I am heaving small problem with sqlite. I have a webapp which connects >> to the database using sqlite-jdbc and performs SELECTs to response to >> different GET requests, while this happens if I try to write to a >> database ( UPDATE or INSERT ) from command line, that (i.e. update >> process) would occasionally fail with error message "SQL error near >> line >> 1: database is locked". Notice that I have only one writer, but many >> readers. Reading documentation (http://www.sqlite.org/ >> lockingv3.html) I >> was under impression that process which intends to update database >> will >> place it in the pending state allowing all currently running reads ( >> SELECTs ) to proceed, while blocking new SELECTs, the lock database >> apply changes and then unlock it allowing all pending and new >> SELECTs to >> proceed. Am I right about it and if so why do I "SQL error near line >> 1: >> database is locked" when trying to write to a database? >> > > I hope this will do until an expert comes along. I think you got it > right, you just don't know something. > > The SELECT activity requires a lock to the database. For instance, > consider a TABLE contact with columns name, address, phone . An index > is declared on just the name column. You execute > > SELECT phone FROM contacts WHERE name = 'Jackie' > > This requires a two-stage process: first use the index to find the ids > of the rows which have the right name. Then look up those rows in the > table and find out which phone numbers they have. Obviously, this > requires locking: you wouldn't want someone to make changes to the > table between those two steps. However, it requires locking only > against writing: other reads going on at the same time are harmless, > but a change between the two steps can invalidate the data. > > So if a SELECT is in progress, other SELECT commands can be allowed to > proceed without problems. But no INSERT or UPDATE can be allowed until > the SELECT is finished. Hence you will sometimes get a lock on the > write. > > How you deal with this, I don't know. Random wait-and-try-again ? Yes, I understood that, but the impression I got is that SELECT will place shared lock on the database. While INSERT or UPDATE will first place PENDING lock indicating that it wants to write. While it is in a PENDING lock state all operations that placed SHARED lock ( such as SELECTs ) will allow to complete and new SHARED locks either denied or blocked ( this part of documentation is not clear as to which one of these two actions are taken ). Then when all SHARED locks are removed due to completion of SELECTs, database moves from PENDING into EXCLUSIVE lock, which is cleared when update/write completed and then new/pending SHARED locks are allowed to proceed. This should mean that with many processes reading and only one writing there is no need to use sqlite3_busy_timeout() function, which is to be used when we have many processes trying to write to the database and/or reader if new SHARED locks are denied while database is in a PENDING and/or EXCLUSIVE lock state ( again, this point it not clear in documentation ). Do I understand it correctly?
-- Dmitri Priimak _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
