Step 6 — Checkout, and what a transaction is for

Five things must all happen, or none of them. This is what transactions exist for.

Topic 80 of 98 · 10 min to read · 50 min to build

After this topic you can

Write a checkout transaction with a deferred rollback.
Lock product rows with SELECT ... FOR UPDATE to prevent overselling.
Explain why all five checkout steps must succeed or fail together.

What a transaction is

A transaction groups several database statements so they either all take effect or none of them do. You open it, run your statements, and then either commit (make it all permanent) or roll back (undo all of it).

Checkout is the textbook case. It has to create an order, copy each cart line onto it, reduce stock for each product, and empty the cart. If the server dies after reducing stock but before creating the order, a customer has been charged nothing and the shop has lost inventory. Without a transaction that can happen.

The three problems checkout has to solve

Problem	What goes wrong without it	The fix
Partial failure	stock drops but no order exists	one transaction around everything
Two people, one last item	both succeed, stock goes to -1	`stock >= quantity` in the UPDATE, then check `RowsAffected`
Prices change mid-checkout	the order total does not match what was charged	copy the price onto the order line

The transaction, with the rollback that always runs

internal/adapter/storage/postgres/order.go — create this file

1// The named return value err is what lets the deferred function know whether
2// to roll back. This is the one place naming a return really earns its keep.
3func (r *Repository) Checkout(ctx context.Context, userID int64) (o *Order, err error) {
4	tx, err := r.db.BeginTx(ctx, nil)
5	if err != nil {
6		return nil, err
7	}
8
9	defer func() {
10		if p := recover(); p != nil {
11			tx.Rollback()
12			panic(p)                    // roll back first, then let the panic continue
13		}
14		if err != nil {
15			tx.Rollback()               // any error above undoes everything
16		}
17	}()
18
19	// ... the steps below all use tx, never r.db ...
20
21	return o, tx.Commit()
22}

Every statement inside must use tx. One stray r.db call runs on another connection, outside the transaction, and will not be rolled back.

Inside, part 1 — read the cart and lock the products

FOR UPDATE locks those product rows until the transaction ends, so a second checkout waits rather than racing you. ORDER BY matters: two transactions locking the same rows in different orders can deadlock.

internal/adapter/storage/postgres/order.go — inside Checkout

1	rows, err := tx.QueryContext(ctx, `
2		SELECT ci.product_id, ci.quantity, p.name, p.price_cents
3		  FROM cart_items ci
4		  JOIN products p ON p.id = ci.product_id
5		 WHERE ci.user_id = $1
6		 ORDER BY ci.product_id
7		   FOR UPDATE OF p`, userID)
8	if err != nil {
9		return nil, err
10	}
11	items, err := scanItems(rows)
12	if err != nil {
13		return nil, err
14	}
15	if len(items) == 0 {
16		return nil, ErrEmptyCart
17	}

Inside, part 2 — create the order

internal/adapter/storage/postgres/order.go — continue

1	var total int64
2	for _, i := range items {
3		total += i.PriceCents * int64(i.Quantity)
4	}
5
6	o = &Order{UserID: userID, TotalCents: total, Status: "placed"}
7	if err = tx.QueryRowContext(ctx,
8		`INSERT INTO orders (user_id, total_cents) VALUES ($1, $2)
9		 RETURNING id, created_at`,
10		userID, total).Scan(&o.ID, &o.CreatedAt); err != nil {
11		return nil, err
12	}

Inside, part 3 — copy each line and take the stock

This is the most important code in the project, and the WHERE on the UPDATE is why.

internal/adapter/storage/postgres/order.go — continue

