Olá Habr! Com este artigo, inicio uma série de loops (ou um loop de séries? Em geral, uma ótima idéia) sobre a estrutura interna do PostgreSQL.

O material será baseado em cursos de treinamento em administração que realizamos com Pavel pluzanov . Nem todo mundo gosta de assistir a um vídeo (eu definitivamente não gosto), mas ler slides, mesmo com comentários, é completamente "errado".

Obviamente, os artigos não repetirão o conteúdo dos cursos individualmente. Falarei apenas sobre como tudo está organizado, omitindo a administração real, mas tentarei fazer isso com mais detalhes e em detalhes. E acredito que esse conhecimento é útil para o desenvolvedor do aplicativo, não menos que o administrador.

Vou me concentrar naqueles que já têm alguma experiência com o PostgreSQL e, pelo menos em termos gerais, imaginam o que está acontecendo. Para iniciantes, o texto será um pouco pesado. Por exemplo, não direi uma palavra sobre como instalar o PostgreSQL e executar o psql.

As coisas que serão discutidas não mudam muito de versão para versão, mas usarei o atual 11º PostgreSQL “vanilla”.

O primeiro ciclo é dedicado a questões relacionadas ao isolamento e à multiversão, e seu plano é o seguinte:

1. Isolamento, como entendido pelo padrão e PostgreSQL (este artigo);
2. Camadas, arquivos, páginas - o que está acontecendo no nível físico;
3. Versões de linha, transações virtuais e aninhadas ;
4. Instantâneos de dados e visibilidade de versões de linha, horizonte de eventos ;
5. Limpeza na página e atualizações HOT ;
6. Limpeza normal (vácuo);
7. Limpeza automática (autovacuum);
8. O contador de transações transborda e congela .

Bem, vamos lá.

O que é isolamento e por que é importante?

Provavelmente todo mundo pelo menos conhece a existência de transações, conheceu o acrônimo ACID e ouviu falar sobre os níveis de isolamento. Mas ainda é preciso encontrar a opinião de que essa é uma teoria que não é necessária na prática. Portanto, passarei algum tempo tentando explicar por que isso é realmente importante.

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

Mas o que são dados "corretos"? É sabido que, no nível do banco de dados, você pode criar restrições de integridade (como NOT NULL ou UNIQUE). Se os dados sempre satisfizerem as restrições de integridade (e isso ocorre porque o DBMS garante isso), elas são holísticas.

São corretos e integrais - a mesma coisa? Na verdade não. Nem todas as restrições podem ser formuladas no nível do banco de dados. Parte das restrições é muito complicada, por exemplo, abrange várias tabelas ao mesmo tempo. E mesmo que a restrição, em princípio, possa ser definida no banco de dados, mas por alguma razão não, isso não significa que possa ser violada.

Portanto, a correção é mais rigorosa que a integridade , mas não sabemos exatamente o que é. Resta reconhecer que o padrão de correção é um aplicativo que, como queremos acreditar, foi escrito corretamente e nunca se engana. De qualquer forma, se o aplicativo não violar a integridade, mas violar a correção, o DBMS não o saberá e não pegará sua mão.

A partir de agora, chamaremos de correção o termo consistência.

Vamos supor, no entanto, que o aplicativo execute apenas a sequência correta de instruções. Qual é o papel do DBMS, se o aplicativo estiver correto?

Em primeiro lugar, verifica-se que uma sequência correta de instruções pode interromper temporariamente a consistência dos dados, e isso - por incrível que pareça - é normal. Um exemplo banal, mas compreensível, é transferir 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 escrever no SQL como 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 fundos em uma conta, a segunda - aumenta em outra. A primeira operação viola a consistência dos dados, a segunda - restaura.

Um bom exercício é implementar a regra descrita acima no nível de restrições de integridade. Você é fraco? ©

