Python sqlite3 module not rolling back transactions

Question

Consider this test case:

import sqlite3

con1 = sqlite3.connect('test.sqlite')
con1.isolation_level = None
con2 = sqlite3.connect('test.sqlite')
con2.isolation_level = None
cur1 = con1.cursor()
cur2 = con2.cursor()
cur1.execute('CREATE TABLE foo (bar INTEGER, baz STRING)')
con1.isolation_level = 'IMMEDIATE'
cur1.execute('INSERT INTO foo VALUES (1, "a")')
cur1.execute('INSERT INTO foo VALUES (2, "b")')
print cur2.execute('SELECT * FROM foo').fetchall()
con1.commit()
print cur2.execute('SELECT * FROM foo').fetchall()
con1.rollback()
print cur2.execute('SELECT * FROM foo').fetchall()

From my knowledge I was expecting to see this as a result:

[]
[(1, u'a'), (2, u'b')]
[]

But here it's resulting in this:

[]
[(1, u'a'), (2, u'b')]
[(1, u'a'), (2, u'b')]

So the call to rollback() method in the first connection didn't reverted the previously commited changes. Why? Shouldn't it roll back them?

Thank you in advance.

stderr stderr · Accepted Answer · 2011-02-19T14:35:03

You can't both commit and rollback the same transaction. con1.commit() ends your transaction on that cursor. The next con1.rollback() is either being silently ignored or is rolling back an empty transaction.

Python sqlite3 module not rolling back transactions

1 Answers