READ COMMITTED (lê efetivado) é o nível de isolamento padrão no PostgreSQL. Quando uma transação usa este nível de isolamento, uma consulta SELECT (sem a cláusula FOR UPDATE/SHARE) vê apenas os dados efetivados antes do início da consulta; nunca vê dados não efetivados nem alterações efetivadas por transações concorrentes durante a execução da consulta. Com efeito, uma consulta SELECT vê o instantâneo do banco de dados no instante em que a consulta começa a ser executada. Entretanto, o comando SELECT vê os efeitos de atualizações anteriores executadas em sua própria transação, mesmo que ainda não tenham sido efetivadas. Note também que dois comandos SELECT sucessivos podem ver dados diferentes, mesmo que estejam na mesma transação, se outras transações efetivarem alterações após o primeiro comando SELECT iniciar, e antes do segundo comando SELECT iniciar.

Os comandos UPDATE, DELETE, SELECT FOR UPDATE e SELECT FOR SHARE se comportam da mesma forma que o comando SELECT em termos de procura de linhas de destino: encontram apenas as linhas de destino que estavam efetivadas na hora do início do comando. Entretanto, essa linha de destino pode já ter sido atualizada (ou excluída, ou bloqueada) por outra transação concorrente no momento em que for encontrada. Neste caso, o candidato a atualizar aguarda até que a primeira transação de atualização seja efetivada ou desfeita (se ainda estiver em andamento). Se o primeiro atualizador desfizer a transação, seus efeitos serão negados e o segundo atualizador poderá prosseguir com a atualização da linha originalmente encontrada. Se o primeiro atualizador efetivar a transação, o segundo atualizador ignora a linha se o primeiro atualizador a excluir, caso contrário tenta aplicar sua operação à versão atualizada da linha. A condição de procura do comando (a cláusula WHERE) é reavaliada para ver se a versão atualizada da linha ainda corresponde à condição da procura. Neste caso, o segundo atualizador prossegue com sua operação usando a versão atualizada da linha. No caso dos comandos SELECT FOR UPDATE e SELECT FOR SHARE, isto significa que o cliente recebe a versão atualizada da linha que está bloqueada.

O comando INSERT com a cláusula ON CONFLICT DO UPDATE se comporta de maneira semelhante. No modo READ COMMITTED cada linha indicada para inserção irá inserir ou atualizar. A menos que existam erros não relacionados, um destes dois resultados será garantido. Se o conflito se originar em outra transação, cujos efeitos ainda não são visíveis ao comando INSERT, a cláusula UPDATE irá afetar esta linha, embora possivelmente nenhuma versão desta linha seja convencionalmente visível para o comando.

O comando INSERT com a cláusula ON CONFLICT DO NOTHING pode fazer com que a inserção não prossiga para uma linha devido ao resultado de outra transação cujos efeitos não são visíveis para o instantâneo do comando INSERT. Novamente, este caso se dá apenas no modo READ COMMITTED.

O comando MERGE permite ao usuário especificar várias combinações de subcomandos INSERT, UPDATE e DELETE. Um comando MERGE com subcomandos INSERT e UPDATE é semelhante a um comando INSERT com uma cláusula ON CONFLICT DO UPDATE, mas não garante que o INSERT ou o UPDATE irá ocorrer. Se o comando MERGE tentar uma operação de UPDATE ou DELETE e a linha for atualizada concorrentemente, mas a condição de junção ainda for atendida para a tupla de destino corrente e a tupla de origem corrente, o comando MERGE irá se comportar da mesma forma que os comandos UPDATE ou DELETE e irá executar sua ação na versão atualizada da linha. Entretanto, como o comando MERGE pode especificar várias ações e estas ações podem ser condicionais, as condições para cada ação são reavaliadas na versão atualizada da linha, começando pela primeira ação, mesmo que a ação que originalmente correspondia apareça mais tarde na lista de ações. Por outro lado, se a linha for atualizada concorrentemente de forma que a condição de junção falhe, então o comando MERGE irá avaliar as ações NOT MATCHED BY SOURCE e NOT MATCHED [BY TARGET] em seguida e irá executar a primeira de cada tipo que for bem-sucedida. Se a linha for excluída concorrentemente, o comando MERGE irá avaliará as ações NOT MATCHED [BY TARGET] e irá executará a primeira que for bem-sucedida. Se o comando MERGE tentar um INSERT e estiver presente um índice único e uma linha duplicada for inserida simultaneamente, será gerado um erro de violação de unicidade; o comando MERGE não tenta evitar tais erros reiniciando a avaliação das condições MATCHED.

