PostgreSQL: Transações e Controle de Concorrência no…

Última atualização: 2026-08-26

1. O Que Você Vai Aprender

• Compreender as quatro propriedades ACID e como o PostgreSQL as implementa
• Usar BEGIN/COMMIT/ROLLBACK para controlar transações
• Usar SAVEPOINT para rollback parcial dentro de uma transação
• Compreender as diferenças entre os três níveis de isolamento do PostgreSQL e como escolher
• Dominar o mecanismo MVCC de controle de concorrência multiversão
• Usar bloqueios de linha, bloqueios de tabela e Bloqueios Consultivos
• Compreender a detecção e tratamento de deadlocks
• Comparar estratégias de bloqueio pessimista vs otimista
• Usar commit em duas fases (2PC) para transações distribuídas

2. A História

Alice é responsável pelo sistema de pedidos de uma plataforma de e-commerce. Durante uma promoção, um item de edição limitada tinha apenas 1 unidade restante em estoque, e dois usuários fizeram pedidos quase simultaneamente:

• O Usuário A leu o estoque como 1 e se preparou para deduzir
• O Usuário B também leu o estoque como 1 e se preparou para deduzir

Sem proteção de transação, ambos poderiam fazer pedidos com sucesso, levando o estoque a -1 e causando venda excessiva. Alice precisa dos mecanismos de isolamento de transação, MVCC e bloqueio de linha do PostgreSQL para garantir que apenas um usuário deduza o estoque com sucesso enquanto o outro é bloqueado ou recebe um erro—garantindo a consistência dos dados.

3. Conceito: ACID e Fundamentos de Transações

(1) As Quatro Propriedades ACID

▶ Exemplo: Atomicidade ACID—Transferência ou Tem Sucesso Total ou Rollback Total

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

Output:

-- instrução SQL executada com sucesso

(2) Instruções de Controle de Transação

▶ Exemplo: Reverter uma Transaç��o para Restaurar Dados

BEGIN;
DELETE FROM orders WHERE order_id = 999;
-- Ops, exclusão errada!
ROLLBACK;
-- Os dados são restaurados

Output:

-- instrução SQL executada com sucesso

(3) Modo Autocommit

O PostgreSQL habilita Autocommit por padrão—cada instrução SQL é automaticamente commitada como sua própria transação.

▶ Exemplo: Desabilitar Autocommit e Fazer Commit Manualmente

\set AUTOCOMMIT off
DELETE FROM orders WHERE order_status = 'cancelled';
-- Verificar antes de commitar
SELECT count(*) FROM orders WHERE order_status = 'cancelled';
COMMIT;

Output:

# comando executado com sucesso

(4) Rollback Parcial com SAVEPOINT

Defina um savepoint dentro de uma transação para poder reverter apenas até esse savepoint, em vez de toda a transação.

▶ Exemplo: Usar SAVEPOINT para Rollback Parcial

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
-- A segunda atualização estava errada, reverter para o savepoint
ROLLBACK TO SAVEPOINT sp1;
-- A primeira atualização ainda está ativa, pode commitar
COMMIT;

Output:

-- instrução SQL executada com sucesso

4. Conceito: Níveis de Isolamento de Transação

(1) Os Três Níveis de Isolamento do PostgreSQL

O PostgreSQL implementa apenas três níveis de isolamento (READ UNCOMMITTED é mapeado para READ COMMITTED):

▶ Exemplo: Definir o Nível de Isolamento

BEGIN ISOLATION LEVEL READ COMMITTED;
-- ou
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ou
BEGIN ISOLATION LEVEL SERIALIZABLE;

Output:

-- instrução SQL executada com sucesso

(2) READ COMMITTED vs REPEATABLE READ na Prática

▶ Exemplo: READ COMMITTED—Duas Consultas na Mesma Transação Retornam Resultados Diferentes

-- Sessão 1
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE account_id = 1; -- Retorna 1000

-- Sessão 2 (outra conexão)
UPDATE accounts SET balance = 800 WHERE account_id = 1;
COMMIT;

-- De volta à Sessão 1
SELECT balance FROM accounts WHERE account_id = 1; -- Retorna 800 (vê alteração commitada)
COMMIT;

Output:

 count 
-------
     5
