Níveis de isolamento do Aurora MySQL

Descubra como as instâncias de banco de dados de um cluster do Aurora MySQL implementam a propriedade de isolamento do banco de dados. Este tópico explica como o comportamento padrão do Aurora MySQL se equilibra entre consistência rigorosa e alta performance. Você pode usar essas informações para decidir quando alterar as configurações padrão com base nas características da sua workload.

Níveis de isolamento disponíveis para instâncias do gravador

Você pode usar os níveis de isolamento REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED e SERIALIZABLE na instância primária de um cluster de banco de dados do Aurora MySQL. Esses níveis de isolamento funcionam no Aurora MySQL da mesma maneira que no RDS para MySQL.

Nível de isolamento REPEATABLE READ (leitura repetível) para instâncias leitoras

Por padrão, as instâncias de banco de dados do Aurora MySQL configuradas como réplicas somente leitura do Aurora sempre usam o nível de isolamento REPEATABLE READ. Essas instâncias de banco de dados ignoram quaisquer instruções SET TRANSACTION ISOLATION LEVEL e continuam usando o nível de isolamento REPEATABLE READ.

Você não pode definir o nível de isolamento para instâncias de banco de dados de leitor usando parâmetros de banco de dados ou parâmetros de cluster de banco de dados.

Nível de isolamento READ COMMITTED (leitura confirmada) para instâncias leitoras

Se sua aplicação incluir uma workload com alta demanda de gravação na instância primária e consultas demoradas nas réplicas do Aurora, você poderá enfrentar um atraso significativo na limpeza. Atraso na limpeza acontece quando a coleta de lixo interna é bloqueada por consultas demoradas. O sintoma visto é um valor alto para history list length na saída do comando SHOW ENGINE INNODB STATUS. Você pode monitorar esse valor usando a métrica RollbackSegmentHistoryListLength no CloudWatch. O atraso substancial na limpeza pode reduzir a eficácia dos índices secundários, reduzir a performance geral da consulta e gerar desperdício de espaço de armazenamento.

Se enfrentar esses problemas, você poderá definir uma configuração no nível da sessão do Aurora MySQL, aurora_read_replica_read_committed, para usar o nível de isolamento READ COMMITTED nas réplicas do Aurora. Ao aplicar essa configuração, você pode ajudar a reduzir a desaceleração e o desperdício de espaço resultantes da execução de consultas demoradas ao mesmo tempo em que as transações que modificam suas tabelas.

Recomendamos que você entenda o comportamento específico do Aurora MySQL de isolamento de READ COMMITTED antes de usar essa configuração. O comportamento de READ COMMITTED da réplica do Aurora está em conformidade com o padrão ANSI SQL. No entanto, o isolamento é menos rigoroso do que o comportamento típico de READ COMMITTED do MySQL com o qual você pode estar familiarizado. Portanto, os resultados da consulta com READ COMMITTED em uma réplica de leitura do Aurora MySQL poderão ser diferentes dos resultados da mesma consulta com READ COMMITTED na instância primária do Aurora MySQL ou no RDS para MySQL. Você pode usar a configuração aurora_read_replica_read_committed para esses casos como um relatório abrangente que verifica um banco de dados muito grande. Por outro lado, você pode evitá-la para consultas breves com pequenos conjuntos de resultados, em que a precisão e a repetibilidade são importantes.

O nível de isolamento READ COMMITTED não está disponível para sessões em um cluster secundário em um banco de dados global Aurora que usam o recurso de encaminhamento de gravação. Para obter informações sobre o encaminhamento de gravação, consulte Como usar o encaminhamento de gravação em um banco de dados global Amazon Aurora.

Usar READ COMMITTED para leitores

Para usar o nível de isolamento READ COMMITTED para réplicas do Aurora, defina a configuração aurora_read_replica_read_committed como ON. Use essa configuração no nível da sessão enquanto estiver conectada a uma réplica específica do Aurora. Para fazer isso, execute os comandos SQL a seguir:

set session aurora_read_replica_read_committed = ON;
set session transaction isolation level read committed;

Você pode usar essa configuração temporariamente para executar consultas únicas interativas. Você também pode executar uma aplicação de relatórios ou análise de dados que se beneficie do nível de isolamento READ COMMITTED, mantendo a configuração padrão inalterada para outras aplicações.

Quando a configuração aurora_read_replica_read_committed estiver ativada, use o comando SET TRANSACTION ISOLATION LEVEL para especificar o nível de isolamento das transações apropriadas.

