DML (Data Manipulation Language) é o conjunto de comandos que lê e escreve
dados — INSERT, UPDATE, DELETE e
MERGE. DTL (Data Transaction Language) controla a atomicidade
dessas operações — BEGIN, COMMIT,
ROLLBACK e SAVEPOINT. Os dois são inseparáveis:
um UPDATE que modifica dados financeiros sem uma transacção
explícita é um risco — se o processo falhar a meio, os dados ficam num
estado inconsistente. Perceber ACID, isolation levels e deadlocks é
perceber por que razão as bases de dados relacionais são fiáveis por design.
-- ── Inserção simples ──────────────────────────────────────────────────────────
INSERT INTO categoria (nome) VALUES ('Electrónica');
-- Múltiplas linhas numa só instrução (muito mais eficiente que um INSERT por linha):
INSERT INTO categoria (nome) VALUES
('Electrónica'),
('Vestuário'),
('Livros'),
('Desporto');
-- RETURNING — retorna valores gerados (id auto-increment, defaults, computed columns):
INSERT INTO utilizador (email, nome, password_hash)
VALUES ('joao@example.com', 'João Silva', '$2b$12$...')
RETURNING id, criado_em;
-- Útil para obter o id gerado sem um SELECT separado
-- Em Java/JDBC: Statement.RETURN_GENERATED_KEYS ou PreparedStatement com RETURNING
-- Inserir o resultado de uma query (INSERT ... SELECT):
INSERT INTO produto_arquivo (id, nome, preco, arquivado_em)
SELECT id, nome, preco, NOW()
FROM produto
WHERE activo = FALSE AND criado_em < NOW() - INTERVAL '1 year';
-- Move produtos inactivos antigos para tabela de arquivo
-- ── UPSERT — INSERT com tratamento de conflito ────────────────────────────────
-- Fundamental para operações idempotentes: "inserir se não existir, actualizar se existir"
-- ON CONFLICT DO NOTHING — ignorar conflitos silenciosamente:
INSERT INTO produto_tag (produto_id, tag_id)
VALUES (1, 5)
ON CONFLICT DO NOTHING;
-- Se (produto_id=1, tag_id=5) já existir, não faz nada e não lança erro
-- ON CONFLICT DO UPDATE — actualizar em caso de conflito (verdadeiro UPSERT):
INSERT INTO stock (produto_id, quantidade)
VALUES (42, 100)
ON CONFLICT (produto_id)
DO UPDATE SET
quantidade = stock.quantidade + EXCLUDED.quantidade,
-- EXCLUDED: refere-se aos valores que tentámos inserir (os que causaram o conflito)
actualizado_em = NOW();
-- Se produto_id=42 já existe: adiciona 100 ao stock existente
-- Se não existe: insere com quantidade=100
-- UPSERT com chave composta:
INSERT INTO permissao (utilizador_id, recurso, pode_ler, pode_escrever)
VALUES (7, 'relatorios', TRUE, FALSE)
ON CONFLICT (utilizador_id, recurso)
DO UPDATE SET
pode_ler = EXCLUDED.pode_ler,
pode_escrever = EXCLUDED.pode_escrever,
actualizado_em = NOW()
WHERE permissao.actualizado_em < NOW() - INTERVAL '1 hour';
-- WHERE no DO UPDATE: só actualiza se a condição for verdadeira
-- Se for falsa: não actualiza nem lança erro (como DO NOTHING)-- ── UPDATE básico ────────────────────────────────────────────────────────────
UPDATE produto
SET preco_base = preco_base * 1.05 -- aumentar preços 5%
WHERE categoria_id = 1
AND activo = TRUE;
-- RETURNING no UPDATE (PostgreSQL):
UPDATE utilizador
SET ultimo_login = NOW()
WHERE id = 42
RETURNING id, email, ultimo_login;
-- ── UPDATE com JOIN (UPDATE ... FROM) ─────────────────────────────────────────
-- Actualizar baseado em dados de outra tabela:
UPDATE produto p
SET preco_base = p.preco_base * (1 + a.percentagem / 100)
FROM ajuste_preco a
WHERE a.categoria_id = p.categoria_id
AND a.activo = TRUE;
-- Equivalente ao UPDATE com JOIN no MySQL: UPDATE p JOIN a ON ...
-- No PostgreSQL: UPDATE ... FROM (sem JOIN explícito — a condição fica no WHERE)
-- ── UPDATE com subquery ───────────────────────────────────────────────────────
UPDATE encomenda
SET total = (
SELECT COALESCE(SUM(quantidade * preco_unit), 0)
FROM linha_encomenda
WHERE encomenda_id = encomenda.id
)
WHERE estado = 'pendente';
-- Recalcula o total de todas as encomendas pendentes
-- COALESCE: retorna 0 se não houver linhas (evita NULL)
-- ── UPDATE em massa com CTE ───────────────────────────────────────────────────
-- Common Table Expression para clareza em updates complexos:
WITH encomendas_a_fechar AS (
SELECT id
FROM encomenda
WHERE estado = 'pendente'
AND criada_em < NOW() - INTERVAL '30 days'
)
UPDATE encomenda
SET estado = 'cancelado',
cancelado_em = NOW()
FROM encomendas_a_fechar
WHERE encomenda.id = encomendas_a_fechar.id
RETURNING encomenda.id, encomenda.cliente_id;
-- ── Armadilha comum: UPDATE sem WHERE ────────────────────────────────────────
-- UPDATE produto SET preco_base = 0; ← actualiza TODOS os produtos!
-- Em produção: sempre verificar com SELECT antes de UPDATE/DELETE:
SELECT COUNT(*) FROM produto WHERE categoria_id = 1 AND activo = TRUE;
-- Confirmar o número de linhas afectadas antes de executar o UPDATE-- ── DELETE ────────────────────────────────────────────────────────────────────
DELETE FROM sessao
WHERE expira_em < NOW();
-- Apaga sessões expiradas
-- DELETE com RETURNING:
DELETE FROM notificacao
WHERE lida = TRUE AND criada_em < NOW() - INTERVAL '90 days'
RETURNING id, utilizador_id;
-- DELETE com subquery:
DELETE FROM linha_encomenda
WHERE encomenda_id IN (
SELECT id FROM encomenda WHERE estado = 'cancelado'
);
-- DELETE com JOIN (DELETE ... USING no PostgreSQL):
DELETE FROM linha_encomenda l
USING encomenda e
WHERE l.encomenda_id = e.id
AND e.estado = 'cancelado'
AND e.cancelado_em < NOW() - INTERVAL '1 year';
-- Soft delete — padrão comum em produção:
-- Em vez de apagar, marcar como inactivo para preservar integridade referencial e auditoria
ALTER TABLE utilizador ADD COLUMN apagado_em TIMESTAMPTZ;
UPDATE utilizador
SET apagado_em = NOW()
WHERE id = 42;
-- "Apagar" o utilizador sem remover a linha
-- Todas as queries de leitura filtram: WHERE apagado_em IS NULL
-- ── MERGE (SQL:2003 standard) — upsert declarativo ───────────────────────────
-- PostgreSQL 15+ suporta MERGE nativo. Para versões anteriores, usar INSERT ... ON CONFLICT
MERGE INTO stock AS destino
USING (VALUES (42, 50), (43, 0), (44, 200)) AS origem(produto_id, quantidade)
ON destino.produto_id = origem.produto_id
WHEN MATCHED AND origem.quantidade = 0 THEN
DELETE -- remover stock zero
WHEN MATCHED THEN
UPDATE SET quantidade = origem.quantidade, -- actualizar stock existente
actualizado_em = NOW()
WHEN NOT MATCHED THEN
INSERT (produto_id, quantidade) -- inserir novo produto no stock
VALUES (origem.produto_id, origem.quantidade);
-- MERGE avalia cada linha da origem contra o destino e aplica a acção correspondente
-- Mais legível que múltiplos INSERT/UPDATE/DELETE separados em lógica ETLUma transacção é um conjunto de operações que o SGBD trata como uma unidade atómica. ACID é o acrónimo das quatro propriedades que garantem a fiabilidade das transacções em bases de dados relacionais.
-- ── As quatro propriedades ACID ──────────────────────────────────────────────
-- ATOMICIDADE: tudo ou nada
-- Se qualquer operação dentro da transacção falhar, NENHUMA é confirmada.
-- Nunca ficamos com "metade" de uma transferência bancária.
-- CONSISTÊNCIA: o schema permanece válido
-- Constraints, foreign keys, checks são verificados no COMMIT.
-- Uma transacção não pode deixar a base de dados em estado inválido.
-- ISOLAMENTO: transacções concorrentes não se interferem (depende do isolation level)
-- Duas transacções a editar os mesmos dados não se vêem mutuamente até ao COMMIT.
-- DURABILIDADE: após o COMMIT, os dados persistem
-- Mesmo que o servidor falhe imediatamente após o COMMIT, os dados estão em disco
-- (garantido pelo WAL — Write-Ahead Log).
-- ── BEGIN / COMMIT / ROLLBACK ─────────────────────────────────────────────────
BEGIN; -- iniciar transacção
-- (no PostgreSQL, pode usar também START TRANSACTION)
UPDATE conta SET saldo = saldo - 500 WHERE id = 1; -- débito
UPDATE conta SET saldo = saldo + 500 WHERE id = 2; -- crédito
-- Verificar que nenhuma conta ficou negativa:
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM conta WHERE saldo < 0) THEN
RAISE EXCEPTION 'Saldo insuficiente';
END IF;
END;
$$;
COMMIT; -- confirmar — ambos os UPDATEs são tornados permanentes
-- ou:
ROLLBACK; -- reverter — nenhum UPDATE é aplicado
-- Em caso de erro, o PostgreSQL faz ROLLBACK automático da transacção.
-- Auto-commit: por default, cada statement SQL é uma transacção implícita.
-- Para agrupar vários statements: BEGIN explícito é necessário.
-- ── SAVEPOINT — ponto de retorno parcial dentro de uma transacção ─────────────
BEGIN;
INSERT INTO encomenda (cliente_id, total) VALUES (42, 0) RETURNING id;
-- id = 101
SAVEPOINT sp_encomenda_criada;
-- Ponto de retorno — podemos voltar aqui sem cancelar toda a transacção
INSERT INTO linha_encomenda (encomenda_id, produto_id, quantidade, preco_unit)
VALUES (101, 5, 2, 89.99);
-- Algo correu mal com a linha:
ROLLBACK TO SAVEPOINT sp_encomenda_criada;
-- A encomenda ainda existe, a linha foi revertida
-- Tentar com dados corrigidos:
INSERT INTO linha_encomenda (encomenda_id, produto_id, quantidade, preco_unit)
VALUES (101, 5, 1, 89.99);
UPDATE encomenda SET total = 89.99 WHERE id = 101;
RELEASE SAVEPOINT sp_encomenda_criada;
-- Liberta o savepoint (opcional — é libertado automaticamente no COMMIT)
COMMIT;O nível de isolamento controla que anomalias de concorrência são possíveis. Mais isolamento significa menos anomalias mas mais contention — o SGBD tem de bloquear mais recursos para garantir as propriedades. PostgreSQL implementa os quatro níveis do standard SQL.
-- ── Anomalias de concorrência ────────────────────────────────────────────────
-- Dirty Read: ler dados não confirmados de outra transacção
-- T1 actualiza, T2 lê o valor novo, T1 faz ROLLBACK → T2 leu dados que nunca existiram
-- Non-Repeatable Read: ler o mesmo dado duas vezes e obter resultados diferentes
-- T1 lê linha X (valor=10), T2 actualiza X para 20 e faz COMMIT, T1 lê X novamente (valor=20)
-- Phantom Read: uma query retorna linhas diferentes quando executada duas vezes
-- T1 conta linhas (n=5), T2 insere uma linha nova e faz COMMIT, T1 conta novamente (n=6)
-- ── Níveis de isolamento e anomalias que previnem ─────────────────────────────
--
-- Nível | Dirty Read | Non-Repeatable | Phantom Read
-- ─────────────────────┼────────────┼────────────────┼─────────────
-- READ UNCOMMITTED | possível | possível | possível
-- READ COMMITTED | ✗ never | possível | possível ← default PostgreSQL
-- REPEATABLE READ | ✗ never | ✗ never | possível*
-- SERIALIZABLE | ✗ never | ✗ never | ✗ never
--
-- * PostgreSQL REPEATABLE READ também previne Phantom Reads (implementação SSI)
-- Definir isolation level para uma transacção:
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; -- default
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Ou após BEGIN:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... operações ...
COMMIT;
-- ── Quando usar cada nível ────────────────────────────────────────────────────
-- READ COMMITTED (default):
-- A maioria das aplicações web — operações independentes, sem leitura-modificação-escrita
-- Exemplo: inserir uma encomenda, actualizar um perfil
-- REPEATABLE READ:
-- Relatórios que lêem os mesmos dados múltiplas vezes na mesma transacção
-- Exportação de dados consistente
-- Exemplo: gerar um relatório PDF que lê várias tabelas
-- SERIALIZABLE:
-- Operações críticas que dependem de ausência de inserções concorrentes
-- Transferências bancárias, reservas de lugares, stock limitado
-- Exemplo:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT quantidade FROM stock WHERE produto_id = 42;
-- Se quantidade >= 1: prosseguir
UPDATE stock SET quantidade = quantidade - 1 WHERE produto_id = 42;
INSERT INTO linha_encomenda (...) VALUES (...);
COMMIT;
-- SERIALIZABLE garante que duas transacções concorrentes não vendem o mesmo último item-- Um deadlock ocorre quando duas transacções esperam uma pela outra indefinidamente
-- O PostgreSQL detecta deadlocks automaticamente e cancela uma das transacções
-- ── Como ocorre um deadlock ───────────────────────────────────────────────────
--
-- Transacção A | Transacção B
-- ──────────────────────┼──────────────────────
-- BEGIN; | BEGIN;
-- UPDATE conta |
-- SET saldo=saldo-100 |
-- WHERE id=1; | ← A obtém lock na linha id=1
-- | UPDATE conta
-- | SET saldo=saldo-200
-- | WHERE id=2; ← B obtém lock na linha id=2
-- UPDATE conta |
-- SET saldo=saldo+100 |
-- WHERE id=2; | ← A aguarda B libertar id=2
-- | UPDATE conta
-- | SET saldo=saldo+200
-- | WHERE id=1; ← B aguarda A libertar id=1
-- ← DEADLOCK DETECTADO — PostgreSQL cancela uma das transacções com:
-- "ERROR: deadlock detected
-- DETAIL: Process 1234 waits for ShareLock on transaction 5678;
-- blocked by process 5678.
-- Process 5678 waits for ShareLock on transaction 1234;
-- blocked by process 1234."
-- ── Como prevenir deadlocks ───────────────────────────────────────────────────
-- Regra 1: aceder sempre às tabelas/linhas na mesma ordem em todas as transacções
-- Se A e B acedem sempre a id=1 antes de id=2, nunca ocorre deadlock:
-- Transacção A (corrigida):
BEGIN;
UPDATE conta SET saldo = saldo - 100 WHERE id = 1; -- sempre id=1 primeiro
UPDATE conta SET saldo = saldo + 100 WHERE id = 2; -- depois id=2
COMMIT;
-- Transacção B (corrigida):
BEGIN;
UPDATE conta SET saldo = saldo - 200 WHERE id = 1; -- sempre id=1 primeiro
UPDATE conta SET saldo = saldo + 200 WHERE id = 2; -- depois id=2
COMMIT;
-- Regra 2: SELECT ... FOR UPDATE — obter o lock na fase de leitura
BEGIN;
-- Obter locks em ordem determinística:
SELECT * FROM conta WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- ORDER BY id garante que os locks são obtidos sempre na mesma ordem
UPDATE conta SET saldo = saldo - 100 WHERE id = 1;
UPDATE conta SET saldo = saldo + 100 WHERE id = 2;
COMMIT;
-- FOR UPDATE: bloqueia as linhas para escrita — outras transacções aguardam
-- FOR SHARE: bloqueia as linhas para leitura — permite outras leituras, bloqueia escritas
-- FOR NO KEY UPDATE: como FOR UPDATE mas permite FK inserts nas linhas bloqueadas
-- NOWAIT: em vez de aguardar, lança erro imediatamente se o lock não estiver disponível
-- SKIP LOCKED: salta as linhas bloqueadas — útil para job queues
-- Regra 3: transacções curtas — quanto menos tempo uma transacção mantém locks,
-- menor a probabilidade de conflito
-- Evitar: trabalho pesado de aplicação (chamadas HTTP, processamento) dentro de BEGIN/COMMIT
-- Tratar o erro de deadlock na aplicação (Java):
-- try {
-- executeTransaction();
-- } catch (SQLException e) {
-- if ("40P01".equals(e.getSQLState())) { // PostgreSQL deadlock error code
-- // Retry com backoff exponencial
-- Thread.sleep(retryDelayMs);
-- executeTransaction();
-- }
-- }-- ── Padrão: transferência bancária segura ────────────────────────────────────
CREATE OR REPLACE FUNCTION transferir(
p_origem INTEGER,
p_destino INTEGER,
p_valor NUMERIC
) RETURNS VOID AS $$
BEGIN
-- Validar saldo (com lock para evitar race condition):
IF (SELECT saldo FROM conta WHERE id = p_origem FOR UPDATE) < p_valor THEN
RAISE EXCEPTION 'Saldo insuficiente na conta %', p_origem;
END IF;
UPDATE conta SET saldo = saldo - p_valor WHERE id = p_origem;
UPDATE conta SET saldo = saldo + p_valor WHERE id = p_destino;
INSERT INTO movimento (conta_id, tipo, valor, timestamp)
VALUES (p_origem, 'debito', p_valor, NOW()),
(p_destino, 'credito', p_valor, NOW());
END;
$$ LANGUAGE plpgsql;
-- Chamar dentro de uma transacção:
BEGIN;
SELECT transferir(1, 2, 500.00);
COMMIT;
-- ── Padrão: job queue com SKIP LOCKED ────────────────────────────────────────
-- Múltiplos workers a processar uma fila sem conflitos:
CREATE TABLE job_queue (
id BIGSERIAL PRIMARY KEY,
tipo VARCHAR(50) NOT NULL,
payload JSONB NOT NULL,
estado VARCHAR(20) NOT NULL DEFAULT 'pendente',
criado_em TIMESTAMPTZ NOT NULL DEFAULT NOW(),
processado_em TIMESTAMPTZ
);
-- Cada worker executa:
BEGIN;
SELECT id, tipo, payload
FROM job_queue
WHERE estado = 'pendente'
ORDER BY criado_em
LIMIT 1
FOR UPDATE SKIP LOCKED; -- obtém o próximo job livre, salta os bloqueados por outros workers
-- (guardar o id retornado: job_id)
UPDATE job_queue
SET estado = 'a_processar'
WHERE id = :job_id;
COMMIT;
-- ... processar o job ...
BEGIN;
UPDATE job_queue
SET estado = 'concluido',
processado_em = NOW()
WHERE id = :job_id;
COMMIT;
-- SKIP LOCKED é a forma mais eficiente de implementar uma job queue em PostgreSQL
-- Sem SKIP LOCKED: todos os workers aguardam o mesmo lock → contention desnecessáriaINSERT ... ON CONFLICT DO UPDATE (UPSERT) para operações idempotentes — evita race conditions de "verificar depois inserir".RETURNING após INSERT/UPDATE/DELETE para obter valores gerados sem SELECT adicional.SELECT COUNT(*) antes de executar UPDATE ou DELETE sem WHERE — nunca confiar no resultado sem confirmar o scope.apagado_em TIMESTAMPTZ) em vez de DELETE real preserva integridade referencial e permite auditoria.READ COMMITTED é o default do PostgreSQL — suficiente para a maioria das operações web.REPEATABLE READ para relatórios que lêem os mesmos dados múltiplas vezes; SERIALIZABLE para operações críticas de stock ou saldo.SELECT ... FOR UPDATE ORDER BY id.BEGIN e COMMIT.FOR UPDATE SKIP LOCKED é o padrão correcto para job queues em PostgreSQL — evita contention entre workers.40P01 (deadlock) na camada de aplicação com retry e backoff exponencial.