>> Another option is to start IMMEDIATE transaction in the second process >> to avoid this course of action altogether. > > Yes. That won't fix the issue (you'll still get contention issues > that will require restarting the transaction) but they'll show up > at the beginning of the transaction, rather than the end.
And because they happen at the beginning of transaction one can deal with that with a simple statement retry just like Mikolaj deals with SQLITE_BUSY after a single INSERT in the first process I guess. So he won't see it as "transaction restarts" in this case, just statement retries. That's why it can be seen as fixing the issue. Pavel On Wed, Mar 7, 2012 at 12:28 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Wed, Mar 07, 2012 at 08:41:17AM -0500, Pavel Ivanov scratched on the wall: >> First your second process gets a SHARED lock on the database to read >> it, then your first process gets RESERVED lock on the database to >> indicate that it will change it. Then your second process tries to >> promote its SHARED lock to RESERVED one, sees that RESERVED lock has >> been already taken and can't proceed (returns SQLITE_BUSY). At this >> point first process can't commit its transaction because there's >> SHARED lock on it and second process can't proceed with its >> transaction because there's RESERVED lock on it. To continue you have >> to rollback transaction in the second process and start it over again. > > The SQLite docs explain a bit of what is going on here: > > http://sqlite.org/c3ref/busy_handler.html > > As Pavel says, sooner or later one of the connections needs to give > up and issue a ROLLBACK to kill the transaction and break the > deadlock. > > "Using SQLite" spends several pages on this issue, as it is rather > non-obvious to someone that hasn't messed with SQLite quite a bit. > >> Another option is to start IMMEDIATE transaction in the second process >> to avoid this course of action altogether. > > Yes. That won't fix the issue (you'll still get contention issues > that will require restarting the transaction) but they'll show up > at the beginning of the transaction, rather than the end. > > -j > > > > >> 2012/3/7 Miko??aj Radwan <mikolaj.rad...@coretechnology.pl>: >> > Hi all, >> > >> > For a couple days now I've been trying to figure out this problem and >> > can't seem to manage. >> > >> > I have two processes, both of which connect to the same SQLite database >> > and do the following: >> > >> > process 1: >> > >> > # opens database >> > PRAGMA foreign_keys = ON; >> > PRAGMA journal_mode = PERSIST; >> > INSERT INTO sometable (somecolumns) VALUES (somevalues); >> > # closes database >> > >> > process 2: >> > >> > # opens database >> > PRAGMA foreign_keys = ON; >> > PRAGMA journal_mode = PERSIST; >> > BEGIN DEFERRED TRANSACTION; >> > SELECT somecolumns FROM sometable WHERE someconditions; >> > DELETE FROM sometable WHERE someconditions; >> > COMMIT TRANSACTION; >> > # closes database >> > >> > After a couple iterations both processes start getting the "database >> > locked" error and never work again unless one of them is killed. >> > >> > The funny thing is that I tested this on two configurations. One is a >> > 32-bit Debian box with libsqlite-dev 3.7.3-1 installed. The other is a >> > 64-bit Gentoo box with sqlite 3.7.7.1. The problem only arises on the >> > first one. When I noticed it I looked through the version history but >> > have not seen any mention of a bug like that being solved. >> > >> > Any ideas? >> > >> > Thanks in advance, >> > Mikolaj >> > _______________________________________________ >> > 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 > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson > _______________________________________________ > 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