(1 row)

▶ Exemplo: REPEATABLE READ—Duas Consultas na Mesma Transação Retornam Resultados Consistentes

-- Sessão 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_id = 1; -- Retorna 1000

-- Sessão 2 (outra conexão)
UPDATE accounts SET balance = 800 WHERE account_id = 1;
COMMIT;

-- De volta à Sessão 1
SELECT balance FROM accounts WHERE account_id = 1; -- Ainda retorna 1000
COMMIT;

Output:

 count 
-------
     5
(1 row)

(3) Nível de Isolamento SERIALIZABLE

SERIALIZABLE é o nível mais rigoroso; o PostgreSQL usa Serializable Snapshot Isolation (SSI) para detectar conflitos de serialização.

▶ Exemplo: Detecção de Conflito no SERIALIZABLE

-- Sessão 1
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE account_id = 1;

-- Sessão 2
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

-- De volta à Sessão 1
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
-- ERRO: não foi possível serializar o acesso devido a atualização concorrente

Output:

 count 
-------
     5
(1 row)

5. Conceito: MVCC - Controle de Concorrência Multiversão

(1) Princípio Central do MVCC

MVCC (Multi-Version Concurrency Control) é o núcleo do controle de concorrência do PostgreSQL. Cada transação vê um snapshot dos dados em um ponto no tempo; leituras não bloqueiam escritas, e escritas não bloqueiam leituras.

Cada versão de linha (tupla) contém quatro campos ocultos:

▶ Exemplo: Visualizar Informações de Versão de uma Linha

SELECT xmin, xmax, * FROM products WHERE product_id = 1;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Fluxo de Concorrência de Leitura/Escrita no MVCC

sequenceDiagram
    participant R as Leitor(Tx1)
    participant W as Escritor(Tx2)
    participant T as Tabela

    R->>T: SELECT (snapshot no início de Tx1)
    T-->>R: Retorna versão V1 (xmin=100, xmax=0)
    W->>T: UPDATE (cria nova versão)
    T-->>T: V1 xmax=200, V2 xmin=200 xmax=0
    R->>T: SELECT novamente (mesmo snapshot)
    T-->>R: Ainda retorna V1 (Tx2 não commitou)
    W->>T: COMMIT
    Note over T: V2 agora visível para novas transações
    R->>T: SELECT novamente (mesmo snapshot)
    T-->>R: Ainda retorna V1 (REPEATABLE READ)

sequenceDiagram
    participant R as Leitor(Tx1)
    participant W as Escritor(Tx2)
    participant T as Tabela

    R->>T: SELECT (snapshot no início de Tx1)
    T-->>R: Retorna versão V1 (xmin=100, xmax=0)
    W->>T: UPDATE (cria nova versão)
    T-->>T: V1 xmax=200, V2 xmin=200 xmax=0
    R->>T: SELECT novamente (mesmo snapshot)
    T-->>R: Ainda retorna V1 (Tx2 não commitou)
    W->>T: COMMIT
    Note over T: V2 agora visível para novas transações
    R->>T: SELECT novamente (mesmo snapshot)
    T-->>R: Ainda retorna V1 (REPEATABLE READ)

▶ Exemplo: Leituras MVCC Não Bloqueiam Escritas

-- Sessão 1: Leitura de longa duração
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM orders WHERE order_date = '2025-01-01';
-- Esta consulta NÃO bloqueia escritores

-- Sessão 2: Escrita concorrente (sem bloqueio da Sessão 1)
UPDATE orders SET order_status = 'shipped' WHERE order_id = 100;
COMMIT;

Output:

UPDATE 3

(3) O Custo do MVCC: Tuplas Mortas e VACUUM

Um UPDATE no MVCC não modifica a linha original—cria uma nova versão. A linha antiga se torna uma tupla morta e precisa de limpeza VACUUM.

▶ Exemplo: Visualizar Contagem de Tuplas Mortas

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: Disparar VACUUM Manualmente

VACUUM orders;              -- Recupera espaço, não bloqueia leituras
VACUUM FULL orders;         -- Reescrita completa da tabela, bloqueia a tabela
VACUUM ANALYZE orders;      -- Recupera + atualiza estatísticas

Output:

-- instrução SQL executada com sucesso

