Multiversion concurrency control (MVCC) lets PostgreSQL handle catalog reads while order transactions change inventory. Readers can proceed without blocking writers, while committed data remains consistent. section 2.4.3
PostgreSQL uses read committed isolation by default. A statement does not see another transaction’s uncommitted changes, and concurrent updates to the same row wait when necessary to prevent dirty writes. section 2.4.3
Two customers decrement inventory
Run the inventory transaction from two psql sessions: BEGIN; SELECT stock_quantity FROM products.catalog WHERE id = 1; UPDATE products.catalog SET stock_quantity = stock_quantity - 1 WHERE id = 1; COMMIT;. The second SELECT is not blocked and can initially return 199. After both transactions commit, SELECT name, stock_quantity FROM products.catalog WHERE id = 1; returns Sunrise Blend with stock_quantity 197, showing that neither decrement overwrote the other. section 2.4.3
Listing 2.15 Customer buying two products
BEGIN;
INSERT INTO sales.orders (id, customer_id, total_amount)
VALUES ('19a0cffc-8757-453c-a4d2-b554fdc08954', 1, 26.53);
INSERT INTO sales.order_items (order_id, product_id, quantity, price)
VALUES ('19a0cffc-8757-453c-a4d2-b554fdc08954', 1, 1, 16.54),
('19a0cffc-8757-453c-a4d2-b554fdc08954', 4, 1, 9.99);
UPDATE products.catalog
SET stock_quantity = stock_quantity - 1
WHERE id IN (1, 4);
COMMIT;
Use the order transaction to see MVCC alongside atomic inserts, inventory updates, and rollback behavior. section 2.4.2
