Transactional Access Patterns

An OLTP workload is many small, concurrent operations, each touching a few rows found by key, and each expected to finish in milliseconds. Correctness comes from ACID transactions: a group of changes is atomic (all or nothing), keeps the database consistent with its constraints, is isolated from other transactions running at the same time, and is durable once committed. The listing runs two BookNest purchases against PostgreSQL 18 1,289 ; the second tries to sell a book with no stock left.

Two purchases as transactions in PostgreSQL 18: one commits, one is rolled back

-- One customer action = one short transaction touching a handful of rows by key.
BEGIN;
INSERT INTO orders (order_id, book_id, qty) VALUES (1009, 2, 1);
UPDATE books SET stock = stock - 1 WHERE id = 2;
COMMIT;
SELECT id, title, stock FROM books WHERE id = 2;
-- Selling an out-of-stock book violates the CHECK constraint, so nothing is saved.
BEGIN;
INSERT INTO orders (order_id, book_id, qty) VALUES (1010, 3, 1);
UPDATE books SET stock = stock - 1 WHERE id = 3;
COMMIT;
SELECT count(*) AS orders FROM orders;

Output

BEGIN
INSERT 0 1
UPDATE 1
COMMIT
 id |          title           | stock
----+--------------------------+-------
  2 | Patterns of the Deep Web |     4
(1 row)
BEGIN
INSERT 0 1
ERROR:  new row for relation "books" violates check constraint "books_stock_check"
DETAIL:  Failing row contains (3, Salt and Saffron, 24.00, -1).
ROLLBACK
 orders
--------
      1
(1 row)

The second order's INSERT succeeded on its own, yet the failed UPDATE made PostgreSQL turn COMMIT into ROLLBACK, so no order exists for a book that could not be shipped. That guarantee is what an OLTP database is for. The schema is in demos/ch01/oltp/.

MENU