Olá Habr! Com este artigo, inicio um conjunto de séries (ou uma série de conjuntos? - Em uma palavra, a ideia é grandiosa) sobre a estrutura interna do PostgreSQL.

O material será baseado em cursos de treinamento (em russo) sobre administração que Pavel pluzanov e eu estamos criando. Nem todo mundo gosta de assistir a vídeos (eu definitivamente não), e ler slides, mesmo com comentários, não é bom.

Infelizmente, o único curso disponível em inglês no momento é Introdução ao PostgreSQL 11 por 2 dias .

Obviamente, os artigos não serão exatamente iguais ao conteúdo dos cursos. Vou falar apenas sobre como tudo está organizado, omitindo a própria administração, mas tentarei fazê-lo com mais detalhes e com mais profundidade. E acredito que esse conhecimento é tão útil para um desenvolvedor de aplicativos quanto para um administrador.

Vou direcionar aqueles que já têm alguma experiência no uso do PostgreSQL e, pelo menos em geral, entendem o que é o quê. O texto será muito difícil para iniciantes. Por exemplo, não direi uma palavra sobre como instalar o PostgreSQL e executar o psql.

O material em questão não varia muito de versão para versão, mas usarei o atual, o 11º PostgreSQL de baunilha.

A primeira série lida com questões relacionadas ao isolamento e simultaneidade multiversão, e o plano da série é o seguinte:

1. Isolamento conforme entendido pelo padrão e pelo PostgreSQL (este artigo).
2. Garfos, arquivos, páginas - o que está acontecendo no nível físico.
3. Versões de linha , transações virtuais e subtransações.
4. Instantâneos de dados e a visibilidade das versões de linha; o horizonte de eventos.
5. Aspiração na página e atualizações QUENTES .
6. Vácuo normal .
7. Autovacuum .
8. Identificação da transação envolvente e congelada .

Lá vamos nós!

Antes de começar, gostaria de agradecer a Elena Indrupskaya por traduzir os artigos para o inglês.

O que é isolamento e por que é importante?

Provavelmente, todos estão pelo menos conscientes da existência de transações, encontraram a abreviatura ACID e ouviram falar sobre níveis de isolamento. Mas ainda enfrentamos a opinião de que isso se refere à teoria, o que não é necessário na prática. Portanto, passarei algum tempo tentando explicar por que isso é realmente importante.

É improvável que você fique satisfeito se um aplicativo obtiver dados incorretos do banco de dados ou se o aplicativo gravar dados incorretos no banco de dados.

Mas o que são dados "corretos"? Sabe-se que restrições de integridade , como NOT NULL ou UNIQUE, podem ser criadas no nível do banco de dados. Se os dados sempre atenderem às restrições de integridade (e é assim que o DBMS garante isso), eles serão integrais.

Corretas e integrais são as mesmas coisas? Não exatamente. Nem todas as restrições podem ser especificadas no nível do banco de dados. Algumas das restrições são muito complicadas, por exemplo, que abrangem várias tabelas ao mesmo tempo. E mesmo que uma restrição em geral possa ter sido definida no banco de dados, mas por alguma razão não foi, isso não significa que a restrição possa ser violada.

Portanto, a correção é mais forte que a integridade , mas não sabemos exatamente o que isso significa. Não temos nada além de admitir que o "padrão ouro" de correção é um aplicativo que, como gostaríamos de acreditar, foi escrito corretamente e nunca corre errado. De qualquer forma, se um aplicativo não violar a integridade, mas violar a correção, o DBMS não o saberá e não pegará o aplicativo em flagrante.

Além disso, usaremos o termo consistência para nos referirmos à correção.

Suponhamos, no entanto, que um aplicativo execute apenas sequências corretas de operadores. Qual é o papel do DBMS se o aplicativo estiver correto como está?

Primeiro, verifica-se que uma sequência correta de operadores pode interromper temporariamente a consistência dos dados e, curiosamente, isso é normal. Um exemplo banal, mas claro, é uma transferência de fundos de uma conta para outra. A regra de consistência pode parecer assim: uma transferência nunca altera a quantidade total de dinheiro nas contas (é muito difícil especificar no SQL uma restrição de integridade, portanto, existe no nível do aplicativo e é invisível para o DBMS). Uma transferência consiste em duas operações: a primeira reduz os fundos em uma conta e a segunda - aumenta na outra. A primeira operação quebra a consistência dos dados, enquanto a segunda a restaura.

Um bom exercício é implementar a regra acima no nível de restrições de integridade.

E se a primeira operação for realizada e a segunda não? De fato, sem muito barulho: durante a segunda operação, pode ocorrer uma falha de eletricidade, uma pane no servidor, uma divisão por zero - qualquer que seja. É claro que a consistência será quebrada, e isso não pode ser permitido. Em geral, é possível resolver esses problemas no nível do aplicativo, mas à custa de enormes esforços; no entanto, felizmente, não é necessário: isso é feito pelo DBMS. Mas, para fazer isso, o DBMS deve saber que as duas operações são um todo indivisível. Ou seja, uma transação .

Parece interessante: como o DBMS sabe que as operações compõem uma transação, ajuda a manter a consistência, garantindo que as transações sejam atômicas, e faz isso sem saber nada sobre regras específicas de consistência.

