Re: [sqlite] Nested transactions

[Simon Slavin]

On 23 Nov 2013, at 11:35pm, Darren Duncan  wrote:

> On 2013.11.23 7:20 AM, Simon Slavin wrote:
>> Had the person who devised SQL thought it through, he'd have thought up 
>> savepoints instead of transactions and we wouldn't use transactions at all.
> 
> This is an interesting proposal, and makes a lot of sense to me, especially 
> given that savepoints today don't have the precondition of a "transaction" 
> being active to use them, so on their own "savepoint" is like a 
> generalization of a "transaction". -- Darren Duncan

My guess is that the engine would treat the outmost level of savepoint 
specially: that the outmost level of savepoint is equivalent to a transaction.

I sometimes work in a computer language (not available to the public, sorry) 
which allows a set of tasks to be divided up by criteria that the programmer 
can assign, or distributed among different processors as soon as one becomes 
free.  These things are conventional and have been done previously.  But this 
language can do it with 'if' tasks.  You can parcel up a test into two parts 
(e.g. one proof for even values, another for odd values; one for current 
records, another for each archive) and the test automatically terminates when 
the first answer of 'not true' is returned.  And the programming structure 
reminds me of how SQL does SAVEPOINTs.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested transactions

[Darren Duncan]

On 2013.11.23 7:20 AM, Simon Slavin wrote:

Had the person who devised SQL thought it through, he'd have thought up 
savepoints instead of transactions and we wouldn't use transactions at all.


This is an interesting proposal, and makes a lot of sense to me, especially 
given that savepoints today don't have the precondition of a "transaction" being 
active to use them, so on their own "savepoint" is like a generalization of a 
"transaction". -- Darren Duncan


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested transactions

[Simon Slavin]

On 23 Nov 2013, at 8:41am, Igor Korot  wrote:

> I
> don't have to use SAVEPOINT/RELEASE in this case.

Savepoints /is/ nested transactions.  At least the effect is the same.  Had the 
person who devised SQL thought it through, he'd have thought up savepoints 
instead of transactions and we wouldn't use transactions at all.

So don't worry about it, just use them.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested transactions

[Darren Duncan]

On 2013.11.23 1:31 AM, Clemens Ladisch wrote:

Igor Korot wrote:

If I understand correctly, I can do this (pseudo-code):

BEGIN TRANSACTION;
// some SQL statements
BEGIN TRANSACTION;


sqlite> begin; begin;
Error: cannot start a transaction within a transaction


This scenario will not end up with with unfinished transaction and I
don't have to use SAVEPOINT/RELEASE in this case.


You have to use SAVEPOINT/RELEASE.

(Why don't you want to use it?)


I agree.  The concept of nested transactions is identical behaviorally to 
savepoints; they are just different syntax for the same thing, which is to let 
you undo a portion of the current transaction rather than the whole thing.  But 
only the parentmost transaction is a real transaction, with the ACID properties, 
eg only the parentmost committing actually saves anything for good.


Maybe what you're wanting is "autonomous transactions", which can be useful, but 
you can also implement that yourself just by having a second connection to the 
database from your application, which is behaviorally the same.  Although with 
SQLite it may not be helpful if one connection would block for the other, but 
other DBMSs that don't lock the whole database they may be useful with.


-- Darren Duncan


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested transactions

[Clemens Ladisch]

Igor Korot wrote:
> If I understand correctly, I can do this (pseudo-code):
>
> BEGIN TRANSACTION;
> // some SQL statements
> BEGIN TRANSACTION;

sqlite> begin; begin;
Error: cannot start a transaction within a transaction

> This scenario will not end up with with unfinished transaction and I
> don't have to use SAVEPOINT/RELEASE in this case.

You have to use SAVEPOINT/RELEASE.

(Why don't you want to use it?)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nested transactions

[Dan]

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

Re: [sqlite] Nested transactions

[Pavel Ivanov]

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

Re: [sqlite] nested transactions

[Brad Stiles]

> This will be a point release:  3.6.8.  There are no
> incompatibilities.  An important aspect of our social contract is
> that SQLite continues to be compatible moving forward.  There are 
> hundreds of millions of SQLite3 databases in the world, and we do 
> not want to abandon them.

Software companies around the world, please note the way you *should* be
doing business...

Thanks.
Brad

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] nested transactions

[D. Richard Hipp]

On Jan 2, 2009, at 12:28 AM, Chris Wedgwood wrote:

> On Thu, Jan 01, 2009 at 08:19:01PM -0500, D. Richard Hipp wrote:
>
>> FWIW, nested transactions (in the form of SAVEPOINTs) will appear in
>> the next SQLite release, which we hope to get out by mid-January.
>
> Is that going to be 4.0.x then?  I'm assuming there will need to be
> incompatible file format changes sooner or later.

This will be a point release:  3.6.8.  There are no  
incompatibilities.  An important aspect of our social contract is that  
SQLite continues to be compatible moving forward.  There are hundreds  
of millions of SQLite3 databases in the world, and we do not want to  
abandon them.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] nested transactions?