E se a primeira operação for concluída e a segunda não? Afinal, é fácil: durante a segunda operação, a eletricidade pode ser perdida, o servidor pode cair, a divisão por zero pode ocorrer - mas você nunca sabe. É claro que a consistência está sendo violada e isso não deve ser permitido. Em princípio, é possível resolver essas situações no nível da aplicação ao custo de esforços incríveis, mas, felizmente, não é necessário: o DBMS cuida disso. Mas, para isso, ela deve saber que duas operações constituem um todo indivisível. Isso é uma transação .

Parece interessante: sabendo que as operações constituem uma transação, o DBMS ajuda a manter a consistência, garantindo a atomicidade das transações, 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 absolutamente corretas, uma a uma, juntas elas podem funcionar incorretamente. Isso se deve ao fato de a ordem das operações ser mista: não se pode presumir que todas as operações de uma transação sejam executadas primeiro e somente então todas as operações de outra.

Uma observação sobre simultaneidade. De fato, ao mesmo tempo, as transações podem funcionar em um sistema com um processador multinúcleo, com uma matriz de disco etc. Mas todas as mesmas considerações são verdadeiras para um servidor que executa comandos sequencialmente, no modo de compartilhamento de tempo: tantos ciclos, uma transação é executada, tantos ciclos são diferentes . Às vezes, o termo execução competitiva é usado para resumir.

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

Um exemplo simples: se um aplicativo deseja obter os dados corretos do banco de dados, pelo menos ele não deve ver alterações em 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 .

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

Obviamente, é impossível recusar a execução simultânea: caso contrário, que tipo de desempenho pode ser discutido? Mas você não pode trabalhar com dados incorretos.

E novamente o DBMS vem em socorro. Você pode executar transações como se fossem seqüencialmente, como se uma após a outra. Em outras palavras, isoladamente um do outro. Na realidade, o DBMS pode executar operações misturadas, mas ao mesmo tempo garantir que o resultado da execução simultânea coincida com o resultado de qualquer uma das possíveis execuções sequenciais. E isso elimina quaisquer possíveis anomalias.

Então, chegamos à definição:

Uma transação é o conjunto de operações executadas por um aplicativo que transfere o 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).

Esta definição combina as três primeiras letras do acrônimo ACID. Eles estão tão intimamente relacionados entre si que simplesmente não faz sentido considerar um sem o outro. De fato, é difícil destacar a letra D (durabilidade). Afinal, no caso de uma falha no sistema, permanecem alterações nas 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, juntamente com uma diminuição na taxa de transferência do sistema. Portanto, na prática, muitas vezes (nem sempre, mas quase sempre) o isolamento enfraquecido é aplicado, o que impede algumas, mas não todas, anomalias. E isso significa que parte do trabalho para garantir a correção dos dados recai sobre o aplicativo. É por isso que é muito importante entender qual nível de isolamento é usado no sistema, quais garantias ele oferece e quais não e como escrever o código correto nessas condições.

Níveis e anomalias de isolamento de SQL

O padrão SQL há muito descreveu quatro níveis de isolamento. Esses níveis são determinados listando as anomalias que são permitidas ou não durante a execução de transações nesse nível. Portanto, para falar sobre esses níveis, você precisa se familiarizar com as anomalias.

Enfatizo que nesta parte estamos falando sobre o padrão, isto é, sobre uma certa teoria sobre a qual a prática depende muito, mas que ao mesmo tempo está em desacordo. Portanto, todos os exemplos aqui são especulativos. Eles usarão as mesmas operações nas contas dos clientes: isso é bastante óbvio, embora, reconhecidamente, não tenha nada a ver com a forma como as operações bancárias são realmente organizadas.

Atualização perdida

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

Por exemplo, duas transações aumentarão o valor na mesma conta em 100 ₽. A primeira transação lê o valor atual (1000 ₽), depois a segunda transação lê o mesmo valor. A primeira transação aumenta a quantidade (resulta em 1100 ₽) e grava esse valor. A segunda transação faz o mesmo - obtém os mesmos 1.100 ₽ e os grava. Como resultado, o cliente perdeu 100 ₽.

