What belongs in one transaction?

Commit and rollback

BEGIN starts an explicit transaction. The statements that follow belong to it until the application ends it with COMMIT or ROLLBACK. These commands let the application control the boundary explicitly: it starts the work, checks whether each part succeeded, then decides whether to keep or discard the changes.

PostgreSQL also runs an individual statement in a transaction. With autocommit, a successful statement commits on its own. That is convenient for an independent edit, but three separately committed statements give us three separate outcomes. Explicitly grouping them changes what can be abandoned together. A client library may manage transaction boundaries for us, so the application’s actual behaviour needs checking rather than inferring it from the absence of a written BEGIN. PostgreSQL’s BEGIN reference describes these boundaries.

A connection is an application’s active link to the database; the database conversation on that connection is called a session. The transaction can read its own unfinished changes. Other sessions do not read those uncommitted table changes. After commit, a reader using a sufficiently recent view can see the completed purchase. An older view may still show the earlier state; commit does not force every existing reader to refresh. We’ll examine those views in Isolation and snapshots.

Start with the rule

Our shop wants a recorded purchase to reserve exactly the quantities on its order lines. We start a fresh checkout example: the historical orders O12 and O13 remain, their purchases are already accounted for, and O14 does not yet exist. Suppose Ada, customer C4, buys two Blue mugs, product P7. Three mugs are available before this new checkout. Completing this purchase means creating order O14, recording its line for two mugs at €20 each, and reducing available stock to one.

Each partial result tells a different, wrong story. Reduced stock without an order leaves two mugs unavailable with no purchase explaining why. An order and line without reduced stock let the shop promise those same mugs again. An order without its line loses the detail the warehouse needs. The three changes belong in one transaction because the completed state depends on all three.

Constraints still have work to do. A foreign key requires the line to refer to an existing order and product. A check requires a positive quantity. Those declarations do not require this application to insert the line before committing an otherwise valid order. The transaction gives us a way to keep a chosen group together; it does not choose the right group or discover an omitted statement.

The stock count below is what remains available for this new purchase; earlier purchases have already been accounted for.

Working through checkout

To run the SQL yourself, start the shared practice setup. It explains opening PostgreSQL, loading the files and choosing a fresh database for this article.

The practice schema from Designing your data supplies customers, products, orders and order lines. Load it into an empty practice database, then add the stock table below. We keep availability separate from the product’s descriptive fields. This example has one stock record per product and one currency, EUR.

CREATE TABLE stock (
  product_id text PRIMARY KEY REFERENCES products (product_id),
  available integer NOT NULL CHECK (available >= 0)
);
INSERT INTO stock (product_id, available) VALUES ('P7', 3);

The application first begins the transaction and asks to reserve two mugs. The condition available >= 2 requires enough stock; the subtraction uses the row’s value inside the database. RETURNING reports the row that was changed, including its new availability.

BEGIN;
UPDATE stock
SET available = available - 2
WHERE product_id = 'P7' AND available >= 2
RETURNING product_id, available;

This result is a decision point. Exactly one returned row means this attempt reserved the mugs. Zero rows means it reserved nothing: the stock row was absent or the condition did not hold. Zero rows is a successful SQL execution, so it does not automatically abort the transaction. The application must issue ROLLBACK and stop this checkout branch. UPDATE’s result and RETURNING give the application the evidence for that decision.

Only after receiving the one-row result does the application run the following statements, on the same database connection and inside the transaction it just opened. Creating the order before the line satisfies the line’s foreign key.

INSERT INTO orders (order_id, customer_id, placed_at)
VALUES ('O14', 'C4', '2026-10-04 11:00:00+00');

INSERT INTO order_lines
  (order_id, line_no, product_id, quantity, unit_price)
VALUES ('O14', 1, 'P7', 2, 20.00);

COMMIT;

INSERT INTO adds a record to the named table. The column list and VALUES list correspond in order: for the order, 'O14' fills order_id, 'C4' fills customer_id, and the timestamp fills placed_at. The line insert follows the same pattern. Naming the columns makes that mapping visible instead of relying on their order in the table definition.