6. Conceito: Bloqueios

(1) Bloqueios de Linha

O PostgreSQL adquire automaticamente um bloqueio de linha ao modificar uma linha; outras transações devem esperar.

▶ Exemplo: SELECT FOR UPDATE Previne Venda Excessiva

BEGIN;
SELECT stock FROM products WHERE product_id = 1 FOR UPDATE;
-- stock = 1, linha bloqueada
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
COMMIT;
-- Se outra transação tentar FOR UPDATE na mesma linha, ela espera

Output:

UPDATE 3

(2) Bloqueios de Tabela

▶ Exemplo: Bloqueio Explícito de Tabela

LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
-- Realizar operações críticas
COMMIT;

Output:

-- instrução SQL executada com sucesso

(3) Bloqueios Consultivos (Específicos do PostgreSQL)

Bloqueios Consultivos são bloqueios no nível da aplicação, não vinculados a nenhuma linha de tabela, adequados para coordenação distribuída.

▶ Exemplo: Usar Bloqueio Consultivo para Prevenir Processamento Duplicado

-- Tentativa não bloqueante
SELECT pg_try_advisory_lock(12345);
-- Retorna true: obteve o bloqueio, prosseguir
-- Retorna false: outra sessão o mantém, pular

-- Bloqueio consultivo no nível da transação (liberação automática no COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- Fazer o trabalho...
COMMIT; -- Bloqueio liberado automaticamente

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: Bloqueio Consultivo para Tarefas de Instância Única

CREATE FUNCTION run_daily_report() RETURNS void AS $$
BEGIN
  IF pg_try_advisory_lock(99999) THEN
    -- Apenas uma sessão pode executar isso por vez
    INSERT INTO report_log (report_date, status)
    VALUES (CURRENT_DATE, 'running');
    PERFORM pg_sleep(5); -- Simular trabalho
    UPDATE report_log SET status = 'done' WHERE report_date = CURRENT_DATE;
    PERFORM pg_advisory_unlock(99999);
  ELSE
    RAISE NOTICE 'Relatório já em execução em outra sessão';
  END IF;
END;
$$ LANGUAGE plpgsql;

Output:

INSERT 0 1

7. Conceito: Detecção e Tratamento de Deadlock

(1) Como os Deadlocks Surgem

Duas transações esperam pelos bloqueios mantidos uma pela outra, formando uma dependência circular.

flowchart LR
    A[Tx1: Bloquear Linha A] -->|Esperar pela Linha B| B[Tx2: Bloquear Linha B]
    B -->|Esperar pela Linha A| A
    A -->|Deadlock!| C[PG detecta automaticamente em 1s]

flowchart LR
    A[Tx1: Bloquear Linha A] -->|Esperar pela Linha B| B[Tx2: Bloquear Linha B]
    B -->|Esperar pela Linha A| A
    A -->|Deadlock!| C[PG detecta automaticamente em 1s]

▶ Exemplo: Cenário de Deadlock

-- Sessão 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; -- Bloquear linha 1

-- Sessão 2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 2; -- Bloquear linha 2

-- Sessão 1 (agora espera pela linha 2)
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Sessão 2 (deadlock!)
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
-- ERRO: deadlock detectado

Output:

-- instrução SQL executada com sucesso

(2) Detecção e Prevenção de Deadlock

▶ Exemplo: Prevenir Deadlocks com Ordem Fixa de Bloqueio

-- Sempre bloquear linhas em ordem de account_id
BEGIN;
SELECT * FROM accounts WHERE account_id IN (1, 2) ORDER BY account_id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Output:

 count 
-------
     5
(1 row)

8. Conceito: Bloqueio Pessimista vs Bloqueio Otimista

(1) Comparando as Duas Estratégias de Concorrência

▶ Exemplo: Bloqueio Pessimista para Dedução de Estoque

BEGIN;
SELECT stock FROM products WHERE product_id = 1 FOR UPDATE;
-- stock = 1, linha bloqueada
IF stock > 0 THEN
  UPDATE products SET stock = stock - 1 WHERE product_id = 1;
END IF;
COMMIT;

Output:

UPDATE 3

▶ Exemplo: Bloqueio Otimista para Dedução de Estoque

-- Adicionar coluna version
ALTER TABLE products ADD COLUMN version INT DEFAULT 1;

-- Atualização otimista
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE product_id = 1 AND version = 5;
-- Se linhas afetadas = 0, alguém modificou, precisa tentar novamente

Output:

-- instrução SQL executada com sucesso

▶ Exemplo: Lógica de Nova Tentativa no Bloqueio Otimista na Camada de Aplicação

CREATE FUNCTION deduct_stock(p_id INT, p_qty INT) RETURNS BOOLEAN AS $$
DECLARE
  v_version INT;
  v_stock INT;
  v_updated INT;
BEGIN
  LOOP
    SELECT stock, version INTO v_stock, v_version
    FROM products WHERE product_id = p_id;

    IF v_stock < p_qty THEN RETURN FALSE; END IF;

    UPDATE products
    SET stock = stock - p_qty, version = version + 1
    WHERE product_id = p_id AND version = v_version;

    GET DIAGNOSTICS v_updated = ROW_COUNT;
    IF v_updated > 0 THEN RETURN TRUE; END IF;
    -- Conflito, tentar novamente
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Output:

 count 
-------
     5
(1 row)

9. Conceito: Commit em Duas Fases (2PC)

(1) 2PC para Transações Distribuídas

Quando uma transação abrange múltiplos bancos de dados ou recursos externos, o commit em duas fases é necessário para garantir a atomicidade.

▶ Exemplo: Fluxo de Commit em Duas Fases

-- Fase 1: Preparar
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
PREPARE TRANSACTION 'transfer_out';

-- (Coordenador confirma que todos os participantes estão preparados)

-- Fase 2: Commit
COMMIT PREPARED 'transfer_out';

Output:

-- instrução SQL executada com sucesso

▶ Exemplo: Visualizar Transações Preparadas

SELECT * FROM pg_prepared_xacts;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

10. Na Prática: Transação Completa de Dedução de Estoque para E-Commerce

Alice precisa de uma transação completa de dedução de estoque que lide com pedidos concorrentes, previna venda excessiva e registre o pedido.

-- Passo 1: Criar tabelas
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name TEXT NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  price NUMERIC(10,2) NOT NULL,
  version INT NOT NULL DEFAULT 1
);

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  total_price NUMERIC(10,2) NOT NULL,
  order_status TEXT DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT now()
);

