Transactions & MVCC

BEGIN;

-- All reads see data as of the snapshot taken at BEGIN
SELECT balance FROM accounts WHERE id = 1;  -- reads snapshot

-- Writes are visible within this transaction immediately
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Check: balance changes are visible to this transaction
SELECT balance FROM accounts WHERE id IN (1, 2);

COMMIT;  -- durably written to WAL + data pages

-- Set isolation level for a single transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM inventory WHERE product_id = 42;
-- ... other work ...
COMMIT;

-- Set isolation level for a serialisable transaction
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts;
-- SSI detects any write conflicts that would violate serialisability
COMMIT;  -- may raise serialisation failure; retry on error

-- Set default isolation level for the session
SET default_transaction_isolation = 'repeatable read';

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (42, 199.99);
SAVEPOINT after_insert;

-- Try a risky operation
UPDATE inventory SET qty = qty - 1 WHERE product_id = 7;

-- Check if it went wrong
-- (e.g., qty went negative due to concurrent update)
SELECT qty FROM inventory WHERE product_id = 7;

-- Roll back just the inventory update, keep the order insert
ROLLBACK TO SAVEPOINT after_insert;

-- Continue with a different approach
UPDATE inventory SET qty = qty - 1
WHERE product_id = 7 AND qty > 0;

RELEASE SAVEPOINT after_insert;  -- drop the savepoint
COMMIT;

-- Both transactions run SERIALIZABLE
-- T1:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM on_call WHERE dept = 'engineering';
INSERT INTO on_call (name, dept) VALUES ('Alice', 'engineering');
COMMIT;  -- may fail with serialisation error

-- T2 (concurrent):
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM on_call WHERE dept = 'engineering';
INSERT INTO on_call (name, dept) VALUES ('Bob', 'engineering');
COMMIT;  -- one of T1/T2 will be aborted if a cycle is detected

-- Application retry pattern
DO $$
BEGIN
  LOOP
    BEGIN
      -- ... transactional work ...
      EXIT;  -- success
    EXCEPTION WHEN serialization_failure THEN
      -- retry
    END;
  END LOOP;
END $$;

-- Get the current SCN
SELECT absdb_current_scn();

-- Read the orders table as it was at SCN 1048576
SELECT * FROM orders AS OF SCN 1048576;

-- Audit: what was Alice's balance 6 hours ago?
SELECT balance
FROM accounts AS OF SCN (
    SELECT scn FROM absdb_scn_at(now() - INTERVAL '6 hours')
)
WHERE username = 'alice';

-- Compare current vs historical state
SELECT
    current.balance AS current_balance,
    historical.balance AS balance_6h_ago,
    current.balance - historical.balance AS change
FROM accounts AS current
JOIN accounts AS OF SCN 1048576 AS historical USING (id)
WHERE current.username = 'alice';

-- SQL:2023 MERGE statement
MERGE INTO products AS target
USING incoming_products AS source ON target.sku = source.sku
WHEN MATCHED THEN
    UPDATE SET price = source.price, updated_at = now()
WHEN NOT MATCHED THEN
    INSERT (sku, name, price, created_at)
    VALUES (source.sku, source.name, source.price, now());

-- PostgreSQL-compatible UPSERT with RETURNING
INSERT INTO users (email, name, created_at)
VALUES ('alice@example.com', 'Alice', now())
ON CONFLICT (email) DO UPDATE
    SET name       = EXCLUDED.name,
        updated_at = now()
RETURNING id, email, (xmax = 0) AS is_new_row;

-- INSERT ... ON CONFLICT DO NOTHING
INSERT INTO event_log (event_id, type)
VALUES (gen_random_uuid(), 'page_view')
ON CONFLICT (event_id) DO NOTHING;

-- Acquire an exclusive advisory lock (blocks until available)
SELECT pg_advisory_lock(12345);
-- ... critical section ...
SELECT pg_advisory_unlock(12345);

-- Non-blocking try (returns false if unavailable)
SELECT pg_advisory_try_lock(12345) AS acquired;

-- Transaction-scoped advisory lock (auto-released at COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(99999);
-- ... work is serialised while this transaction is open ...
COMMIT;  -- lock released automatically

-- Shared advisory lock (multiple readers, exclusive writer)
SELECT pg_advisory_lock_shared(42);
-- ... multiple sessions can hold this simultaneously ...
SELECT pg_advisory_unlock_shared(42);