Mas há um segundo ponto, mais sutil. Assim que várias transações simultâneas aparecem no sistema, que são absolutamente corretas separadamente, elas podem não funcionar corretamente juntas. Isso ocorre porque a ordem das operações é confusa: você não pode assumir que todas as operações de uma transação são executadas primeiro e depois todas as operações da outra.

Uma observação sobre simultaneidade. De fato, as transações podem ser executadas simultaneamente em um sistema com um processador multinúcleo, matriz de disco etc. Mas o mesmo raciocínio se aplica a um servidor que executa comandos sequencialmente, em um modo de compartilhamento de tempo: durante certos ciclos de relógio, uma transação é executada e, durante os próximos ciclos, a outra. Às vezes, o termo execução simultânea é usado para uma generalização.

Situações em que transações corretas trabalham juntas incorretamente são chamadas de anomalias de execução simultânea.

Para um exemplo simples: se um aplicativo deseja obter dados corretos do banco de dados, ele não deve, pelo menos, ver alterações de outras transações não confirmadas. Caso contrário, você poderá não apenas obter dados inconsistentes, mas também ver algo que nunca esteve no banco de dados (se a transação for cancelada). Essa anomalia é chamada de leitura suja .

Existem outras anomalias mais complexas, com as quais trataremos um pouco mais tarde.

Certamente é impossível evitar a execução simultânea: caso contrário, de que tipo de desempenho podemos falar? Mas você também não pode trabalhar com dados incorretos.

E novamente o DBMS vem em socorro. Você pode fazer transações executadas como se sequencialmente, como se uma após a outra. Em outras palavras - isoladas uma da outra. Na realidade, o DBMS pode executar operações combinadas, mas garantir que o resultado de uma execução simultânea seja o mesmo que o resultado de algumas das possíveis execuções seqüenciais. E isso elimina quaisquer possíveis anomalias.

Então chegamos à definição:

Uma transação é um conjunto de operações executadas por um aplicativo que transfere um banco de dados de um estado correto para outro estado correto (consistência), desde que a transação seja concluída (atomicidade) e sem interferência de outras transações (isolamento).

Essa definição une as três primeiras letras do acrônimo ACID. Eles estão tão intimamente relacionados um com o outro que não faz sentido considerar um sem os outros. De fato, também é difícil destacar a letra D (durabilidade). De fato, quando um sistema falha, ele ainda possui alterações de transações não confirmadas, com as quais você precisa fazer algo para restaurar a consistência dos dados.

Tudo ficaria bem, mas a implementação do isolamento completo é uma tarefa tecnicamente difícil que implica uma redução na taxa de transferência do sistema. Portanto, na prática com muita frequência (nem sempre, mas quase sempre), o isolamento enfraquecido é usado, o que evita algumas, mas nem todas as anomalias. Isso significa que parte do trabalho para garantir a correção dos dados recai sobre o aplicativo. Por esse motivo, é muito importante entender qual nível de isolamento é usado no sistema, quais garantias ele oferece e o que não fornece, e como escrever o código correto nessas condições.

Níveis de isolamento e anomalias no padrão SQL

O padrão SQL há muito descreveu quatro níveis de isolamento. Esses níveis são definidos listando anomalias que são permitidas ou não quando transações são executadas simultaneamente nesse nível. Portanto, para falar sobre esses níveis, é necessário conhecer as anomalias.

Enfatizo que nesta parte estamos falando sobre o padrão, isto é, sobre uma teoria, na qual a prática se baseia significativamente, mas da qual ao mesmo tempo diverge significativamente. Portanto, todos os exemplos aqui são especulativos. Eles usarão as mesmas operações nas contas dos clientes: isso é bastante demonstrativo, embora, reconhecidamente, nada tenha a ver com a forma como as operações bancárias são organizadas na realidade.

Atualização de perda

Vamos começar com a atualização perdida . Essa anomalia ocorre quando duas transações lêem a mesma linha da tabela, uma transação atualiza essa linha e a segunda transação também atualiza a mesma linha sem levar em consideração as alterações feitas pela primeira transação.

Por exemplo, duas transações aumentarão o valor na mesma conta em ₽100 (₽ é o sinal de moeda do rublo russo). A primeira transação lê o valor atual (± 1000) e a segunda transação lê o mesmo valor. A primeira transação aumenta a quantidade (isso dá ₽1100) e grava esse valor. A segunda transação age da mesma maneira: obtém o mesmo ₽1100 e grava esse valor. Como resultado, o cliente perdeu £ 100.

O padrão não permite atualizações perdidas em nenhum nível de isolamento.

Leitura e leitura sujas não confirmadas

Uma leitura suja é o que já conhecemos. Essa anomalia ocorre quando uma transação lê alterações que ainda não foram confirmadas por outra transação.

Por exemplo, a primeira transação transfere todo o dinheiro da conta do cliente para outra conta, mas não confirma a alteração. Outra transação lê o saldo da conta, para obter ₽0, e se recusa a sacar dinheiro para o cliente, embora a primeira transação aborte e reverta suas alterações, portanto o valor 0 nunca existiu no banco de dados.

O padrão permite leituras sujas no nível Read Uncommitted.

