When comitting, SQLite upgrades RESERVED lock to a PENDING lock when the transaction looks to commit. A transaction may upgrade it’s SHARED lock to a RESERVED lock to write after a read, but not vice versa. Only one transaction can hold this lock at a time, others wanting to write fail with SQLITE_BUSY.
Transaction wanting to write acquires a RESERVED lock. Multiple transactions can hold this lock simultaneously. Transaction wanting to read acquires a SHARED lock. Writers holding a PENDING lock block readers and writers from other concurrent transactions. Writers holding a RESERVED lock block writers from other concurrent transactions. The locks permit a single writer and simultaneous readers from concurrent transactions to co-exist. The locks acquired are coarse-grained and apply to the entire database. In this mode, locks are used to implement isolation. Both modes implement isolation differently. We’ll look at the two 5 journaling modes SQLite supports, Rollback journal and WAL. SQLite uses a journal or log for implementing atomic commit & rollback, which ensures that if a transaction is interrupted due to crash or power failure, the database can get back to its previous state. Let’s look at how isolation is implemented for concurrent transactions using DEFERRED behaviour. In case you’re interested, here’s a nice talk, which covers issues that can come up with concurrent transactions in the absence of isolation. SQLite makes sure that transactions remain completely isolated & prevents race conditions even in this mode. DEFERRED behaviourĭEFERRED behaviour, on the other hand, allows multiple transactions to run concurrently, which allows queries from multiple transactions to get interleaved. NOTE: IMMEDIATE behaviour uses a different kind of lock which allows concurrent readers, but blocks other concurrent writers (discussed further in Rollback journal section).īut running only one transaction at a time, might not be performant. The lock is retained till a transaction either commits or aborts. Once a transaction acquires a lock, other concurrent transactions trying to acquire a lock, fail with a SQLITE_BUSY error. Starting transactions with EXCLUSIVE behaviour enforces serial execution by acquiring a exclusive lock at the beginning of a transaction. One way to achieve isolation is to enforce serial execution ie. Some SQL read and write statements */ COMMIT Actual serial execution using transaction behavioursīEGIN DEFERRED /* or IMMEDIATE or EXCLUSIVE */ /*. We try and look at some of these setups, to understand scenarios under which the error may show up.
Because of this, the scenarios causing SQLITE_BUSY errors may change depending on the setup. SQLite may be setup in different ways, and each setup uses a different algorithm to make sure that concurrent transactions remain isolated. It’s left to the user to decide how to retry failed queries (discussed further towards the end). To prevent violation of this isolation guarantee, and to preserve the integrity of the database, SQLite rejects some queries with SQLITE_BUSY errors. This means that even though transactions may be processed concurrently, from a user’s perspective SQLite behaves as if it has processed transactions in a serial order with no concurrency. SQLite guarantees that concurrent transactions are completely isolated 4. To prevent this, databases usually provide some guarantees to protect against race conditions. Concurrent writes may cause race conditions though, leading to inconsistent data. SQLite allows concurrent 2 transactions by letting clients open multiple connections 3 to a database. NOTE: If you’re just looking for a way to handle SQLITE_BUSY errors, skip to this section. I’m writing this post hoping that my high-level understanding, and refs/pointers to SQLite docs, might help others debugging similar issues. While debugging the problem, it helped to understand SQLITE_BUSY better, by going through different parts of the official documentation and drawing parallels to some well-understood concepts 1. Some of my queries were getting stuck in a retry loop and eventually failing with SQLITE_BUSY errors, on hitting max retry limits. I recently stumbled upon a strange occurrence in an ORM’s query retry implementation for SQLite. Activesphere Portfolio People Blog Understanding SQLITE_BUSY
0 kommentar(er)
