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
