The transaction that was atomic on Android and not on the web

TL;DR#

• A five-statement "atomic" write across four tables left the database half-written on the web target when the fourth statement failed. Same SQLDelight code, same transaction { } block, atomic on Android, iOS and desktop.
• transaction { } is SQLDelight's API. What it means is the driver's: the web worker driver executed our nested transaction as a no-op inner block and committed the outer one on the way out of the failing call.
• The fix was to stop nesting, make the failure mode explicit per driver, and add a fault-injection test that runs on all four — which is the only thing that would have caught it.
• What reading on buys: the nesting and savepoint behaviour measured per driver, why no error is raised, and the 61 half-written rows we had to repair.

The case this post is about#

An order submission that writes four tables and must not half-apply:

// core/database/src/commonMain/kotlin/OrderWriter.kt
class OrderWriter(private val db: AppDatabase) {

    suspend fun submit(order: Order) = db.transaction {
        db.orderQueries.insert(order.id, order.total, status = "submitted")
        db.orderLineQueries.deleteFor(order.id)
        order.lines.forEach { db.orderLineQueries.insert(order.id, it.sku, it.qty) }
        // Nested — because reserveStock is also called on its own from elsewhere, and it
        // has its own transaction. "Nested transactions are fine", which is true on three drivers.
        reserveStock(order.lines)
        db.outboxQueries.enqueue(order.id, payload = Json.encodeToString(order))
    }

    suspend fun reserveStock(lines: List<OrderLine>) = db.transaction {
        lines.forEach { db.stockQueries.decrement(it.sku, it.qty) }   // CHECK (qty >= 0)
    }
}

The decrement has a constraint, which is the point — an oversell must fail:

-- stock.sq
CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL CHECK (qty >= 0));
decrement: UPDATE stock SET qty = qty - :by WHERE sku = :sku;

The symptom#

Orders in the database with no stock reserved and no outbox row:

# support ticket: "I was charged and the order never shipped"
sqlite> SELECT o.id, (SELECT COUNT(*) FROM order_line l WHERE l.order_id = o.id) lines,
                     (SELECT COUNT(*) FROM outbox x WHERE x.order_id = o.id) outbox
        FROM "order" o WHERE o.status = 'submitted' AND outbox = 0;

  id                 lines   outbox
  ord_8f2a1               3        0     ← order + lines written, stock NOT decremented, no outbox
  ord_91b4c               2        0
  …
  61 rows

Sixty-one orders, all from web sessions, all with the same shape: the first three statements applied, the fourth failed, and the fifth never ran. And no error anywhere — the submit call threw (the constraint did fire), the UI showed "could not submit", and the user retried. The write that should have rolled back stayed.

# the 61, by platform
  web           61
  android        0
  ios            0
  desktop        0

Zero on three platforms running identical Kotlin.

Why the obvious fix failed#

The first fix was to wrap the call site in another transaction, on the theory that the outer one was missing:

db.transaction { orderWriter.submit(order) }

Which adds a third level of nesting to a problem caused by nesting, and changed nothing: still atomic on three drivers, still partial on web.

The second fix was transactionWithResult with an explicit rollback:

db.transactionWithResult {
    runCatching { … }.onFailure { rollback(Unit) }
}

rollback in SQLDelight unwinds to the start of the transaction it is called in. Called inside the nested one, it unwound the nested block — on the drivers where the nested block was a real savepoint — and left the outer one committing. It is the right primitive aimed one level too deep.

The third attempt was a try/catch that compensated by hand:

catch (e: Exception) {
    db.orderQueries.delete(order.id)       // undo statement 1
    db.orderLineQueries.deleteFor(order.id) // undo statement 2
}

Which works until the compensation itself fails, and encodes the rollback order in a place nothing checks against the forward order. We shipped this for a week as a stop-gap and it left 4 orders in a third state — deleted order row, surviving order lines — because the compensating delete ran before the line delete and a foreign key took the first one out.

The actual mechanism#

SQLDelight's transaction { } compiles to driver calls. The semantics are the driver's, and the drivers disagree on two things that matter.

Nesting. SQLite supports exactly one real transaction per connection; nesting is emulated with SAVEPOINT. Whether a driver issues one is a driver decision:

  driver                        nested transaction        on inner failure
  AndroidSqliteDriver           SAVEPOINT                 unwinds to the savepoint; outer intact
  NativeSqliteDriver (ios)      SAVEPOINT                 unwinds to the savepoint; outer intact
  JdbcSqliteDriver (desktop)    SAVEPOINT                 unwinds to the savepoint; outer intact
  WebWorkerDriver (wasm/js)     NO-OP inner block         exception propagates; OUTER COMMITS

