Making Multi-Location Stock Transfers Atomic with Room

A stock transfer looks like subtracting in one place and adding in another. The hard part is making sure the checks, movement history, and balance updates share one transaction boundary.

Say Main Warehouse holds 10 units of a product and Retail Store holds 3. Someone transfers 4. The first version of this operation that comes to mind is two lines:

source      -= 4
destination += 4

Now let the first write succeed and the second one fail. Main Warehouse says 6. Retail Store still says 3. Four units have entered another dimension.

The dangerous part is that the resulting data can still look plausible. That was the failure state I wanted the transfer operation to make impossible. This article walks through the Kotlin and Room code I used, and the test I use to check it.

The half-finished transfer

The failure needs no exotic cause. Any exception between the two writes will do. In my case a transfer also has to leave a history trail, so it is more than two numbers changing. It produces ledger entries and updates balances, and all of it has to agree.

The guarantee I wanted is easy to say: a transfer either happens completely or leaves nothing behind. No debit without a credit. No ledger row without the balance change that goes with it.

One transfer, two signed rows

First the data model. I record one transfer as two movement rows:

• TRANSFER_OUT with quantityDelta = -quantity, at the source location
• TRANSFER_IN with quantityDelta = +quantity, at the destination location

Both rows share one transferGroupId and one timestamp, and each keeps its own id. The movement history screen can collapse the pair into a single “Main Warehouse → Retail Store” entry, but underneath there are two rows. In normal application flow, movement rows are treated as insert-only history.

Here is the code that creates them, from InventoryRepositoryImpl.kt, lines 144 to 171, shown with the shared source indentation removed for readability:

val now = System.currentTimeMillis()
// One transfer is two signed ledger rows sharing transferGroupId -
// both committed inside this same transaction, never independently.
val transferGroupId = UUID.randomUUID().toString()
stockMovementDao.insert(
    StockMovementEntity(
        id = UUID.randomUUID().toString(),
        productId = productId,
        locationId = sourceLocationId,
        type = MovementType.TRANSFER_OUT,
        quantityDelta = -quantity,
        note = note,
        transferGroupId = transferGroupId,
        createdAt = now
    )
)
stockMovementDao.insert(
    StockMovementEntity(
        id = UUID.randomUUID().toString(),
        productId = productId,
        locationId = destinationLocationId,
        type = MovementType.TRANSFER_IN,
        quantityDelta = quantity,
        note = note,
        transferGroupId = transferGroupId,
        createdAt = now
    )
)

The transaction boundary

Here is where the transaction starts, and what happens before anything is written (lines 131 to 142):

