On Fri, Feb 10, 2012 at 9:32 AM, Marc L. Allen <mlal...@outsitenetworks.com>wrote:
> 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? > One approach is to be prepared to rollback and try again. Or, if you know that your transaction is going to be reading first and later writing, you can start with "BEGIN IMMEDIATE" which goes ahead and starts as a write transaction, guaranteeing that no other process will write ahead of you so that when you get around to writing yourself, the write won't hit a BUSY. > > > -----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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
