Wrap multi-step changes in a transaction

How to do it

Begin, write, and commit in one function, and roll back on any error. Check the number of rows each conditional write changed, and abort when it is zero. Keep transactions short, and never wait for a network call or a person inside one. In SQLite, start write transactions with BEGIN IMMEDIATE, so a busy database waits at the start instead of failing halfway. In document stores, keep data that changes together in one document, or use multi-document transactions where the store offers them.

Example

// pg-promise: t.one throws when no row matches, which rolls the whole transaction back
await db.tx(async t => {
  await t.one('UPDATE stock SET quantity = quantity - $1 WHERE product_id = $2 AND quantity >= $1 RETURNING quantity', [qty, productId]);
  await t.one('INSERT INTO orders (customer_id, total_cents) VALUES ($1, $2) RETURNING id', [customerId, total]);
});

# psycopg: the block commits, or rolls back on any exception
with connection.transaction():
    stock = connection.execute(
        "UPDATE stock SET quantity = quantity - %s WHERE product_id = %s AND quantity >= %s", (qty, product_id, qty)
    )
    if stock.rowcount == 0:
        raise OutOfStock(product_id)
    connection.execute(
        "INSERT INTO orders (customer_id, total_cents) VALUES (%s, %s)", (customer_id, total)
    )

How to check

Make the last step fail on purpose in a test. No row from the earlier steps remains.