Data integrity problem with sqlite3
frank at chagford.com
Tue Aug 11 13:33:31 CEST 2015
I have a 'data integrity' problem with sqlite3 that I have been battling
with for a while. I have not got to the bottom of it yet but I do have some
useful info, so I thought I would post it here in the hope that someone with
some knowledge of the internals of the python sqlite3 module can throw some
light on it.
I am running python 3.4.3 on Windows 7, and I have upgraded sqlite from the
original '3.7.something' to '3.8.6'. I do not change the isolation level
from the default setting.
I have a transaction with a number of steps. One of the later steps raises
an exception, and I execute a rollback. However, some of the earlier steps
are committed to the database. Obviously this is a major problem.
I have added 'set_trace_callback' to see exactly what is going on, and in
the middle of my series of commands I find the following -
According to the docs, the sqlite3 module commits transactions implicitly
before a non-DML, non-query statement (i. e. anything other than
In my traceback I can only see SELECTs and UPDATEs following the implicit
commit, so I do not know what is triggering it. I am trying to reproduce the
problem in a simpler example, but so far without success. My running program
has two connections to the database open, another connection to an in-memory
database, and it is all running under asyncio, so it is quite difficult to
mimic all of this.
I will persevere, but in the meantime, does anyone happen to know under what
other circumstances sqlite3 might issue an implicit commit?
More information about the Python-list