nested transactions/savepoints in sqlite/pysqlite

Sat, 12 Jun 2010 00:39:27 +0800 | Filed under sqlite

Perusing the builtin-sqlite3 docs, as well as those of pysqlite, doesn't seem to offer much clue to using these.

After lots of hair-tearing, I think I've got it figured out.

Here's what most people usually do on their first try:

cur1.execute("create table test(i)")

cur1.execute("insert into test(i) values (5)")
cur1.execute("savepoint sp1")
cur1.execute("insert into test(i) values (5)")

# OperationalError: no such savepoint: sp1
cur1.execute("rollback to savepoint sp1")

Then, some googling reveals that you need to set isolation_level to None - so here's what we get:

cur1.execute("insert into test(i) values (5)")
con1.isolation_level = None
cur1.execute("savepoint sp1")
cur1.execute("insert into test(i) values (5)")

# separate cursor
cur2.execute("select i from test")
print len(cur2.fetchall()) # 1

Oops - the outer implied transaction was commited! Not what we want.

Let's try again, this time with an explicit BEGIN transaction:

# autocommit...
con1.isolation_level = None
# ...but start an explicit transaction.
cur1.execute("begin deferred transaction")

cur1.execute("insert into test(i) values (5)")
cur1.execute("savepoint sp1")
cur1.execute("insert into test(i) values (5)")

# separate cursor
cur2.execute("select i from test")
print len(cur2.fetchall()) # 0

Nice!

I've submitted a patch to pysqlite; hopefully, pysqlite users can get some idea on how to play nice with it.