SELECT ... FOR UPDATE: bloqueo pesimista de filas en SQL

SELECT ... FOR UPDATE toma un bloqueo pesimista sobre las filas que devuelve: mientras tu transaccion siga abierta, nadie mas podra actualizarlas ni borrarlas. Es la herramienta de cabecera cuando lees un valor, calculas algo a partir de el y lo vuelves a escribir, y las carreras entre transacciones no son aceptables.

Por que bloquear filas

El error clasico es leer-modificar-escribir: lees un saldo, calculas un valor nuevo en el codigo de la aplicacion y lo escribes. Entre la lectura y la escritura, otra transaccion hace lo mismo y una de las actualizaciones se pierde sin avisar.

FOR UPDATE lo resuelve bloqueando las filas en el momento de la lectura. El bloqueo se mantiene hasta COMMIT o ROLLBACK.

BEGIN;
SELECT id, amount FROM orders WHERE user_id = 42 AND status = 'pending' FOR UPDATE;
-- rows are now locked until COMMIT
UPDATE orders SET status = 'paid' WHERE user_id = 42 AND status = 'pending';
COMMIT;

Cualquier otra transaccion que intente tomar FOR UPDATE sobre las mismas filas espera hasta tu COMMIT. Un SELECT normal sin bloqueo sigue viendo la version antigua de la fila (MVCC) y no se bloquea.

FOR UPDATE frente a FOR SHARE

PostgreSQL ofrece varias intensidades de bloqueo:

• FOR UPDATE es exclusivo: nadie mas puede bloquear para lectura ni modificar la fila.
• FOR SHARE es compartido: muchas transacciones pueden mantenerlo a la vez, pero ninguna puede hacer UPDATE/DELETE. Util para congelar una fila padre mientras insertas hijas.
• FOR NO KEY UPDATE y FOR KEY SHARE son variantes mas debiles que chocan menos con las comprobaciones de claves foraneas.

BEGIN;
-- freeze the user row while we add an order for them
SELECT id FROM users WHERE id = 42 FOR SHARE;
INSERT INTO orders (user_id, amount, status) VALUES (42, 200, 'pending');
COMMIT;

NOWAIT y SKIP LOCKED

Por defecto, una fila bloqueada te hace esperar. Dos modificadores cambian eso:

• NOWAIT no espera nada: si la fila esta ocupada, la consulta falla de inmediato con un error. Ideal para operaciones interactivas donde devolver "intenta de nuevo" rapido es mejor que quedarse colgado.
• SKIP LOCKED se salta las filas ocupadas y devuelve solo las libres. Es el caballo de batalla de las colas de tareas: cada worker reclama su lote sin pisar a los demas.

-- worker pulls the next free job, ignoring rows other workers already took
BEGIN;
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- process it, then mark done
UPDATE orders SET status = 'processing' WHERE id = :id;
COMMIT;

Transferencia de dinero y orden de bloqueos

Una transferencia toca dos filas, y ahi se esconden los interbloqueos: la transaccion A bloquea la fila 1 y luego la 2; la transaccion B lo hace al reves. Cada una espera a la otra para siempre.

La cura es simple: bloquea siempre las filas en el mismo orden, por ejemplo id ascendente. Un IN (1, 2) con ORDER BY garantiza un orden determinista.

BEGIN;
-- always lock in a deterministic order to avoid deadlocks
SELECT id, amount FROM orders
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

UPDATE orders SET amount = amount - 200 WHERE id = 1;
UPDATE orders SET amount = amount + 200 WHERE id = 2;
COMMIT;

Trampas y letra pequena:

• FOR UPDATE no se puede usar con agregados ni GROUP BY: no hay filas concretas que bloquear.
• Con un JOIN, por defecto se bloquean las filas de todas las tablas; usa FOR UPDATE OF orders para bloquear solo la que quieres.
• Una transaccion larga mantiene sus bloqueos todo ese tiempo: nunca llames a una API externa entre FOR UPDATE y COMMIT.
• SKIP LOCKED puede devolver menos filas que LIMIT: es el precio de la concurrencia, y en las colas es lo esperado.

Cuando elegir el enfoque optimista

Los bloqueos pesimistas cuestan: las transacciones hacen cola unas detras de otras. Si los conflictos son raros, el enfoque optimista es mas barato: anade una columna version y verificala al escribir.

-- optimistic update: succeeds only if nobody changed the row meanwhile
UPDATE orders
SET amount = 300, version = version + 1
WHERE id = 1 AND version = 7;

Si el UPDATE informa 0 filas, alguien se te adelanto: vuelve a leer y reintenta. Regla practica: mucha contencion sobre las mismas filas favorece FOR UPDATE; conflictos raros con paralelismo intenso favorecen la columna de version optimista.

Notas por motor: MySQL (InnoDB) admite FOR UPDATE, FOR SHARE, NOWAIT y SKIP LOCKED, pero su semantica de bloqueo (gap locks) difiere. ClickHouse es una base analitica sin bloqueos a nivel de fila ni transacciones en el sentido habitual, asi que estas clausulas no existen alli.