As atualizações perdidas não são permitidas pelo padrão em nenhum nível de isolamento.

Leitura e leitura sujas não confirmadas

Com a leitura suja , já nos encontramos acima. Essa anomalia ocorre quando uma transação lê as alterações pendentes feitas por outra transação.

Por exemplo, a primeira transação transfere todo o dinheiro da conta do cliente para outra conta, mas não registra a alteração. Outra transação lê o status da conta, recebe 0 ₽ e se recusa a emitir dinheiro para o cliente - apesar do fato de a primeira transação ser interrompida e cancelar suas alterações, para que o valor 0 nunca exista no banco de dados.

A leitura suja é permitida pelo padrão no nível Read Uncommitted.

Leitura não repetida e leitura confirmada

A anomalia de leitura sem repetição ocorre quando uma transação lê a mesma linha duas vezes e, no intervalo entre as leituras, a segunda transação altera (ou exclui) essa linha e confirma as alterações. Em seguida, a primeira transação obterá resultados diferentes.

Por exemplo, permita que a regra de consistência proíba montantes negativos nas contas dos clientes . A primeira transação reduzirá o valor na conta em 100 ₽. Ela verifica o valor atual, recebe 1000 ₽ e decide que é possível uma redução. No momento, a segunda transação reduz o valor da conta para zero e registra as alterações. Se agora a primeira transação verificar novamente o valor, ela receberá 0 ₽ (mas ela já havia decidido diminuir o valor e a conta "vai para menos").

A leitura não repetida é permitida pelo padrão nos níveis Read Uncommitted e Read Committed. Mas a leitura suja de Read Committed não permite.

Leitura fantasma e leitura repetida

A leitura fantasma ocorre quando uma transação lê um conjunto de linhas duas vezes sob a mesma condição e, no intervalo entre as leituras, a segunda transação adiciona linhas que satisfazem essa condição (e confirma as alterações). Em seguida, a primeira transação receberá diferentes conjuntos de linhas.

Por exemplo, suponha que uma regra de consistência proíba um cliente de ter mais de 3 contas . A primeira transação vai abrir uma nova conta, verifica seu número atual (digamos, 2) e decide que a abertura é possível. No momento, a segunda transação também abre uma nova conta para o cliente e registra as alterações. Se agora a primeira transação verificar novamente a quantidade, ela receberá 3 (mas já está abrindo outra conta e o cliente tem 4).

A leitura fantasma é permitida pelo padrão nos níveis de leitura não confirmada, leitura confirmada e leitura repetida. Porém, no nível de leitura repetida, a leitura não repetida não é permitida.

Falta de Anomalias e Serializáveis

O padrão define outro nível - serializável - no qual nenhuma anomalia é permitida. E isso não é o mesmo que proibir uma atualização perdida e uma leitura suja, não repetitiva e fantasma.

O fato é que existem anomalias significativamente mais conhecidas do que aquelas listadas no padrão, e um número desconhecido ainda é desconhecido.

Serializable deve evitar todas as anormalidades em geral . Isso significa que, nesse nível, o desenvolvedor de aplicativos não precisa pensar em executar simultaneamente. Se as transações executarem as seqüências corretas de instruções, trabalhando sozinhas, os dados serão consistentes com a operação simultânea dessas transações.

Placa de resumo

Agora você pode trazer uma tabela conhecida para todos. Mas aqui, para maior clareza, a última coluna é adicionada a ela, que não está no padrão.

Por que exatamente essas anomalias?

Por que apenas algumas das muitas anomalias possíveis no padrão são listadas e por que são?

Aparentemente, ninguém parece saber disso com certeza. Mas a prática aqui definitivamente superou a teoria, portanto, é possível que não pensássemos em outras anomalias (discurso sobre o padrão SQL: 92).

