Make a purchase succeed or fail together

Product 27 has stock 1. A purchase needs to reduce that stock to 0 and create order 501. These changes belong together: the order says who bought the item that is no longer available.

For this page, assume one buyer, an existing product, a new order number, and no other requests. We will introduce competing buyers later. Even with one caller, something can go wrong between two operations.

Stop halfway through

Suppose the engine changes the stock, then rejects the order because its data is invalid. If the stock change remains, the tote disappears from available inventory without a recorded purchase.

A transaction groups work under a promise: either its changes are accepted together, or they are abandoned together. Commit accepts the transaction. Rollback abandons its changes.

Before
Stock: 1
Order 501: absent

After commit
Stock: 0
Order 501: present

After rollback
Stock: 1
Order 501: absent

The successful outcome contains both changes. The abandoned outcome contains neither.

In SQL, the successful path can look like this:

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 27;
INSERT INTO orders (order_id, product_id, paid_price)
VALUES (501, 27, 20);
COMMIT;

BEGIN starts the transaction. The update subtracts one from the selected row’s stock. The insert creates the order. COMMIT completes the group. Our single-buyer assumptions are essential: this is not yet a general checkout implementation.

The all-or-nothing property is called atomicity. The name does not mean that the computer performs both instructions at the same instant. The engine can do several internal steps while preserving the promised outcome.

Three promises to keep separate

Atomicity addresses partial changes. Durability concerns which committed changes survive failures. Isolation concerns what happens when different transactions overlap. An engine needs mechanisms for each promise; adding BEGIN and COMMIT to a homemade program does not implement them.

We will first learn to represent and store rows, then examine logs and recovery. Later we will coordinate overlapping work. Separating those questions makes it possible to see what each mechanism contributes.

A transaction also cannot decide whether the application asked for the right thing. If a purchase requires changing two tables but the application submits only one change, the engine does not invent the missing operation.

Change the failure point

The stock update succeeds. The order insert fails. The caller then rolls back. What should a later lookup find?

Apply the all-or-nothing promise

It should find stock 1 and no order 501. The successful first statement was still part of an unfinished transaction. Rollback abandons that change along with the failed purchase attempt.

If the engine instead kept stock 0, its transaction machinery would have failed to provide atomicity for this example.

This defines behavior from the application’s viewpoint. Next we switch to the engine builder’s viewpoint and implement a much smaller operation: finding one row in memory.

Stop halfway through

Three promises to keep separate

Change the failure point

Further reading