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 r...@rfk.id.au | http://www.rfk.id.au/ramblings/gpg/ for details
signature.asc
Description: This is a digitally signed message part
-- http://mail.python.org/mailman/listinfo/python-list