You haven't explained what the second connection in A is doing. My
educated guess is, the two connections enter into a deadlock. This is
possible when at least one connection starts as a reader and later
attempts to write (the other could be a straight writer). The scenario
goes like this: the reader acquires a SHARED lock and starts reading.
Meanwhile, the writer is ready to write, so it gets a PENDING lock and
waits for readers to clear. But instead, the reader tries to start
writing by acquiring a RESERVED lock - which it cannot do for as long as
the other writer is active.
Once this deadlock happens, the only way to make progress is for one of
the two connections to roll back its transaction. One can avoid this
scenario by starting the transaction with BEGIN IMMEDIATE (or BEGIN
EXCLUSIVE) - this marks the transaction as a writer right away, by
obtaining a RESERVED (or EXCLUSIVE) lock from the start.
Igor Tandetnik
On 10/29/2013 6:26 PM, Normand Mongeau wrote:
Hi,
I have a situation where I always run into an SQLITE_BUSY error. It's quite
involved, here's the high picture:
-3 processes (A, B and C) each have a connection to the same db. Everybody
has a busy handler set for 5 seconds.
-1 of these processes (A) opens more than one connection, as it loads a DLL
that opens its own connection.
-A needs to check for data at regular intervals, so every second it does a
begin transaction, read then commit transaction.
-C deletes data within a transaction. Then it triggers about 400
transactions in B (it basically sends data to B via a TCP/IP layer, B
receives the data and writes in the DB).
-A then sees that data has arrived, and does its own processing, eventually
recording some more data (always within a begin/write/commit).
-After A is done, it goes back into its "check for data" at the same regular
interval.
So far so good. I see the A's begin/read/commit succeed.
As soon as I trigger another transaction in C (any transaction), A gets the
SQLITE_BUSY error, even though the transaction goes through normally in C
(i.e. begin transaction, write, commit transaction).
Any ideas as to what's wrong in the above scenario? How do I avoid the
SQLITE_BUSY error? Is it possible to recover from that error? (Apparently
not. I close the B and C processes (proper shutdown) and A still gets the
error).
Thanks,
Normand
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
