SQLite concurrent writes: map conflicts before measuring speed

Hypothetical, not executed: when SQLite concurrent writes overlap, a form handler and a worker both read capacity 1 for booking row 7. Can both confirm?

Short answer: Multiple writers give no blanket same-record safety guarantee. Stock SQLite serializes writes.[1] sqlite-multiwriter retains same-row conflicts and requires a whole-transaction retry after SQLITE_BUSY_SNAPSHOT.[2] Read-dependent decisions must be reconsidered in that retry; external notifications need separate handling.[4]

For the last seat, a fresh attempt may correctly answer "unavailable." That is the distinction a throughput chart cannot settle. Start with a write-case map and a complete retry boundary, then ask whether the accepted outcomes satisfy the booking rule.

TL;DR: Name the engine, map the operation's reads and writes, and identify what a rejected attempt must recompute. Check the final business state and notification handling before interpreting workload-specific throughput. This is the article's evaluation framework, not an industry standard. Its unit is one booking request, even when that request takes several transaction attempts.

This is a documentation-based explanation, not an executed database evaluation. The sqlite-multiwriter README showed version 0.5.2 when checked on October 10, 2026.[2]

SQLite concurrent writes start with the WAL baseline

What does stock SQLite WAL allow concurrently?

SQLite WAL concurrency has a specific baseline: readers can overlap one writer, but stock SQLite does not allow simultaneous write transactions. SQLite's isolation documentation says writers take turns; WAL, short for write-ahead logging, lets a reader keep seeing its snapshot while a writer commits changes.[1]

Consider three connections: a form handler, an import worker and an agent. All three can submit writes. That does not mean three stock SQLite write transactions progress at once. Counting connections answers who wants to write, not how their work overlaps. This is a conceptual example, not a timing test.

sqlite-multiwriter describes a different path. It is a virtual file system, or VFS, extension. Its README says transactions run in private WALs, then their affected pages are checked against intervening commits before accepted commits are published.[2] Keep that contract separate from stock SQLite WAL, SQLite's BEGIN CONCURRENT branch or a Turso engine claim. "SQLite multiple writers" is too loose a label for a retry policy.

The distinction matters when operating a working site with business records rather than only content pages. A booking page can display availability; the form handler still needs an accepted reservation. Once you know which engine accepts that write, map the overlap that could make it refuse the attempt.

Different rows can still share a conflict

For sqlite-multiwriter, start with the pages affected by the transaction, including its reads. Different row ids, such as 101 and 202, do not establish independence: shared pages, indexes and read dependencies matter. The README describes page validation and retains real same-row conflicts.[2]

The following map is a documentation-based classification. The cases and page placement are hypothetical, not executed or observed storage layouts.

Write case: Different rows, no conflicting affected read/write pages

What overlaps: Independent work can progress in parallel under the documented model.

Retry or rebase boundary: Acceptance still depends on validation, not the row labels.

Write case: Different rows on a shared page

What overlaps: Page-level rejection can be a false conflict at the business-row level.

Retry or rebase boundary: Optional rebase may replay eligible point-write transactions; otherwise the rejected transaction is retried.

Write case: Both writers change row 7

What overlaps: A real same-row conflict remains.

Retry or rebase boundary: Rebase does not erase it; SQLITE_BUSY_SNAPSHOT requires a whole-transaction retry.

Can different rows still collide, and when can rebase help?

Yes. Different rows can share a page. The documented optional rebase applies certain eligible point writes to the latest state instead of rejecting them for shared-page overlap. It does not merge every transaction or remove true same-row conflicts.[2]

The README uses this point-update shape:

UPDATE account SET balance = balance + 10 WHERE id = 7;

This is documentation-only SQL, not a complete balance design or proof of rebase eligibility. A transaction that reads rows with SELECT before UPDATE is excluded from the documented rebase path. Triggers, DDL and certain foreign-key cases impose other restrictions.[2]

Ineligibility means the normal rejection-and-retry path still applies; it does not establish that the transaction is unsafe. Do not remove a correctness check just to qualify for rebase. Our last-seat operation needs a capacity decision. That read changes what its next attempt must do.

A stale snapshot means making the booking decision again

When two writers change the same record, what gets retried?

Under sqlite-multiwriter's documented contract, a transaction rejected with SQLITE_BUSY_SNAPSHOT must be retried in full.[2] Applied to a booking operation, that means recreating the database reads and the decisions that determine its writes. Reusing the old approval would defeat the purpose of a fresh attempt.

Set the business rule first: capacity must not become negative, and the last seat must not have two accepted reservations.

Hypothetical sequence, not an engine test:

1. A's form handler and B's worker each read capacity 1 in their transaction snapshots.
2. A reserves the seat, reduces capacity to 0 and commits its reservation.
3. B's stale conflicting attempt is rejected. B ends that attempt and starts a new transaction.
4. B rereads capacity 0. Its fresh business decision is "unavailable," so it does not accept another reservation.

