Path: csiph.com!eternal-september.org!feeder.eternal-september.org!border1.nntp.ams1.giganews.com!nntp.giganews.com!newsfeed.xs4all.nl!newsfeed7.news.xs4all.nl!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
Return-Path: <python-python-list@m.gmane.org>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
X-Spam-Status: OK 0.003
X-Spam-Evidence: '*H*': 0.99; '*S*': 0.00; '(b)': 0.07; 'meantime,': 0.07; 'postgresql': 0.07; 'subject:sqlite3': 0.07; 'postgres': 0.09; 'postgresql,': 0.09; 'postgresql.': 0.09; 'received:80.91': 0.09; 'received:80.91.229': 0.09; 'received:gmane.org': 0.09; 'received:list': 0.09; 'commit': 0.15; 'server,': 0.15; 'debugging,': 0.16; 'in-memory': 0.16; 'inaccessible': 0.16; 'logging,': 0.16; 'personally,': 0.16; 'rather,': 0.16; 'received:80.91.229.3': 0.16; 'received:plane.gmane.org': 0.16; 'reproduce': 0.16; 'selects': 0.16; 'skip:n 70': 0.16; 'sqlite3': 0.16; 'wrote:': 0.16; '2015': 0.20; 'aug': 0.20; 'purposes': 0.20; '(a)': 0.22; 'implicit': 0.22; 'simpler': 0.22; 'subject:problem': 0.22; 'trying': 0.22; 'wrote': 0.23; 'header:In-Reply-To:1': 0.24; 'module': 0.25; 'header:X-Complaints-To:1': 0.26; 'this.': 0.28; 'btw,': 0.29; 'implicitly': 0.29; 'connection': 0.30; 'transaction': 0.30; 'code': 0.30; 'connections': 0.30; 'window': 0.30; 'skip:s 30': 0.31; 'anyone': 0.32; 'another': 0.32; 'statement': 0.32; 'problem': 0.33; 'traceback': 0.33; "skip:' 20": 0.34; 'tue,': 0.34; 'running': 0.34; 'on,': 0.35; 'machines': 0.35; 'possible,': 0.35; 'quite': 0.35; 'expected': 0.35; 'according': 0.36; 'but': 0.36; 'should': 0.36; 'success.': 0.36; 'to:addr:python-list': 0.36; 'pm,': 0.36; 'subject:: ': 0.37; 'two': 0.37; 'received:org': 0.37; "won't": 0.38; 'anything': 0.38; 'means': 0.39; 'sure': 0.39; 'does': 0.39; 'to:addr:python.org': 0.40; 'subject:with': 0.40; 'your': 0.60; 'back': 0.62; 'more': 0.63; 'different': 0.63; 'series': 0.65; 'differences': 0.66; 'on...': 0.66; 'production.': 0.66; 'subject:Data': 0.66; 'offer': 0.66; 'worth': 0.67; '8bit%:21': 0.70; 'frank': 0.72; '\xe2\x80\x93': 0.72; 'around,': 0.84; 'open,': 0.84; 'triggering': 0.84; 'transactions': 0.91; 'try.': 0.91
X-Injected-Via-Gmane: http://gmane.org/
To: python-list@python.org
From: "Frank Millman" <frank@chagford.com>
Subject: Re: Data integrity problem with sqlite3
Date: Tue, 11 Aug 2015 14:37:38 +0200
References: <mqcmie$po9$1@ger.gmane.org> <CAPTjJmrHmj2bsdSm4CQ=oRGxuTmycTk3W3e3n-QXoLg2TVQMbA@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original
Content-Transfer-Encoding: 8bit
X-Gmane-NNTP-Posting-Host: 197.89.67.51
In-Reply-To: <CAPTjJmrHmj2bsdSm4CQ=oRGxuTmycTk3W3e3n-QXoLg2TVQMbA@mail.gmail.com>
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3502.922
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3502.922
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.20+
Precedence: list
List-Id: General discussion list for the Python programming language <python-list.python.org>
List-Unsubscribe: <https://mail.python.org/mailman/options/python-list>, <mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <http://mail.python.org/pipermail/python-list/>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <https://mail.python.org/mailman/listinfo/python-list>, <mailto:python-list-request@python.org?subject=subscribe>
Newsgroups: comp.lang.python
Message-ID: <mailman.80.1439296681.3627.python-list@python.org>
Lines: 55
NNTP-Posting-Host: 2001:888:2000:d::a6
X-Trace: 1439296681 news.xs4all.nl 2939 [2001:888:2000:d::a6]:53771
X-Complaints-To: abuse@xs4all.nl
Xref: csiph.com comp.lang.python:95258

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

On Tue, Aug 11, 2015 at 9:33 PM, Frank Millman <frank@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