Actually, I inspected carefully the code and to avoid any deadlocks and make the intentions very explicit, every time we're about to write we do:
Begin immediate transaction Our updates Commit transaction Anytime we read we do Begin transaction read rollback transaction (there was a mix of commit and rollback, but I made it uniform by using rollback only) I still can't find the hole in our code. More info: after the scenario is done, any read in C (thus a begin/rollback) doesn't affect A, but _any_ write transaction (thus begin immediate/commit) results in SQLITE_BUSY. Could this be related to this: in A, the database connection is created in the main program, but is passed down to a dll that loads another dll that uses the connection to do the writes. Maybe the dll should open its own connection? Normand -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: October-29-13 6:41 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problem with SQLITE_BUSY 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
