pysqlite db locking issues

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