SQL Transactions

If two people buy the last unit, reading the stock and decrementing it later lets both believe there is availability. We will reserve product and record the order in a single PostgreSQL operation. The case works for tickets, virtual items, or slots; it does not include payments or product delivery.

Last reviewed: October 2, 2026. Tested with: PostgreSQL 17.6, in a transaction with temporary tables. The function, stock decrement, rejection due to lack of units, and rollback were verified. Reference: PostgreSQL 18 documentation. The concurrency test with two sessions is described so it can be reproduced; it was not run during this review.

Create a separate lab

Use an empty test database and an account with permissions to create tables and functions. Do not run these statements on real tables from your store. You can open it with psql or with your provider's SQL editor. Check SELECT version(); to register your engine.

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  stock integer NOT NULL CHECK (stock >= 0)
);

CREATE TABLE reservations (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id bigint NOT NULL REFERENCES products(id),
  buyer_id bigint NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO products (name, stock) VALUES ('Entrada al evento', 1);

The primary key creates the index that will locate the product. CHECK prevents negative stock and invalid quantities even if other code writes directly. The reservation reference requires an existing product. buyer_id is an example identifier: in your application it must come from the authorized session and reference the real users table.

Dos compras compiten por una fila: UPDATE valida stock dentro del bloqueo, una reserva se confirma y la otra recibe stock insuficiente.

Decrement and record together

Create this function in the lab database:

CREATE FUNCTION reserve_product(p_product bigint, p_buyer bigint, p_quantity integer)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
  v_product bigint;
  v_reservation bigint;
BEGIN
  IF p_quantity IS NULL OR p_quantity <= 0 OR p_buyer IS NULL THEN
    RAISE EXCEPTION 'Comprador o cantidad inválidos';
  END IF;

  UPDATE products
  SET stock = stock - p_quantity
  WHERE id = p_product AND stock >= p_quantity
  RETURNING id INTO v_product;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Producto inexistente o stock insuficiente';
  END IF;

  INSERT INTO reservations (product_id, buyer_id, quantity)
  VALUES (v_product, p_buyer, p_quantity)
  RETURNING id INTO v_reservation;

  RETURN v_reservation;
END;
$$;

The condition stock >= p_quantity is in the UPDATE that decrements, not in a previous query. In Read Committed, the second UPDATE competing for the row waits; when the first commits, PostgreSQL re-evaluates the condition on the updated row.

RETURNING returns the product that was actually modified. NOT FOUND detects that no row was decremented. The exception aborts the operation: if the INSERT fails after the decrement, the decrement is not committed either. We do not add SECURITY DEFINER: the function keeps the caller's permissions.

Reproduce the race

Open two sessions to the same database. In the first, run:

BEGIN;
SELECT reserve_product(1, 101, 1);
-- Mantén esta transacción abierta mientras pruebas la segunda sesión.

In the second, run SELECT reserve_product(1, 202, 1);. It should wait. Go back to the first and run COMMIT;. The second should respond with insufficient stock. If you had run ROLLBACK; in the first, the second could reserve the unit.

Check the result:

SELECT id, stock FROM products;
SELECT product_id, buyer_id, quantity FROM reservations;

There should be zero stock and only one reservation. These ids correspond to a new database; if you already inserted rows, use the id returned by INSERT ... RETURNING id.

To check rollback without another session, start a transaction, reserve, and run ROLLBACK; the queries should show the original stock and no reservation from that execution. A sequence can leave gaps after a rollback: the id number does not count confirmed orders.

Take the rule to the backend

Call the function with driver parameters, never by concatenating user text into SQL. The backend must check identity, authorization, and quantity before calling; database constraints are a second defense. Return a clear business error when there is no stock and do not automatically repeat a purchase without an idempotency key.

Do not keep this transaction open while querying a payment gateway. A reservation needs an expiration and a release operation designed separately. If you reserve multiple products, lock in a consistent order and handle deadlocks; this single-row solution does not guarantee rules involving multiple rows under all isolation levels.

If you see «current transaction is aborted» after an exception inside BEGIN, run ROLLBACK before continuing. If the operation waits too long, check the session holding the locked row. The example deliberately gathers all the logic in a single call to prevent the application from confirming the decrement before the order.

Sources and documentation

Project and developer: PostgreSQL, PostgreSQL Global Development Group. Download: postgresql.org. License: PostgreSQL License. Original code and graphics by The Silencers.

SQL Transactions