What happens when two attached SQLite databases are written in different orders during concurrent transactions?

Different lock acquisition order can create a deadlock when concurrent transactions touch attached databases. In practice, a write transaction may lock one database first and then block on the second while another transaction does the reverse. Using a consistent write pattern such as BEGIN IMMEDIATE helps avoid that failure mode.

Why lock order matters when SQLite writes touch attached databases

When a transaction writes to more than one attached SQLite database, the order in which locks are acquired becomes part of the correctness story. If two concurrent writers touch the same attached databases in opposite order, each can hold one lock while waiting for the other, which turns an ordinary write conflict into a deadlock-style stall.

That is why the issue is not just “two writes happened at once”, but “the same resources were claimed in different sequences”. In SQLite, the write path is narrow enough that lock ordering can determine whether the second transaction waits cleanly or gets stuck behind a circular dependency.

Using a consistent write pattern, such as BEGIN IMMEDIATE, reduces that risk by forcing lock acquisition to happen up front rather than halfway through a multi-database write. The practical value is predictability: either a transaction gets the write reservation early, or it fails fast instead of competing late in the transaction.

What concurrent attached-database writes are actually contending over

Attached databases behave like separate files with their own locking state, even though the application may treat them as one logical unit. A transaction that updates both databases has to coordinate access to each file, so the concurrency problem is not abstract, it is about file-level write ordering and reservation order.

That distinction matters because the deadlock risk appears only when both transactions need overlapping write access but do not request the databases in the same sequence. If every writer follows the same path, the engine can serialize access more safely; if not, each transaction can end up waiting on the resource the other one already owns.

The deeper lesson is that “attached” does not mean “merged” from a concurrency perspective. You still need to think about each database file as a separate lock domain, especially when a single logical operation spans both.

How to avoid the failure mode in practice

The safest pattern is to make write acquisition deterministic. BEGIN IMMEDIATE is useful because it requests the write lock before the application has done meaningful work, which reduces the chance that a transaction will discover contention after it has already modified state in memory or across multiple attached files.

Where attached databases are involved, the more important rule is consistency than cleverness. If every code path writes the databases in the same order and starts transactions in the same way, concurrency becomes much easier to reason about and much less likely to fail under load.

It also helps to keep cross-database write transactions small. The longer a transaction holds one lock while waiting to acquire another, the more likely it is to collide with a second writer and expose the ordering problem.

Risk and Threat Considerations

Concurrent write order problems can look like a simple operational hiccup, but they can also become an availability issue under load. The risk increases when application logic spans multiple attached databases and writers are not forced into a consistent reservation pattern, because lock contention can cascade into stalled requests or repeated retry failures.

Failure mechanism: One transaction acquires a write lock on database A first while another acquires a write lock on database B first; each then blocks waiting for the other lock, creating a circular wait that the application experiences as a deadlock or write failure.

Impact: The result can be delayed commits, failed transactions, request timeouts, and retry storms, especially when the database pair sits on a hot path for concurrent updates.

Standards & Framework Alignment

This section maps relevant standards and security frameworks to the operational risks and controls described in this guidance.

CIS Controls v8 and NIST SP 800-53 Rev 5 set the governance and control requirements practitioners need to meet.

Framework	Control / Reference	Relevance
CIS Controls v8	CIS-4 — Secure Configuration of Enterprise Assets and Software	Consistent transaction and locking patterns reduce configuration-driven concurrency failures.
Recommendation — Standardize SQLite write patterns and transaction startup across all application paths.
NIST SP 800-53 Rev 5	CM-2 — Baseline Configuration	Deterministic lock order is part of a stable, controlled application runtime baseline.
	SC-30 — Concealment and Misdirection	Not selected.
Recommendation — Define and enforce one approved write sequence for attached-database transactions.

Practitioner Guidance

What to prioritize: Standardize the write sequence for every code path that touches attached databases, then verify that transaction startup is consistent across workers, jobs, and request handlers. If one path uses deferred locking while another claims the write lock early, the concurrency behavior will be harder to predict.

What to verify: Confirm that the application can tolerate immediate lock acquisition failure and that retries do not simply recreate the same ordering conflict. In practice, the useful test is whether the system degrades cleanly under contention instead of oscillating between blocked writers and repeated aborts.

Practitioner takeaway: With attached SQLite databases, the main control is not just “avoid contention”, it is “remove ambiguity from lock order”, because deterministic transaction behavior is what keeps multi-file writes from turning into circular waits.