sqlite savepoint problem

[Ryan Kelly]

On Fri, 2010-03-12 at 08:48 +0100, Laszlo Nagy wrote:
> >
> > I'm now confused. Also, I could not find anything about these 
> > isolation levels on the sqlite website. The only think I could find is 
> > "PRAGMA read_uncommited". If that is the same as setting 
> > isolation_level to None, then I don't want it.
> Yes, it is. Here is a test:

No it isn't.  The "magic" behind isolation_level is a creation of the
python sqlite bindings.  You can probably tell that I'm not a fan of it.

> import os
> import sqlite3
> import threading
> import time
> 
> FPATH = '/tmp/test.sqlite'
> if os.path.isfile(FPATH):
>     os.unlink(FPATH)
> 
> def getconn():
>     global FPATH
>     conn = sqlite3.connect(FPATH)
>     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")
>             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()
> 
> 
> And the test result:
> 
> Thr1: Inserting 0,1,2,3,4,5
> Thr1: Commited
> Thr1: Selecting all rows:
> (0,)
> (1,)
> (2,)
> (3,)
> (4,)
> (5,)
> Thr1: Wait some...
> Thr2: deleting all rows from a
> Thr2: Now we wait some BEFORE commiting changes.
> Thr1: Selecting again, in the same transaction
> Thr2: Will roll back!
> Exception in thread Thread-2:
> Traceback (most recent call last):
>   File "/usr/lib/python2.6/threading.py", line 525, in __bootstrap_inner
>     self.run()
>   File "test.py", line 44, in run
>     raise Exception
> Exception
> 
> 
> It means that setting isolation_level to None will really allow 
> uncommited changes to be read by other transactions! This is sad, and of 
> course this is something that I do not want.


No it doesn't.  The problem is that using a connection as a context
manager doesn't do what you think. 

It does *not* start a new transaction on __enter__ and commit it on
__exit__.  As far as I can tell it does nothing on __enter__ and calls
con.commit() or con.rollback() on exit.  With isolation_level=None,
these are no-ops.

If you create your own connection wrapper that explicitly creates and
commits transactions, you example will work fine with
isolation_level=None.  Here's the relevant changes:


  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


  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: 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/e018ea89/attachment.sig>