Transaction Control in PostgreSQL

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

-- Rollback on error
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 123;
-- Error occurs here
ROLLBACK;

-- Savepoints
BEGIN;
UPDATE users SET credits = 100;
SAVEPOINT before_risky;
DELETE FROM logs;  -- Risky operation
ROLLBACK TO before_risky;  -- Undo just the DELETE
COMMIT;

-- DETAILED_TAB:
-- Isolation levels
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- Default
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Advisory locks
SELECT pg_advisory_lock(12345);        -- Get lock
-- Do work
SELECT pg_advisory_unlock(12345);      -- Release

-- Row-level locking
SELECT * FROM orders
WHERE id = 123
FOR UPDATE;                            -- Lock for update

SELECT * FROM inventory
WHERE product_id = 456  
FOR SHARE;                             -- Shared lock