[Kervin L. Pierre]

D. Richard Hipp wrote:
On Thu, 2005-03-31 at 17:08 -0500, Kervin L. Pierre wrote:
Are there plans for supporting nested transactions
in the future? 

No.
Shucks. :)
Instead of calling sqlite3_exec("BEGIN") and sqlite3_exec("END")
directly, put them in a wrapper function that counts the number
of nested invocations.  Only execute the SQL at the top level.
Thanks for that workaround.  I will
to to make that work but...
It gets hairy when the API needs
to be thread safe and the function
prototypes are dictated to you. Eg.
a plugin for a multithreaded
application.
One alternative seems to be to
serialize access to the counter
variable.  But I'd like to avoid
that for performance and debugging
reasons.
Having an internal version of each
function that takes those SQLite
parameters, and having the external
version call those only with the
sqlite helper object, seems like a
fix as well.
Please let me know what you think...
internal_func1( sqliteHelper *sql, int param, ){
some_Other_Object_That_Also_Uses_SQLite *otherObj;
otherObj->internal_func1(sql, );
[...]
}
exposed_func1( int param, ){
sqliteHelper *sql;

sql->Begin();
internal_func1(sql, param, ...)
sql->End();
}
But that requires a lot of code for
a single problem. Is there an
elegant solution?
-
Kervin

Re: [sqlite] nested transactions?

[Jan-Eric Duden]

Kervin L. Pierre wrote:
Hello again,
Are there plans for supporting nested transactions
in the future?  Would that be a difficult extension
to code ( eg. if one thought they could give it a
try :) )
The current restriction makes it hard to use SQLite
in developing a API eg
exposed_func1()
{
   sqlite3_exec("BEGIN");
   [...do stuff...]
   func2();
   sqlite3_exec("END");
}
exposed_func2()
{
   sqlite3_exec("BEGIN");
   [...do stuff...]
   sqlite3_exec("END");
   [...do more stuff...]
}
I could commit early, eg. before calling expose_func2(),
but on error the entire function needs to be rolled back,
both inner and outer functions.
Any information and, or insight would be appreciated.
-
Kervin
You can write a wrapper around the sqlite3 transaction APIs that 
performs some sort of reference counting.

For example:
void BeginTransaction()
{
   if(Counter==0)
  sqlite3_exec("BEGIN");
   Counter++;
}
void EndTransaction()
{
   Counter--;
   if(Counter==0)
  sqlite3_exec("END");
}
This is not as nice as real nested transactions, since you can't do a 
partial rollback, but its better than nothing.

Jan-Eric

Re: [sqlite] nested transactions?

[D. Richard Hipp]

On Thu, 2005-03-31 at 17:08 -0500, Kervin L. Pierre wrote:
> Are there plans for supporting nested transactions
> in the future? 

No.

> The current restriction makes it hard to use SQLite
> in developing a API eg
> 
> exposed_func1(){
> sqlite3_exec("BEGIN");
> [...do stuff...]
> func2();
> sqlite3_exec("END");
> }
> 
> exposed_func2(){
> sqlite3_exec("BEGIN");
> [...do stuff...]
> sqlite3_exec("END");
> [...do more stuff...]
> }
> 

Instead of calling sqlite3_exec("BEGIN") and sqlite3_exec("END")
directly, put them in a wrapper function that counts the number
of nested invocations.  Only execute the SQL at the top level.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>