1	for _, i := range items {
2		if _, err = tx.ExecContext(ctx,
3			`INSERT INTO order_items (order_id, product_id, name, price_cents, quantity)
4			 VALUES ($1, $2, $3, $4, $5)`,
5			o.ID, i.ProductID, i.Name, i.PriceCents, i.Quantity); err != nil {
6			return nil, err
7		}
8
9		// THE IMPORTANT LINE: only succeeds if there is still enough stock.
10		res, uErr := tx.ExecContext(ctx,
11			`UPDATE products SET stock = stock - $1
12			  WHERE id = $2 AND stock >= $1`,
13			i.Quantity, i.ProductID)
14		if uErr != nil {
15			err = uErr
16			return nil, err
17		}
18		if n, _ := res.RowsAffected(); n == 0 {
19			err = fmt.Errorf("%w: %s is out of stock", ErrOutOfStock, i.Name)
20			return nil, err       // the deferred rollback undoes everything above
21		}
22	}

Inside, part 4 — empty the cart

internal/adapter/storage/postgres/order.go — finish

1	if _, err = tx.ExecContext(ctx,
2		`DELETE FROM cart_items WHERE user_id = $1`, userID); err != nil {
3		return nil, err
4	}
5
6	return o, tx.Commit()

Only now does anything become real. Until Commit runs, every statement above can still be undone.

The line that prevents overselling

Look closely at WHERE id = $2 AND stock >= $1. The check and the change are the same statement, so nothing can happen between them.

Do it the other way (read the stock without a row lock, check it in Go, then update) and two checkouts can both read "1 left", both decide it is fine, and both subtract. That gap is a race condition. Putting the condition inside the UPDATE removes it.

RowsAffected() == 0 is then how you find out somebody else got there first.

Note: FOR UPDATE and the conditional UPDATE overlap on purpose. The lock makes concurrent checkouts queue; the WHERE keeps the stock rule true even if a later change drops the lock. ORDER BY ci.product_id makes every checkout take its locks in the same order, so two of them cannot deadlock.

Transaction rules

Keep them short. A transaction holds a connection from the pool for its whole life.
Never call an external service inside one. A slow payment provider would hold a database connection and its row locks, and with enough of them, use up the pool.
Always use `tx`, never `db`, for every statement inside.
Lock rows in a consistent order everywhere in your codebase, or two transactions can deadlock.

Prove it works

terminal

1# set stock to exactly 1
2docker exec -it shop-pg psql -U postgres -d shop \
3  -c "UPDATE products SET stock = 1 WHERE id = 1;"
4
5# two customers both put it in their cart
6curl -s -X POST localhost:8080/v1/cart/items -H "Authorization: Bearer $CUST"  -d '{"product_id":1,"quantity":1}'
7curl -s -X POST localhost:8080/v1/cart/items -H "Authorization: Bearer $CUST2" -d '{"product_id":1,"quantity":1}'
8
9# both check out at the same moment
10curl -s -X POST localhost:8080/v1/checkout -H "Authorization: Bearer $CUST"  &
11curl -s -X POST localhost:8080/v1/checkout -H "Authorization: Bearer $CUST2" &
12wait
13
14# exactly one order, and stock is 0 — never -1
15docker exec -it shop-pg psql -U postgres -d shop \
16  -c "SELECT stock FROM products WHERE id=1; SELECT count(*) FROM orders;"

Run it several times. A race that only shows up sometimes is still a race.

The short version

A transaction makes several statements all-or-nothing. Checkout is the textbook case.
Put the condition inside the UPDATE: WHERE id = $2 AND stock >= $1, then check RowsAffected.
Use a named return so the deferred rollback can see whether an error happened.
Keep transactions short, never call an external service inside one, and lock rows in a consistent order.

Remember: Checking a value in Go and then changing it in SQL leaves a gap. Put the check in the WHERE clause and the gap disappears.

Build this before moving on

Build checkout as one transaction, then run the two-customers-one-item test several times. Stock must never go negative and exactly one order must exist.

Then try these in your own editor

1Remove AND stock >= $1 and run the race test again. Work out what still stops stock going negative.
2Force an error after the order insert and confirm the whole thing rolls back.
3Add a time.Sleep inside the transaction and watch the connection pool while sending several checkouts.
4Change one query to use r.db instead of tx and work out exactly what breaks.

Official documentation

Check yourselfTake the Project 3 — Small shop test