INSERT INTO products VALUES
  (1, 'Limited Edition Watch', 1, 299.99, 1),
  (2, 'Wireless Headphones', 50, 89.99, 1);

-- Passo 2: Abordagem de bloqueio pessimista para itens populares
CREATE FUNCTION place_order(
  p_customer_id INT, p_product_id INT, p_qty INT
) RETURNS INT AS $$
DECLARE
  v_stock INT;
  v_order_id INT;
BEGIN
  BEGIN
    -- Bloquear a linha do produto
    SELECT stock INTO v_stock
    FROM products
    WHERE product_id = p_product_id
    FOR UPDATE;

    IF v_stock < p_qty THEN
      RAISE EXCEPTION 'Estoque insuficiente: % < %', v_stock, p_qty;
    END IF;

    -- Deduzir estoque
    UPDATE products
    SET stock = stock - p_qty
    WHERE product_id = p_product_id;

    -- Criar pedido
    INSERT INTO orders (customer_id, product_id, quantity, total_price)
    VALUES (p_customer_id, p_product_id, p_qty,
            p_qty * (SELECT price FROM products WHERE product_id = p_product_id))
    RETURNING order_id INTO v_order_id;

    RETURN v_order_id;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE '%', SQLERRM;
      RETURN -1;
  END;
END;
$$ LANGUAGE plpgsql;

-- Passo 3: Testar pedidos concorrentes
SELECT place_order(101, 1, 1); -- Sucesso, pedido criado
SELECT place_order(102, 1, 1); -- Falha, estoque insuficiente

-- Passo 4: Verificar resultados
SELECT product_id, stock FROM products WHERE product_id = 1;
SELECT order_id, customer_id, order_status FROM orders;

❓ Perguntas Frequentes

