Data integrity problem with sqlite3 - solved
Frank Millman
frank at chagford.com
Wed Aug 12 02:20:17 EDT 2015
"Frank Millman" wrote in message news:mqcslv$tee$1 at ger.gmane.org...
> "Frank Millman" wrote in message news:mqcmie$po9$1 at 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.
More information about the Python-list
mailing list