Why a Single Read-Only Query Can Lock Out Your Active Writers
A slow reporting query can trigger a chain reaction in your database, queuing up writes and blocking subsequent transactions.

When database performance suddenly drops, developers often look for heavy write operations or complex table joins as the primary culprits. However, a single, seemingly harmless read-only query can bring an entire transactional system to a halt. This happens because of how database engines manage lock queues to ensure fairness and prevent writers from waiting indefinitely.
Understanding Database Lock Types
To maintain data integrity, database engines like SQLite use different lock states. A SHARED lock allows multiple processes to read the database at the same time, but it prevents any process from writing to the database file while it remains active. When a process needs to write, it must acquire an EXCLUSIVE lock, which cannot coexist with any other locks of any kind. Before obtaining an EXCLUSIVE lock, a process may hold a PENDING lock, which indicates it wants to write as soon as possible and is waiting for existing SHARED locks to clear.
How Writer Starvation is Prevented
If readers could continually acquire new SHARED locks while a write operation was waiting, the write operation might never execute. To prevent this scenario, known as writer starvation, the database engine uses the PENDING lock. Once a PENDING lock is active, no new SHARED locks are permitted against the database, though existing SHARED locks are allowed to finish their work. This mechanism ensures that writers eventually get their turn, but it introduces a strict queue that can block subsequent operations.
The Queue Bottleneck in Action
Consider a hypothetical e-commerce database handling 1,000 quick checkout writes per second. A manager initiates a slow, 10-minute read-only sales report query, which acquires a shared lock. While this report is running, an automated system maintenance task requests an exclusive schema lock to update a table. Because of the active shared lock, the maintenance task cannot proceed and must wait. To prevent this write-heavy maintenance task from waiting forever, the database engine sets a pending lock, blocking all new incoming checkout writes behind the queued maintenance task. As a result, the entire storefront halts because the quick checkout transactions are stuck in the queue behind the pending write.
Managing Lock Contention
- Keep read-only reporting queries short or run them on a dedicated read replica.
- Schedule heavy maintenance tasks during off-peak hours to avoid blocking active writers.
- Monitor lock wait times to identify queries that hold shared locks for too long.
Your reaction
Loading reactions…


Comments 0
Review comments