Triggers, transações e concorrência

A sessão S12 tem um lugar livre e dois compradores tentam reservá-lo. Cada compra precisa de reduzir a capacidade e criar o bilhete em conjunto. Uma transação agrupa essas operações; um trigger executa uma rotina em resposta a uma alteração. Nenhum dos dois, sozinho, coordena automaticamente as duas compras.

Para estudar essa coordenação, acrescentamos livres a sessoes. Inicialmente vale lotacao; depois deve ser igual a lotacao menos a quantidade de bilhetes ativos, reservados ou pagos. Todos os caminhos de compra, cancelamento e alteração de lotação têm de manter essa invariante na mesma transação.

ACID e o que cada letra promete

BEGIN;
UPDATE sessoes SET livres = livres - 1 WHERE id = 12 AND livres > 0;
-- Verificar que foi alterada exatamente uma linha antes de continuar.
INSERT INTO bilhetes(utilizador_id,sessao_id,codigo,estado)
VALUES (7,12,'B-1043','reservado');
COMMIT;

Se o UPDATE afetar zero linhas, faz ROLLBACK e responde «esgotado». Se o INSERT falhar, anula também a redução dos lugares. SAVEPOINT nome e ROLLBACK TO nome permitem anular uma parte sem terminar a transação inteira. Um erro de instrução em PostgreSQL deixa normalmente a transação em estado de erro até ser anulada ou recuperada com um savepoint apropriado.

BEGIN;
UPDATE sessoes SET livres=livres-1 WHERE id=12 AND livres>0;
SELECT livres FROM sessoes;
ROLLBACK;
SELECT livres FROM sessoes;
BEGIN;
UPDATE sessoes SET livres=livres-1 WHERE id=12 AND livres>0;
SAVEPOINT depois_do_contador;
INSERT INTO bilhetes VALUES(1);
ROLLBACK TO depois_do_contador;
COMMIT;
SELECT livres, (SELECT COUNT(*) FROM bilhetes) AS bilhetes FROM sessoes;

CREATE TABLE sessoes(id INTEGER PRIMARY KEY,livres INTEGER CHECK(livres>=0));
CREATE TABLE bilhetes(id INTEGER PRIMARY KEY);
INSERT INTO sessoes VALUES(12,1);

O programa mostra um contador a regressar de 0 para 1 após ROLLBACK. Na segunda transação, a inserção de um bilhete é anulada até ao savepoint, mas a alteração anterior confirma-se. O último resultado tem zero lugares e zero bilhetes. Esse estado ilustra que atomicidade não escolhe a invariante por nós, ou seja, o agrupamento das operações tem de corresponder à regra. O bloco usa uma só ligação PostgreSQL, por isso não demonstra transações concorrentes.

Um COUNT dentro de um trigger pode falhar

Imagina uma sessão de lotação 1, sem bilhetes. Um trigger faz COUNT(*), vê zero e permite inserir. Duas transações podem executar:

Ficaram dois bilhetes para um lugar. Cada trigger correu dentro de uma transação, mas as leituras não reservaram a capacidade. Um CHECK numa linha também não deve consultar outras linhas para tentar resolver este caso.

Coordenar a alteração da mesma linha

Em PostgreSQL, no isolamento padrão Read Committed, este UPDATE é uma forma de disputar um contador único:

UPDATE sessoes
SET livres = livres - 1
WHERE id = 12 AND livres > 0
RETURNING id, livres;

A primeira compra bloqueia e altera a linha. A segunda espera. Se a primeira confirmar, o PostgreSQL volta a avaliar a condição sobre a versão atual da linha: livres já é zero, logo a segunda não altera nenhuma linha. Se a primeira anular, a segunda pode reservar esse lugar. O RETURNING permite distinguir esses resultados.

O tempo avança de cima para baixo. A espera impede B de tomar uma decisão com o contador anterior. Só depois da confirmação de A é que B conclui a sua tentativa.

O esquema pode ter CHECK (livres BETWEEN 0 AND lotacao), mas o contador só coincide com os bilhetes se todas as compras, cancelamentos e alterações de lotação seguirem o mesmo protocolo. Criar um bilhete por outro caminho sem diminuir livres quebra a regra. A inserção e o contador pertencem à mesma transação; alterações diretas exigem permissões limitadas ou uma operação central na base de dados.

Outra estratégia é bloquear primeiro a sessão com SELECT ... FOR UPDATE e só depois contar e inserir. Todos os escritores têm de obter esse mesmo bloqueio, dentro da mesma transação que decide e escreve: fora de uma transação, cada instrução confirma de imediato e o bloqueio é libertado antes da escrita seguinte. Em Read Committed, a contagem deve ser uma instrução posterior à aquisição do bloqueio, para obter um snapshot atualizado. Um bloqueio num sítio que os outros caminhos ignoram não coordena nada, e bloquear sem depois atualizar a linha não impede que outra transação a altere quando o bloqueio terminar.

Snapshots e níveis de isolamento

