On Fri, Feb 10, 2012 at 9:05 AM, Rob Richardson <rdrichard...@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
