SELECT ... FOR UPDATE: bloqueio pessimista de linhas no SQL

SELECT ... FOR UPDATE adquire um bloqueio pessimista sobre as linhas que retorna: enquanto sua transacao estiver aberta, ninguem mais podera atualiza-las ou apaga-las. E a ferramenta certa quando voce le um valor, calcula algo a partir dele e grava de volta, e corridas entre transacoes nao sao aceitaveis.

Por que bloquear linhas

O bug classico e ler-modificar-gravar: ler um saldo, calcular um valor novo no codigo da aplicacao e grava-lo. Entre a leitura e a gravacao, outra transacao faz a mesma coisa e uma das atualizacoes se perde silenciosamente.

FOR UPDATE resolve isso bloqueando as linhas no momento da leitura. O bloqueio e mantido ate COMMIT ou 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;

Qualquer outra transacao que tente usar FOR UPDATE nas mesmas linhas espera ate o seu COMMIT. Um SELECT simples sem bloqueio continua vendo a versao antiga da linha (MVCC) e nao bloqueia.

FOR UPDATE versus FOR SHARE

O PostgreSQL oferece varios niveis de bloqueio:

• FOR UPDATE e exclusivo: ninguem mais pode bloquear para leitura nem modificar a linha.
• FOR SHARE e compartilhado: varias transacoes podem mante-lo ao mesmo tempo, mas nenhuma pode fazer UPDATE/DELETE. Util para congelar uma linha pai enquanto voce insere filhas.
• FOR NO KEY UPDATE e FOR KEY SHARE sao variantes mais fracas que conflitam menos com as verificacoes de chave estrangeira.

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 e SKIP LOCKED

Por padrao, uma linha bloqueada faz voce esperar. Dois modificadores mudam isso:

• NOWAIT nao espera nada: se a linha estiver ocupada, a consulta falha imediatamente com um erro. Bom para operacoes interativas, onde retornar "tente de novo" rapido e melhor que travar.
• SKIP LOCKED pula as linhas ocupadas e retorna apenas as livres. E o cavalo de batalha das filas de tarefas: cada worker pega o seu lote sem atrapalhar os outros.

-- 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 dinheiro e ordem de bloqueio

Uma transferencia toca duas linhas, e e ai que os deadlocks se escondem: a transacao A bloqueia a linha 1 e depois a 2; a transacao B faz o inverso. Cada uma espera a outra para sempre.

A cura e simples: bloqueie sempre as linhas na mesma ordem, por exemplo id crescente. Um IN (1, 2) com ORDER BY garante uma ordem deterministica.

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;

Pegadinhas e letras miudas:

• FOR UPDATE nao pode ser usado com agregados ou GROUP BY: nao ha linhas concretas a bloquear.
• Com um JOIN, por padrao as linhas de todas as tabelas sao bloqueadas; use FOR UPDATE OF orders para bloquear apenas a que voce quer.
• Uma transacao longa mantem seus bloqueios o tempo todo: nunca chame uma API externa entre FOR UPDATE e COMMIT.
• SKIP LOCKED pode retornar menos linhas que o LIMIT: e o preco da concorrencia, e em filas isso e esperado.

Quando optar pela abordagem otimista

Bloqueios pessimistas tem custo: as transacoes formam fila umas atras das outras. Se os conflitos sao raros, a abordagem otimista sai mais barata: adicione uma coluna version e verifique-a na gravacao.

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

Se o UPDATE reportar 0 linhas, alguem chegou antes de voce: releia e tente de novo. Regra pratica: muita contencao sobre as mesmas linhas favorece FOR UPDATE; conflitos raros sob alto paralelismo favorecem a coluna de versao otimista.

Notas por motor: o MySQL (InnoDB) suporta FOR UPDATE, FOR SHARE, NOWAIT e SKIP LOCKED, mas sua semantica de bloqueio (gap locks) e diferente. O ClickHouse e um banco analitico sem bloqueios de linha nem transacoes no sentido usual, entao essas clausulas nao existem la.