Transactions, MVCC, and Concurrency

Understand ACID, transaction boundaries, isolation levels, locks, and safe concurrent updates.

Transactions and ACID

A transaction groups statements into one logical unit. ACID refers to atomicity, consistency, isolation, and durability. BEGIN starts a transaction, COMMIT makes its changes permanent, and ROLLBACK discards uncommitted changes. Transactions are crucial when multiple writes must succeed together, such as creating an order and reducing inventory.

Keep transactions appropriately short. Long-running transactions can retain old row versions, increase contention, and interfere with maintenance. Never assume that two separate queries are atomic together unless they are in the same transaction or protected by another suitable mechanism.

Transfer funds atomically

BEGIN;

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

COMMIT;

💡 Both balance changes are committed together if the transaction succeeds. Production code should verify that the source account exists and has sufficient funds, and should handle errors with rollback behavior.

Key Takeaways

Transactions group related changes.
ACID describes key transaction guarantees.
Keep transactions short and handle failures deliberately.

Knowledge Check

Which command discards uncommitted changes in the current transaction?

MVCC and Isolation Levels

PostgreSQL uses multiversion concurrency control (MVCC) so statements can read consistent row versions while concurrent transactions modify data. Readers and writers often avoid blocking each other, though locks and conflicts still occur. Isolation levels determine which concurrency anomalies may be observed.

READ COMMITTED is PostgreSQL's default and gives each statement a snapshot as of that statement's start. REPEATABLE READ uses a stable transaction snapshot but can still require retry handling in some scenarios. SERIALIZABLE provides stronger guarantees by detecting executions that cannot be serialized, and transactions may need to be retried after serialization failures.

Set isolation level

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;
-- perform related reads and writes
COMMIT;

💡 The transaction uses a consistent snapshot for its reads. Application code must still handle serialization errors and other transaction failures where applicable.

Key Takeaways

MVCC provides snapshots of row versions.
READ COMMITTED is PostgreSQL's default isolation level.
Stronger isolation can require retrying transactions.

Knowledge Check

What is PostgreSQL's default transaction isolation level?

Locks, Row-Level Locking, and Deadlocks

Locks coordinate conflicting operations. SELECT ... FOR UPDATE locks selected rows so competing transactions cannot modify them in conflicting ways until the transaction ends. This is useful for workflows that read a row and then make a decision based on it. Use row locks carefully and keep the transaction short.

A deadlock occurs when transactions wait on each other's locks in a cycle. PostgreSQL detects deadlocks and aborts one transaction. Applications should handle the error and may retry appropriate transactions. Consistent lock ordering can reduce deadlock risk, but retry logic is still valuable.

Lock a row before updating

BEGIN;

SELECT quantity
FROM inventory
WHERE product_id = 42
FOR UPDATE;

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42 AND quantity > 0;

COMMIT;

💡 The row lock coordinates concurrent access to the inventory row. Production code should check whether the update affected a row and should ensure the transaction handles insufficient stock correctly.

Key Takeaways

FOR UPDATE locks selected rows for conflicting operations.
Deadlocks can cause PostgreSQL to abort one transaction.
Use consistent lock ordering and bounded retry logic where appropriate.

Knowledge Check

What can PostgreSQL do when it detects a deadlock?

Transactions and ACID

MVCC and Isolation Levels

Locks, Row-Level Locking, and Deadlocks

STAY UPDATED WITH BYTES!