Leitura não repetível e leitura confirmada

Uma anomalia de leitura não repetível ocorre quando uma transação lê a mesma linha duas vezes e, entre as leituras, a segunda transação modifica (ou exclui) essa linha e confirma as alterações. Em seguida, a primeira transação obterá resultados diferentes.

Por exemplo, permita que uma regra de consistência proíba valores negativos nas contas dos clientes . A primeira transação reduzirá o valor da conta em ₽100. Ele verifica o valor atual, obtém ₽1000 e decide que a diminuição é possível. Ao mesmo tempo, a segunda transação reduz o valor da conta para zero e confirma as alterações. Se a primeira transação agora verificar novamente o valor, ela receberá get0 (mas já decidiu reduzir o valor e a conta "ficará no vermelho").

O padrão permite leituras não repetíveis nos níveis Read Uncommitted e Read Committed. Mas a leitura confirmada não permite leituras sujas.

Leitura fantasma e leitura repetida

Uma leitura fantasma ocorre quando uma transação lê um conjunto de linhas pela mesma condição duas vezes e, entre as leituras, a segunda transação adiciona linhas que atendem a essa condição (e confirma as alterações). Em seguida, a primeira transação terá um conjunto diferente de linhas.

Por exemplo, permita que uma regra de consistência impeça que um cliente tenha mais de 3 contas . A primeira transação abrirá uma nova conta, verificará o número atual de contas (por exemplo, 2) e decide que a abertura é possível. Ao mesmo tempo, a segunda transação também abre uma nova conta para o cliente e confirma as alterações. Agora, se a primeira transação verificar novamente o número, obterá 3 (mas já está abrindo outra conta e o cliente parece ter 4).

O padrão permite leituras fantasmas nos níveis de leitura não confirmada, leitura confirmada e leitura repetida. No entanto, a leitura não repetível não é permitida no nível de leitura repetida.

A ausência de anomalias e serializáveis

O padrão define mais um nível - serializável - que não permite nenhuma anomalia. E isso não é o mesmo que proibir atualizações perdidas e leituras sujas, não repetíveis ou fantasmas.

O fato é que existem anomalias muito mais conhecidas do que as listadas no padrão e também um número desconhecido de outras ainda desconhecidas.

O nível serializável deve evitar absolutamente todas as anomalias. Isso significa que, nesse nível, um desenvolvedor de aplicativos não precisa pensar em execução simultânea. Se as transações executarem uma sequência correta de operadores trabalhando separadamente, os dados também serão consistentes quando essas transações forem executadas simultaneamente.

Tabela Resumo

Agora podemos fornecer uma tabela conhecida. Mas aqui a última coluna, que está faltando no padrão, é adicionada para maior clareza.

Por que exatamente essas anomalias?

Por que o padrão lista apenas algumas das muitas anomalias possíveis e por que elas são exatamente essas?

Ninguém parece saber com certeza. Mas aqui a prática está evidentemente à frente da teoria, portanto é possível que naquele momento (do padrão SQL: 92) outras anomalias não fossem apenas pensadas.

Além disso, assumiu-se que o isolamento deve ser construído em bloqueios. A idéia por trás do amplamente utilizado protocolo de bloqueio de duas fases (2PL) é que, durante a execução, uma transação bloqueia as linhas com as quais está trabalhando e libera os bloqueios após a conclusão. Simplificando consideravelmente, quanto mais bloqueios uma transação adquire, melhor ela é isolada de outras transações. Mas o desempenho do sistema também sofre mais, porque, em vez de trabalharem juntas, as transações começam a ficar na fila para as mesmas linhas.

Meu sentimento é que é apenas o número de bloqueios necessários, o que explica a diferença entre os níveis de isolamento do padrão.

Se uma transação bloquear as linhas a serem modificadas da atualização, mas não da leitura, obtemos o nível Read Uncommitted: alterações perdidas não são permitidas, mas dados não confirmados podem ser lidos.

Se uma transação bloquear as linhas a serem modificadas tanto na leitura quanto na atualização, obteremos o nível Read Committed: você não poderá ler dados não confirmados, mas poderá obter um valor diferente (leitura não repetível) ao acessar a linha novamente.

Se uma transação bloqueia as linhas para serem lidas e modificadas, tanto para a leitura quanto para a atualização, obtemos o nível de leitura repetida: reler a linha retornará o mesmo valor.

Mas há um problema com o Serializable: você não pode bloquear uma linha que ainda não existe. Portanto, uma leitura fantasma ainda é possível: outra transação pode adicionar (mas não excluir) uma linha que atenda às condições de uma consulta executada anteriormente e essa linha será incluída na re-seleção.

Portanto, para implementar o nível Serializable, os bloqueios normais não são suficientes - você precisa bloquear condições (predicados) em vez de linhas. Portanto, esses bloqueios foram chamados de predicado . Eles foram propostos em 1976, mas sua aplicabilidade prática é limitada por condições bastante simples para as quais é claro como unir dois predicados diferentes. Até onde eu sei, esses bloqueios nunca foram implementados em nenhum sistema até agora.

Níveis de isolamento no PostgreSQL

