Move Spare Parts Without Losing Half the Transfer

Botie subtracts three replacement seals from the north store. The next statement fails before the south store is updated. The database now describes missing stock even though the intended transfer was one operation.

A transaction gives these related changes a boundary. We can publish them together or abandon the database changes together. We will also use a savepoint to undo an optional part of a larger operation.

Stage both changes, check the resulting balances, then select all staged rows or all original rows.

[
  {
    "id": "north",
    "units": 7
  },
  {
    "id": "south",
    "units": 5
  }
]

The transfer consists of validating the request, recording it, subtracting source stock and adding destination stock. Committing only the subtraction would report a result the business never intended.

Keep the boundary as small as possible while preserving that invariant. Long transactions can hold resources and delay other writers. Avoid waiting for a person or an unrelated network call while a transaction is open.

A transaction boundary follows the operation’s meaning, not an arbitrary number of statements.

Atomicity concerns all-or-nothing database effects. Consistency means the transaction should preserve declared invariants; the application must still express those rules correctly. Isolation governs interaction with other transactions. Durability concerns retaining committed changes under the database’s documented configuration and failure assumptions.

A transaction containing an incorrect unrestricted UPDATE can commit perfectly and still damage every row. Transactions do not discover the intended predicate or business policy.

We will study isolation separately because concurrent work creates failures that a single-session example cannot display.

BEGIN starts the explicit unit. COMMIT publishes its successful database changes. ROLLBACK abandons its uncommitted changes. The SQL worksheet does not automatically commit between your statements, so these boundaries can be exercised directly.

Driver defaults matter outside the worksheet. Python sqlite3 versions and connection settings affect implicit transactions, and executescript has special transaction behaviour. Read the driver contract and keep application exception handling responsible for rolling back a failed unit.

A failed statement does not necessarily roll back every earlier statement in the transaction. Check the engine and conflict policy.

A savepoint names a position within the current work. ROLLBACK TO returns to that position while keeping the savepoint active; RELEASE removes the savepoint boundary. Releasing an inner savepoint is not the same as durably committing the outer transaction.

This is useful when an optional batch item may be abandoned while the application deliberately keeps earlier valid work. It should not hide a failure that invalidates the whole business operation.

Nested BEGIN transactions are not how SQLite provides this behaviour.

CREATE TABLE stock(id TEXT PRIMARY KEY NOT NULL,units INTEGER NOT NULL CHECK(units>=0));
INSERT INTO stock VALUES('north',10),('south',2);
CREATE TABLE request(id TEXT,source TEXT,destination TEXT,amount INTEGER); INSERT INTO request VALUES('T1','north','south',3);
CREATE TABLE transfers(id TEXT PRIMARY KEY NOT NULL,source TEXT NOT NULL,destination TEXT NOT NULL,amount INTEGER NOT NULL);

BEGIN;
UPDATE stock SET units=units+1 WHERE id='north';
SAVEPOINT optional_count;
UPDATE stock SET units=0 WHERE id='south';
ROLLBACK TO optional_count;
RELEASE optional_count;
COMMIT;
SELECT id,units FROM stock ORDER BY id;

The challenge has exactly one proposed transfer and an initially empty transfers table. It first inserts an audit row only if both stores exist, the amount is positive, the stores differ and the source has enough stock. Both stock updates then follow that accepted row.

A live multi-writer implementation also needs a concurrency strategy. For SQLite a deliberately acquired write transaction may be appropriate; PostgreSQL can use row locks or carefully designed conditional updates. A read performed outside the write transaction is not a reservation of stock.

The browser fixture demonstrates a transaction’s logical effects, not simultaneous users or crash recovery.

A database rollback cannot unsend a message already accepted by another service. If a committed transfer must trigger an external message, an outbox record written in the same database transaction can support reliable later delivery. The delivery process still needs deduplication and recovery.

A cancellation after commit requires a new compensating business operation when reversal is allowed. Preserve the audit trail instead of claiming the original transfer never occurred.

Treat a commit result that was lost over a connection as an uncertain outcome to reconcile, rather than blindly repeating a consequential operation.