Contador atomico: UPDATE SET n = n + 1 sem perder atualizacoes

Um contador parece o registro mais simples de um banco de dados: um numero que voce precisa incrementar. No entanto, e justamente nos contadores que a correcao quebra sob concorrencia, porque o codigo ingenuo le o valor, soma um e escreve de volta em tres passos separados. O banco consegue fazer os tres de forma atomica em um unico comando.

O bug da atualizacao perdida

Considere uma tabela de contadores de visualizacoes por usuario:

CREATE TABLE counters (
    id      bigint PRIMARY KEY,
    n       bigint NOT NULL DEFAULT 0
);

O codigo tipico da aplicacao e um read-modify-write:

-- step 1: read
SELECT n FROM counters WHERE id = 1;   -- got 41
-- step 2: app adds 1 in memory -> 42
-- step 3: write back
UPDATE counters SET n = 42 WHERE id = 1;

Suponha que dois processos leiam 41 ao mesmo tempo. Os dois somam um e os dois escrevem 42. Dois incrementos viram um so: essa e a atualizacao perdida. Sob carga o contador fica sistematicamente atrasado, e nenhum try/catch pega isso: nao ha erro, apenas incrementos descartados em silencio.

Um comando em vez de tres

A solucao e empurrar a leitura, a aritmetica e a escrita para o proprio UPDATE:

UPDATE counters SET n = n + 1 WHERE id = 1;

Aqui n + 1 e avaliado sobre o valor atual da linha no momento da escrita, nao sobre o que a aplicacao leu um segundo atras. O PostgreSQL pega um bloqueio de linha: um segundo UPDATE concorrente espera o primeiro confirmar e depois soma sobre o valor ja atualizado. Dois comandos dao +2, como deve ser. A atomicidade e uma propriedade do comando unico, nao da sua disciplina.

O mesmo truque vale para qualquer total acumulado, como a soma de pedidos de um cliente:

UPDATE users
SET orders_total = orders_total + (
    SELECT amount FROM orders WHERE id = 5001
)
WHERE id = 42;

Upsert: criar ou incrementar

Muitas vezes a linha do contador ainda nao existe: o primeiro evento deve cria-la e os seguintes incrementa-la. INSERT ... ON CONFLICT resolve os dois casos em um comando:

INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = counters.n + 1;

• Se a linha nao existe, ela e inserida com 1.
• Se existe, o ramo DO UPDATE dispara, onde counters.n aponta para o valor atual da tabela.
• Pegadinha: nao escreva SET n = EXCLUDED.n, porque EXCLUDED e a insercao rejeitada (1), e o contador ficaria preso em um para sempre. Voce precisa de counters.n + 1.

Esse upsert tambem e atomico e seguro contra corridas: insercoes concorrentes do mesmo id se serializam no indice unico.

Retornar o novo valor com RETURNING

Depois de incrementar, costuma ser preciso o resultado, por exemplo a nova posicao em uma fila. Um SELECT separado reabriria a janela de corrida. O RETURNING devolve o valor do mesmo comando atomico:

UPDATE counters
SET n = n + 1
WHERE id = 1
RETURNING n;

Voce recebe o valor que a sua propria transacao atribuiu, sem reler. Funciona com o upsert tambem:

INSERT INTO counters (id, n)
VALUES (1, 1)
ON CONFLICT (id)
DO UPDATE SET n = counters.n + 1
RETURNING n;

Pontos quentes e limites

A atomicidade resolve a correcao, mas nao elimina por magica a contencao. Se toda requisicao bate na mesma linha, essa linha vira um ponto quente: as transacoes formam fila atras do bloqueio de linha, e a vazao fica limitada por uma unica escrita.

• Pegadinha: uma transacao longa que incrementa o contador e depois faz mais trabalho segura o bloqueio de linha ate o COMMIT. Incremente o mais tarde possivel e confirme rapido.
• Para contadores muito quentes, fragmente-os: N linhas parciais (id, shard, n), escreva em um shard aleatorio e leia com SUM(n). Isso espalha a contencao por varias linhas.
• Se voce so precisa de um numero unico que cresce de forma monotona e nao de uma contagem exata, uma SEQUENCE (ou GENERATED AS IDENTITY) e mais barata: ela nao bloqueia nem desfaz, mas permite lacunas na numeracao.

Diferencas entre motores: no MySQL/InnoDB, UPDATE ... SET n = n + 1 e atomico do mesmo jeito, e o upsert se escreve como INSERT ... ON DUPLICATE KEY UPDATE n = n + 1; nao ha RETURNING, entao usam-se truques com LAST_INSERT_ID() ou um novo SELECT na mesma transacao. O ClickHouse e um motor analitico: UPDATEs pontuais sao caros la, entao os contadores costumam ser construidos com SummingMergeTree ou AggregatingMergeTree, onde os valores sao somados quando as partes se fundem em vez de por linha.

A conclusao e simples: nunca calcule o incremento na aplicacao. Deixe o banco fazer a leitura, a soma e a escrita em uma unica expressao n = n + 1, e o contador seguira correto sob qualquer concorrencia.