sqlite savepoint problem

[Laszlo Nagy]

>
> 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