Devido às regras acima, é possível que um comando de atualização veja um instantâneo inconsistente: o comando pode ver os efeitos dos comandos de atualização concorrentes nas mesmas linhas que está tentando atualizar, mas não vê os efeitos destes comandos em outras linhas do banco de dados. Esse comportamento torna o modo READ COMMITTED inadequado para comandos que envolvem condições de procura complexas; no entanto, é o comportamento ideal para os casos mais simples. Por exemplo, considere transferir US$ 100 de uma conta para outra:

BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;

Se duas dessas transações tentarem alterar concorrentemente o saldo da conta 7534, queremos claramente que a segunda transação comece com a versão atualizada da linha da conta. Como cada comando está afetando apenas uma linha predeterminada, deixá-lo ver a versão atualizada da linha não cria nenhuma inconsistência problemática.

O uso mais complexo pode produzir resultados indesejáveis no modo READ COMMITTED. Por exemplo, considere um comando DELETE operando em dados que estão sendo adicionados e removidos de seus critérios de procura por outro comando. Supondo que website seja uma tabela contendo duas linhas com website.hits igual a 9 e 10:

BEGIN;
UPDATE website SET hits = hits + 1;
-- executado a partir de outra sessão: DELETE FROM website WHERE hits = 10;
COMMIT;

O comando DELETE não terá efeito, mesmo havendo uma linha website.hits = 10 antes e depois do comando UPDATE. Isto ocorre, porque o valor da linha de pré-atualização 9 é ignorado, e quando o comando UPDATE é concluído, e o comando DELETE obtém o bloqueio, o novo valor da linha não é mais 10, e sim 11, que não corresponde mais aos critérios.

Como o modo READ COMMITTED inicia cada comando com um novo instantâneo, que inclui todas as transações efetivadas até aquele instante, os comandos subsequentes na mesma transação verão os efeitos da transação concorrente confirmada em qualquer caso. O ponto em questão acima é se um comando único vê ou não uma visão absolutamente 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 simples de usar; entretanto, não é suficiente para todos os casos. As aplicações que fazem consultas e atualizações complexas podem exigir uma visão mais rigorosamente consistente do banco de dados que o modo READ COMMITTED fornece.

O nível de isolamento REPEATABLE READ (leitura repetível) vê apenas os dados efetivados antes do início da transação; durante a execução da transação, ela nunca vê dados não efetivados nem alterações efetivados por transações concorrentes. (Entretanto, cada consulta vê os efeitos das atualizações anteriores executadas em sua própria transação, mesmo que ainda não tenham sido efetivadas.) Essa é uma garantia mais forte que a exigida pelo padrão SQL para este nível de isolamento, e evita todos os fenômenos descritos na Tabela 13.1, exceto para anomalias de serialização. Como mencionado acima, isto é permitido especificamente pelo padrão, que descreve apenas as proteções mínimas que cada nível de isolamento deve fornecer.

Este nível é diferente de READ COMMITTED, porque uma consulta em uma transação de leitura repetível vê um instantâneo do início da transação, e não do início da instrução corrente dentro da transação. Assim, comandos SELECT sucessivos dentro de uma transação única veem os mesmos dados, ou seja, não veem as alterações feitas por outras transações efetivadas após o início de sua própria transação.

As aplicações que usam este nível de isolamento devem estar preparadas para repetir as transações devido a falhas de serialização.

Os comandos UPDATE, DELETE, MERGE, SELECT FOR UPDATE, e SELECT FOR SHARE se comportam da mesma forma que o comando SELECT em termos de procura de linhas de destino: encontram apenas as linhas de destino que estavam efetivadas na hora do início da transação. Entretanto, essa linha de destino pode já ter sido atualizada (ou excluída, ou bloqueada) por outra transação concorrente no momento em que for encontrada. Neste caso, a transação REPEATABLE READ aguarda até que a primeira transação de atualização efetive ou desfaça a operação (se ainda estiver em andamento). Se o primeiro atualizador desfizer, seus efeitos serão negados, e a transação REPEATABLE READ poderá prosseguir com a atualização da linha originalmente encontrada. Mas se o primeiro atualizador efetivar (e realmente atualizou ou excluiu a linha, e não apenas a bloqueou), a transação REPEATABLE READ será desfeita com a mensagem

ERRO: Não foi possível serializar o acesso devido
      a existência de atualização concorrente.

porque uma transação REPEATABLE READ não pode modificar ou bloquear linhas alteradas por outras transações após o início de sua própria transação.

Quando uma aplicação recebe esta mensagem de erro, deve interromper a transação corrente, e tentar novamente toda a transação desde o início. Na segunda vez, a transação verá a alteração efetivada anteriormente como parte de sua visão inicial do banco de dados e, portanto, não haverá conflito lógico em usar a nova versão da linha como ponto de partida para a atualização da nova transação.

Note que apenas as transações de atualização podem precisar ser repetidas; transações somente de leitura nunca terão conflitos de serialização.