Além disso, assumiu-se que o isolamento deveria ser construído sobre intertravamentos. A idéia do protocolo de bloqueio de duas fases (2PL) amplamente utilizado é que, durante a transação, a transação bloqueia as linhas com as quais está trabalhando e, quando concluída, libera os bloqueios. Simplificando bastante, quanto mais bloqueios uma transação captura, melhor é isolada de outras transações. Mas o desempenho do sistema sofre ainda mais, porque, em vez de trabalharem juntas, as transações começam a se alinhar pelas mesmas linhas.

Parece-me que a diferença entre os níveis de isolamento do padrão é explicada precisamente pelo número de bloqueios necessários.

Se uma transação impedir que as linhas modificadas sejam alteradas, mas não lidas, obtemos o nível Read Uncommitted: alterações perdidas não são permitidas, mas dados não confirmados podem ser lidos.

Se a transação bloquear linhas mutáveis ​​da leitura e da alteração, obteremos o nível Read Committed: você não poderá ler dados não confirmados, mas quando acessar a linha novamente, poderá obter um valor diferente (leitura não repetida).

Se uma transação bloquear as linhas legíveis e mutáveis ​​da leitura e da alteração, obteremos o nível de leitura repetida: a leitura repetida da linha produzirá o mesmo valor.

Mas há um problema com o Serializable: não é possível bloquear uma linha que ainda não existe. Por esse motivo, a possibilidade de leitura fantasma permanece: outra transação pode adicionar (mas não excluir) uma linha que se enquadre nas condições de uma consulta executada anteriormente e essa linha será buscada novamente.

Portanto, para implementar o nível de serializável, os bloqueios comuns não são suficientes - você precisa bloquear não as linhas, mas as condições (predicados). 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 combinar dois predicados diferentes. Até onde eu sei, não chegou à implementação desses bloqueios em nenhum sistema.

Níveis de isolamento do PostgreSQL

Com o tempo, o isolamento de captura instantânea substituiu os protocolos de gerenciamento de transações de bloqueio. Sua idéia é que cada transação funcione com um instantâneo consistente dos dados em um determinado momento, no qual apenas as alterações que foram registradas antes da criação do instantâneo caem.

Esse isolamento não permite automaticamente leitura suja. Formalmente, no PostgreSQL, você pode especificar o nível Read Uncommitted, mas ele funcionará exatamente como Read Committed. Portanto, não falaremos mais sobre o nível Read Uncommitted.

O PostgreSQL implementa uma multi- versão deste protocolo. A idéia do multi-versioning é que várias versões da mesma string podem coexistir em um DBMS. Isso permite criar uma captura instantânea dos dados usando as versões disponíveis e conviver com um mínimo de bloqueios. De fato, apenas alterações repetidas na mesma linha são bloqueadas. Todas as outras operações são executadas ao mesmo tempo: gravar transações nunca bloqueia transações de leitura e transações de leitura nunca bloqueiam ninguém.

Usando snapshots de dados, o isolamento no PostgreSQL é mais rígido do que o padrão exige: o nível de leitura repetida não permite não apenas repetições, mas também leituras fantasmas (embora não forneça isolamento completo). E isso é alcançado sem perda de eficácia.

Como o multi-versioning é implementado “sob o capô”, falaremos nos artigos a seguir e agora examinaremos detalhadamente cada um dos três níveis através dos olhos do usuário (como você sabe, o mais interessante está oculto por trás de “outras anomalias”). Para fazer isso, crie um quadro de contas. Alice e Bob têm US $ 1.000 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

Falta de leitura suja

É fácil verificar se os dados sujos não podem ser lidos. Vamos começar 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, podendo 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 registramos 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, iniciamos outra transação com o mesmo nível Read Committed. Para distinguir entre transações diferentes, os comandos da segunda transação serão recuados e riscados.

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

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

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

Como esperado, outra transação não vê alterações não confirmadas - a leitura suja não é permitida.