After successful commit, one mug remains available and both O14 records exist. There is no separate commit between them. If the application uses a connection pool, it needs a transaction API that keeps this sequence on the same connection. Sending each statement through an arbitrary available connection would not express the transaction shown here.

A failure after the first change

Now consider a separate attempt from the same initial state: three mugs available and no O14. The stock update succeeds, then the order insert succeeds. A bug sends quantity zero for the line, which the schema refuses.

BEGIN;
UPDATE stock SET available = available - 2
WHERE product_id = 'P7' AND available >= 2;

INSERT INTO orders (order_id, customer_id, placed_at)
VALUES ('O14', 'C4', '2026-10-04 11:00:00+00');

-- Deliberate mistake: a line cannot have quantity zero.
INSERT INTO order_lines
  (order_id, line_no, product_id, quantity, unit_price)
VALUES ('O14', 1, 'P7', 0, 20.00);
-- ERROR: the new row violates the quantity CHECK.

-- Issue this after the error, on the same connection:
ROLLBACK;

Run this case against a fresh copy of the setup, and issue the rollback after the error. PostgreSQL puts the explicit transaction into a failed state. The application cannot continue ordinary work as though only that insert had been skipped. Here it rolls back the whole purchase, discarding the order and the stock reduction.

Savepoints can mark a place inside a transaction to return to after an error. They are useful when a portion of the work may legitimately be abandoned. They do not make a required order line optional. Recovering to a savepoint and committing the stock reservation without its purchase would preserve the wrong state. The transaction tutorial explains error recovery, and the SAVEPOINT reference gives the commands.

SELECT available FROM stock WHERE product_id = 'P7';
SELECT count(*) AS orders FROM orders WHERE order_id = 'O14';
SELECT count(*) AS lines FROM order_lines WHERE order_id = 'O14';

Rollback discards this transaction’s work. It does not mean setting the entire database back to an earlier moment or undoing another customer’s completed order. Nor can a later rollback erase O14 after it has committed. Cancelling a completed purchase is a new change with its own rules: restore the appropriate availability and record that the order was cancelled together. ROLLBACK applies to the current transaction.

When payment happens elsewhere

A payment provider has its own records and decisions. Calling it between BEGIN and COMMIT does not bring those records into this database transaction. If payment succeeds and the line insert then fails, a database rollback does not refund the charge. Reversing the order merely changes the unfinished case: a committed order may be followed by a declined payment.

For this shop, one possible design gives the order an explicit awaiting payment state. A short database transaction reserves stock and records that pending purchase. A separate operation asks the provider for payment. Once its outcome is confirmed, another transaction records payment or cancels the pending purchase and releases stock. The application and warehouse must understand that a pending order is not yet ready to fulfil.

This design makes the intermediate state something the application can revisit. It still needs a way to find pending purchases after a restart, identify repeated requests, and deal with payment that remains uncertain. A cancellation handler must change an eligible pending order and release its stock together, and recognise a cancellation already handled. Otherwise a repeated result could add the same mugs back twice. These are requirements of this proposed workflow, not capabilities supplied by adding a status column.

There is also a cost to keeping the transaction open while waiting for payment. Updating the stock record acquires a row lock: another transaction trying to change that same record must wait until this one commits or rolls back. A slow payment call can therefore hold up other purchases of the same product. Holding the lock longer does nothing to settle an uncertain payment. Handling waits, deadlocks, and retries develops that recovery problem; the provider’s own recovery contract must supply the other half.

Reviewing a transaction boundary

A useful way to review this code is to pause after each statement and ask what happens if the next operation fails. Every required change to the purchase should still be inside the transaction at that point. The application should test meaningful results, including a successful update that changed no rows, and commit only once the intended local state is complete.

Prepare work that does not depend on the current database state before opening the transaction, such as parsing the request and checking that its quantity is a positive integer. Availability is different: another purchase can change it. In our example, the conditional UPDATE checks availability as it reserves stock. An earlier read of the count would not give the same permission to buy, whether that read happened before or after BEGIN.

Atomicity answers what happens when this purchase fails partway through. Two purchases can each be complete while jointly promising too much stock. Our conditional update was chosen to address that second problem too. To see why it matters, we need to let another request run between this one’s steps.