PostgreSQL: Transactions, Locks, Isolation Levels, MVCC, VACUUM

PostgreSQL: A Deep Dive into Transactions, Locks, and Serializable Isolation

Developers often encounter non-obvious query behavior in PostgreSQL: freezes, unexpected UPDATE results during concurrent operations, or transaction failures. Understanding internal mechanisms like isolation levels, locks, and how VACUUM works is critical for building reliable and performant applications. This article thoroughly dissects these concepts, illustrating them with practical SQL examples, and highlights recent PostgreSQL improvements.

Time in Transactions: now() vs clock_timestamp()

PostgreSQL offers two primary functions for retrieving the current time, whose behavior differs within the context of transactions. The now() function returns the start time of the current transaction and remains unchanged throughout its execution. This ensures consistency of timestamps for operations within a single transaction, for example, when setting created_at and updated_at.

BEGIN;
SELECT now();
-- ... other operations ...
SELECT now(); -- Will return the same time as the first call
SELECT clock_timestamp(); -- May differ
COMMIT;

Unlike now(), the clock_timestamp() function provides the actual current execution time, which can change even within a single query or transaction. Understanding this difference helps avoid errors when working with temporal data.

Transaction Isolation Levels in PostgreSQL

PostgreSQL features a simplified model of isolation levels compared to other RDBMS, offering only three:

• Read Committed: The default level, where each command within a transaction sees a fresh snapshot of the data. Dirty reads are fundamentally impossible in PostgreSQL, so Read Uncommitted is equivalent to Read Committed.
• Repeatable Read: Ensures that all read operations within a transaction see the same data snapshot, fixed at the moment of the first query. This prevents phantom reads and non-repeatable reads.
• Serializable: The strictest level, guaranteeing that concurrently executing transactions yield the same result as if they were executed sequentially. This is achieved through the Serializable Snapshot Isolation (SSI) mechanism.

An important feature of PostgreSQL is its support for DDL (Data Definition Language) within transactions. This allows for creating, altering, or dropping data structures and rolling them back if necessary, enhancing the safety of migrations and complex schema changes.

Locking Mechanisms and Concurrency Control

Despite using Multi-Version Concurrency Control (MVCC), PostgreSQL actively employs locks for write operations. When two transactions attempt to update the same row, the second transaction will wait for the first to complete. Consider this example:

-- Session 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- ... do not commit ...

-- Session 2:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- This query will hang, waiting for Session 1 to complete.

The table below demonstrates the main lock types in PostgreSQL, their purpose, and conflicting modes:

| Lock Type | When Acquired | Conflicts With |

| :-------------------- | :------------------------------------------ | :---------------------------- |

| ACCESS SHARE | SELECT | ACCESS EXCLUSIVE |

| ROW SHARE | SELECT FOR UPDATE/SHARE | EXCLUSIVE, ACCESS EXCLUSIVE |

| ROW EXCLUSIVE | INSERT, UPDATE, DELETE | SHARE and above |

| SHARE UPDATE EXCLUSIVE | CREATE INDEX CONCURRENTLY, ANALYZE, VACUUM | SHARE UPDATE EXCLUSIVE and above |

| SHARE | CREATE INDEX | ROW EXCLUSIVE and above |

| SHARE ROW EXCLUSIVE | CREATE TRIGGER, ALTER TABLE (some) | ROW SHARE and above |

| EXCLUSIVE | Allows only reads | ROW SHARE and above |

| ACCESS EXCLUSIVE | DROP TABLE, ALTER TABLE, VACUUM FULL | All |

The stricter the lock, the higher its number in the table and the wider the range of conflicting operations. Operations with conflicting locks cannot execute simultaneously on the same object; one will wait. It's important to note that CREATE INDEX CONCURRENTLY allows building indexes without blocking DML operations, unlike standard CREATE INDEX.

Read Anomalies: Phantom and Non-Repeatable Data

At the Read Committed isolation level, read anomalies can occur, leading to inconsistent results:

• Phantom Reads: When a transaction executes the same query twice, and the second time it sees new rows inserted by another committed transaction between those queries.

```sql

-- Session 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT sum(value) FROM t_test2; -- For example, 330

-- Session 2 (concurrently):

INSERT INTO t_test2 VALUES (1, 30); COMMIT;

-- Session 1:

SELECT sum(value) FROM t_test2; -- Now 360, new rows appeared.

COMMIT;

```

• Non-Repeatable Reads: When a transaction reads the same data twice, and the second time it sees modified data, altered by another committed transaction.

```sql

-- Session 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Returns a row

-- Session 2 (concurrently):

UPDATE t_test2 SET value = 15 WHERE class = 1 AND value = 10; COMMIT;

-- Session 1:

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Empty! Data changed.

COMMIT;

```

To prevent these anomalies, the Repeatable Read isolation level is used. At this level, a transaction operates on a fixed data snapshot that does not change until its completion, regardless of changes made by other transactions.

Peculiarities of the Serializable Isolation Level

The Serializable level is designed to provide maximum isolation, simulating sequential transaction execution. However, this can lead to serialization errors (ERROR: could not serialize access) if PostgreSQL detects a potential conflict that could violate the execution order. In such cases, one of the transactions will be aborted and must be retried.