The last row is the bug. The web worker driver treats an inner transaction as a pass-through — it does not open a savepoint — and when the exception leaves the inner block, the outer block's exit path commits what has already been written. Three statements in, one statement failed, and the transaction ended as a commit.

And nothing reports this. There is no "nested transactions unsupported" warning, because nesting is supported in the sense that the code runs; what differs is the isolation it provides. The API is identical, the compile is identical, and the only observable difference is in the data after a failure — which is why 61 rows accumulated before anyone saw it.

Plus a second difference we found while testing, which bites in the other direction:

  driver                        concurrent writers
  AndroidSqliteDriver           serialised by the driver's write lock
  JdbcSqliteDriver              SQLITE_BUSY after a 5s default timeout
  WebWorkerDriver               single worker thread — serialised by construction
  NativeSqliteDriver            serialised by the driver's write lock

So desktop can throw SQLITE_BUSY where no other target can, under concurrency the others simply queue.

The fix#

One transaction, no nesting, with the shared-helper case solved by splitting the statements from the transaction rather than by nesting transactions.

// core/database/src/commonMain/kotlin/OrderWriter.kt
class OrderWriter(private val db: AppDatabase) {

    /**
     * ONE transaction, no nesting. The WebWorkerDriver treats an inner transaction { } as a
     * pass-through rather than a SAVEPOINT, so an exception from inside it leaves the outer
     * block committing what was already written — measured: 61 half-written orders, web only,
     * with no error distinguishable from a correct rollback.
     *
     * The shared logic that used to carry its own transaction is now a plain function over the
     * queries. Reuse is achieved by sharing the STATEMENTS, not by nesting the transaction.
     */
    suspend fun submit(order: Order) = db.transaction {
        db.orderQueries.insert(order.id, order.total, status = "submitted")
        db.orderLineQueries.deleteFor(order.id)
        order.lines.forEach { db.orderLineQueries.insert(order.id, it.sku, it.qty) }
        decrementStock(order.lines)              // ← no transaction of its own
        db.outboxQueries.enqueue(order.id, payload = Json.encodeToString(order))
    }

    /** Statements only. Callable inside any transaction; opens none. */
    private fun decrementStock(lines: List<OrderLine>) =
        lines.forEach { db.stockQueries.decrement(it.sku, it.qty) }

    /** The standalone caller supplies the transaction, which is where it belongs. */
    suspend fun reserveStock(lines: List<OrderLine>) = db.transaction { decrementStock(lines) }
}

And the test that would have caught it — fault injection, run on every target, because the defect is invisible without a failing statement:

// commonTest/kotlin/OrderWriterAtomicityTest.kt
class OrderWriterAtomicityTest {

    /**
     * The only test shape that finds this class of bug: the happy path is atomic-looking on
     * every driver, and the difference appears solely in the data AFTER a mid-transaction
     * failure. Our 184 existing database tests all committed successfully.
     */
    @Test
    fun aFailureMidWriteLeavesNothingBehind() = runTest {
        val db = testDatabase()
        db.stockQueries.upsert(sku = "SKU-1", qty = 5)

        val order = Order(id = "ord_1", total = 100, lines = listOf(
            OrderLine("SKU-1", qty = 2),
            OrderLine("SKU-1", qty = 99),        // ← violates CHECK (qty >= 0). The injected fault.
        ))

        assertFailsWith<Throwable> { OrderWriter(db).submit(order) }

        // Every table, not just the one the failing statement touched: the bug wrote rows in
        // tables the failure never reached.
        assertEquals(0, db.orderQueries.countById("ord_1").executeAsOne(),
            "the order row survived a failed transaction — this driver did not roll back")
        assertEquals(0, db.orderLineQueries.countFor("ord_1").executeAsOne())
        assertEquals(0, db.outboxQueries.countFor("ord_1").executeAsOne())
        assertEquals(5, db.stockQueries.qtyOf("SKU-1").executeAsOne(), "stock moved")
    }

    @Test
    fun theSharedHelperOpensNoTransactionOfItsOwn() = runTest {
        // A structural assertion, because the fix is "do not nest" and nothing else enforces it.
        val src = readSource("core/database/src/commonMain/kotlin/OrderWriter.kt")
        assertEquals(2, Regex("db\\.transaction").findAll(src).count(),
            "a third transaction { } appeared — nesting is the defect this test exists for")
    }
}

