PostgreSQL: Transacciones, Bloqueos, Niveles de Aislamiento, MVCC, VACUUM

PostgreSQL: Una Inmersión Profunda en Transacciones, Bloqueos y Aislamiento Serializable

Los desarrolladores a menudo se encuentran con comportamientos no obvios en las consultas de PostgreSQL: bloqueos, resultados inesperados en UPDATE durante operaciones concurrentes o fallos de transacción. Comprender mecanismos internos como los niveles de aislamiento, los bloqueos y cómo funciona VACUUM es crucial para construir aplicaciones fiables y de alto rendimiento. Este artículo analiza a fondo estos conceptos, ilustrándolos con ejemplos prácticos de SQL, y destaca las mejoras recientes en PostgreSQL.

El Tiempo en las Transacciones: `now()` vs `clock_timestamp()`

PostgreSQL ofrece dos funciones principales para recuperar la hora actual, cuyo comportamiento difiere en el contexto de las transacciones. La función now() devuelve la hora de inicio de la transacción actual y permanece inalterada durante toda su ejecución. Esto garantiza la consistencia de las marcas de tiempo para las operaciones dentro de una única transacción, por ejemplo, al establecer created_at y updated_at.

BEGIN;
SELECT now();
-- ... otras operaciones ...
SELECT now(); -- Devolverá la misma hora que la primera llamada
SELECT clock_timestamp(); -- Puede diferir
COMMIT;

A diferencia de now(), la función clock_timestamp() proporciona la hora de ejecución actual real, que puede cambiar incluso dentro de una única consulta o transacción. Comprender esta diferencia ayuda a evitar errores al trabajar con datos temporales.

Niveles de Aislamiento de Transacciones en PostgreSQL

PostgreSQL presenta un modelo simplificado de niveles de aislamiento en comparación con otros SGBD, ofreciendo solo tres:

Read Committed: El nivel por defecto, donde cada comando dentro de una transacción ve una instantánea fresca de los datos. Las lecturas sucias son fundamentalmente imposibles en PostgreSQL, por lo que Read Uncommitted es equivalente a Read Committed.
Repeatable Read: Garantiza que todas las operaciones de lectura dentro de una transacción vean la misma instantánea de datos, fijada en el momento de la primera consulta. Esto evita lecturas fantasma y lecturas no repetibles.
Serializable: El nivel más estricto, garantizando que las transacciones que se ejecutan concurrentemente produzcan el mismo resultado que si se ejecutaran secuencialmente. Esto se logra a través del mecanismo de Aislamiento de Instantánea Serializable (SSI).

Una característica importante de PostgreSQL es su soporte para DDL (Lenguaje de Definición de Datos) dentro de transacciones. Esto permite crear, modificar o eliminar estructuras de datos y revertirlas si es necesario, mejorando la seguridad de las migraciones y los cambios de esquema complejos.

Mecanismos de Bloqueo y Control de Concurrencia

A pesar de utilizar el Control de Concurrencia Multiversión (MVCC), PostgreSQL emplea activamente bloqueos para operaciones de escritura. Cuando dos transacciones intentan actualizar la misma fila, la segunda transacción esperará a que la primera finalice. Considere este ejemplo:

-- Sesión 1:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- ... no confirmar ...

-- Sesión 2:
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- Esta consulta se quedará colgada, esperando que la Sesión 1 finalice.

La siguiente tabla demuestra los principales tipos de bloqueo en PostgreSQL, su propósito y los modos en conflicto:

| Tipo de Bloqueo | Cuándo se Adquiere | Entra en Conflicto Con |

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

| ACCESS SHARE | SELECT | ACCESS EXCLUSIVE |

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

| ROW EXCLUSIVE | INSERT, UPDATE, DELETE | SHARE y superiores |

| SHARE UPDATE EXCLUSIVE | CREATE INDEX CONCURRENTLY, ANALYZE, VACUUM | SHARE UPDATE EXCLUSIVE y superiores |

| SHARE | CREATE INDEX | ROW EXCLUSIVE y superiores |

| SHARE ROW EXCLUSIVE | CREATE TRIGGER, ALTER TABLE (algunos) | ROW SHARE y superiores |

| EXCLUSIVE | Permite solo lecturas | ROW SHARE y superiores |

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

