"Chris Angelico" wrote in message
news:CAPTjJmrHmj2bsdSm4CQ=orgxutmyctk3w3e3n-qxolg2tvq...@mail.gmail.com...
On Tue, Aug 11, 2015 at 9:33 PM, Frank Millman <fr...@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
--
https://mail.python.org/mailman/listinfo/python-list
