Data integrity problem with sqlite3

[Chris Angelico]

On Tue, Aug 11, 2015 at 9:33 PM, Frank Millman <frank at chagford.com> wrote:
> 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?
>

Not sure if it'll actually *solve* your problem, but it might help you
to find out more about what's going on... Try switching to PostgreSQL.
Among the key differences are (a) transactional DDL, which means you
won't get those implicit commits, and (b) server-side logging, so you
might get a different window into what your code is doing.

Personally, I use Postgres as much as possible, but in your situation,
it might be better for you to continue using sqlite3 in production.
But for the purposes of debugging, it should be worth a try.

ChrisA