Cuanto más estricto es el bloqueo, mayor es su número en la tabla y más amplio es el rango de operaciones en conflicto. Las operaciones con bloqueos en conflicto no pueden ejecutarse simultáneamente en el mismo objeto; una esperará. Es importante destacar que CREATE INDEX CONCURRENTLY permite construir índices sin bloquear operaciones DML, a diferencia del CREATE INDEX estándar.

Anomalías de Lectura: Datos Fantasma y No Repetibles

En el nivel de aislamiento Read Committed, pueden ocurrir anomalías de lectura, lo que lleva a resultados inconsistentes:

Lecturas Fantasma: Cuando una transacción ejecuta la misma consulta dos veces, y la segunda vez ve nuevas filas insertadas por otra transacción confirmada entre esas consultas.

```sql

-- Sesión 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT sum(value) FROM t_test2; -- Por ejemplo, 330

-- Sesión 2 (concurrentemente):

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

-- Sesión 1:

SELECT sum(value) FROM t_test2; -- Ahora 360, aparecieron nuevas filas.

COMMIT;

```

Lecturas No Repetibles: Cuando una transacción lee los mismos datos dos veces, y la segunda vez ve datos modificados, alterados por otra transacción confirmada.

```sql

-- Sesión 1:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Devuelve una fila

-- Sesión 2 (concurrentemente):

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

-- Sesión 1:

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- ¡Vacío! Los datos cambiaron.

COMMIT;

```

Para evitar estas anomalías, se utiliza el nivel de aislamiento Repeatable Read. En este nivel, una transacción opera sobre una instantánea de datos fija que no cambia hasta su finalización, independientemente de los cambios realizados por otras transacciones.

Peculiaridades del Nivel de Aislamiento Serializable

El nivel Serializable está diseñado para proporcionar el máximo aislamiento, simulando la ejecución secuencial de transacciones. Sin embargo, esto puede llevar a errores de serialización (ERROR: could not serialize access) si PostgreSQL detecta un conflicto potencial que podría violar el orden de ejecución. En tales casos, una de las transacciones será abortada y deberá reintentarse.

Un aspecto interesante de Serializable son los conflictos falsos, causados por bloqueos de predicado (SSI). PostgreSQL almacena estos bloqueos a nivel de página, no de filas individuales. Si dos transacciones trabajan con conjuntos de datos lógicamente diferentes, pero estos datos están físicamente ubicados en la misma página de disco (lo que a menudo ocurre con tablas pequeñas), puede ocurrir un conflicto de serialización, aunque lógicamente no lo haya. En tablas grandes, donde los datos se distribuyen en diferentes páginas, dichos conflictos son menos comunes.

Resolución de Problemas de Concurrencia

Consideremos un problema clásico de condición de carrera de datos: dos transacciones intentan modificar simultáneamente datos basándose en lecturas desactualizadas. Por ejemplo, Alice y Bob están de guardia, y cada uno quiere irse si al menos una persona permanece de servicio. Si ambas transacciones usan Repeatable Read y ven a dos personas de guardia, ambas se confirman con éxito, lo que lleva a que nadie quede de guardia.

Para resolver estos problemas, se utilizan los siguientes enfoques:

Serializable: La segunda transacción fallará con un error de serialización, y podrá reintentarse. Esto garantiza un resultado correcto, pero requiere manejo de errores en la aplicación.
SELECT ... FOR UPDATE: Usar FOR UPDATE durante una lectura bloquea explícitamente las filas seleccionadas, impidiendo que otras transacciones las modifiquen hasta que la actual finalice. La segunda sesión esperará hasta que la primera termine, y luego verá los datos actualizados.

```sql

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM d_test WHERE on_call = true FOR UPDATE; -- Bloquea filas

-- ... operaciones adicionales ...

COMMIT;

```

Operación atómica: Combinar la verificación de la condición y la acción en una única consulta. Si la condición no se cumple en el momento del UPDATE, la consulta no afectará a ninguna fila, eliminando la ventana de carrera.

```sql

UPDATE d_test

SET on_call = false

WHERE name = 'Alice'

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

```

Bloqueos Consultivos (Advisory Locks)

PostgreSQL proporciona un mecanismo de bloqueo consultivo (pg_advisory_lock, pg_advisory_xact_lock) que permite controlar el acceso concurrente a recursos arbitrarios no directamente vinculados a tablas o filas. Estos bloqueos operan sobre valores enteros y pueden usarse para coordinar procesos.