set transaction isolation level read committed;

Diferenças no comportamento READ COMMITTED (LEITURA CONFIRMADA) das réplicas do Aurora

A configuração aurora_read_replica_read_committed disponibiliza o nível de isolamento READ COMMITTED para uma Réplica do Aurora, com comportamento de consistência otimizado para transações de longa execução. O nível de isolamento READ COMMITTED nas réplicas do Aurora é menos rigoroso do que nas instâncias primárias do Aurora. Por esse motivo, habilite essa configuração apenas nas Réplicas do Aurora, onde você sabe que suas consultas podem aceitar a possibilidade de certos tipos de resultados inconsistentes.

Suas consultas podem enfrentar certos tipos de anomalias de leitura quando a configuração aurora_read_replica_read_committed está habilitada. Dois tipos de anomalias são especialmente importantes para entender e lidar com o código da aplicação. Uma non-repeatable read (leitura não repetível) ocorre quando outra transação é confirmada enquanto sua consulta está em execução. Uma consulta demorada pode ter dados diferentes no início e no final da consulta. Uma phantom read (leitura fantasma) ocorre quando outras transações fazem com que as linhas existentes sejam reorganizadas enquanto a consulta está em execução, e uma ou mais linhas são lidas duas vezes pela consulta.

Suas consultas podem ter contagens de linhas inconsistentes como resultado de leituras fantasmas. Suas consultas também podem retornar resultados incompletos ou inconsistentes devido a leituras não repetíveis. Por exemplo, vamos supor que uma operação de junção se refira a tabelas modificadas simultaneamente por instruções SQL como INSERT ou DELETE. Nesse caso, a consulta de junção poderá ler uma linha de uma tabela, mas não a linha correspondente de outra tabela.

O padrão SQL ANSI permite esses comportamentos para o nível de isolamento READ COMMITTED. No entanto, esses comportamentos são diferentes da implementação típica do MySQL no READ COMMITTED. Portanto, antes de habilitar a configuração aurora_read_replica_read_committed, verifique qualquer código SQL existente para saber se ele opera conforme o esperado no modelo de consistência mais flexível.

A contagem de linhas e outros resultados podem não ser altamente consistentes no nível de isolamento READ COMMITTED enquanto essa configuração estiver habilitada. Dessa forma, geralmente habilita-se a configuração apenas ao executar consultas analíticas que agregam grandes quantidades de dados e não exigem precisão absoluta. Se você não tiver esse tipo de consultas demoradas com uma workload de gravação intensa, provavelmente não precisará da configuração aurora_read_replica_read_committed. Sem a combinação de consultas demoradas e uma workload de gravação intensa, é improvável que você enfrente problemas com o comprimento da lista de histórico.

exemplo Consultas que mostram comportamento de isolamento para READ COMMITTED em réplicas do Aurora

O exemplo a seguir mostra como as consultas READ COMMITTED em uma Réplica do Aurora podem retornar resultados não repetíveis se as transações modificarem as tabelas associadas simultaneamente. A tabela BIG_TABLE contém 1 milhão de linhas antes do início de qualquer consulta. Outras instruções de linguagem de manipulação de dados (DML) adicionam, removem ou alteram linhas enquanto estão em execução.

As consultas na instância primária do Aurora no nível de isolamento READ COMMITTED produzem resultados previsíveis. No entanto, os custos indiretos de manter a visualização de leitura consistente durante toda a vida útil de todas as consultas demoradas podem levar a uma coleta de lixo cara posteriormente.

As consultas na Réplica do Aurora no nível de isolamento READ COMMITTED são otimizadas para minimizar esses custos indiretos da coleta de lixo. A desvantagem é que os resultados podem variar dependendo do fato de as consultas recuperarem linhas adicionadas, removidas ou reorganizadas por transações confirmadas enquanto a consulta está em execução. As consultas não precisam, mas têm permissão para considerar essas linhas. Para fins de demonstração, as consultas verificam apenas o número de linhas na tabela usando a função COUNT(*).

