database transactions acid isolation concurrency postgresql

Database transactions and ACID properties

Maria Garcia Feb 2026

2 tabs

-- Basic transaction
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If both succeed, commit
COMMIT;

-- If error occurs, rollback
-- ROLLBACK;

-- Transaction with error handling (PostgreSQL)
DO $$
BEGIN
  BEGIN
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;

    -- Check business rule
    IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
      RAISE EXCEPTION 'Insufficient funds';
    END IF;

  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'Transaction failed: %', SQLERRM;
      ROLLBACK;
  END;
END $$;

-- Savepoints for partial rollback
BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 101, 2);
SAVEPOINT after_items;

-- Oops, wrong quantity
ROLLBACK TO SAVEPOINT after_items;

-- Try again
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 101, 5);

COMMIT;

-- Read phenomena and isolation levels

-- Dirty Read: Reading uncommitted changes
-- Transaction 1:
BEGIN;
UPDATE products SET price = 200 WHERE id = 1;
-- Not committed yet

-- Transaction 2 (with Read Uncommitted):
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT price FROM products WHERE id = 1;  -- Sees 200 (dirty read)

-- Non-repeatable Read: Same query, different results
-- Transaction 1:
BEGIN;
SELECT price FROM products WHERE id = 1;  -- Gets 100
-- ... time passes ...
SELECT price FROM products WHERE id = 1;  -- Gets 200!

-- Transaction 2 (committed between reads):
UPDATE products SET price = 200 WHERE id = 1;
COMMIT;

-- Phantom Read: New rows appear
-- Transaction 1:
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Gets 5
-- ... time passes ...
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Gets 6!

-- Transaction 2 (inserted between reads):
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- Set isolation level for transaction
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM accounts WHERE id = 1;
COMMIT;

-- Isolation levels (PostgreSQL):

-- 1. READ UNCOMMITTED (not supported in PostgreSQL, falls back to READ COMMITTED)
-- Allows: Dirty reads, non-repeatable reads, phantom reads

-- 2. READ COMMITTED (default)
-- Prevents: Dirty reads
-- Allows: Non-repeatable reads, phantom reads
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 3. REPEATABLE READ
-- Prevents: Dirty reads, non-repeatable reads
-- Allows: Phantom reads (in some databases, not PostgreSQL)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM products WHERE price > 100;
-- Same query returns same results even if other transactions modify data
COMMIT;

-- 4. SERIALIZABLE (highest isolation)
-- Prevents: All phenomena
-- May cause serialization failures
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts;
UPDATE accounts SET balance = balance * 1.05;
COMMIT;
-- If another transaction modifies accounts concurrently,
-- this will fail with serialization error

-- Deadlock example
-- Transaction 1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- ... waits ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction 2 (runs concurrently):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- ... waits ...
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;
-- Deadlock! Database will rollback one transaction

-- Prevent deadlocks: Always access resources in same order
-- Both transactions should lock accounts in same order (e.g., by ID)

-- Lock types

-- Row-level lock
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- Other transactions can't modify this row until commit
UPDATE orders SET status = 'processing' WHERE id = 1;
COMMIT;

-- NOWAIT: Don't wait for lock
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;
-- Fails immediately if row is locked

-- SKIP LOCKED: Skip locked rows
SELECT * FROM tasks
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- Great for job queues

-- Advisory locks (PostgreSQL)
SELECT pg_advisory_lock(12345);
-- Perform work
SELECT pg_advisory_unlock(12345);

-- Table-level lock
BEGIN;
LOCK TABLE orders IN EXCLUSIVE MODE;
-- No other transactions can read or write
COMMIT;

-- Check active locks
SELECT
  pid,
  usename,
  pg_blocking_pids(pid) as blocked_by,
  query
FROM pg_stat_activity
WHERE state = 'active';

2 files · sql Explain with highlit

Transactions ensure data consistency through ACID properties. Atomicity guarantees all-or-nothing execution. Consistency maintains database constraints. Isolation prevents concurrent transaction interference. Durability persists committed changes. I use transactions for multi-step operations requiring consistency. Isolation levels—Read Uncommitted, Read Committed, Repeatable Read, Serializable—balance consistency and performance. Read Committed is default for most databases. Serializable provides highest isolation but lowest concurrency. Deadlocks occur when transactions wait circularly—database rolls one back. SAVEPOINT enables partial rollback. Two-phase commit coordinates distributed transactions. Understanding transaction isolation prevents race conditions and data corruption. Proper transaction scoping is critical for data integrity.