Storm and sqlite locking
The Storm ORM struggles with sqlite3's transaction behaviour as they explain in their source code. Looking at the implementation of .raw_execute() the side effect of their solution to this is that they start an explicit transaction on every statement that gets executed. Including SELECT.
This, in turn, sucks big time. If you look at sqlite's locking behviour you will find that it should be possible to read from the database using concurrent connections (i.e. from multiple processes and/or threads at the same time). However since storm explicitly starts that transaction for a select it means the connection doing the read now holds a SHARED lock until you end the transaction (by doing a commit or rollback). But since it's holding this shared lock, for no good reason, it means no other connection can acquire the EXCLUSIVE lock at the same time.
The upshot of this seems to be that you need to call .commit() even after just reading the database, thus ensuring you let go of the shared lock. Can't say I like that.
2 comments:
Anonymous said...
The SQLite usage model is that you go in and do things and it holds locks for as brief periods as possible. This does mean that if you start a regular transaction (not exclusive/immediate) then it can fail at a later point once the lock is actually acquired.
The real problem is using an abstraction layer that tries to pretend all databases are the same. (No one else matches SQLite's manifest typing which is much the same as how Python compares to statically typed languages.)
The news is that SQLite currently has write ahead logging under development which will allow for read concurrency and reads won't block writes. See http://sqlite.org/draft/wal.html for details. Note that it will be considered beta/experimental level for a few releases before being production ready.
Paul Boddie said...
Not a Storm-specific thing, but rather a DB-API thing which probably dictates some terms on which Storm operates: upon performing a query under the DB-API you'll automatically start a transaction unless you're in autocommit mode. This actually means that you may be obliged to periodically commit or rollback even if you haven't modified any data, anyway.
New comments are not allowed.