"As recommended, BEGIN IMMEDIATE should prevent thread2 from even
starting a transaction if thread1 did so first, however I think this
will only work correctly if the same connection handle is used in both,
else they still may not know about eachother."
Simply not true... If you have different connection handles to the same db..
Then it would be wise to use BEGIN EXCLUSIVE. The reason is that sqlite will
acquire an EXCLUSIVE lock, in the file when you use begin EXLCUSIVE. Or it will
return a sqlite error sqlite_busy, simply retry...
BEGIN IMMEDIATE will acquire a reserved lock. Other uses may still be reading
and this lock type must escalate to a PENDING and then to an EXCLUSIVE.
While the reserved lock is enabled other users (threads) may perform reads.
But they may not perform begin immediate/exlusive etc...
Once an exclusive lock is acquired no other user (thread) may access the DB..
Example:
THREAD1 THREAD2
sqlite3_prepare
sqlite3_step
(Step through query) BEGIN EXCLUSIVE -- Loop here on sqlite BUSY.
INSERTS -- You should not get any qlite
busy here!
COMMIT -- Nor should you get sqlite busy
here!!!
The problem you were facing is that whith a begin immediate sqlite acquires a
"RESERVED" lock. This is an intent to write lock. You could still actually get
sqlite busy errors during your inserts or commit operations. But the thread
wrting thread should eventually be able to acquire the lock and continue as
long as the reading threads close off their locks before the writer times out.
HTH.
Ken
