Concurrencia en PostgreSQL

Escritura concurrente en PostgreSQL

Lo principal. No hagas «leer en código — verificar — escribir»: en read committed esto pierde los cambios de otros. Elige el medio según el tipo de invariante:

Invariante	Medio
Cambiar una fila si la condición sigue siendo verdadera	un solo UPDATE ... WHERE <condición> + verificar número de filas
Leer, decidir en código, escribir la misma fila	SELECT ... FOR NO KEY UPDATE y escritura en una transacción
Sin duplicado	índice único + INSERT ... ON CONFLICT
Los intervalos no se superponen	EXCLUDE USING gist (btree_gist)
Regla sobre un conjunto de filas que no se puede expresar como restricción	serializable en todos los participantes + reintento
Cola de tareas	FOR UPDATE SKIP LOCKED
Un solo manejador para todos	pg_advisory_xact_lock

La restricción en la base de datos es más confiable que en el código: mantiene el invariante en cualquier nivel de aislamiento y con cualquier cliente. El código Go de transacciones y reintentos está en el skill pgx-txmanager. La verificación de afirmaciones está en CHECKS.md.

Niveles de aislamiento

• Read committed (por defecto): cada comando ve una instantánea fresca. UPDATE en una fila ocupada espera la transacción ajena, luego relee la fila y vuelve a verificar WHERE.
• Repeatable read y serializable no releen: en caso de conflicto, error 40001 («could not serialize access»); serializable lo da con más frecuencia. Si subes el nivel, escribe inmediatamente el reintento de toda la transacción, de lo contrario el usuario recibirá un error. El 40001 puede llegar incluso en COMMIT.
• La instantánea de repeatable read se construye con el primer comando después de BEGIN, no con el propio BEGIN. El momento es importante: haz la lectura como primera consulta.
• Serializable garantiza el resultado solo entre transacciones serializable. Si proteges un invariante con él, todas las transacciones que lo tocan deben ir en serializable.
• Dentro de serializable, mira EXPLAIN. Seq Scan toma un bloqueo de predicado en toda la tabla y aumenta la proporción de 40001; la lectura por índice bloquea solo las filas leídas y las páginas del índice. Crea los índices necesarios. En una tabla pequeña, Seq Scan es normal.

Cambiar si la condición es verdadera: condición en UPDATE

Pasa la acción a la base de datos, no el número calculado: SET sent = sent + 1, SET left = left - $1 WHERE left >= $1, WHERE id = $1 AND status = 'free'. Cero filas afectadas (RowsAffected() == 0 o pgx.ErrNoRows con RETURNING) significa que la condición no se cumple o la fila no existe. Esto es un error de dominio, no un éxito.

tag, err := q.Exec(ctx,
	`UPDATE seats SET status = 'booked', customer_id = $2
	 WHERE id = $1 AND status = 'free'`,
	seatID, customerID,
)
if err != nil {
	return fmt.Errorf("book seat %d: %w", seatID, err)
}
if tag.RowsAffected() == 0 { // el asiento ya está ocupado o no existe
	return fmt.Errorf("book seat %d: %w", seatID, ErrConflict)
}

En repeatable read y serializable, el mismo conflicto termina en 40001 y requiere reintento.

Leer y escribir la misma fila

SELECT ... FOR NO KEY UPDATE (el modo está en la sección «Modo de bloqueo») y escritura en una misma transacción (trManager.Do). En autocommit, el bloqueo se libera inmediatamente después del SELECT. Una fila que aún no existe no está protegida por el bloqueo: la inserción de un duplicado solo la detiene un índice único o una restricción.

Decisión sobre un conjunto de filas (write skew)

«El ejecutor está libre», «la reserva cabe en el intervalo», «queda un guardia en el turno»: dos transacciones leen la misma instantánea, cada una escribe su fila, y ambas confirman. En read committed, esto produce dos reservas superpuestas. Repeatable read no salva: solo detecta escritura en la misma fila.

• Decisión sobre filas existentes: bloquea (FOR NO KEY UPDATE / FOR UPDATE) exactamente las filas sobre las que decides.
• Decisión sobre la ausencia de una fila («no hay reserva superpuesta»): no hay nada que bloquear. Se necesita una restricción:

  • duplicado por valor: índice único;

  • superposición de intervalos: restricción de exclusión (exclusion constraint). En read committed, la segunda transacción espera a la primera y obtiene 23P01, sin serializable y sin reintentos:

    CREATE EXTENSION IF NOT EXISTS btree_gist; -- trusted: basta con permiso CREATE en la base de datos
    ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
        EXCLUDE USING gist (resource_id WITH =, during WITH &&); -- during tstzrange
    

    23P01 (exclusion_violation) se convierte en un error de dominio de conflicto.

• La regla no se puede expresar con una restricción: serializable para todos los participantes y reintento ante 40001.

Modo de bloqueo de fila

Bloqueas una fila para leerla y actualizarla, y no cambias columnas con índice único apto para clave externa (PK, claves únicas; los índices parciales y los índices por expresión no cuentan) — usa FOR NO KEY UPDATE. FOR UPDATE déjalo para DELETE y cambio de dichas columnas. Por qué: la verificación de clave externa al insertar una fila hija toma FOR KEY SHARE en el padre. Este entra en conflicto con FOR UPDATE pero no con FOR NO KEY UPDATE. Un FOR UPDATE innecesario en la fila padre (cliente, recurso, pedido) detiene la inserción de filas que la referencian.

