sqlite transaction isolation, select for update, race condition

[Clay Gerrard]

Yesterday I was working with some code that needed a "select ... for
update" concept to avoid a race condition.
Adding .with_lockmode('update') works a treat on InnoDB and Postgres,
but for sqlite I end up having to sneak in a "if session.bind.name ==
'sqlite'; session.execute('begin immediate transaction')" before doing
the select.

This seems to work for now, but it feels like cheating. Better way to
do this?

-clayg

[Michael Bayer]

SQLite doesn't have support for SELECT..FOR UPDATE, and with_lockmode() ultimately has no impact when using SQLite as nothing is rendered. SQLite's concurrency model is based on a lock of the entire database file - hardly a row lock - I wouldn't think such a strategy applies on that backend ?

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Daniel Holth]

Is this pysqlite issue about SELECT not starting a transaction related? http://code.google.com/p/pysqlite/issues/detail?id=21

[Clay Gerrard]

On Apr 27, 9:19 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> SQLite doesn't have support for SELECT..FOR UPDATE, and with_lockmode() ultimately has no impact when using SQLite as nothing is rendered.  

IDK why sqlite doesn't support a way to elevate the lock on a select
in the middle of a deferred transaction like it does with updates and
inserts; but yeah, better the dialect to noop than raise a syntax
error.

> SQLite's concurrency model is based on a lock of the entire database file - hardly a row lock - I wouldn't think such a strategy applies on that backend ?

I kinda feel the opposite, if I can't have a row lock - then yeah, go
ahead and lock the whole database!

But here's the rub, generally speaking a select isn't going to place a
read lock (or "reserved lock") on a sqlite database - you get a
"shared" lock, so other processes can't do insert's or updates, but
they could still select the row I'm about to update even while I'm in
an uncommited transaction. In order to prevent another process from
reading the row I'm about to update form the database until I'm done
updating it - it seems that sqlite expects this unconventional "begin
IMMEDIATE transaction" notation, which will lock further reads against
the database until I commit.

I'm just thinking there might be a better way in sqlalchemy to get at
these weird sqlite transaction levels - http://www.sqlite.org/lang_transaction.html
- other than doing a session.execute? -

-clayg

[Michael Bayer]

then yes, for your case this is exactly the pysqlite bug Daniel mentions: http://code.google.com/p/pysqlite/issues/detail?id=21 . Pysqlite doesn't open the transaction until DML is encountered specifically to reduce file locks. This should be configurable, as well as the type of BEGIN emitted. SQLAlchemy itself never emits BEGIN.

[Clay Gerrard]

On Apr 27, 9:37 am, Daniel Holth <dho...@gmail.com> wrote:
> Is this pysqlite issue about SELECT not starting a transaction related?http://code.google.com/p/pysqlite/issues/detail?id=21

Hrmmm... well... that's interesting... it might be related, but maybe
not? I'm not setting the isolation level when I create the engine. I
don't think the explicit begin would help unless I specifically tell
it to begin immediate transaction.

I tried adding isolation_level='SERIALIZABLE' to the kwargs when I
call create_engine, no change. I changed my explicit 'BEGIN IMMEIDATE
TRANSACTION' to just a 'BEGIN' - and got a bunch of database is locked
errors. I removed the explicit BEGIN all together and I was back to
my race condition - which seems to be closer to what that bug is
about...

Still... good info, thanks!

[Clay Gerrard]

On Apr 27, 10:41 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
> then yes, for your case this is exactly the pysqlite bug Daniel mentions:  http://code.google.com/p/pysqlite/issues/detail?id=21.  Pysqlite doesn't open the transaction until DML is encountered specifically to reduce file locks.   This should be configurable, as well as the type of BEGIN emitted.      SQLAlchemy itself never emits BEGIN.

oic, so if pysqlite was doing the right thing with the explicit BEGIN
I would expect to be able to do something like this:

engine = create_engine('sqlite:///foo.db',
connect_args={'isolation_level': 'IMMEDIATE'})

... and all connections would automatically issue the correct begin
statement and acquire a reserved lock at the beginning of the
transaction. But as it is, they don't do anything until they get down
to the update, and it's kind of a disaster.

Thanks for the info,

-clayg

[Torsten Landschoff]

Hi Clay,

On Wed, 2011-04-27 at 11:21 -0700, Clay Gerrard wrote:

> ... and all connections would automatically issue the correct begin
> statement and acquire a reserved lock at the beginning of the
> transaction. But as it is, they don't do anything until they get down
> to the update, and it's kind of a disaster.

I had this disaster a number of times as well. For that reason I created
a patch for this issue http://bugs.python.org/issue10740

That change makes the time when pysqlite starts a transaction
configurable. The obvious approach to start a transaction on each
command breaks "pragma foreign_keys=on" since it has to be used outside
a transaction.

I welcome comments and suggestions about that patch.

Greetings, Torsten

--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff

Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561

mailto:torsten.l...@dynamore.de
http://www.dynamore.de

Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz