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

List:       sqlite-users
Subject:    Re: [sqlite] Nested transactions
From:       Dan <danielk1977 () gmail ! com>
Date:       2009-07-25 5:31:09
Message-ID: 995BAF9E-5405-4E58-9BF1-BCCC25971B3F () gmail ! com
[Download RAW message or body]


On Jul 25, 2009, at 2:26 AM, Pavel Ivanov wrote:

> 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.

This was true for a long time. But as of version 3.6.5, behaviour has
changed so that transactions may be committed even if the database
connection has ongoing read queries.

Assuming no BEGIN has been issued (sqlite3_get_autocommit() returns 1),
each DELETE statement in the pseudo-code below will cause SQLite to
upgrade from a read to a write lock, delete records from the database
file, then downgrade back to a read lock.

Dan.


>
> 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

_______________________________________________
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]