Tempo	Instrução DML na instância primária do Aurora	Consulta na instância primária do Aurora com READ COMMITTED (LEITURA CONFIRMADA)	Consulta na réplica do Aurora com READ COMMITTED
T1	INSERT INTO big_table SELECT * FROM other_table LIMIT 1000000; COMMIT;
T2		Q1: SELECT COUNT(*) FROM big_table;	Q2: SELECT COUNT(*) FROM big_table;
T3	INSERT INTO big_table (c1, c2) VALUES (1, 'one more row'); COMMIT;
T4		Se Q1 terminar agora, o resultado será 1.000.000.	Se Q2 terminar agora, o resultado será 1.000.000 ou 1.000.001.
T5	DELETE FROM big_table LIMIT 2; COMMIT;
T6		Se Q1 terminar agora, o resultado será 1.000.000.	Se Q2 terminar agora, o resultado será 1.000.000 ou 1.000.001 ou 999.999 ou 999.998.
T7	UPDATE big_table SET c2 = CONCAT(c2,c2,c2); COMMIT;
T8		Se Q1 terminar agora, o resultado será 1.000.000.	Se Q2 terminar agora, o resultado será 1.000.000 ou 1.000.001 ou 999.999 ou, possivelmente, um número superior.
T9		Q3: SELECT COUNT(*) FROM big_table;	Q4: SELECT COUNT(*) FROM big_table;
T10		Se Q3 terminar agora, o resultado será 999.999.	Se Q4 terminar agora, o resultado será 999.999.
T11		Q5: SELECT COUNT(*) FROM parent_table p JOIN child_table c ON (p.id = c.id) WHERE p.id = 1000;	Q6: SELECT COUNT(*) FROM parent_table p JOIN child_table c ON (p.id = c.id) WHERE p.id = 1000;
T12	INSERT INTO parent_table (id, s) VALUES (1000, 'hello'); INSERT INTO child_table (id, s) VALUES (1000, 'world'); COMMIT;
T13		Se Q5 terminar agora, o resultado será 0.	Se Q6 terminar agora, o resultado será 0 ou 1.

Se as consultas forem concluídas rapidamente, antes de qualquer outra transação executar instruções DML e ser confirmada, os resultados serão previsíveis e iguais entre a instância principal e a Réplica do Aurora. Vamos examinar as diferenças de comportamento em detalhes, começando pela primeira consulta.

Os resultados para Q1 são altamente previsíveis, porque READ COMMITTED na instância primária usa um modelo de consistência sólido semelhante ao nível de isolamento REPEATABLE READ.

Os resultados para Q2 podem variar dependendo de quais transações são confirmadas enquanto a consulta está em execução. Por exemplo, vamos supor que outras transações executem instruções DML e sejam confirmadas enquanto as consultas estiverem em execução. Nesse caso, a consulta na Réplica do Aurora com o nível de isolamento READ COMMITTED poderá ou não levar em consideração as alterações. As contagens de linhas não são previsíveis da mesma maneira que no nível de isolamento REPEATABLE READ. Elas também não são tão previsíveis quanto as consultas executadas no nível de isolamento READ COMMITTED na instância primária ou em uma instância do RDS para MySQL.

A instrução UPDATE em T7 não altera realmente o número de linhas na tabela. No entanto, ao alterar o comprimento de uma coluna de tamanho variável, essa instrução pode fazer com que as linhas sejam reorganizadas internamente. Uma transação READ COMMITTED demorada pode visualizar a versão antiga de uma linha e, posteriormente, na mesma consulta, visualizar a nova versão da mesma linha. A consulta também pode ignorar as versões antiga e nova da linha, por isso a contagem de linhas pode ser diferente da esperada.

Os resultados de Q5 e Q6 podem ser idênticos ou ligeiramente diferentes. A consulta Q6 na Réplica do Aurora em READ COMMITTED pode, mas não precisa, visualizar as novas linhas confirmadas enquanto a consulta estiver em execução. Ela também pode visualizar a linha de uma tabela, mas não de outra. Se a consulta de junção não encontrar uma linha correspondente nas duas tabelas, ela retornará uma contagem igual a zero. Se a consulta localizar as duas novas linhas em PARENT_TABLE e CHILD_TABLE, ela retornará uma contagem igual a um. Em uma consulta demorada, as pesquisas nas tabelas unidas podem ocorrer em momentos amplamente distintos.

nota

Essas diferenças de comportamento dependem do momento em que as transações são confirmadas e quando as consultas processam as linhas da tabela subjacente. Dessa forma, é mais provável que você visualize essas diferenças nas consultas de relatório que demoram minutos ou horas e são executadas em clusters do Aurora que processam transações OLTP ao mesmo tempo. Esses são os tipos de cargas de trabalho mistas que mais se beneficiam do nível de isolamento READ COMMITTED nas Réplicas do Aurora.