pg_advisory_lock: Bloqueo a nivel de sesión. Persiste hasta que se libera explícitamente (pg_advisory_unlock) o hasta que la conexión se cierra.
pg_advisory_xact_lock: Bloqueo a nivel de transacción. Se libera automáticamente al hacer COMMIT o ROLLBACK.

Es importante recordar que los bloqueos consultivos solo funcionan dentro de una única base de datos. Para sistemas distribuidos con múltiples instancias de PostgreSQL, se requieren soluciones externas (por ejemplo, Redis, ZooKeeper, etcd).

El Papel de VACUUM en MVCC

MVCC (Control de Concurrencia Multiversión) en PostgreSQL implica que con cada UPDATE o DELETE, las versiones antiguas de las filas no se eliminan inmediatamente, sino que se marcan como 'tuplas muertas'. Estas tuplas muertas ocupan espacio y deben limpiarse para liberar espacio para nuevos datos y evitar la hinchazón de la tabla. El proceso VACUUM es responsable de esta tarea.

Por defecto, VACUUM no reduce el tamaño físico del archivo de la tabla. Simplemente marca el espacio liberado como disponible para su reutilización. Esto significa que las operaciones INSERT o UPDATE posteriores pueden ocupar este espacio sin aumentar el tamaño del archivo.

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; -- Crea nuevas versiones de filas
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~7 MB (el tamaño se duplicó)

VACUUM t_test; -- Limpia tuplas muertas, pero el tamaño del archivo no cambia
SELECT pg_size_pretty(pg_relation_size('t_test')); -- Todavía ~7 MB

La reducción física del tamaño del archivo de la tabla solo ocurre si VACUUM puede 'truncar la cola', es decir, si las páginas liberadas están al final del archivo. Por ejemplo, después de eliminar un gran número de filas con ids altos:

DELETE FROM t_test WHERE id > 50000;
VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 MB (el tamaño se redujo)

El autovacuum automático es un proceso en segundo plano de importancia crítica que monitorea constantemente los cambios en las tablas y ejecuta VACUUM para mantener la salud de la base de datos. Deshabilitarlo sin una comprensión profunda de las consecuencias es un antipatrón en las versiones modernas de PostgreSQL.

Evolución de PostgreSQL: Qué ha Cambiado en los Últimos 5 Años

Si bien los principios básicos de transacciones y bloqueos permanecen constantes, PostgreSQL evoluciona continuamente, ofreciendo mejoras orientadas al rendimiento y la usabilidad:

Optimización de VACUUM: PostgreSQL 13 introdujo el procesamiento paralelo de índices durante VACUUM, y PostgreSQL 16 añadió la omisión de páginas sin cambios desde la última pasada. Estas mejoras aceleraron significativamente las operaciones de VACUUM en tablas grandes, haciendo que la gestión manual sea menos relevante.
REINDEX CONCURRENTLY (PG 12): Permite reconstruir índices sin bloquear operaciones DML en la tabla, lo cual es crítico para sistemas de alta carga.
Monitorización de Bloqueos Mejorada: Las vistas del sistema pg_stat_activity y pg_locks se han ampliado con nuevos campos, y la extensión pg_wait_sampling proporciona estadísticas detalladas de espera, simplificando el diagnóstico de problemas de rendimiento.

Puntos Clave:

now() vs clock_timestamp(): now() fija la hora de inicio de la transacción para consistencia, clock_timestamp() proporciona la hora actual real.
Niveles de Aislamiento: PostgreSQL ofrece Read Committed (por defecto), Repeatable Read (para prevenir anomalías de lectura) y Serializable (para máxima consistencia con el riesgo de errores de serialización).
Bloqueos: A pesar de MVCC, PostgreSQL utiliza bloqueos de escritura. Comprender la tabla de bloqueos y sus conflictos es esencial para optimizar el acceso concurrente.
Serializable y Conflictos Falsos: El nivel Serializable puede causar errores debido a bloqueos de predicado a nivel de página, requiriendo que las transacciones sean reintentadas.
VACUUM y MVCC: VACUUM limpia tuplas muertas pero típicamente no reduce el tamaño del archivo; solo marca espacio para su reutilización. La reducción física ocurre si las páginas liberadas están al final del archivo.