So, you're assuming the OP actually started a transaction? Because, otherwise, isn't the SELECT in step 2 and the UPDATE in step 5 separate transactions?
If there is a BEGIN in there somewhere, we're talking about: App1: BEGIN SELECT UPDATE .. App2 BEGIN UPDATE ... Right? And you're saying that this causes a problem if App2 gets in between App1's SELECT and UPDATE? > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: Friday, February 10, 2012 9:13 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Database locked in multi process scenario > > On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson <RDRichardson@rad- > con.com>wrote: > > > Isn't it almost a requirement of a transaction that only one be open > > at a time in a database? If there could be more than one > transaction, > > then transaction 1 might start, transaction 2 starts, transaction 1 > > fails, transaction 1 is rolled back, and what happens to transaction > > 2? One could imagine one transaction working an table 1 and a second > > working on table 2 which has no connection, but then someone comes > > along and adds a trigger to table 1 that updates table 2. Now we > have > > two simultaneous independent transactions working on table 2. > > > > SQLite supports only SERIALIZABLE transaction semantics. That means > the end result of the database is as if the various transactions had > occurred in a strictly linear sequence. > > But SQLite does allow multiple simultaneous transactions to be in play, > as long as no more than one of them is a write transaction. When a > read transaction begins, it sees a snapshot of the database from the > moment in time when the transaction started. In change that occur to > the database file from other database connections are invisible to that > transaction. > > The OPs problem is that he has a old read transaction open which is > looking at an historical snapshot of the database, that does not > contain the latest changes to the database. Then he tries to promote > that read transaction to a write transaction. But that is not allowed, > because doing so would "fork" the history of the database file. The > result might not be serializable. Before you can write, you have to > first be looking at the most up-to-date copy of the database. > > > > > > > RobR, who has been struggling for months with a program that might > > open the same SQLite file at the same time from two points in the > > program, and who has realized that the program is not well designed. > > > > -----Original Message----- > > From: sqlite-users-boun...@sqlite.org [mailto: > > sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP > > Sent: Friday, February 10, 2012 8:52 AM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Database locked in multi process scenario > > > > In the real code, there is no sleep/wait or pause. It so happens that > > the write of the app2 is scheduled in between. > > > > What you are suggesting is that at any point of time only one process > > can have a transaction open in a database? > > > > > > Sreekumar > > On Feb 10, 2012 7:12 PM, "Simon Slavin" <slav...@bigfraud.org> wrote: > > > > > > > > On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote: > > > > > > > well, the 'wait' is a simulation of what happens in the real > code. > > > > > > > > The error is fatal to the application as it never ever recovers > > > > from it even though the writer has finalized and terminated. > > > > > > In a multi-process environment I recommend that you do not pause > for > > > such a long time between the first _step() and the _reset() or > > > _finalize(). You can _bind() a statement then wait a long time to > > > execute it, but once you have done your first _step() you want to > > > get through the data and release the database for other processes. > > > > > > If you still have the database locked and another process tries to > > > modify it, one process or the other will have to deal with a BUSY, > > > or a LOCKED, or something like that. In your own setup, it turns > > > out to be process 1. But a slightly different setup would make > > > process 2 see a > > BUSY instead. > > > > > > Simon. > > > _______________________________________________ > > > 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 > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > 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