Leitura sem repetição

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

 => COMMIT; 

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

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

 | => COMMIT; 

A solicitação já está recebendo novos dados - essa é a anomalia da leitura não repetida , que é permitida no nível Read Committed.

Conclusão prática : em uma transação, é impossível tomar decisões com base nos dados lidos pela instrução anterior - porque tudo pode mudar entre os horários em que as instruções são executadas. Aqui está um exemplo cujas variações são tão comuns no código do aplicativo que ele é 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 conforme desejado, para que esse “cheque” não salve nada. É conveniente imaginar que entre os operadores de uma transação quaisquer outros operadores de outras transações possam "cunhar", por exemplo, assim:

  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, reorganizando os operadores, você puder estragar tudo, o código será gravado incorretamente. E não se engane que tal combinação de circunstâncias não acontecerá - acontecerá.

Como escrever o código corretamente? As oportunidades, em regra, se resumem ao seguinte:

• Não escreva código.
  Isso não é uma piada. Por exemplo, neste caso, a verificação se transforma facilmente em uma restrição de integridade:
  ALTER TABLE accounts ADD CHECK amount >= 0;
  Agora, nenhuma verificação é necessária: basta executar a ação e, se necessário, lidar com a exceção que surgirá no caso de uma tentativa de violar a integridade.
  
• Use uma única instrução SQL.
  Problemas de consistência surgem devido ao fato de que no intervalo entre operadores outra transação pode terminar e os dados visíveis serão alterados. E se houver apenas um operador, não haverá lacunas.
  O PostgreSQL possui ferramentas suficientes para resolver problemas complexos com uma única instrução SQL. Observamos as expressões gerais de tabela (CTE), nas quais, entre outras coisas, você pode usar as instruções INSERT / UPDATE / DELETE, bem como a instrução INSERT ON CONFLICT, que implementa a lógica "inserir e, se já houver uma linha, atualizar" em uma instrução.
  
• Bloqueios de usuário.
  O último recurso é definir manualmente um bloqueio exclusivo em todas as linhas necessárias (SELECT FOR UPDATE) ou em toda a tabela (LOCK TABLE). Isso sempre funciona, mas nega os benefícios do multi-versioning: em vez de executar simultaneamente, parte das operações será executada sequencialmente.
  

Leitura inconsistente

Antes de embarcar no próximo nível de isolamento, é preciso admitir que nem tudo é tão simples. A implementação do PostgreSQL é tal que permite outras anomalias menos conhecidas que não são regulamentadas pelo padrão.

Digamos que a primeira transação começou a transferir fundos de uma conta Bob para outra:

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

No momento, outra transação calcula o saldo de Bob, com o cálculo sendo executado em um ciclo em todas as contas de Bob. De fato, a transação começa na primeira conta (e, obviamente, vê o estado anterior):

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

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

Neste ponto, 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ê um novo valor):

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

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

 | => COMMIT; 

Assim, a segunda transação recebeu um total de 1100 ₽, ou seja, dados incorretos. Esta é uma anomalia de leitura inconsistente .

Como evitar essa anomalia, permanecendo no Read Committed? Obviamente, use um operador. Por exemplo, assim:

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

Até agora, argumentei que a visibilidade dos dados só pode mudar entre os operadores, mas é tão óbvio? E se a solicitação for executada por um longo tempo, ela poderá ver parte dos dados em um estado e parte em outro?

Confira. Uma maneira conveniente de fazer isso é inserir um atraso artificial no operador chamando a função pg_sleep. Seu parâmetro define o tempo de atraso em segundos.

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

Enquanto essa construção estiver em andamento, em outra transação, transferimos fundos de volta:

 | => 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 estava no momento em que foi iniciado. Isso certamente está correto.

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