Com o tempo, os protocolos de gerenciamento de transações baseados em bloqueio foram substituídos pelo protocolo Snapshot Isolation (SI). Sua idéia é que cada transação funcione com uma captura instantânea consistente dos dados em um determinado momento, e apenas essas alterações entrem na captura instantânea que foram confirmadas antes de serem criadas.

Esse isolamento impede automaticamente leituras sujas. Formalmente, você pode especificar o nível Read Uncommitted no PostgreSQL, mas ele funcionará exatamente da mesma maneira que o Read Committed. Portanto, ainda não falaremos sobre o nível Read Uncommitted.

O PostgreSQL implementa uma variante multiversão deste protocolo. A idéia da simultaneidade de várias versões é que várias versões da mesma linha podem coexistir em um DBMS. Isso permite criar uma captura instantânea dos dados usando versões existentes e usar um mínimo de bloqueios. Na verdade, apenas as alterações subsequentes na mesma linha são bloqueadas. Todas as outras operações são executadas simultaneamente: transações de gravação nunca bloqueiam transações somente leitura e transações somente leitura nunca bloqueiam nada.

Ao usar instantâneos de dados, o isolamento no PostgreSQL é mais rígido do que o exigido pelo padrão: o nível de Leitura Repetível não permite não apenas leituras não repetíveis, mas também leituras fantasmas (embora não forneça isolamento completo). E isso é alcançado sem perda de eficiência.

Falaremos nos próximos artigos sobre como a simultaneidade multiversão é implementada “sob o capô” e agora examinaremos detalhadamente cada um dos três níveis com o olhar do usuário (como você sabe, o mais interessante está oculto por trás de “outras anomalias ”). Para fazer isso, vamos criar um quadro de contas. Alice e Bob têm 0001000 cada, mas Bob tem duas contas abertas:

=> CREATE TABLE accounts( id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, number text UNIQUE, client text, amount numeric ); => INSERT INTO accounts VALUES (1, '1001', 'alice', 1000.00), (2, '2001', 'bob', 100.00), (3, '2002', 'bob', 900.00); 

Leitura confirmada

A ausência de leitura suja

É fácil garantir que dados sujos não possam ser lidos. Começamos a transação. Por padrão, ele usará o nível de isolamento Read Committed:

 => BEGIN; => SHOW transaction_isolation; 

  transaction_isolation ----------------------- read committed (1 row) 

Mais precisamente, o nível padrão é definido pelo parâmetro, que pode ser alterado se necessário:

 => SHOW default_transaction_isolation; 

  default_transaction_isolation ------------------------------- read committed (1 row) 

Portanto, em uma transação aberta, retiramos fundos da conta, mas não confirmamos as alterações. A transação vê suas próprias alterações:

 => UPDATE accounts SET amount = amount - 200 WHERE id = 1; => SELECT * FROM accounts WHERE client = 'alice'; 

  id | number | client | amount ----+--------+--------+-------- 1 | 1001 | alice | 800.00 (1 row) 

Na segunda sessão, iniciaremos outra transação com o mesmo nível Read Committed. Para distinguir entre as transações, os comandos da segunda transação serão recuados e marcados com uma barra.

Para repetir os comandos acima (o que é útil), você precisa abrir dois terminais e executar o psql em cada um. No primeiro terminal, você pode inserir os comandos de uma transação e no segundo - os da outra.

 | => BEGIN; | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+--------- | 1 | 1001 | alice | 1000.00 | (1 row) 

Como esperado, a outra transação não vê alterações não confirmadas, pois leituras sujas não são permitidas.

Leitura não repetível

Agora deixe a primeira transação confirmar as alterações e a segunda execute novamente a mesma consulta.

 => COMMIT; 

 | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

 | => COMMIT; 

A consulta já obtém novos dados - e essa é a anomalia de leitura não repetível , permitida no nível Read Committed.

Conclusão prática : em uma transação, você não pode tomar decisões com base nos dados lidos por um operador anterior, porque as coisas podem mudar entre a execução dos operadores. Aqui está um exemplo cujas variações ocorrem com tanta frequência no código do aplicativo que é considerado um antipadrão clássico:

  IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN UPDATE accounts SET amount = amount - 1000 WHERE id = 1; END IF; 

Durante o tempo decorrido entre a verificação e a atualização, outras transações podem alterar o estado da conta de qualquer maneira, de modo que esse "cheque" se protege do nada. É conveniente imaginar que entre os operadores de uma transação quaisquer outros operadores de outras transações possam "cunhar", por exemplo, da seguinte maneira:

  IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN ----- | UPDATE accounts SET amount = amount - 200 WHERE id = 1; | COMMIT; ----- UPDATE accounts SET amount = amount - 1000 WHERE id = 1; END IF; 

Se tudo puder ser estragado reorganizando os operadores, o código será gravado incorretamente. E não se engane que tal coincidência não acontecerá - com certeza.

Mas como escrever código corretamente? As opções tendem a ser as seguintes:

• Não para escrever código.
  Isso não é uma piada. Por exemplo, nesse caso, a verificação se transforma facilmente em uma restrição de integridade:
  ALTER TABLE accounts ADD CHECK amount >= 0;
  Nenhuma verificação é necessária agora: basta executar a operação e, se necessário, lidar com a exceção que ocorrerá se uma violação de integridade for tentada.
  