Uma dirty read lê uma alteração não confirmada. Uma non-repeatable read volta a ler uma linha e encontra um valor confirmado diferente. Um phantom é uma alteração no conjunto de linhas que satisfaz o mesmo predicado. Write skew acontece quando duas transações leem uma condição comum e alteram linhas diferentes, preservando a condição só quando analisadas isoladamente.

No exemplo de dois médicos de serviço, ambos leem que há dois e cada um retira o seu próprio turno. Um snapshot estável não basta para impedir que fiquem zero. Serializable pode detetar o conflito e exigir uma nova tentativa. Ao receber um erro de serialização, repete a transação completa com um novo snapshot, não só a última instrução.

Para seguir execuções concorrentes e identificar as anomalias e as garantias de cada nível de isolamento, pergunta «que leituras consegue cada transação ver?»: pausa em cada entrelaçamento e decide se o resultado final seria possível numa execução sequencial.

CMU Database Group, Concurrency Control Theory, 2024Carregar vídeo do YouTube

Abrir no YouTube

Deadlock não é uma espera qualquer

A bloqueia a linha 1 e pede a 2; B bloqueia a 2 e pede a 1. Há um ciclo de espera, um deadlock. PostgreSQL deteta o ciclo e aborta uma das transações. Adquirir os mesmos recursos numa ordem consistente reduz esse risco. Mantém transações curtas e prepara a aplicação para repetir uma transação abortada quando isso for seguro.

Triggers: momento, unidade e retorno

• BEFORE: pode validar ou alterar NEW antes de guardar. Num trigger por linha, devolver NULL pode cancelar a operação dessa linha.
• AFTER: observa a alteração concluída; continua dentro da transação, antes do COMMIT. O retorno de um trigger AFTER por linha é ignorado.
• INSTEAD OF: substitui a operação, por exemplo numa vista suportada por um trigger por linha.
• FOR EACH ROW: dispara uma vez por linha afetada. FOR EACH STATEMENT: uma vez por instrução, mesmo quando nenhuma linha é afetada.

NEW está disponível na inserção; OLD na eliminação; ambos na atualização. Não uses OLD num INSERT. Uma exceção no trigger pode anular a instrução; as alterações do trigger pertencem à mesma transação.

BEGIN;
CREATE FUNCTION auditar_preco() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.preco IS DISTINCT FROM OLD.preco THEN
    INSERT INTO historico_preco(sessao_id,preco_anterior,preco_novo)
    VALUES (NEW.id,OLD.preco,NEW.preco);
  END IF;
  RETURN NEW;
END;
$$;
CREATE TRIGGER preco_alterado
AFTER UPDATE OF preco ON sessoes
FOR EACH ROW EXECUTE FUNCTION auditar_preco();

UPDATE sessoes SET preco = 15.00 WHERE id = 1;
SELECT sessao_id, preco_anterior, preco_novo FROM historico_preco;
COMMIT;
BEGIN;
UPDATE sessoes SET preco = 18.00 WHERE id = 1;
ROLLBACK;
SELECT preco FROM sessoes WHERE id = 1;
SELECT COUNT(*) AS alteracoes FROM historico_preco;

CREATE TABLE sessoes (id INTEGER PRIMARY KEY, preco NUMERIC(8,2));
CREATE TABLE historico_preco (
  sessao_id INTEGER REFERENCES sessoes(id),
  preco_anterior NUMERIC(8,2),
  preco_novo NUMERIC(8,2)
);
INSERT INTO sessoes VALUES (1, 12.50);

UPDATE OF preco depende das colunas mencionadas pela instrução, não de o valor ter mudado. O teste IS DISTINCT FROM distingue a mudança real e trata NULL de forma definida. A alteração de 12,50 para 15 euros cria uma linha no histórico. A alteração para 18 euros e a sua linha de histórico desaparecem com o ROLLBACK, por isso o preço final continua a ser 15 e há só uma alteração confirmada.

Uma transação da base de dados não anula um pagamento que já aconteceu num serviço externo. Pagamentos, emails e chamadas remotas precisam de um protocolo próprio: regista uma operação pendente e processa-a com identificação que impeça duplicados. E não mantenhas um bloqueio de linha enquanto esperas pela pessoa preencher um formulário.

Função, procedure e trigger

Uma rotina guardada na base de dados concentra código junto dos dados. Em PostgreSQL, uma função é usada numa expressão, enquanto uma procedure é chamada com CALL. Um trigger é ativado por um evento e executa uma função de trigger.

CREATE FUNCTION preco_com_taxa(p numeric) RETURNS numeric
LANGUAGE SQL IMMUTABLE AS $$ SELECT p * 1.23 $$;
SELECT preco_com_taxa(10);

CREATE TABLE mensagens_exemplo (texto text);
CREATE PROCEDURE registar_mensagem(p_texto text)
LANGUAGE SQL AS $$ INSERT INTO mensagens_exemplo VALUES (p_texto) $$;
CALL registar_mensagem('confirmado');
SELECT texto FROM mensagens_exemplo;

O exemplo PostgreSQL dá 12.30 e uma linha confirmado. CALL não é uma forma alternativa de invocar qualquer função. Uma procedure pode ter parâmetros de saída; uma função pode produzir efeitos, por isso «função calcula, procedure altera» não é uma definição segura.

