On Apr 27, 2011, at 11:18 AM, Clay Gerrard wrote: > > > 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? -
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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
