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 ? Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