Cola de tareas

Varios workers toman una tarea con un solo operador:

UPDATE tasks
SET status = 'running', taken_at = now()
WHERE id = (
	SELECT id FROM tasks
	WHERE status = 'ready'
	ORDER BY id
	LIMIT 1
	FOR UPDATE SKIP LOCKED
)
RETURNING id

• SKIP LOCKED omite las filas ocupadas, y los workers no esperan entre sí. El par «SELECT ... FOR UPDATE SKIP LOCKED, luego UPDATE» en una misma transacción también es correcto. No se puede hacer un SELECT sin bloqueo y un UPDATE separado: dos workers tomarían la misma tarea.
• ORDER BY en la subconsulta es para un orden predecible.
• Un worker puede fallar con la tarea en sus manos. Práctica: plazo de arrendamiento según taken_at — una tarea en running por más del plazo se considera nuevamente lista.
• SKIP LOCKED da una visión inconsistente de los datos — solo para colas.

No quedarse colgado en un bloqueo ajeno

Por defecto, una consulta espera un bloqueo indefinidamente, y el usuario ve la aplicación congelada.

• SET LOCAL lock_timeout dentro de la transacción, no como parámetro de sesión: la conexión del pool luego irá a otras consultas. El timeout actúa en cada espera de bloqueo, no en toda la transacción.
• UPDATE y DELETE no tienen NOWAIT. Si necesitas un error instantáneo, primero haz SELECT ... FOR UPDATE NOWAIT sobre esas filas en la misma transacción.
• NOWAIT y SKIP LOCKED afectan solo a filas. Un bloqueo de tabla lo espera la consulta como siempre, por ejemplo tras ALTER TABLE. Por eso lock_timeout es necesario incluso con ellos.
• El error en estos casos es 55P03 (lock_not_available). Reintenta toda la transacción.

return s.trManager.Do(ctx, func(ctx context.Context) error {
	q := s.getter.DefaultTrOrDB(ctx, s.pool)
	// constante sin entrada; ajusta el valor según la operación
	if _, err := q.Exec(ctx, `SET LOCAL lock_timeout = '1s'`); err != nil {
		return err
	}
	return s.repo.MoveBooking(ctx, from, to)
})

La cola de espera no es justa. Nuevos FOR SHARE / FOR KEY SHARE pueden adelantarse a un UPDATE que espera en una fila caliente, y un flujo de dichos bloqueos lo mantiene esperando indefinidamente; el orden entre los que esperan no está garantizado. Para filas calientes (contador compartido, stock de un producto popular), pon lock_timeout y reintenta la transacción.

Análisis de un bloqueo. Los bloqueos de fila no se ven en pg_locks: el proceso en espera se ve allí como esperando el transactionid del poseedor. Quién está bloqueando lo muestra pg_blocking_pids(pid) (no lo llames en un bucle frecuente). Los bloqueos de fila de una tabla los muestra la extensión pgrowlocks de contrib (la instala el superusuario).

Bloqueos de asesoramiento (Advisory locks)

«Solo un manejador» (worker único de outbox, migrador) — pg_advisory_xact_lock dentro de una transacción: el candado se libera automáticamente al hacer commit o rollback.

const advisoryOutboxRelay int64 = 1 // clave fija del subsistema

err := s.trManager.Do(ctx, func(ctx context.Context) error {
	q := s.getter.DefaultTrOrDB(ctx, s.pool)
	if _, err := q.Exec(ctx, `SELECT pg_advisory_xact_lock($1)`, advisoryOutboxRelay); err != nil {
		return err
	}
	return s.relayBatch(ctx) // un lote; el candado se libera con la transacción
})

• pg_advisory_lock a nivel de sesión — solo en una conexión dedicada tomada con pool.Acquire; libéralo en la misma conexión tantas veces como lo hayas adquirido. El candado de sesión no se libera ni con COMMIT ni con rollback, y la misma sesión puede volver a tomarlo. En un pool, la conexión regresa con el candado, el unlock va a otra conexión, y el candado se «fuga».
• No mantengas una transacción abierta por un candado en un worker infinito: toma un candado xact por cada lote de trabajo. Para un líder de larga duración, usa un candado de sesión en una conexión dedicada.
• La clave es una constante numérica fija por subsistema o un par (int, int). No uses hashtext: la función no está documentada, el hash de 32 bits produce colisiones.

Funciones en consultas

Una función en plpgsql o SQL que lee tablas y se llama en una consulta junto con la lectura de tablas, márcala como STABLE. VOLATILE toma una nueva instantánea para cada consulta interna, y en read committed los datos de la tabla y de la función provendrían de momentos diferentes. IMMUTABLE solo para funciones que no leen tablas; VOLATILE para funciones con efectos secundarios.

Códigos de error

Código	Qué es	Qué hacer
40001 serialization_failure	conflicto en repeatable read / serializable	reintentar toda la transacción
40P01 deadlock_detected	bloqueo mutuo	reintentar toda la transacción; tomar los bloqueos en el mismo orden
55P03 lock_not_available	NOWAIT, lock_timeout	reintentar toda la transacción o devolver «ocupado»
23505 unique_violation	duplicado	conflicto de dominio; duplicado esperado — ON CONFLICT
23P01 exclusion_violation	superposición por restricción de exclusión	conflicto de dominio