return try {
    database.withTransaction {
        val sourceQuantity = inventoryBalanceDao.getBalance(productId, sourceLocationId)?.quantity ?: 0L
        if (quantity > sourceQuantity) return@withTransaction TransferResult.InsufficientStock

        val destinationQuantity = inventoryBalanceDao.getBalance(productId, destinationLocationId)?.quantity ?: 0L
        val newDestinationQuantity = try {
            Math.addExact(destinationQuantity, quantity)
        } catch (overflow: ArithmeticException) {
            return@withTransaction TransferResult.QuantityOverflow
        }
        val newSourceQuantity = sourceQuantity - quantity

That snippet stops before the closing braces on purpose. The try and the withTransaction block keep going. Right after it come the two movement inserts shown above, then two balance upserts, one for the source and one for the destination. Those four writes sit inside the same withTransaction. The block then builds a TransferResult.Transferred and returns it.

A catch for SQLiteConstraintException sits outside the block and maps that database constraint failure to TransferResult.Failed. An exception escaping the transaction causes Room to roll it back; only SQLiteConstraintException is converted to Failed here.

Left: two independent writes with a failure in between. Right: the same work inside one Room transaction, which commits all of it or rolls back all of it.

Why the stock check lives inside

Validation is split on purpose.

Outside the transaction, in the use case and again in the repository, are the checks that depend only on the arguments: the quantity has to be above zero, and the source has to differ from the destination. The repository repeats those checks so direct repository calls are still guarded.

Inside the transaction are the checks that depend on stored data: the source balance, the insufficient-stock check, the destination balance, and the overflow check. Quantities are stored as Long values scaled by 1000, and the overflow check uses Math.addExact.

The source check belongs inside because a balance read performed before entering the transaction creates a time-of-check/time-of-use window: the value can change before the write. Reading inside the transaction makes the read, the check and the write one unit. For that reason, the use case deliberately leaves the stored-balance check to the repository transaction.

Transfer and Issue reject a result that would go negative. Adjustment is the correction tool and can legitimately produce negative stock, so I only claim these two operations refuse to create it.

Testing the rollback path

The rollback test uses a destination location that doesn’t exist. The source side is valid, so validation passes and the TRANSFER_OUT row is inserted first. The TRANSFER_IN insert then references the missing location, and the locationId foreign key rejects that insert.

The test runs against a real in-memory Room database. Quantities are scaled Long values, so 5_000L means 5 units and 2_000L means 2 units.

Excerpted from the test; an outdated comment in the source is omitted here.

fun transfer_destinationLocationDoesNotExist_rollsBackEntireTransactionAtomically() = runBlocking {
    inventoryRepository.receive(productId, locationId, quantity = 5_000L, note = null)

    val result = inventoryRepository.transfer(productId, locationId, "missing-location", quantity = 2_000L, note = null)

    assertEquals(TransferResult.Failed, result)
    assertEquals(5_000L, database.inventoryBalanceDao().getBalance(productId, locationId)?.quantity)
    assertNull(database.inventoryBalanceDao().getBalance(productId, "missing-location"))
    assertNoTransferMovementsExist()
}

The source starts with 5 units, and the test tries to move 2 to a destination that doesn’t exist. The TRANSFER_OUT insert runs first. The TRANSFER_IN insert then fails and the repository returns Failed.

The assertions check four things. The result is Failed. The source balance is still 5 units. No balance row exists for the missing destination. And assertNoTransferMovementsExist() counts zero TRANSFER_OUT or TRANSFER_IN rows in the ledger.

The last one carries the claim. The TRANSFER_OUT row had already been inserted when the next insert failed, and it is not in the table afterwards. The already-executed TRANSFER_OUT row was rolled back when the next insert failed.

What that test proves, and what it doesn’t

The failure happens at the second insert, before the two balance upserts are reached. So this test directly proves rollback of the earlier movement insert. It does not directly demonstrate a balance upsert being rolled back.

The source balance reads 5 units because that upsert never ran. Because the balance upserts sit inside the same transaction, the transaction boundary covers them too. This particular test simply never reaches that path, so it does not demonstrate their rollback directly.

Two transfers, one source

There is one more test for this. The source starts with 5 units, and two 3-unit transfers are started together. One succeeds. The other returns InsufficientStock. The source ends at 2 units, with exactly one TRANSFER_OUT row.

The test uses runBlocking with async, so it does not demonstrate thread-level parallel execution. It shows that the second transfer is rejected instead of pushing the source below zero.

The boundary of the guarantee

Everything above is local. It shows Room and SQLite atomicity inside one application process, against one local database, on one device.

It says nothing about multi-device atomicity or distributed transactions. V1 has no cloud sync and no backend conflict resolution. And I have no crash or power-loss test: nothing here kills the process mid-write and checks the result.

The reusable lesson

When several reads and writes together define one business invariant, keep the read, the validation and the write inside one transaction boundary. Here the invariant is that stock moves from one place to another or doesn’t move at all. The checks, both ledger rows and both balances belong in the same unit, so none of them can be left half done.

I built this for Epicnesia Inventory Kit, an Android project written in Kotlin and Jetpack Compose.