Network round-trips, coordinator overhead, blocking on failure) and operational complexity (recovery Procedures, heuristic outcomes) make it a last resort. Prefer sagas for most distributed workflows.
Assumes conflicts are likely and prevents them by acquiring locks before accessing data.
Uses locks (SELECT ... FOR UPDATE) Transactions wait for conflicting locks to be released High contention reduces throughput Best for: write-heavy workloads, low contention, or when retries are expensive SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE ;
-- If another transaction holds a lock on this row, we block here
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42 ;
Assumes conflicts are rare and detects them after the fact.
No locks during reads On write, check that the data has not changed since it was read If a conflict is detected, abort and retry Best for: read-heavy workloads, low contention, or when conflicts are truly rare -- Version column approach:
SELECT quantity, version FROM inventory WHERE product_id = 42 ;
-- Application stores: quantity=100, version=5
UPDATE inventory SET quantity = 99 , version = 6
WHERE product_id = 42 AND version = 5 ;
-- If 1 row affected: success. If 0 rows affected: conflict, retry.
Aspect Pessimistic Optimistic Conflict handling Prevented by locks Detected on write, resolved by retry Throughput (low contention) Lower (lock overhead) Higher (no lock overhead) Throughput (high contention) Higher (serialised access) Lower (many retries) Latency May wait for locks Immediate reads Complexity Simpler logic Must implement retry logic Deadlocks Possible Not possible Best for Write-heavy, hot rows Read-heavy, cold rows
Transactions are like signing a contract with a friend. Both of you agree that the deal is binding only if all conditions are met. If one party walks away before the ink is dry, the entire agreement is void. Atomicity guarantees this all-or-nothing property. Durability means that once both signatures are on the paper, the agreement survives even if the building burns down.
MVCC is like a time machine for your database. Each transaction sees the world as it existed at a specific moment in time, regardless of what other transactions are doing simultaneously. This means readers never block writers and writers never block readers, because they are each looking at their own snapshot. The trade-off is that old versions of data linger until cleanup, much like archaeological layers of sediment that must be managed.
A transaction that stays open for minutes or hours holds snapshots that prevent VACUUM from Reclaiming dead tuples. In PostgreSQL, this causes table and index bloat. Monitor long-running Transactions:
SELECT pid, now () - xact_start AS duration, query, state
WHERE state IN ( ' idle in transaction ' , ' active ' )
AND now () - xact_start > INTERVAL ' 5 minutes '
Under REPEATABLE READ and SERIALIZABLE isolation, the database may abort your transaction with a Serialization error. If your application does not catch and retry these errors, users will see Spurious failures. Implement retry logic with exponential backoff.
Connection poolers in transaction mode (PgBouncer) reset the session state between transactions. If You SET a variable (e.g., SET search_path TO tenant_123), it will not persist to the next Transaction. Use SET LOCAL for transaction-scoped settings, or use session pooling.
Changing the isolation level for a single query without understanding the anomaly it introduces is Dangerous. For example, reading aggregate counts at READ UNCOMMITTED for a dashboard may show Uncommitted transactions that will be rolled back, leading to incorrect metrics.
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE blocks all reads and writes on the table from all Other transactions. Use the most restrictive lock that suffices: ACCESS SHARE (default for SELECT), ROW EXCLUSIVE (default for UPDATE/DELETE/INSERT), or SHARE UPDATE EXCLUSIVE (for VACUUM-like operations).
A runaway query can hold locks indefinitely, blocking other transactions. Set statement timeouts as A safety net:
SET statement_timeout = ' 30s ' ;
ALTER ROLE web_app SET statement_timeout = ' 30s ' ;
ALTER DATABASE mydb SET statement_timeout = ' 30s ' ;
The classic example of why transactions are necessary: transferring money between two accounts Requires atomicity — either both debits and credits happen, or neither does.
-- Check sufficient funds (consistent read within this transaction)
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE ;
-- If balance is insufficient, the application rolls back here
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1 ;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2 ;
INSERT INTO transfers (from_account, to_account, amount, transfer_at)
VALUES ( 1 , 2 , 500 , NOW ());
The FOR UPDATE lock on the source account prevents concurrent transfers from overdrafting. The Entire operation is atomic: if any step fails, the database rolls back all changes.
Prevent overselling by locking inventory within a transaction:
-- Lock the inventory row for this product
SELECT quantity FROM inventory
-- Check availability (application logic)
-- If quantity < requested, ROLLBACK
UPDATE inventory SET quantity = quantity - 3
INSERT INTO orders (product_id, quantity, status ) VALUES ( 42 , 3 , ' reserved ' );
For high-throughput inventory systems, this pessimistic approach may become a bottleneck. Consider Optimistic concurrency control instead:
SET quantity = quantity - 3
WHERE product_id = 42 AND quantity >= 3 ;
-- Check rows_affected: if 0, the reservation failed (insufficient stock)
-- If 1, proceed with the order
When you need to update the database and publish a message/event atomically (e.g., “create order” And “publish OrderCreated event”), the outbox pattern solves this without distributed transactions:
-- 1. Perform the business operation
INSERT INTO orders (customer_id, total, status ) VALUES ( 42 , 99 . 99 , ' created ' );
-- 2. Write the outgoing event to the outbox table (same transaction)
INSERT INTO outbox (event_type, aggregate_id, payload, created_at)
VALUES ( ' OrderCreated ' , order_id, ' {"orderId": 123, "total": 99.99} ' , NOW ());
-- 3. A background process (poller or CDC) reads from the outbox and publishes to the message broker
-- 4. After successful publish, the process marks the outbox entry as published
This guarantees that the event is published if and only if the database transaction commits. The Background process must be idempotent (handle duplicate publishes gracefully).
PostgreSQL assigns a 32-bit transaction ID (xid) to every transaction. The xid space is Approximately 4 billion transactions. When the xid counter wraps around, old data becomes invisible. This is called xid wraparound and it causes data loss if not prevented.
-- Check xid consumption:
SELECT datname, age(datfrozenxid) AS xid_age,
pg_size_pretty(pg_database_size(datname)) AS db_size
ORDER BY age(datfrozenxid) DESC ;
-- If xid_age approaches 2 billion (2,000,000,000), autovacuum is not keeping up.
-- Emergency action: run VACUUM FREEZE on the database:
Autovacuum normally prevents wraparound by running VACUUM on tables before they approach the Wraparound threshold. If autovacuum is disabled or misconfigured, manual intervention is required.
PostgreSQL’s visibility rules determine which row versions a transaction can see:
For a row with (xmin, xmax):
1. Is xmin committed? If no, the row was inserted by a transaction that rolled back. Invisible.
2. Is xmax zero? If yes, the row has not been deleted. Visible (subject to snapshot).
3. Is xmax committed? If yes, the row was deleted by a committed transaction. Invisible.
4. Is xmax from an in-progress transaction? The row is visible to transactions in the same
snapshot but not to later transactions.
- A transaction sees all rows committed by transactions with xid < snapshot_xmin
- A transaction does NOT see rows committed by transactions with xid >= snapshot_xmax
- Between xmin and xmax: visible if committed, invisible if aborted, subject to snapshot
Every SAVEPOINT creates a subtransaction, which consumes an xid. A transaction with 1000 Savepoints consumes 1001 xids. For long-running batch jobs, this accelerates xid wraparound.
-- Bad: one savepoint per row (1000 rows = 1001 xids):
-- RELEASE SAVEPOINT sp; -- releases the subtransaction but the xid is still consumed
-- Better: batch savepoints (savepoint every 100 rows):
Row-Level Security (RLS) policies are evaluated within the transaction’s security context. If you Change the current user within a transaction, RLS policies are re-evaluated:
-- Enable RLS on a table:
ALTER TABLE documents ENABLE ROW LEVEL SECURITY ;
CREATE POLICY tenant_isolation ON documents
USING (tenant_id = current_setting( ' app.current_tenant ' ):: INTEGER );
-- Within a transaction, switch tenant context:
SET LOCAL app . current_tenant = ' 42 ' ;
SELECT * FROM documents; -- Only sees tenant 42's documents
