Data integrity problem with sqlite3

[Frank Millman]

"Chris Angelico"  wrote in message 
news:CAPTjJmrHmj2bsdSm4CQ=oRGxuTmycTk3W3e3n-QXoLg2TVQMbA at mail.gmail.com...

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.
>

My PostgreSQL is inaccessible at the moment as I am moving machines around, 
but I have tested it with MS SQL Server, and it behaves as expected – the 
transaction is fully rolled back and nothing is committed to the database.

BTW, I am not using sqlite3 ‘in production’. Rather, I offer a choice of 3 
databases to my users – PostgreSQL, SQL Server, and sqlite3, so I have to 
make sure that my program works with all of them.

Frank