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