• Para usar uma única instrução SQL.
  Surgem problemas de consistência, pois no intervalo de tempo entre os operadores, outra transação pode ser concluída, o que alterará os dados visíveis. E se houver um operador, não haverá intervalos de tempo.
  O PostgreSQL possui técnicas suficientes para resolver problemas complexos com uma instrução SQL. Vamos observar as expressões de tabela comum (CTE), nas quais, entre o restante, você pode usar as instruções INSERT / UPDATE / DELETE, bem como a instrução INSERT ON CONFLICT, que implementa a lógica de "insert, mas se a linha já existir, update ”em uma declaração.
  
• Bloqueios personalizados.
  O último recurso é definir manualmente um bloqueio exclusivo em todas as linhas necessárias (SELECT FOR UPDATE) ou mesmo em toda a tabela (LOCK TABLE). Isso sempre funciona, mas anula os benefícios da simultaneidade de várias versões: algumas operações serão executadas seqüencialmente em vez da execução simultânea.
  

Leitura inconsistente

Antes de avançar para o próximo nível de isolamento, você deve admitir que não é tão simples quanto parece. A implementação do PostgreSQL é tal que permite outras anomalias, menos conhecidas, que não são regulamentadas pelo padrão.

Vamos supor que a primeira transação iniciou a transferência de fundos de uma conta de Bob para a outra:

 => BEGIN; => UPDATE accounts SET amount = amount - 100 WHERE id = 2; 

Ao mesmo tempo, outra transação conta o saldo de Bob e o cálculo é realizado em um loop em todas as contas de Bob. De fato, a transação começa com a primeira conta (e, obviamente, vê o estado anterior):

 | => BEGIN; | => SELECT amount FROM accounts WHERE id = 2; 

 | amount | -------- | 100.00 | (1 row) 

Neste momento, a primeira transação é concluída com êxito:

 => UPDATE accounts SET amount = amount + 100 WHERE id = 3; => COMMIT; 

E o outro lê o estado da segunda conta (e já vê o novo valor):

 | => SELECT amount FROM accounts WHERE id = 3; 

 | amount | --------- | 1000.00 | (1 row) 

 | => COMMIT; 

Portanto, a segunda transação obteve 1100 no total, ou seja, dados incorretos. E esta é uma anomalia de leitura inconsistente .

Como evitar essa anomalia enquanto permanece no nível Read Committed? Obviamente, use um operador. Por exemplo:

  SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

Até aqui, afirmei que a visibilidade dos dados só poderia mudar entre os operadores, mas isso é tão óbvio? E se a consulta demorar, ela poderá ver parte dos dados em um estado e parte em outro?

Vamos verificar. Uma maneira conveniente de fazer isso é inserir um atraso forçado no operador chamando a função pg_sleep. Seu parâmetro especifica o tempo de atraso em segundos.

 => SELECT amount, pg_sleep(2) FROM accounts WHERE client = 'bob'; 

Enquanto esse operador é executado, transferimos os fundos de volta em outra transação:

 | => BEGIN; | => UPDATE accounts SET amount = amount + 100 WHERE id = 2; | => UPDATE accounts SET amount = amount - 100 WHERE id = 3; | => COMMIT; 

O resultado mostra que o operador vê os dados no estado em que estavam no momento em que a execução do operador foi iniciada. Isso é, sem dúvida, correto.

  amount | pg_sleep ---------+---------- 0.00 | 1000.00 | (2 rows) 

Mas não é tão simples aqui também. O PostgreSQL permite definir funções, e as funções têm o conceito de uma categoria de volatilidade . Se uma função VOLATILE for chamada em uma consulta e outra consulta for executada nessa função, a consulta dentro da função verá dados que são inconsistentes com os dados na consulta principal.

 => CREATE FUNCTION get_amount(id integer) RETURNS numeric AS $$ SELECT amount FROM accounts a WHERE a.id = get_amount.id; $$ VOLATILE LANGUAGE sql; 

 => SELECT get_amount(id), pg_sleep(2) FROM accounts WHERE client = 'bob'; 

 | => BEGIN; | => UPDATE accounts SET amount = amount + 100 WHERE id = 2; | => UPDATE accounts SET amount = amount - 100 WHERE id = 3; | => COMMIT; 

Nesse caso, obtemos dados incorretos - ₽100 são perdidos:

  get_amount | pg_sleep ------------+---------- 100.00 | 800.00 | (2 rows) 

Enfatizo que esse efeito é possível apenas no nível de isolamento Read Committed e somente com as funções VOLATILE. O problema é que, por padrão, exatamente esse nível de isolamento e essa categoria de volatilidade são usados. Não caia na armadilha!

Leitura inconsistente em troca de alterações perdidas

Também podemos obter uma leitura inconsistente dentro de um único operador durante uma atualização, embora de uma maneira um tanto inesperada.

Vamos ver o que acontece quando duas transações tentam modificar a mesma linha. Agora, Bob tem 0001000 em duas contas:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 (2 rows) 

Iniciamos uma transação que reduz o saldo de Bob:

 => BEGIN; => UPDATE accounts SET amount = amount - 100 WHERE id = 3; 

