db

Update: This issue went away when I upgraded to pysqlite 2.4.1 (from 2.3.2), so I doubt the info below is really correct.

I just stumbled upon this issue (“database is locked”), so I’m posting in case someone googles for this.

With Pysqlite, the default isolation level is DEFERRED, which means that for all write statements (INSERT/UPDATE/etc.) pysqlite implicitly opens a transaction and you have to commit it to allow other writers to proceed. Apparently, when committing the writer thread holds a global lock, not just a write lock, which prevents readers from making progress too. This lock is held even if your transaction only has SELECT (read) statements. So you have to explicitly commit those transactions.

So, I thought, hey, why not just make all my SELECT statements execute within “autocommit” connections (isolation_level=None)? Turns out that you have to close the connection explicitly after executing a SQL query even in autocommit mode to unlock the db for other threads:

con = getDBConnFromSomewhere(isolation_level=None)
c = con.cursor()
res = c.execute("SELECT ...")
# do something with result
c.close()
con.close() # this is important

I couldn’t find a simple explanation of SQLite’s isolation levels on Google or the official site. I just tried them out with pysqlite.

SQLite has three isolation levels: DEFERRED, IMMEDIATE and EXCLUSIVE. The default is DEFERRED. Since SQLite requires a global db lock to commit writes, you can only have one writer per database with many potential readers. The levels seem to work like this:

• DEFERRED. You can start a deferred transaction with “BEGIN DEFERRED” and it won’t block other writes. If you begin a write (UPDATE/INSERT/…) query it will attempt to acquire the global write lock. So it will either block other writers or block while waiting for other writers to complete their transactions. It will also wait for all the currently-running readers to finish before committing, but the readers aren’t blocked until you actually commit the transaction. I didn’t test this, but I assume that while the write is taking place, the readers are blocked.
• IMMEDIATE. “BEGIN IMMEDIATE” will attempt to acquire a write lock as soon as possible. If it’s successful, no other writers will be able to make progress until your transaction is complete, even if there are no modifications in it. Readers aren’t affected.
• EXCLUSIVE. As soon as “BEGIN EXCLUSIVE” is executed, if a write lock is acquired, no other queries can be executed outside the current transaction. They all block until you commit/rollback, including readers.

Does this sound right?