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: [email protected] [mailto:sqlite-users-
> [email protected]] 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: [email protected] [mailto:
> > [email protected]] 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" <[email protected]> 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
> > > [email protected]
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [email protected]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > _______________________________________________
> > sqlite-users mailing list
> > [email protected]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> [email protected]
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
