Re: Data integrity problem with sqlite3 - solved

Path	csiph.com!eternal-september.org!feeder.eternal-september.org!newsfeed0.kamp.net!newsfeed.kamp.net!newsfeed.freenet.ag!87.79.20.101.MISMATCH!newsreader4.netcologne.de!news.netcologne.de!bcyclone03.am1.xlned.com!bcyclone03.am1.xlned.com!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; 'between.': 0.16; 'lock,': 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; 'subject:problem': 0.22; 'select': 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; 'implied': 0.29; 'issuing': 0.29; 'allows': 0.30; 'post': 0.31; 'statement': 0.32; 'useful': 0.33; 'problem': 0.33; 'add': 0.34; 'server': 0.34; 'could': 0.35; 'knowledge': 0.35; 'but': 0.36; 'there': 0.36; 'to:addr:python-list': 0.36; 'subject:: ': 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; 'times': 0.63; 'statement,': 0.66; 'subject:Data': 0.66; 'here': 0.66; 'decided': 0.66; 'frank': 0.72; 'bitten': 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	Tue, 11 Aug 2015 15:17:44 +0200
References	<mqcmie$po9$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.67.51
In-Reply-To	<mqcmie$po9$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.82.1439299087.3627.python-list@python.org> (permalink)
Lines	35
NNTP-Posting-Host	2001:888:2000:d::a6
X-Trace	1439299087 news.xs4all.nl 2956 [2001:888:2000:d::a6]:36764
X-Complaints-To	abuse@xs4all.nl
X-Received-Bytes	4178
X-Received-Body-CRC	1159950698
Xref	csiph.com comp.lang.python:95260

Show key headers only | View raw

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

Apologies for any wasted time.

Frank

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

Thread

Re: Data integrity problem with sqlite3 - solved "Frank Millman" <frank@chagford.com> - 2015-08-11 15:17 +0200

csiph-web