This illustrates the application contract we want to check. It does not prove that the engine always enforces it, or that retrying must produce a successful reservation.

The stale decision can hide outside the SQL. Suppose B saves seat_available=true before entering its retry loop, then uses that boolean to authorize each new update. The SQL may run in a fresh transaction while the permission to reserve still comes from the old snapshot. Put the capacity read and dependent decision inside the operation that runs again.

The requested quantity, one seat, can remain a request input. The answer to "is it available?" belongs to each attempt. This is the practical consequence of the retry contract, not an additional isolation guarantee.

Also distinguish two refusals. B's first transaction attempt was rejected because of a conflict. Its next attempt can decline the same booking request because no seat remains. Another retry is not a way to turn that business answer into "yes." Bound conflict retries and surface exhaustion; do not turn every error into an endless loop. There is no universal attempt count supplied by this example.

A SQLite transaction retry also depends on the exact error and engine. SQLite's Transaction documentation describes a specific stock case: another reader causes COMMIT to return SQLITE_BUSY, leaving the transaction active so that COMMIT can be retried later.[6] That is different from the VFS's stale-snapshot restart. A "SQLite database is locked" message alone is too vague to choose between those policies.

Row 7 now has a fresh decision boundary. A decision depending on another row needs a wider map.

Different write rows do not prove a business rule

Hypothetical: two approvers are available. Each transaction sees the other approver available, then marks only its own row unavailable. If both decisions were accepted, the rule "at least one approver remains available" would fail despite the different write rows.

PostgreSQL's isolation documentation defines serializable execution as having the same effect as running transactions one at a time in some order. It also explains why its Repeatable Read level can still permit serialization anomalies.[5] In our two-approver illustration, either transaction running second against the first's completed change should reconsider its decision. The problematic result comes from overlapping read dependencies, not two writes to one row.

This is a generic write-skew explanation, not a claim about default sqlite-multiwriter behavior. The VFS README says its default read-page validation rejects the usual write skew when an intervening commit rewrites a page the transaction read.[2] Give that check its due.

Keep the qualification too. The README calls its isolation tested, not proved serializable. It says page-1 reads are not validated, and mw_readcheck=0 disables read validation and makes write skew possible.[2] That setting is a caution here, not a recommendation. PostgreSQL's definition does not transfer PostgreSQL's guarantees to this VFS.

The useful application check is narrower: record what each approver read to justify becoming unavailable, then inspect the final rule. A write list containing two different ids omits the reason either update was allowed. Even a correctly accepted database state leaves another boundary when the operation sends a confirmation elsewhere.

A confirmation email lives outside the rollback

Does retrying the SQL also make notifications and cross-row rules safe?

No. Retrying a database transaction alone does not establish its business invariant or coordinate an external notification. AWS describes a database write paired with an event notification as a dual-write consistency problem, and advises against notifying on a rolled-back transaction.[4]

Apply that guidance to our hypothetical booking. B sends "your seat is confirmed" before its stale commit is rejected. Rolling back B's SQL does not unsend the email. Recomputing capacity afterward cannot change the message the recipient already received.

Moving the send after commit closes that particular sequence, but leaves another: A commits its reservation and the process fails before sending. The booking exists; the notification was never issued. AWS describes both directions of this failure.[4]

The outbox pattern gives the database part a shared boundary. Write the reservation and its event record in the same successful transaction, in the same database. A separate dispatcher processes committed event records. This is AWS's architectural pattern applied by inference to the booking example, not a verified configuration for this VFS or a hosted provider.[4]

Delivery still needs duplicate handling. Give A's accepted reservation a stable event identity, such as reservation-A-confirmed, and have the consumer track processed identities under an explicit idempotency policy. Row 7 identifies the shared capacity record; it is not a universal deduplication key for every reservation. AWS warns that event processing can produce duplicate messages.[4]

An outbox alone does not make an external email service deduplicate correctly or establish exactly-once delivery. The accepted reservation and its eventual notification remain separate checks. Those obligations belong beside the speed result, even when parallel writers are genuinely useful.

Parallel writers can be useful within a narrow contract

Independent append-heavy work is a legitimate evaluation case. Marco Bambini's announcement makes the case for parallel writers, and the README reports workload-specific performance results.[3][2] Its reported test with 16 threads updating the same 4 rows still contains retries. Those are maker-reported results, not our measurements.

Do not conclude that concurrency helps only independent writes: the maker also reports gains under contention. The narrower conclusion is that useful gains can coexist with true conflicts and retry obligations.[2] A last-seat workload needs its own accepted-outcome evidence. An append benchmark cannot supply it.

Can this be added to any hosted SQLite or libSQL service?