Uma rotina pode reduzir viagens entre aplicação e base de dados e centralizar uma operação. Tem custos de testes, manutenção e dependência do dialeto ou linguagem, como PL/pgSQL. Não concluas que será sempre mais rápida nem que o código será reutilizável noutro SGBD. Procedures podem controlar transações apenas nas condições admitidas pelo PostgreSQL; colocar COMMIT numa função não o torna válido.

Identificadores gerados e sessão da base de dados

INSERT ... RETURNING id devolve o identificador da linha que acabaste de criar. Evita procurar MAX(id), que pode corresponder à inserção de outra ligação.

Uma coluna GENERATED ALWAYS AS IDENTITY usa uma sequência; depois de essa ligação obter um valor, currval('sessoes_id_seq') devolve o último valor que ela própria obteve, sem que outra ligação mude esse resultado. Antes de existir uma chamada nessa ligação, currval dá erro. A sessão da base de dados é aqui a ligação, não a sessão de um evento nem o cookie HTTP.

Uma sequência pode consumir números mesmo quando a transação é anulada. Os identificadores devem ser únicos, mas não se deve exigir que sejam consecutivos.

Cancelar sem libertar duas vezes

Retomemos a regra de cancelamento: só um bilhete reservado pode ser cancelado pelo seu comprador, e a transição liberta exatamente um lugar. Ler o estado, somar um lugar e só depois atualizar o bilhete permite que duas tentativas usem a mesma leitura antiga.

Liga a alteração do contador às linhas que acabaram de mudar de estado:

WITH cancelado AS (
 UPDATE bilhetes SET estado='cancelado'
 WHERE id=1043 AND utilizador_id=7 AND estado='reservado'
 RETURNING sessao_id
)
UPDATE sessoes s SET livres=s.livres+1
FROM cancelado c WHERE s.id=c.sessao_id
RETURNING s.id,s.livres;
SELECT id,estado FROM bilhetes ORDER BY id;

WITH cancelado AS (
 UPDATE bilhetes SET estado='cancelado'
 WHERE id=1043 AND utilizador_id=7 AND estado='reservado'
 RETURNING sessao_id
)
UPDATE sessoes s SET livres=s.livres+1
FROM cancelado c WHERE s.id=c.sessao_id
RETURNING s.id,s.livres;
SELECT id,estado FROM bilhetes ORDER BY id;

CREATE TABLE sessoes (
 id integer PRIMARY KEY,
 lotacao integer NOT NULL CHECK(lotacao>0),
 livres integer NOT NULL CHECK(livres BETWEEN 0 AND lotacao)
);
CREATE TABLE bilhetes (
 id integer PRIMARY KEY,
 sessao_id integer NOT NULL REFERENCES sessoes(id),
 utilizador_id integer NOT NULL,
 estado text NOT NULL CHECK(estado IN ('reservado','pago','cancelado'))
);
INSERT INTO sessoes VALUES(12,2,0);
INSERT INTO bilhetes VALUES(1043,12,7,'reservado'),(1044,12,8,'pago');

A CTE cancelado faz o UPDATE do bilhete apenas se id, comprador e estado coincidirem; o RETURNING fornece a sessão dessa transição. O UPDATE exterior usa esse resultado para libertar o lugar. É uma única instrução PostgreSQL: se falhar uma restrição no contador, a alteração do bilhete também é anulada.

O bloco executa a mesma instrução duas vezes na mesma base. Na primeira tentativa, S12 passa de zero para um lugar livre. A segunda já não encontra o estado reservado, logo não liberta outro lugar. Um bilhete pago ou de outra pessoa também não satisfaz o predicado. Não interpretes zero linhas como diagnóstico único: pode significar ausência, falta de permissão ou estado incompatível; a aplicação decide a resposta sem revelar dados alheios.

No Read Committed, duas tentativas sobre o mesmo bilhete disputam a mesma linha. Depois de a primeira confirmar, a segunda reavalia o predicado e já não encontra reservado. Todos os caminhos de compra, cancelamento e alteração de capacidade continuam a precisar do mesmo protocolo de invariantes e de uma ordem consistente de bloqueios. Cada execução do editor prepara uma base nova; as duas tentativas dentro deste bloco partilham a base dessa execução. O entrelaçamento exige duas ligações num servidor.

Contar ativações num UPDATE em lote

Suponhamos um trigger AFTER UPDATE FOR EACH ROW e outro AFTER UPDATE FOR EACH STATEMENT sobre preços. A instrução UPDATE precos SET preco=preco WHERE id<=2 encontra duas linhas. O primeiro dispara duas vezes e o segundo uma, mesmo que o valor de cada preço fique igual.

Encontrar uma linha para atualizar e mudar efetivamente o seu valor são condições diferentes. Para só auditar mudanças reais, conserva o teste NEW.preco IS DISTINCT FROM OLD.preco no corpo do trigger ou uma condição adequada. A linha pode ser processada sem o trigger inserir qualquer registo no histórico.