Ao mesmo tempo, em outra transação, os juros são acumulados em todas as contas de clientes com o saldo total igual ou superior a 1.000:

 | => UPDATE accounts SET amount = amount * 1.01 | WHERE client IN ( | SELECT client | FROM accounts | GROUP BY client | HAVING sum(amount) >= 1000 | ); 

A execução do operador UPDATE consiste em duas partes. Primeiro, realmente SELECT é executado, o que seleciona as linhas a serem atualizadas que atendem à condição apropriada. Como a alteração na primeira transação não é confirmada, a segunda transação não pode vê-la e a alteração não afeta a seleção de linhas para acumulação de juros. Bem, então, as contas de Bob atendem à condição e, uma vez executada a atualização, seu saldo deve aumentar em ± 10.

O segundo estágio da execução é atualizar as linhas selecionadas uma a uma. Aqui a segunda transação é forçada a "travar" porque a linha com id = 3 já está bloqueada pela primeira transação.

Enquanto isso, a primeira transação confirma as alterações:

 => COMMIT; 

Qual será o resultado?

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+---------- 2 | 2001 | bob | 202.0000 3 | 2002 | bob | 707.0000 (2 rows) 

Bem, por um lado, o comando UPDATE não deve ver as alterações da segunda transação. Mas, por outro lado, não deve perder as alterações confirmadas na segunda transação.

Depois que o bloqueio é liberado, UPDATE relê a linha que está tentando atualizar (mas apenas esta). Como resultado, Bob acumulou ₽9, com base no valor de 00900. Mas se Bob tivesse 900 dólares, suas contas não deveriam estar na seleção.

Portanto, a transação obtém dados incorretos: algumas das linhas são visíveis em um ponto no tempo e outras em outra. Em vez de uma atualização perdida, novamente obtemos a anomalia de leitura inconsistente .

Os leitores atentos observam que, com alguma ajuda do aplicativo, você pode obter uma atualização perdida, mesmo no nível de Leitura confirmada. Por exemplo:

  x := (SELECT amount FROM accounts WHERE id = 1); UPDATE accounts SET amount = x + 100 WHERE id = 1; 

O banco de dados não é o culpado: ele obtém duas instruções SQL e não sabe nada sobre o fato de que o valor de x + 100 está de alguma forma relacionado ao valor das contas. Evite escrever código dessa maneira.

Leitura Repetível

A ausência de leituras não repetíveis e fantasmas

O próprio nome do nível de isolamento assume que a leitura é repetível. Vamos verificar e, ao mesmo tempo, garantir que não haja leituras fantasmas. Para fazer isso, na primeira transação, revertemos as contas de Bob para o estado anterior e criamos uma nova conta para Charlie:

 => BEGIN; => UPDATE accounts SET amount = 200.00 WHERE id = 2; => UPDATE accounts SET amount = 800.00 WHERE id = 3; => INSERT INTO accounts VALUES (4, '3001', 'charlie', 100.00); => SELECT * FROM accounts ORDER BY id; 

  id | number | client | amount ----+--------+---------+-------- 1 | 1001 | alice | 800.00 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 4 | 3001 | charlie | 100.00 (4 rows) 

Na segunda sessão, iniciamos a transação com o nível Leitura Repetível, especificando-a no comando BEGIN (o nível da primeira transação não é essencial).

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT * FROM accounts ORDER BY id; 

 | id | number | client | amount | ----+--------+--------+---------- | 1 | 1001 | alice | 800.00 | 2 | 2001 | bob | 202.0000 | 3 | 2002 | bob | 707.0000 | (3 rows) 

Agora a primeira transação confirma as alterações e a segunda executa novamente a mesma consulta.

 => COMMIT; 

 | => SELECT * FROM accounts ORDER BY id; 

 | id | number | client | amount | ----+--------+--------+---------- | 1 | 1001 | alice | 800.00 | 2 | 2001 | bob | 202.0000 | 3 | 2002 | bob | 707.0000 | (3 rows) 

 | => COMMIT; 

A segunda transação ainda vê exatamente os mesmos dados que no início: nenhuma alteração nas linhas existentes ou novas linhas são visíveis.

Nesse nível, você pode evitar se preocupar com algo que pode mudar entre dois operadores.

Erro de serialização em troca de alterações perdidas

Discutimos anteriormente que, quando duas transações atualizam a mesma linha no nível Read Committed, pode ocorrer uma anomalia de leitura inconsistente. Isso ocorre porque a transação em espera relê a linha bloqueada e, portanto, não a vê no mesmo ponto no tempo que as outras linhas.

No nível de Leitura Repetível, essa anomalia não é permitida, mas se ocorrer, nada poderá ser feito - portanto, a transação termina com um erro de serialização. Vamos verificar repetindo o mesmo cenário com acumulação de juros:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 (2 rows) 

 => BEGIN; => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; 

 | => BEGIN ISOLATION LEVEL REPEATABLE READ;<span/> | => UPDATE accounts SET amount = amount * 1.01<span/> | WHERE client IN (<span/> | SELECT client<span/> | FROM accounts<span/> | GROUP BY client<span/> | HAVING sum(amount) >= 1000<span/> | );<span/> 

 => COMMIT; 

 | ERROR: could not serialize access due to concurrent update 

 | => ROLLBACK; 

