Laszlo Nagy
3/12/2010 7:49:00 AM
>
> 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:
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. If I change it to DEFERRED
then I get a correct 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
(0,)
(1,)
(2,)
(3,)
(4,)
(5,)
Thr2: Will roll back!
However, then savepoints won't work. Is there any way to use read
commited (or higher) isolation level, and have savepoints working at the
same time?
I don't see how would savepoints be useful without at least read
commited isolation level. :-(
L