No compatibility follows from the shared SQLite label. The README describes a local-filesystem VFS path, WAL-only operation, no network filesystem support and no atomic transactions across attached database files.[2] Hosted-provider support is unverified here.

The working-site category context and site-scoped plugin context are background only. Application plugins are not evidence of database VFS access or adoption.

The README lists randomized checks and a Docker/ext4 power-loss test, while other filesystems and actual hardware power loss remain unverified by the maker.[2] These are its stated verification limits, not tests run for this article.

Keep stock SQLite's serialized writer if it meets the workload. Shorten transaction work only without weakening invariants; evaluate another supported engine against its own contract. Formal proof need not precede every experiment. Ask for evidence proportional to the business risk, in one report that keeps speed and accepted outcomes together.

Ask for one report with throughput and invariants

Return to booking row 7. The report must explain what happens to a request after capacity changes, not merely how many transactions commit.

This five-row worksheet is a proposed evidence request, synthesized from the source contracts. It is not a completed test report. Fill it with evidence for a pinned engine version and a representative concurrent workload.[1][2][4][5]

Evidence row: Transaction contract

What to supply: Exact SQL, relevant reads and writes, engine/version, isolation and rebase/readcheck settings.

Last-seat check: Capacity and the reservation decision are inside the retried operation.

Evidence row: Operation and attempt accounting

What to supply: Logical requests separately from attempts; commits, conflict rejections, business rejections and abandoned or exhausted requests, with defined denominators.

Last-seat check: Retries are not new booking requests; two requests for one seat are not two accepted bookings.

Evidence row: Final-state invariant

What to supply: The stated rule checked after the representative workload.

Last-seat check: Capacity is nonnegative; the last seat does not have two accepted reservations.

Evidence row: Retry and event boundary

What to supply: Fresh decisions per attempt, bounded retries, visible exhaustion and, where needed, outbox identity and duplicate handling.

Last-seat check: No confirmation for a rejected reservation; repeated event identities follow an explicit duplicate-handling policy.

Evidence row: Timing and durability

What to supply: Throughput and tail latency including retries, durability settings, filesystem and relevant crash-test coverage.

Last-seat check: Speed appears beside the invariant and each request's final outcome.

B's second attempt is still part of B's original request. A committed read-only check reporting "unavailable" would not be an accepted reservation. This is why transaction attempts, committed transactions and accepted business operations can be different counts. Define them rather than quietly dropping rejected or exhausted requests from the report.

The order is the recommendation: classify overlap, name what the engine rejects, specify what the application recomputes, check final state and effects, then compare speed. A valid final state in one run is evidence for that version and workload, not proof for every future schedule. Provider compatibility, universal serializability and production readiness do not follow from a transactions-per-second column.

With SQLite concurrent writes, row 7 makes the point concrete: a fresh attempt may correctly refuse a booking. That outcome protects the last-seat rule even though it adds no accepted reservation. A higher commit count cannot substitute for that check.

Use the five-row worksheet on one write path and identify the decision that must run again after a conflict.

References

1. SQLite, "Isolation In SQLite" (inspected 2026-10-10). Official documentation of serialized stock writes, WAL snapshot isolation and stale-snapshot restart behavior. Foundational reference, not an extension or provider test. https://sqlite.org/isolation.html
2. sqliteai, "sqlite-multiwriter" README (inspected 2026-10-10; version 0.5.2 displayed). Documented page validation, whole-transaction retry, limited optional rebase, isolation and filesystem limits. Implementation behavior, benchmarks and verification coverage are maker statements, not independently reproduced here. https://github.com/sqliteai/sqlite-multiwriter
3. Marco Bambini, "We solved SQLite's single-writer limitation" (inspected 2026-10-10). Maker announcement and workload-specific performance motivation. Shares a maker with reference 2; not independent confirmation. https://marcobambini.substack.com/p/we-solved-sqlites-single-writer-limitation
4. AWS Prescriptive Guidance, "Transactional outbox pattern" (inspected 2026-10-10). Database/event dual-write failure cases, committed outbox processing and idempotent consumption. Applied to the hypothetical booking by inference, not as a verified SQLite integration or exactly-once guarantee. https://docs.aws.amazon.com/prescriptive-guidance/latest/cloud-design-patterns/transactional-outbox.html
5. PostgreSQL 18 documentation, "Transaction Isolation" (inspected 2026-10-10). Serializable execution definition and PostgreSQL-specific isolation/retry behavior. Supports the generic dependency explanation, not guarantees assigned to stock SQLite or sqlite-multiwriter. https://www.postgresql.org/docs/current/transaction-iso.html
6. SQLite, "Transaction" (inspected 2026-10-10). Official documented case in which a stock COMMIT returning SQLITE_BUSY leaves the transaction active and can be retried. Not a universal retry policy for every error. https://sqlite.org/lang_transaction.html