Os dados permaneceram consistentes:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 700.00 (2 rows) 

O mesmo erro ocorrerá no caso de qualquer outra mudança competitiva de uma linha, mesmo que as colunas de nossa preocupação não tenham sido realmente alteradas.

Conclusão prática : se o seu aplicativo usar o nível de isolamento Leitura Repetível para transações de gravação, ele deverá estar pronto para repetir as transações que foram finalizadas com um erro de serialização. Para transações somente leitura, esse resultado não é possível.

Gravação inconsistente

Portanto, no PostgreSQL, no nível de isolamento de Leitura Repetível, todas as anomalias descritas no padrão são evitadas. Mas nem todas as anomalias em geral. Acontece que existem exatamente duas anomalias que ainda são possíveis. (Isso é verdade não apenas no PostgreSQL, mas também em outras implementações do Snapshot Isolation.)

A primeira dessas anomalias é uma gravação inconsistente .

Deixe a seguinte regra de consistência se manter: valores negativos nas contas do cliente serão permitidos se o valor total em todas as contas desse cliente permanecer não negativo .

A primeira transação obtém o valor nas contas de Bob: ₽900.

 => BEGIN ISOLATION LEVEL REPEATABLE READ; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

  sum -------- 900.00 (1 row) 

A segunda transação recebe a mesma quantidade.

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

 | sum | -------- | 900.00 | (1 row) 

A primeira transação acredita que o valor de uma das contas pode ser reduzido em ₽600.

 => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; 

E a segunda transação chega à mesma conclusão. Mas reduz outra conta:

 | => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; | => COMMIT; 

 => COMMIT; => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+--------- 2 | 2001 | bob | -400.00 3 | 2002 | bob | 100.00 (2 rows) 

Conseguimos fazer com que o saldo de Bob ficasse vermelho, embora cada transação estivesse funcionando corretamente sozinha.

Anomalia de transação somente leitura

Esta é a segunda e a última das anomalias possíveis no nível de leitura repetível. Para demonstrá-lo, você precisará de três transações, duas das quais alterarão os dados e a terceira somente os lerá.

Mas primeiro vamos restaurar o estado das contas de Bob:

 => UPDATE accounts SET amount = 900.00 WHERE id = 2; => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 3 | 2002 | bob | 100.00 2 | 2001 | bob | 900.00 (2 rows) 

Na primeira transação, são acumulados juros sobre o valor disponível em todas as contas de Bob. Os juros são creditados em uma de suas contas:

 => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 1 => UPDATE accounts SET amount = amount + ( SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01 WHERE id = 2; 

Em seguida, outra transação retira dinheiro da conta de outro Bob e confirma suas alterações:

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 2 | => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; | => COMMIT; 

Se a primeira transação for confirmada neste momento, nenhuma anomalia ocorrerá: poderíamos assumir que a primeira transação foi executada primeiro e depois a segunda (mas não vice-versa porque a primeira transação viu o estado da conta com id = 3 antes disso). conta foi alterada pela segunda transação).

Mas imagine que, nesse ponto, a terceira transação (somente leitura) comece, que lê o estado de alguma conta que não é afetada pelas duas primeiras transações:

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 3 | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

E somente depois que a primeira transação for concluída:

 => COMMIT; 

Que estado a terceira transação deve ver agora?

 | SELECT * FROM accounts WHERE client = 'bob'; 

Uma vez iniciada, a terceira transação podia ver as alterações da segunda transação (que já havia sido confirmada), mas não da primeira (que ainda não havia sido confirmada). Por outro lado, já verificamos acima que a segunda transação deve ser considerada iniciada após a primeira. Qualquer que seja o estado que a terceira transação veja será inconsistente - essa é apenas a anomalia de uma transação somente leitura. Mas no nível de leitura repetível, é permitido:

 | id | number | client | amount | ----+--------+--------+-------- | 2 | 2001 | bob | 900.00 | 3 | 2002 | bob | 0.00 | (2 rows) 

 | => COMMIT; 

Serializable

O nível serializável evita todas as anomalias possíveis. De fato, o Serializable é construído sobre o Isolamento de Instantâneo. As anomalias que não ocorrem com a leitura repetida (como uma leitura suja, não repetível ou fantasma) também não ocorrem no nível serializável. E aquelas anomalias que ocorrem (uma gravação inconsistente e uma anomalia de transação somente leitura) são detectadas e a transação é interrompida - ocorre um erro familiar de serialização: não foi possível serializar o acesso .

Gravação inconsistente

Para ilustrar isso, vamos repetir o cenário com uma anomalia de gravação inconsistente:

 => BEGIN ISOLATION LEVEL SERIALIZABLE; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

  sum ---------- 910.0000 (1 row) 

 | => BEGIN ISOLATION LEVEL SERIALIZABLE; | => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

 | sum | ---------- | 910.0000 | (1 row) 

 => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; 

 | => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; | => COMMIT; 

 => COMMIT; 

 ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt. HINT: The transaction might succeed if retried. 

Assim como no nível de leitura repetida, um aplicativo que usa o nível de isolamento serializável deve repetir transações que terminaram com um erro de serialização, conforme a mensagem de erro nos solicitar.

