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?