sqlite savepoint problem

[Ryan Kelly]

On Fri, 2010-03-12 at 08:32 +0100, Laszlo Nagy wrote:
> > From memory you can't issue a "CREATE TABLE" statement inside a
> > transaction, at least not at the default isolation level.  Such a
> > statement will automatically commit the current transaction.  Doesn't
> > help with your current problem but worth pointing out :-)
> >   
> Thank you. I'll keep in mind.
> > When debugging strange transaction behaviour, I find it easiest to
> > create the connection with isolation_level=None so that are no implicit
> > transactions being created behind your back.  Not sure why, but setting
> > this makes your example work for me.
> >   
> Yes, same for me. But setting it to None means auto commit mode! See here:
> 
> http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions
> 
> 
> But it does not work that way. Look at this example
> 
> import sqlite3
> 
> conn = sqlite3.connect(':memory:')
> conn.isolation_level = None
> with conn:
>     conn.execute("create table a ( i integer ) ")
> 
> with conn:
>     conn.execute("insert into a values (1)")
>     conn.execute("SAVEPOINT sp1")
>     conn.execute("insert into a values (2)")
>     conn.execute("SAVEPOINT sp2")
>     conn.execute("insert into a values (3)")
>     conn.execute("ROLLBACK TO sp2")
>     conn.execute("insert into a values (4)")
>     conn.execute("RELEASE sp1")
> 
> with conn:
>     for row in conn.execute("select * from a"):
>         print row
>        
> 
> It prints:
> 
> (1,)
> (2,)
> (4,)
> 
> So everything is working. Nothing is auto commited. But if I change it 
> to "DEFERRED" or "IMMEDIATE" or "EXCLUSIVE" then it won't work. Why?

I have a theory, based on a quick perusal of the sqlite3 bindings
source.

The bindings think that "SAVEPOINT sp1" is a "non-DML, non-query"
statement. So when isolation_level is something other than None, this
statement implicitly commits the current transaction and throws away
your savepoints!

Annotating your example:

  # entering this context actually does nothing
  with conn:
     # a transaction is magically created before this statement
     conn.execute("insert into a values (1)")
     # and is implicitly committed before this statement
     conn.execute("SAVEPOINT sp1")
     # a new transaction is magically created
     conn.execute("insert into a values (2)")
     # and committed, discarding the first savepoint.
     conn.execute("SAVEPOINT sp2")
     # a new transaction is magically created
     conn.execute("insert into a values (3)")
     # and committed, discarding the very savepoint we are trying to use. 
     conn.execute("ROLLBACK TO sp2")
     conn.execute("insert into a values (4)")
     conn.execute("RELEASE sp1")


In your previous multi-threaded example, try adding a "SAVEPOINT sp1"
statement after deleting the rows in Thread2.  You'll see that the
delete is immediately committed and the rows cannot be read back by
Thread1.  (modified version attached for convenience).


  Cheers,

     Ryan

-- 
Ryan Kelly
http://www.rfk.id.au  |  This message is digitally signed. Please visit
ryan at rfk.id.au        |  http://www.rfk.id.au/ramblings/gpg/ for details

-------------- next part --------------
A non-text attachment was scrubbed...
Name: test_savepoint_commit.py
Type: text/x-python
Size: 1818 bytes
Desc: not available
URL: <http://mail.python.org/pipermail/python-list/attachments/20100315/7fb26687/attachment-0001.py>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: This is a digitally signed message part
URL: <http://mail.python.org/pipermail/python-list/attachments/20100315/7fb26687/attachment-0001.sig>