And the output it produced:

$ ./gradlew :core:database:allTests --tests '*OrderWriterAtomicity*'
aFailureMidWriteLeavesNothingBehind     PASSED × 4 targets
theSharedHelperOpensNoTransactionOfItsOwn PASSED × 4 targets

$ git stash && ./gradlew :core:database:allTests --tests '*aFailureMidWrite*'
aFailureMidWriteLeavesNothingBehind     PASSED (android)
aFailureMidWriteLeavesNothingBehind     PASSED (ios)
aFailureMidWriteLeavesNothingBehind     PASSED (desktop)
aFailureMidWriteLeavesNothingBehind     FAILED (wasmJs)
  the order row survived a failed transaction — this driver did not roll back
  expected: 0  actual: 1

One target red, three green, on the same source — which is the whole post in four lines of test output.

And the repair, which had to happen before the fix shipped:

$ deno task repair-partial-orders --dry-run
  61 orders with status=submitted and no outbox row
   57 → safe to delete (no payment captured)
    4 → payment captured; flagged for manual reconciliation
$ deno task repair-partial-orders --apply
  deleted 57 orders + 134 order_line rows
  flagged 4 for reconciliation

How we proved it#

That the web driver does not open a savepoint. The measurement that identified the mechanism, taken by logging the SQL the driver actually issues:

  android / ios / desktop, entering a nested transaction
    SAVEPOINT "sqldelight_1"
  web worker, same code
    (nothing)

That the outer transaction commits on the way out. Confirmed by reading the data rather than inferring from the absence of a savepoint:

  web, after the injected failure
    order rows        1      ← statement 1, committed
    order_line rows   3      ← statement 3, committed
    stock changed     no     ← statement 4 failed
    outbox rows       0      ← statement 5 never ran

Statements before the failure are committed, statements after it never ran. That is a commit, not a rollback.

That no error distinguishes it. The inventory, because the absence of a signal is why it reached 61 rows:

  signal                                     present on web?
  exception from submit()                     yes — identical to the correct case
  driver warning / log                        no
  SQLDelight compile warning                  no
  anything naming "nested" or "savepoint"     no

The exception is the same. The difference is only in the rows afterwards.

That rollback was aimed one level too deep. Tried, since it is the API that looks built for this:

  rollback called inside the nested block, android   → unwinds the savepoint; outer commits
  rollback called inside the nested block, web       → unwinds nothing; outer commits
  rollback called in the outer block                 → correct on all four

That the compensating-delete stop-gap created a third bad state. Measured during the week we shipped it, and the reason it is not the fix:

  orders left inconsistent by the compensation itself    4
  shape                                                  order row deleted, order_line rows survived
  cause                                                  compensation order ≠ reverse of write order

That 184 existing tests could not have caught it. Counted, because "we have database tests" was the reason nobody looked:

$ grep -rln 'transaction' --include='*Test.kt' core/database | wc -l
  12
$ grep -rln 'assertFailsWith' --include='*Test.kt' core/database | wc -l
   0

Twelve tests exercise transactions, none of them injects a failure. A transaction test that always commits tests the commit path on every driver and the rollback path on none.

What I cannot tell you is how the web driver behaves for a nested transaction that succeeds and an outer one that then fails. Our fix removed nesting entirely, so we stopped measuring; the inner writes should roll back with the outer transaction on every driver, and "should" is doing work there that I have not verified.

And an honest gap. The structural test counts db.transaction occurrences in one file, which is a blunt instrument — it catches the specific regression and would pass a nested transaction added in a different file. A lint rule over the whole module is the right shape and is not written.

What this generalizes to#

An API that compiles identically everywhere can still mean different things per backend, and transactions are the sharpest case because the difference is only observable after a failure — so a test suite that exercises success paths proves nothing about the guarantee you are relying on. When shared code depends on a semantic rather than a signature, the test has to inject the fault, and it has to run on every backend: the one that behaves differently will not tell you, and the users of exactly one platform will.

---

Named differently elsewhere#

Some teams call this "savepoint emulation"; the SQLite documentation calls nesting "subtransactions". The behaviour is the same thing under either name.

Related#

• The migration with no rollback — the same word, one layer up, where the rollback is a schema rather than a write.
• A SQLDelight driver per platform — the four drivers this post compares, and what else differs between them.

Keep reading#

One post a week on shipping mobile software. No digests, no course pitch. Subscribe.