--- prii...@stanford.edu wrote: > > 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
Hello Dmitri, I understaod it the same way. However recently I observed that a PENDING lock does not perform its useful function (prevent writer starvation) in case readers and writers are threads of a single process! May that be the case? Best regards, Edzard Pasma. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
