[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Nested transactions
From:       Pavel Ivanov <paivanof () gmail ! com>
Date:       2009-07-24 19:26:54
Message-ID: f3d9d2130907241226q5c3e3b7apa1c0145f5b3e33c1 () mail ! gmail ! com
[Download RAW message or body]

First of all in case you've mentioned it will be not nested
transaction but one transaction including all deletes and it will be
committed when select statement will be finished.

As a second note: it's pretty bad idea to change table which is used
in not yet finished select statement. In SQLite it can lead to
unpredictable behavior. E.g. rows that you've already deleted can
still be selected in subsequent fetches or new rows that you've
inserted would be returned by issued earlier select statement. And
this can lead effectively to infinite loop.
So indeed you better use some memory structures for storing results of
your select first, before changing the table.

Pavel

On Fri, Jul 24, 2009 at 3:12 PM, Guido Ostkamp<ooo@ostkamp.fastmail.fm> wrote:
> Hello,
>
> just a short question:
>
> With the current sqlite version, is it possible to have nested
> transactions, e.g.
>
>     exec select ... from table
>     while (fetch row) {
>         if (criteria)
>             exec delete from t where ...
>     }
>
> which means execute some 'select', fetching the results in a loop and
> inside the loop executing e.g. 'delete' statements on the same table based
> on the data returned by the fetch?
>
> The 'definitive guide to sqlite' book that I have (which is based on an
> now outdated version of sqlite) says it isn't and I have to put the
> results of the select into another temporary table or local storage to
> work around the problem, but I thought I had heard about new support for
> nested transactions.
>
> Thanks for any insight.
>
> Regards
>
> Guido
> _______________________________________________
> 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

[prev in list] [next in list] [prev in thread] [next in thread]