Mas aqui não é tão simples. O PostgreSQL permite definir funções, enquanto as funções têm o conceito de uma categoria de variabilidade . Se uma função volátil (com a categoria VOLATILE) for chamada em uma solicitação e outra solicitação for executada nessa função, essa solicitação dentro da função verá dados que não são consistentes com os dados da solicitação 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 na categoria de variabilidade VOLATILE. O problema é que esse nível de isolamento e essa categoria de variabilidade são usados ​​por padrão, então devo admitir - o rake está muito bem. Não pise!

Leitura inconsistente em troca de alterações perdidas

Uma leitura inconsistente na estrutura de um operador pode - de uma maneira um tanto inesperada - ser obtida durante uma atualização.

Vamos ver o que acontece quando você tenta alterar a mesma linha com duas transações. Bob agora tem 1000 ₽ 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, outra transação acumula juros em todas as contas de clientes com um saldo total igual ou superior a 1000 ₽:

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

A execução de uma instrução UPDATE consiste em duas partes. Primeiro, um SELECT é realmente executado, o que seleciona as linhas correspondentes à condição para atualização. Como a alteração da primeira transação não é fixa, a segunda transação não pode vê-la e não afeta a escolha das linhas para o cálculo dos juros. Portanto, as contas de Bob se enquadram nessa condição e, após a conclusão da atualização, o saldo deve aumentar em 10 ₽.

O segundo estágio da execução - as linhas selecionadas são atualizadas uma após a outra. Aqui a segunda transação é forçada a "congelar", porque o id da linha = 3 já está bloqueado 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) 

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

Depois que o bloqueio é liberado, UPDATE relê a linha que está tentando atualizar (mas apenas uma!). O resultado é que Bob acumulou 9 ₽, com base na quantidade de 900 ₽. Mas se Bob tivesse 900 ₽, suas contas não deveriam ter sido incluídas na amostra.

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

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

  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 recebe duas instruções SQL e não sabe nada que o valor de x + 100 esteja de alguma forma relacionado a accounts.amount. Não escreva código dessa maneira.

Leitura Repetível

Falta de leituras não repetidas e fantasmas

O nome do próprio nível de isolamento indica que a leitura é repetível. Vamos verificar isso e, ao mesmo tempo, estaremos convencidos da ausência de leituras fantasmas. Para fazer isso, na primeira transação, retorne as contas de Bob ao estado anterior e crie 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, indicando-a no comando BEGIN (o nível da primeira transação não é importante).

 | => 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 solicitação.

 => 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 continua a ver exatamente os mesmos dados que no início: nem as alterações nas linhas existentes nem as novas linhas são visíveis.

Nesse nível, você não precisa se preocupar com alguma mudança entre os dois operadores.

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

Dissemos acima que, ao atualizar a mesma linha com duas transações no nível Read Committed, uma anomalia de leitura inconsistente pode ocorrer. Isso se deve ao fato de a transação pendente reler a linha bloqueada e, portanto, não a encontrar no mesmo ponto no tempo que o restante das 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. Verificamos repetindo o mesmo cenário com porcentagens:

 => 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; | => UPDATE accounts SET amount = amount * 1.01 | WHERE client IN ( | SELECT client | FROM accounts | GROUP BY client | HAVING sum(amount) >= 1000 | ); 

 => 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 alteração de linha competitiva, mesmo que as colunas de nosso interesse não tenham realmente sido alteradas.

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

Entrada 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 um pouco. Acontece que existem exatamente duas anomalias que permanecem possíveis. (Isso é verdade não apenas no PostgreSQL, mas também em outras implementações de isolamento baseadas em instantâneos.)

A primeira dessas anomalias é um registro inconsistente .

Permita que esta regra de consistência se aplique: valores negativos são permitidos nas contas do cliente se o valor total em todas as contas desse cliente permanecer não negativo .

A primeira transação recebe 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, com razão, 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 pontuação:

 | => 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 reduzir o saldo de Bob, embora cada uma das transações funcione corretamente individualmente.

Anomalia somente leitura

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

Mas primeiro, restaure o status da conta 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) 