Ganhamos simplicidade de programação, mas o preço disso é o encerramento forçado de alguma fração das transações e a necessidade de repeti-las. A questão, é claro, é o tamanho dessa fração. Se apenas as transações encerradas se sobrepõem incompativelmente a outras transações, teria sido bom. Porém, essa implementação inevitavelmente exigiria muitos recursos e seria ineficiente porque você precisaria rastrear as operações em cada linha.

Na verdade, a implementação do PostgreSQL é tal que permite falsos negativos: algumas transações absolutamente normais que são apenas “azaradas” também serão abortadas. Como veremos mais adiante, isso depende de muitos fatores, como a disponibilidade de índices apropriados ou a quantidade de RAM disponível. Além disso, existem outras restrições de implementação (bastante severas), por exemplo, consultas no nível Serializable não funcionarão em réplicas e não usarão planos de execução paralelos. Embora o trabalho para melhorar a implementação continue, as limitações existentes tornam esse nível de isolamento menos atraente.

Planos paralelos aparecerão já no PostgreSQL 12 ( patch ). E consultas sobre réplicas podem começar a funcionar no PostgreSQL 13 ( outro patch ).

Anomalia de transação somente leitura

Para que uma transação somente leitura não resulte em uma anomalia e não sofra com ela, o PostgreSQL oferece uma técnica interessante: essa transação pode ser bloqueada até que sua execução seja segura. Este é o único caso em que um operador SELECT pode ser bloqueado por atualizações de linha. É assim que se parece:

 => UPDATE accounts SET amount = 900.00 WHERE id = 2; => UPDATE accounts SET amount = 100.00 WHERE id = 3; => SELECT * FROM accounts WHERE client = 'bob' ORDER BY id; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 900.00 3 | 2002 | bob | 100.00 (2 rows) 

 => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 1 => UPDATE accounts SET amount = amount + ( SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01 WHERE id = 2; 

 | => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 2 | => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; | => COMMIT; 

A terceira transação é declarada explicitamente READ ONLY e DEFERRABLE:

 | => BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -- 3 | => SELECT * FROM accounts WHERE client = 'alice'; 

Ao tentar executar a consulta, a transação é bloqueada porque, caso contrário, causaria uma anomalia.

 => COMMIT; 

E somente depois que a primeira transação é confirmada, a terceira continua a execução:

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

 | => SELECT * FROM accounts WHERE client = 'bob'; 

 | id | number | client | amount | ----+--------+--------+---------- | 2 | 2001 | bob | 910.0000 | 3 | 2002 | bob | 0.00 | (2 rows) 

 | => COMMIT; 

Outra observação importante: se o isolamento serializável for usado, todas as transações no aplicativo deverão usar esse nível. Você não pode misturar transações Confirmadas de Leitura (ou Leitura Repetível) com Serializable. Ou seja, você pode misturar, mas o Serializable se comportará como Leitura Repetível sem nenhum aviso. Discutiremos por que isso acontece mais tarde, quando falarmos sobre a implementação.

Portanto, se você decidir usar o Serializble, é melhor definir globalmente o nível padrão (embora isso, é claro, não impeça a especificação explícita de um nível incorreto):

 ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

Você pode encontrar uma apresentação mais rigorosa das questões relacionadas às transações, consistência e anomalias no livro e na aula de Boris Novikov “Fundamentos das tecnologias de banco de dados” (disponível apenas em Russion).

Qual nível de isolamento usar?

O nível de isolamento Read Committed é usado por padrão no PostgreSQL, e é provável que esse nível seja usado na grande maioria dos aplicativos. Esse padrão é conveniente porque, nesse nível, um cancelamento de transação é possível apenas em caso de falha, mas não como um meio de evitar inconsistência. Em outras palavras, um erro de serialização não pode ocorrer.

O outro lado da moeda é um grande número de possíveis anomalias, que foram discutidas em detalhes acima. O engenheiro de software sempre deve mantê-los em mente e escrever código para não permitir que eles apareçam. Se você não puder codificar as ações necessárias em uma única instrução SQL, precisará recorrer ao bloqueio explícito. O mais problemático é que o código é difícil de testar quanto a erros associados à obtenção de dados inconsistentes, e os próprios erros podem ocorrer de maneiras imprevisíveis e não reproduzíveis e, portanto, difíceis de corrigir.

O nível de isolamento de leitura repetida elimina alguns dos problemas de inconsistência, mas, infelizmente, não todos. Portanto, você não deve se lembrar apenas das anomalias restantes, mas também modificar o aplicativo para que ele lide corretamente com erros de serialização. Certamente é inconveniente. Mas para transações somente leitura, esse nível complementa perfeitamente o Read Committed e é muito conveniente, por exemplo, para criar relatórios que usam várias consultas SQL.

Finalmente, o nível serializável permite que você não se preocupe com a inconsistência, o que facilita muito a codificação. A única coisa necessária ao aplicativo é poder repetir qualquer transação ao obter um erro de serialização. Mas a fração de transações interrompidas, a sobrecarga adicional e a incapacidade de paralelizar consultas podem reduzir significativamente a taxa de transferência do sistema. Observe também que o nível serializável não é aplicável em réplicas e que não pode ser misturado com outros níveis de isolamento.

Continue lendo .