O modo REPEATABLE READ fornece uma garantia rigorosa de que cada transação tenha uma visão completamente estável do banco de dados. Entretanto, essa visão nem sempre será consistente com alguma execução em série (uma de cada vez) de transações concorrentes do mesmo nível. Por exemplo, neste nível mesmo uma transação de leitura apenas pode ver um registro de controle atualizado mostrando que o lote foi concluído, mas não ver um dos registros de detalhes que é logicamente parte do lote, porque leu uma revisão anterior do registro de controle. As tentativas de impor regras de negócios por transações executadas neste nível de isolamento, provavelmente não funcionarão corretamente sem o uso cuidadoso de bloqueios explícitos para bloquear transações conflitantes.

O nível de isolamento REPEATABLE READ é implementado usando uma técnica conhecida na literatura acadêmica de banco de dados, e em alguns outros produtos de banco de dados, como isolamento de instantâneo (snapshot isolation). As diferenças de comportamento e desempenho podem ser observadas quando se compara com sistemas que usam a técnica tradicional de bloqueio que reduz a simultaneidade. Alguns outros sistemas podem até oferecer isolamento de leitura repetível e instantâneo como níveis de isolamento distintos com comportamentos diferentes. Os fenômenos permitidos que distinguem as duas técnicas não foram formalizados por pesquisadores de banco de dados até depois que o padrão SQL foi desenvolvido, e estão fora do escopo deste manual. Para um tratamento completo, consulte [berenson95].

O nível de isolamento SERIALIZABLE (serializável) fornece o isolamento de transação mais estrito. Esse nível emula a execução de transações seriais para todas as transações efetivadas; como se as transações tivessem sido executadas uma após a outra, em série, em vez de concorrentemente. Entretanto, assim como no nível REPEATABLE READ, as aplicações que usam este nível devem estar preparadas para repetir as transações devido a falhas de serialização. Na verdade, este nível de isolamento funciona exatamente da mesma forma que REPEATABLE READ, exceto por também monitorar as condições que podem fazer com que a execução de um conjunto simultâneo de transações serializáveis se comporte de maneira inconsistente com todas as possíveis execuções seriais (uma de cada vez) dessas transações. Esse monitoramento não introduz nenhum bloqueio além do presente em REPEATABLE READ (leitura repetível), mas há alguma sobrecarga no monitoramento, e a detecção das condições que podem causar uma anomalia de serialização acionam uma falha de serialização.

Como exemplo, considere a tabela mytab contendo inicialmente:

 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

Suponha que a transação serializável A calcule:

SELECT SUM(value) FROM mytab WHERE class = 1;

e, em seguida, insira o resultado (30) no campo value em uma nova linha com o campo class = 2. Ao mesmo tempo, a transação serializável B calcula:

SELECT SUM(value) FROM mytab WHERE class = 2;

e obtém o resultado 300, que insere em uma nova linha com o campo class = 1. Em seguida, as duas transações tentam efetivar (COMMIT). Se qualquer uma das transações estivesse sendo executada no nível de isolamento REPEATABLE READ, as duas teriam permissão para efetivar; mas como não há ordem de execução em série consistente com este resultado, o uso de transações SERIALIZABLE permite que uma transação seja efetivada, mas desfaz a outra com a mensagem:

ERRO: Não foi possível serializar o acesso devido a
      dependências de leitura/escrita entre as transações.

Isto acontece, porque se A tivesse sido executada antes de B, então B teria calculado a soma 330, e não 300. Da mesma forma, a outra ordem teria resultado em uma soma diferente calculada por A.

Ao se confiar em transações SERIALIZABLE para evitar anomalias, é importante que qualquer dado lido de uma tabela permanente de usuário não seja considerado válido até que a transação que o leu tenha sido efetivada com sucesso. Isto é verdade mesmo para as transações de leitura apenas, exceto que os dados lidos dentro de uma transação de leitura apenas adiável (deferrable) são considerados válidos assim que são lidos, porque tal transação espera até que possa obter um instantâneo garantido de estar livre destes problemas antes de começar a ler quaisquer dados. Em todos os outros casos, as aplicações não devem depender de resultados lidos durante uma transação interrompida posteriormente; em vez disso, devem tentar novamente a transação até que seja bem-sucedida.

Para garantir a verdadeira serialização, o PostgreSQL usa bloqueio de predicado, significando que mantém bloqueios que permitem determinar se uma escrita teria impacto no resultado de uma leitura anterior por uma transação concorrente, se ela tivesse sido executada primeiro. No PostgreSQL este tipo de bloqueio não causa nenhum bloqueio real e, portanto, não pode desempenhar qualquer papel na causa de um impasse (deadlock). Os bloqueios de predicado são usados para identificar e sinalizar dependências entre transações serializáveis concorrentes que, em determinadas combinações, podem levar a anomalias de serialização. Por outro lado, uma transação READ COMMITTED ou REPEATABLE READ que deseja garantir a consistência dos dados pode precisar bloquear a tabela inteira, o que pode bloquear outros usuários que desejam usar essa tabela, ou pode usar SELECT FOR UPDATE ou SELECT FOR SHARE, que não apenas pode bloquear outras transações, mas também causar acesso ao disco.

