Edzard Pasma wrote:
> --- [email protected] 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.
No. That is not my case. I have many readers which reside in the same
process, but in different threads and one writer, which is completely
separate process. Here is an example.
$ echo "create table Z ( t int );" | sqlite3 a.db
$ echo "insert into Z ( t ) values ( 1 );" | sqlite3 a.db
$ for i in {1..10000}; do; echo "select * from Z;" | sqlite3 a.db >
/dev/null; done
In another terminal run following several times.
$ echo " update Z set t = 2;" | sqlite3 a.db
At some point this will produce
$ echo "update Z set t = 2;" | sqlite3 a.db
SQL error near line 1: database is locked
Am I misunderstanding something here or this error should have only
happened
in the terminal where we are doing select?
--
Dmitri Priimak
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
