Path: csiph.com!goblin3!goblin2!goblin.stu.neva.ru!newsfeed.xs4all.nl!newsfeed8.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.004
X-Spam-Evidence: '*H*': 0.99; '*S*': 0.00; 'postgresql': 0.07; 'subject:sqlite3': 0.07; 'received:80.91': 0.09; 'received:80.91.229': 0.09; 'received:gmane.org': 0.09; 'received:list': 0.09; 'throw': 0.09; 'python': 0.10; 'commit': 0.15; "'begin": 0.16; '3.2,': 0.16; '3.3.': 0.16; 'between.': 0.16; 'intended.': 0.16; 'lock,': 0.16; 'maintainers': 0.16; 'mistake.': 0.16; 'received:80.91.229.3': 0.16; 'received:plane.gmane.org': 0.16; 'sense,': 0.16; 'spotted': 0.16; 'sqlite3': 0.16; 'solution.': 0.18; 'first,': 0.20; 'progress.': 0.22; 'subject:problem': 0.22; 'select': 0.23; 'seems': 0.23; 'wrote': 0.23; '(this': 0.24; 'header:In-Reply-To:1': 0.24; 'module': 0.25; 'header:X-Complaints-To:1': 0.26; 'figure': 0.27; 'equivalent': 0.27; 'followed': 0.27; 'possibility': 0.27; "skip:' 10": 0.28; 'figured': 0.29; 'implied': 0.29; 'issuing': 0.29; 'allows': 0.30; 'transaction': 0.30; 'post': 0.31; 'continuing': 0.32; 'statement': 0.32; 'changed': 0.33; 'useful': 0.33; 'problem': 0.33; "skip:' 20": 0.34; 'add': 0.34; 'server': 0.34; 'could': 0.35; 'knowledge': 0.35; 'but': 0.36; 'there': 0.36; 'lines': 0.36; 'to:addr:python-list': 0.36; 'subject:: ': 0.37; 'thanks': 0.37; 'received:org': 0.37; 'thought': 0.37; 'anything': 0.38; 'someone': 0.38; 'to:addr:python.org': 0.40; 'subject:with': 0.40; 'some': 0.40; 'hope': 0.61; 'more': 0.63; 'times': 0.63; 'p.s.': 0.65; 'statement,': 0.66; 'subject:Data': 0.66; 'here': 0.66; 'decided': 0.66; 'life': 0.67; 'frank': 0.72; 'bitten': 0.84; 'overlooked': 0.84; 'trick.': 0.84; 'info,': 0.93
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 - solved
Date: Wed, 12 Aug 2015 08:20:17 +0200
References: <mqcmie$po9$1@ger.gmane.org> <mqcslv$tee$1@ger.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Gmane-NNTP-Posting-Host: 197.89.232.8
In-Reply-To: <mqcslv$tee$1@ger.gmane.org>
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.99.1439360441.3627.python-list@python.org>
Lines: 48
NNTP-Posting-Host: 2001:888:2000:d::a6
X-Trace: 1439360441 news.xs4all.nl 2902 [2001:888:2000:d::a6]:40776
X-Complaints-To: abuse@xs4all.nl
Xref: csiph.com comp.lang.python:95273

"Frank Millman"  wrote in message news:mqcslv$tee$1@ger.gmane.org...

> "Frank Millman"  wrote in message news:mqcmie$po9$1@ger.gmane.org...
>
> > Hi all
> >
> > I have a 'data integrity' problem with sqlite3 that I have been battling 
> > with for a while. I have not got to the bottom of it yet but I do have 
> > some useful info, so I thought I would post it here in the hope that 
> > someone with some knowledge of the internals of the python sqlite3 
> > module can throw some light on it.
>
> Oops, I have just spotted my mistake.
>
> There are times when I want to issue a SELECT statement with a lock, as it 
> will be followed by an UPDATE and I do not want anything to change in 
> between.
>
> MS SQL Server allows you to add 'WITH (UPDLOCK)' to a SELECT statement, 
> PostgreSQL allows you to add 'FOR UPDATE'.
>
> I could not find an equivalent for sqlite3, but in my wisdom (this was 
> some time ago) I decided that issuing a 'BEGIN IMMEDIATE' would do the 
> trick.
>
> I had not anticipated that this would generate an implied COMMIT first, 
> but it makes sense, and this is what has bitten me. Now I must try to 
> figure out a better solution.

For the record, I have figured out a better solution.

I was on the right lines with 'BEGIN IMMEDIATE', but I had overlooked the 
possibility that there could be a transaction already in progress.

Now I have changed it to -

    if not conn.in_transaction:
       cur.execute('BEGIN IMMEDIATE')

So far it seems to be working as intended.

Frank

P.S. Many thanks to the maintainers of the sqlite3 module for continuing to 
enhance it. 'in_transaction' was added in 3.2, and 'set_trace_callback' was 
added in 3.3. Without these my life would have been much more difficult.