I see. So, the implied commit doesn't occur until you finalize? As a result, the subsequent update in step 5 was added to his non-finalized select?
Still.. what is the correct way to handle the explicit scenario? I mean, having one process do a BEGIN SELECT UPDATE and another do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect from a problem? Detect the error, rollback, and try again? > -----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:28 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Database locked in multi process scenario > > On Fri, Feb 10, 2012 at 9:19 AM, Marc L. Allen > <mlal...@outsitenetworks.com>wrote: > > > > > 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? > > > > The OP said "Step 3: The statement is not reset or finalized". That > doesn't guarantee that the automatic read transaction that was started > by the statement is still open, but it is pretty good hint. > > Remember, every statement runs within a transaction. Otherwise, the > information coming out of the SELECT at the beginning might be > incompatible with information that comes out at the end, if another > connection modified the database while the select was running. It is > not necessary to explicitly start a transaction with BEGIN in order to > be in a transaction. > If you don't manually do BEGIN, then BEGIN ... COMMIT is automatically > inserted around each SQL statement you run. > > > > > > 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 > > > > > > -- > 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