Os bloqueios de predicado no PostgreSQL, assim como na maioria dos outros sistemas de banco de dados, são baseados nos dados realmente acessados por uma transação. Os bloqueios de predicado são mostrados na visão do sistema pg_locks, com o campo mode igual a SIReadLock. Os bloqueios específicos adquiridos durante a execução de uma consulta dependem do plano usado pela consulta, e vários bloqueios de granulação mais fina (por exemplo, bloqueios de tupla) podem ser combinados em menos bloqueios de granularidade mais grossa (por exemplo, bloqueios de página) durante o curso da transação, para evitar o esgotamento da memória usada para rastrear os bloqueios. Uma transação READ ONLY pode liberar seus bloqueios SIRead antes da conclusão, se detectar que nenhum conflito ainda pode ocorrer, o que poderia levar a uma anomalia de serialização. Na verdade, as transações READ ONLY geralmente conseguem estabelecer este fato na inicialização, e evitar qualquer bloqueio de predicado. Se for solicitado explicitamente uma transação SERIALIZABLE READ ONLY DEFERRABLE, essa transação será bloqueada até ser possível estabelecer este fato. (Esse é o único caso em que as transações serializáveis são bloqueadas, mas as transações REPEATABLE READ não.) Por outro lado, os bloqueios SIRead geralmente precisam ser mantidos após a efetivação da transação, até que as transações de leitura e escrita sobrepostas sejam concluídas.

O uso consistente de transações serializáveis pode simplificar o desenvolvimento. A garantia de que qualquer conjunto de transações serializáveis concorrentes efetivadas com sucesso terá o mesmo efeito como se fossem executadas uma de cada vez significa que, se for possível demonstrar que uma única transação, conforme está escrita, fará a coisa certa quando for executada sozinha, pode-se ter a confiança de que fará a coisa certa em qualquer combinação de transações serializáveis, mesmo sem qualquer informação sobre o que essas outras transações podem fazer, ou não será efetivada com sucesso. É importante que o ambiente que utilize essa técnica tenha uma forma generalizada de lidar com falhas de serialização (que sempre retornam com um valor SQLSTATE igual a '40001'), porque será muito difícil prever exatamente quais transações podem contribuir para as dependências de leitura/escrita e precisam ser desfeitas para evitar anomalias de serialização. O monitoramento de dependências de leitura/escrita tem um custo, assim como o reinício das transações terminadas por falha de serialização, mas estes custos são compensados pelo custo dos bloqueios envolvidos no uso de bloqueios explícitos e SELECT FOR UPDATE ou SELECT FOR SHARE. As transações serializáveis são a melhor opção de desempenho para alguns ambientes.

Embora o nível de isolamento de transação SERIALIZABLE do PostgreSQL só permita que transações concorrentes sejam efetivadas se puderem provar haver uma ordem serial de execução que produziria o mesmo efeito, o PostgreSQL nem sempre evita que sejam gerados erros que não ocorreriam numa execução serial verdadeira. Em particular, é possível ver violações de restrição de unicidade causadas por conflitos com transações serializáveis sobrepostas, mesmo após verificar explicitamente que a chave não está presente antes de tentar inseri-la. Isto pode ser evitado certificando-se que todas as transações serializáveis que inserem chaves potencialmente conflitantes verifiquem explicitamente se podem fazê-lo primeiro. Por exemplo, imagine uma aplicação que solicita ao usuário uma nova chave e, em seguida, verifica se a chave já não existe tentando selecioná-la primeiro, ou gera uma nova chave selecionando a chave máxima existente e somando um. Se algumas transações serializáveis inserem novas chaves diretamente sem seguir este protocolo, violações de restrições de unicidade podem ser relatadas, mesmo nos casos onde não podem ocorrer em uma execução serial das transações concorrentes.

Para um desempenho ideal quando se conta com transações serializáveis para controle de concorrência, os seguintes problemas devem ser considerados:

O nível de isolamento serializável é implementado usando uma técnica conhecida na literatura acadêmica de banco de dados como “isolamento de instantâneo serializável” (Serializable Snapshot Isolation), que se baseia no isolamento de instantâneo adicionando verificações de anomalias de serialização. Algumas diferenças de comportamento e desempenho podem ser observadas quando se compara com outros sistemas que usam a técnica tradicional de bloqueio. Consulte [ports12] para obter informações detalhadas.