A primeira transação cobra juros de Bob sobre o valor dos fundos em todas as contas. 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 de outra conta de Bob e captura suas alterações:

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

Se, neste momento, a primeira transação for confirmada, não haverá anomalia: poderíamos supor que a primeira transação foi concluída primeiro e depois a segunda (mas não vice-versa, porque a primeira transação viu o status do ID da conta = 3 antes que a conta fosse alterado pela segunda transação).

Mas suponha que neste momento a terceira transação (somente leitura) comece, que lê o status 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'; 

Após o início, a terceira transação pôde ver alterações na segunda transação (que já foi confirmada), mas não na primeira (que ainda não foi confirmada). Por outro lado, já estabelecemos 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 é a anomalia apenas da transação de 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

No nível serializável, todas as possíveis anomalias são evitadas. De fato, o Serializable é implementado como um complemento no isolamento, com base em instantâneos de dados. As anomalias que não ocorrem durante a leitura repetida (como leitura fantasma suja, não repetível e fantasma) também não ocorrem no nível serializável. E aquelas anomalias que surgem (gravação inconsistente e anomalia apenas da transação de leitura) são detectadas e a transação é abortada - o já conhecido erro de serialização não pode serializar o acesso.

Entrada inconsistente

Para ilustrar, repetimos 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 em um erro de serialização, o que também é relatado pela dica na mensagem de erro.

Temos a simplicidade da programação, mas o preço é a quebra forçada de uma certa proporção de transações e a necessidade de repeti-las. A questão toda, é claro, é o tamanho dessa participação. Se apenas essas transações fossem encerradas que realmente se cruzam incompativelmente nos dados com outras transações, tudo seria legal. Mas essa implementação inevitavelmente acabaria consumindo muitos recursos e ineficiente, pois teria que rastrear operações com cada linha.

De fato, a implementação do PostgreSQL é tal que permite disparos falsos negativos: algumas transações completamente normais que simplesmente "não têm sorte" serão interrompidas. Como veremos mais adiante, isso depende de vários motivos, por exemplo, a disponibilidade de índices adequados ou a quantidade disponível de RAM. Além disso, existem outras restrições de implementação (bastante sérias), por exemplo, solicitações no nível Serializable não funcionarão em réplicas, planos de execução paralelos não serão usados ​​para elas. E, embora o trabalho para melhorar a implementação não pare, as restrições existentes reduzem a atratividade desse nível de isolamento.

Planos paralelos aparecerão no PostgreSQL 12 ( patch ). E consultas sobre réplicas podem ser obtidas no PostgreSQL 13 ( outro patch ).

Anomalia somente leitura

Para que apenas uma transação de leitura não possa levar a uma anomalia e não sofra dela, o PostgreSQL oferece um mecanismo interessante: essa transação pode ser bloqueada até que sua execução seja segura. Este é o único caso em que uma instrução SELECT pode ser bloqueada por atualizações de linha. Aqui está o que 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 apenas pelo leitor (SOMENTE LER) e adiada (DEFERRABLE):

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

Quando você tenta executar uma solicitação, a transação é bloqueada, porque, caso contrário, sua execução levará a uma anomalia.

 => COMMIT; 

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

 | 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 Read Committed (ou Repeatable Read) com Serializable. Ou seja, você pode misturar alguma coisa, mas o Serializable se comportará como uma Leitura Repetível sem aviso. , , .

Serializble — ( , , ):

  ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

, , , « ».

?

O nível de isolamento Read Committed é usado por padrão no PostgreSQL, e parece que esse nível é usado na grande maioria dos aplicativos. É conveniente que uma quebra de transação seja possível apenas no caso de uma falha, mas não para evitar inconsistência. Em outras palavras, um erro de serialização não pode ocorrer.

, . , . SQL-, . , , , .

Repeatable Read , , , . , , . , , . Read Committed , , , SQL-.

, Serializable , . , — . , , . , Serializable , , .

.