Data integrity problem with sqlite3

Frank Millman frank at chagford.com
Tue Aug 11 13:33:31 CEST 2015


Hi all

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 -

    COMMIT
    BEGIN IMMEDIATE

According to the docs, the sqlite3 module commits transactions implicitly 
before a non-DML, non-query statement (i. e. anything other than 
SELECT/INSERT/UPDATE/DELETE/REPLACE).

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?

Thanks

Frank Millman




More information about the Python-list mailing list