P: Por que o PostgreSQL não tem um nível de isolamento READ UNCOMMITTED? R: O PostgreSQL mapeia READ UNCOMMITTED para READ COMMITTED, porque sob a arquitetura MVCC é impossível ler dados não commitados (sempre lê um snapshot), então leituras sujas não podem acontecer no PG.

P: REPEATABLE READ pode prevenir leituras fantasmas? R: O REPEATABLE READ do PostgreSQL realmente pode prevenir leituras fantasmas, indo além do que o padrão SQL exige desse nível. No padrão, apenas SERIALIZABLE previne fantasmas, mas o mecanismo de snapshot MVCC do PG também bloqueia fantasmas sob REPEATABLE READ.

P: SAVEPOINTs podem ser aninhados? R: Sim. SAVEPOINTs suportam aninhamento; um ROLLBACK TO SAVEPOINT interno reverte apenas até aquele savepoint, não afetando os externos. RELEASE SAVEPOINT libera o savepoint nomeado e todos os savepoints criados depois dele.

P: Qual a diferença entre SELECT FOR UPDATE e um UPDATE direto? R: SELECT FOR UPDATE bloqueia a linha primeiro e depois decide se modifica—ideal para operações compostas de "ler e depois escrever"; um UPDATE direto faz tudo em um passo. A vantagem do SELECT FOR UPDATE é que você pode fazer um julgamento de negócio antes de modificar.

P: Quando devo usar VACUUM FULL? R: Apenas quando uma tabela está muito inchada e o VACUUM de rotina não consegue recuperar espaço. VACUUM FULL precisa de um bloqueio ACCESS EXCLUSIVE, durante o qual a tabela fica completamente indisponível. A manutenção de rotina deve depender do autovacuum.

P: Qual a diferença entre um Bloqueio Consultivo e um bloqueio comum? R: Um Bloqueio Consultivo é definido pela aplicação, não vinculado a uma tabela ou linha, e não é liberado automaticamente no fim da transação (a menos que você use a versão xact). Bloqueios comuns são gerenciados automaticamente pelo PostgreSQL e vinculados a objetos específicos do banco de dados.

P: Quando o 2PC é usado? R: 2PC é usado principalmente para transações distribuídas entre bancos de dados ou sistemas, exigindo um coordenador para commit uniforme. Uma transação de banco único usa BEGIN/COMMIT simples; 2PC tem sobrecarga extra e requer configurar max_prepared_transactions.

📖 Resumo

• ACID são as quatro garantias de uma transação, implementadas pelo PostgreSQL via WAL, MVCC, restrições e bloqueios
• BEGIN/COMMIT/ROLLBACK controlam os limites da transação; SAVEPOINT permite rollback parcial
• O PostgreSQL implementa três níveis de isolamento: READ COMMITTED (padrão), REPEATABLE READ, SERIALIZABLE
• MVCC permite que leituras e escritas não bloqueiem umas às outras; cada transação vê um snapshot dos dados; atualizações criam novas versões
• Tuplas mortas são limpas pelo VACUUM/autovacuum; cenários de alta atualização precisam de atenção à estratégia de limpeza
• Bloqueio de linha SELECT FOR UPDATE previne conflitos de modificação concorrente; Bloqueio Consultivo é para coordenação no nível da aplicação
• Deadlocks são detectados automaticamente pelo PostgreSQL (1 segundo) e uma transação é revertida
• Bloqueios pessimistas são adequados para cenários de alto conflito; bloqueios otimistas para cenários de baixo conflito
• Commit em duas fases (2PC) garante atomicidade para transaç��es distribuídas

📝 Exercícios

1. ⭐ Escreva uma transação que transfira 200 USD da conta 1 para a conta 2 na tabela accounts, e faça rollback se a conta 1 tiver saldo insuficiente.

2. ⭐⭐ Use SAVEPOINT para escrever uma transação: primeiro insira um registro de pedido, depois tente atualizar o estoque; se o estoque for insuficiente, faça rollback para o savepoint mas mantenha o pedido (marque seu status como 'failed'), depois faça commit da transação.

3. ⭐⭐⭐ Implemente uma versão de bloqueio otimista de dedução de estoque: use uma coluna version, tente novamente automaticamente em conflito concorrente até 3 vezes, e retorne FALSE se todas as 3 falharem. Registre também cada nova tentativa em uma tabela retry_log.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós