O padrão SQL define quatro níveis de isolamento de transação em termos de três fenômenos que devem ser evitados entre transações concorrentes. Os fenômenos não desejáveis são:
A transação lê dados não efetivados (uncommitted) escritos por uma transação concorrente.
A transação lê uma segunda vez os dados, e descobre que os dados foram modificados por outra transação (que os efetivou após ter sido feita a leitura anterior).
A transação executa uma segunda vez uma consulta que retorna um conjunto de linhas que satisfaz uma determinada condição de procura, e descobre que o conjunto de linhas que satisfaz a condição é diferente devido a uma outra transação efetivada recentemente.
Os quatro níveis de isolamento de transação, e seus comportamentos correspondentes, estão descritos na Tabela 9-1.
Tabela 9-1. Níveis de isolamento da transação no SQL
| Nível de isolamento | Dirty Read | Nonrepeatable Read | Phantom Read |
|---|---|---|---|
| Read uncommitted | Possível | Possível | Possível |
| Read committed | Impossível | Possível | Possível |
| Repeatable read | Impossível | Impossível | Possível |
| Serializable | Impossível | Impossível | Impossível |
O PostgreSQL disponibiliza os níveis de isolamento read committed e serializable (serializável).
O Read Committed (lê efetivado) é o nível de isolamento padrão do PostgreSQL. Quando uma transação processa sob este nível de isolamento, o comando SELECT enxerga apenas os dados efetivados antes da consulta começar; nunca enxerga dados não efetivados, ou as mudanças efetivadas durante a execução da consulta pelas transações concorrentes (Entretanto, o SELECT enxerga os efeitos das atualizações anteriores executadas dentro de sua própria transação, mesmo que ainda não tenham sido efetivadas). Na verdade, o comando SELECT enxerga um instantâneo do banco de dados, conforme este era no instante que a consulta começou a executar. Observe que dois comandos SELECTs sucessivos enxergam dados diferentes, mesmo estando dentro de uma mesma transação, se outras transações efetivarem alterações durante a execução do primeiro comando SELECT.
Os comando UPDATE, DELETE e SELECT FOR UPDATE se comportam do mesmo modo que o SELECT para encontrar as linhas de destino: somente encontram linhas de destino efetivadas até a hora do início do comando. Entretanto, alguma linha de destino pode ter sido atualizada (ou excluída ou marcada para atualização) por outra transação concorrente, na hora que for encontrada. Neste caso, o atualizador (would-be updater) aguarda a transação de atualização que começou primeiro efetivar ou desfazer (se ainda estiver executando). Se o primeiro atualizador desfizer (rolls back), então seus efeitos são negados e o segundo atualizador pode prosseguir com a atualização da linha original encontrada. Se o primeiro atualizador efetivar, o segundo atualizador ignora a linha se esta foi excluída pelo primeiro atualizador, senão tenta aplicar esta operação na versão atualizada da linha. A condição de procura do comando (cláusula WHERE) é avaliada novamente para verificar se a versão atualizada da linha ainda corresponde à condição de procura. Se corresponder, o segundo atualizador prossegue sua operação começando a partir da versão atualizada da linha.
Devido à regra acima é possível os comandos de atualização enxergarem instantâneos inconsistentes --- podem enxergar os efeitos de comandos de atualização concorrentes que afetam as mesmas linhas que estão tentando atualizar, mas não enxergam os efeitos destes comandos em outras linhas do banco de dados. Este comportamento torna o Read Committed menos apropriado para os comandos envolvendo condições de procura complexas. Entretanto, é apropriado para casos mais simples. Por exemplo, considere a atualização do saldo bancário pela transação mostrada abaixo:
BEGIN; UPDATE conta SET saldo = saldo + 100.00 WHERE num_conta = 12345; UPDATE conta SET saldo = saldo - 100.00 WHERE num_conta = 7534; COMMIT;
Se duas transações deste tipo tentam mudar concorrentemente o saldo da conta 12345 é claro que se deseja que a segunda transação comece a partir da versão atualizada da linha da conta. Como cada comando afeta apenas uma linha predeterminada, permitir enxergar a versão atualizada da linha não cria nenhuma inconsistência problemática.
Uma vez que no modo Read Committed cada novo comando começa com um novo instantâneo, incluindo todas as transações efetivadas até este instante, os comandos seguintes na mesma transação sempre enxergam os efeitos das transações concorrentes efetivadas. O ponto em questão é se dentro de um único comando é enxergada uma visão totalmente consistente do banco de dados.
O isolamento parcial da transação fornecido pelo modo Read Committed é adequado para muitas aplicações, e este modo é rápido e fácil de ser utilizado. Entretanto, para aplicações que efetuam consultas e atualizações complexas, pode ser necessário garantir uma visão consistente mais rigorosa do banco de dados que a fornecida pelo modo Read Committed.
Serializável é o nível mais rigoroso de isolamento da transação. Este nível emula a execução serial da transação, como se todas as transações fossem executadas uma após a outra, em série, em vez de concorrentemente. Entretanto, as aplicações que utilizam este nível de isolamento devem estar preparadas para tentar novamente as transações devido a falhas na serialização.
Quando uma transação está no nível serializável, o comando a SELECT enxerga apenas os dados efetivados antes da transação começar; nunca enxerga dados não efetivados ou mudanças efetivadas durante a execução da transação por transações concorrentes (Entretanto, o comando SELECT enxerga os efeitos de atualizações anteriores executadas dentro de sua própria transação, mesmo que ainda não tenham sido efetivadas). Isto é diferente do Read Committed, porque o comando SELECT enxerga um instantâneo do início da transação, e não do início do comando corrente dentro da transação. Portanto, comandos SELECTs sucessivos dentro de uma mesma transação sempre enxergam os mesmos dados.
Os comandos UPDATE, DELETE e SELECT FOR UPDATE se comportam do mesmo modo que o SELECT para encontrar as linhas de destino: somente encontram linhas de destino efetivadas até a hora do início da transação. Entretanto, alguma linha de destino pode ter sido atualizada (ou excluída ou marcada para atualização) por outra transação concorrente na hora que for encontrada. Neste caso, a transação serializável aguarda a transação de atualização que começou primeiro efetivar ou desfazer as alterações (se ainda estiver executando). Se a transação desfizer as alterações, então seus efeitos são negados e a transação serializável pode prosseguir com a atualização da linha original encontrada. Porém, se as alterações forem efetivadas (e a linha for realmente atualizada ou excluída, e não apenas selecionada para atualização), então a transação serializável é desfeita com a mensagem
ERROR: Can't serialize access due to concurrent update
porque uma transação serializável não pode modificar linhas alteradas por outra transação após ter começado.
Quando uma aplicação recebe esta mensagem de erro, deve abortar a transação corrente e tentar executar novamente toda a transação a partir do início. Da segunda vez em diante, a transação passa a enxergar a modificação efetivada anteriormente como parte da sua visão inicial do banco de dados e, portanto, não existirá conflito lógico em usar a nova versão da linha como o ponto de partida para a atualização na nova transação.
Observe que somente as transações que atualizam podem precisar de novas tentativas --- as transações apenas de leitura nunca ocasionam conflito de serialização.
O modo serializável fornece uma garantia rigorosa que cada transação enxerga apenas visões completamente consistentes do banco de dados. Entretanto, a aplicação precisa estar preparada para executar novamente as transações quando as atualizações concorrentes tornam impossível sustentar a ilusão de uma execução serial. Uma vez que o custo de refazer transações complexas pode ser significativo, este modo é recomendado somente quando as transações efetuando atualizações contêm lógica suficientemente complexa a ponto de produzir respostas erradas no modo Read Committed. Habitualmente, o modo serializável é necessário quando a transação realiza várias consultas sucessivas que necessitam enxergar visões idênticas do banco de dados.