An interesting aspect of Serializable is false conflicts, caused by predicate locks (SSI). PostgreSQL stores these locks at the page level, not individual rows. If two transactions work with logically different datasets, but this data is physically located on the same disk page (which often happens with small tables), a serialization conflict can occur, even though logically there isn't one. In large tables, where data is distributed across different pages, such conflicts are less common.

Solving Concurrency Problems

Let's consider a classic data race problem: two transactions simultaneously try to modify data based on outdated readings. For example, Alice and Bob are on call, and each wants to leave if at least one person remains on duty. If both transactions use Repeatable Read and see two people on call, they both commit successfully, leading to no one being on call at all.

To solve such problems, the following approaches are used:

• Serializable: The second transaction will fail with a serialization error, and it can then be retried. This guarantees a correct outcome but requires error handling in the application.
• SELECT ... FOR UPDATE: Using FOR UPDATE during a read explicitly locks the selected rows, preventing other transactions from modifying them until the current one completes. The second session will wait until the first finishes, and then it will see the updated data.

```sql

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM d_test WHERE on_call = true FOR UPDATE; -- Locks rows

-- ... further operations ...

COMMIT;

```

• Atomic operation: Combining the condition check and action into a single query. If the condition is not met at the time of the UPDATE, the query will not affect any rows, eliminating the race window.

```sql

UPDATE d_test

SET on_call = false

WHERE name = 'Alice'

AND (SELECT count(*) FROM d_test WHERE on_call = true) > 1;

```

Advisory Locks

PostgreSQL provides an advisory locking mechanism (pg_advisory_lock, pg_advisory_xact_lock) that allows controlling concurrent access to arbitrary resources not directly tied to tables or rows. These locks operate on integer values and can be used to coordinate processes.

• pg_advisory_lock: Session-level lock. Persists until explicitly released (pg_advisory_unlock) or until the connection is closed.
• pg_advisory_xact_lock: Transaction-level lock. Automatically released upon COMMIT or ROLLBACK.

It's important to remember that advisory locks only work within a single database. For distributed systems with multiple PostgreSQL instances, external solutions are required (e.g., Redis, ZooKeeper, etcd).

The Role of VACUUM in MVCC

MVCC (Multi-Version Concurrency Control) in PostgreSQL implies that with every UPDATE or DELETE, old versions of rows are not immediately removed but are marked as 'dead tuples'. These dead tuples occupy space and must be cleaned up to free space for new data and prevent table bloat. The VACUUM process is responsible for this task.

By default, VACUUM does not reduce the physical size of the table file. It merely marks the freed space as available for reuse. This means that subsequent INSERT or UPDATE operations can occupy this space without increasing the file size.

CREATE TABLE t_test (id int) WITH (autovacuum_enabled = off);
INSERT INTO t_test SELECT * FROM generate_series(1, 100000);
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 MB

UPDATE t_test SET id = id + 1; -- Creates new row versions
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~7 MB (size doubled)

VACUUM t_test; -- Cleans dead tuples, but file size doesn't change
SELECT pg_size_pretty(pg_relation_size('t_test')); -- Still ~7 MB

Physical reduction of the table file size only occurs if VACUUM can 'truncate the tail' — that is, if the freed pages are at the end of the file. For example, after deleting a large number of rows with high ids:

DELETE FROM t_test WHERE id > 50000;
VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 MB (size reduced)

Automatic autovacuum is a critically important background process that constantly monitors changes in tables and runs VACUUM to maintain database health. Disabling it without a deep understanding of the consequences is an anti-pattern in modern PostgreSQL versions.

PostgreSQL Evolution: What's Changed in the Last 5 Years

While the basic principles of transactions and locks remain constant, PostgreSQL continuously evolves, offering improvements aimed at performance and usability:

• VACUUM Optimization: PostgreSQL 13 introduced parallel index processing during VACUUM, and PostgreSQL 16 added skipping pages unchanged since the last pass. These improvements significantly accelerated VACUUM operations on large tables, making manual management less relevant.
• REINDEX CONCURRENTLY (PG 12): Allows rebuilding indexes without blocking DML operations on the table, which is critical for high-load systems.
• Improved Lock Monitoring: System views pg_stat_activity and pg_locks have been augmented with new fields, and the pg_wait_sampling extension provides detailed wait statistics, simplifying performance problem diagnosis.

Key Takeaways:

• now() vs clock_timestamp(): now() fixes the transaction start time for consistency, clock_timestamp() provides the actual current time.
• Isolation Levels: PostgreSQL offers Read Committed (default), Repeatable Read (to prevent read anomalies), and Serializable (for maximum consistency with the risk of serialization errors).
• Locks: Despite MVCC, PostgreSQL uses write locks. Understanding the lock table and their conflicts is essential for optimizing concurrent access.
• Serializable and False Conflicts: The Serializable level can cause errors due to page-level predicate locks, requiring transactions to be retried.
• VACUUM and MVCC: VACUUM cleans dead tuples but typically doesn't reduce file size; it only marks space for reuse. Physical reduction occurs if freed pages are at the end of the file.

— Editorial Team