Transactions and Isolation Levels

-- Basic transaction
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- ROLLBACK; would undo everything

-- Explicit isolation level (per-transaction)
BEGIN ISOLATION LEVEL REPEATABLE READ;
    -- Inside, all SELECTs see a consistent snapshot from the txn start
COMMIT;

-- Isolation levels (from weakest to strongest):
--   READ UNCOMMITTED  - dirty reads possible (Postgres treats as READ COMMITTED)
--   READ COMMITTED    - default in most DBs; each statement sees a fresh snapshot
--   REPEATABLE READ   - txn-long consistent snapshot; phantom reads possible
--   SERIALIZABLE      - full serializable; may abort on conflicts (retry needed)

-- Savepoints — partial rollback
BEGIN;
    INSERT INTO orders (...) VALUES (...);
    SAVEPOINT sp1;
    -- something risky
    UPDATE inventory SET qty = qty - 1 WHERE id = 42;
    -- if it fails:
    ROLLBACK TO SAVEPOINT sp1;
    -- ... continue with other work ...
COMMIT;

-- SELECT FOR UPDATE — lock the row until txn ends (prevents lost updates)
BEGIN;
    SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
    UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;