Re: Data integrity problem with sqlite3

Path	csiph.com!eternal-september.org!feeder.eternal-september.org!feeds.phibee-telecom.net!newsfeed.xs4all.nl!newsfeed8.news.xs4all.nl!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
Return-Path	<rosuav@gmail.com>
X-Original-To	python-list@python.org
Delivered-To	python-list@mail.python.org
X-Spam-Status	OK 0.017
X-Spam-Evidence	'H': 0.97; 'S': 0.00; '(b)': 0.07; 'meantime,': 0.07; 'subject:sqlite3': 0.07; 'cc:addr:python-list': 0.09; 'postgres': 0.09; 'postgresql.': 0.09; 'commit': 0.15; 'debugging,': 0.16; 'from:addr:rosuav': 0.16; 'from:name:chris angelico': 0.16; 'in-memory': 0.16; 'logging,': 0.16; 'personally,': 0.16; 'reproduce': 0.16; 'selects': 0.16; 'sqlite3': 0.16; 'wrote:': 0.16; '2015': 0.20; 'cc:2**0': 0.20; 'cc:addr:python.org': 0.20; 'aug': 0.20; 'purposes': 0.20; '(a)': 0.22; 'implicit': 0.22; 'simpler': 0.22; 'subject:problem': 0.22; 'trying': 0.22; 'header:In-Reply-To:1': 0.24; 'module': 0.25; 'message-id:@mail.gmail.com': 0.27; 'this.': 0.28; 'implicitly': 0.29; 'connection': 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; 'received:google.com': 0.35; 'on,': 0.35; 'possible,': 0.35; 'quite': 0.35; 'according': 0.36; 'but': 0.36; 'should': 0.36; 'success.': 0.36; 'pm,': 0.36; 'subject:: ': 0.37; 'two': 0.37; "won't": 0.38; 'anything': 0.38; 'means': 0.39; 'sure': 0.39; 'does': 0.39; 'subject:with': 0.40; 'your': 0.60; 'more': 0.63; 'different': 0.63; 'series': 0.65; 'differences': 0.66; 'on...': 0.66; 'production.': 0.66; 'subject:Data': 0.66; 'worth': 0.67; 'frank': 0.72; 'chrisa': 0.84; 'open,': 0.84; 'triggering': 0.84; 'to:none': 0.91; 'transactions': 0.91; 'try.': 0.91
DKIM-Signature	v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:cc :content-type; bh=V+v7uSctCv3BZAj2dAD8NH33y0BIQL2/sXBhbRtUKRM=; b=J3mRqIBB61lbPeSFPH/B92EfIZs/09goNDjshN1puKxP8tnYnRMqjBAJNRzvXd5KQX tuN4doWzAguY6KzZhR96hyPRDBE8ircsrzkgpAe9r+Gr7QRibO8Lz9RBDu0SoKS0fCE1 7q6j3yGwT7F9eTi5Xekd1BoR5/aRNSvu16jogNOPFXIeaUCJxO8b/vPRTe+031r+IhSO p1xBQi0l+XsDvvGUkYqP5Vcexglsl78LSYwmfjKw25JrKQDkKwLYvVm88kGBZeG+eTa9 ZPIXUlvp5FnciVV2M7fRNxAMSZ0nKLsqWYnES/C1y2i2+ojJ06VVDt6ae3I9uawtKGVB b+PA==
MIME-Version	1.0
X-Received	by 10.107.31.134 with SMTP id f128mr25055382iof.19.1439293439221; Tue, 11 Aug 2015 04:43:59 -0700 (PDT)
In-Reply-To	<mqcmie$po9$1@ger.gmane.org>
References	<mqcmie$po9$1@ger.gmane.org>
Date	Tue, 11 Aug 2015 21:43:59 +1000
Subject	Re: Data integrity problem with sqlite3
From	Chris Angelico <rosuav@gmail.com>
Cc	"python-list@python.org" <python-list@python.org>
Content-Type	text/plain; charset=UTF-8
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.76.1439293441.3627.python-list@python.org> (permalink)
Lines	33
NNTP-Posting-Host	2001:888:2000:d::a6
X-Trace	1439293441 news.xs4all.nl 2888 [2001:888:2000:d::a6]:34734
X-Complaints-To	abuse@xs4all.nl
Xref	csiph.com comp.lang.python:95255

Show key headers only | View raw

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.

ChrisA

Back to comp.lang.python | Previous | Next | Find similar | Unroll thread

Thread

Re: Data integrity problem with sqlite3 Chris Angelico <rosuav@gmail.com> - 2015-08-11 21:43 +1000

csiph-web