Re: 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
r...@rfk.id.au        |  http://www.rfk.id.au/ramblings/gpg/ for details



import os
import sqlite3
import threading
import time

FPATH = '/tmp/test.sqlite'
if os.path.isfile(FPATH):
    os.unlink(FPATH)

class MyConn(sqlite3.Connection):
    def __enter__(self):
        self.execute("BEGIN")
        return self
    def __exit__(self,exc_type,exc_info,traceback):
        if exc_type is None:
            self.execute("COMMIT")
        else:
            self.execute("ROLLBACK")

def getconn():
    global FPATH
    conn = sqlite3.connect(FPATH)#,factory=MyConn)
    conn.isolation_level = None
    return conn

class Thr1(threading.Thread):
    def run(self):
        conn = getconn()
        print "Thr1: Inserting 0,1,2,3,4,5"
        with conn:
            for i in range(6):
                conn.execute("insert into a values (?)",[i])
        print "Thr1: Commited"
        with conn:
            print "Thr1: Selecting all rows:"
            for row in conn.execute("select * from a"):
                print row
            print "Thr1: Wait some..."
            time.sleep(3)
            print "Thr1: Selecting again, in the same transaction"
            for row in conn.execute("select * from a"):
                print row


class Thr2(threading.Thread):
    def run(self):
        conn = getconn()
        with conn:
            print "Thr2: deleting all rows from a"
            conn.execute("delete from a")
            conn.execute("savepoint sp1")
            print "Thr2: Now we wait some BEFORE commiting changes."
            time.sleep(3)
            print "Thr2: Will roll back!"
            raise Exception


def main():
    with getconn() as conn:
        conn.execute("create table a ( i integer ) ")
    thr1 = Thr1()
    thr1.start()
    time.sleep(1)
    thr1 = Thr2()
    thr1.start()

main()

signature.asc
Description: This is a digitally signed message part

-- 
http://mail.python.org/mailman/listinfo/python-list