Livelli di isolamento di Aurora MySQL

Scopri come le istanze database in un cluster Aurora MySQL implementano la proprietà di isolamento del database. In questo argomento viene spiegato come il comportamento predefinito di Aurora MySQL si bilancia tra coerenza rigorosa e prestazioni elevate. Puoi utilizzare queste informazioni per decidere quando modificare le impostazioni predefinite in base alle caratteristiche del tuo carico di lavoro.

Livelli di isolamento disponibili per le istanze di scrittura

È possibile utilizzare i livelli di isolamento REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED e SERIALIZABLE sull'istanza primaria di un cluster di database Aurora MySQL. Questi livelli di isolamento funzionano allo stesso modo in Aurora MySQL e in RDS for MySQL.

Livello di isolamento REPEATABLE READ per le istanze di lettura

Per impostazione predefinita, le istanze database Aurora MySQL configurate come repliche di Aurora di sola lettura utilizzano sempre il livello di isolamento REPEATABLE READ. Queste istanze database ignorano qualsiasi istruzione SET TRANSACTION ISOLATION LEVEL e continuano a usare il livello di isolamento REPEATABLE READ.

Non è possibile impostare il livello di isolamento per le istanze database di lettura utilizzando parametri DB o parametri del cluster database.

Livello di isolamento READ COMMITTED per le istanze di lettura

Se l'applicazione include un carico di lavoro ad alta intensità di scrittura nell'istanza primaria e query a esecuzione prolungata nelle repliche di Aurora, è possibile che si verifichi un notevole ritardo di eliminazione. Un ritardo di eliminazione si verifica quando la garbage collection interna è bloccata da query a esecuzione prolungata. Il sintomo è un valore elevato per history list length nell'output del comando SHOW ENGINE INNODB STATUS. È possibile monitorare questo valore utilizzando il parametro RollbackSegmentHistoryListLength in CloudWatch. Un notevole ritardo dell'eliminazione può ridurre l'efficacia degli indici secondari, diminuire le prestazioni complessive delle query e usare inutilmente lo spazio di archiviazione.

Se si verificano questi problemi, puoi usare l'impostazione di configurazione a livello di sessione Aurora MySQL, aurora_read_replica_read_committed, per utilizzare il livello di isolamento READ COMMITTED nelle repliche di Aurora. L'applicazione di questa impostazione può aiutare a ridurre i rallentamenti e lo spazio usato inutilmente che possono derivare dall'esecuzione di query a esecuzione prolungata contemporaneamente alle transazioni che modificano le tabelle.

Prima di usare questa impostazione, ti consigliamo di comprendere il funzionamento di Aurora MySQL specifico dell'isolamento READ COMMITTED. Il comportamento READ COMMITTED della replica di Aurora è conforme allo standard ANSI SQL. Tuttavia, l'isolamento è meno restrittivo del comportamento READ COMMITTED tipico di MySQL che potresti già conoscere. Pertanto, potresti vedere risultati diversi per la query in READ COMMITTED su una replica di lettura di Aurora MySQL rispetto alla stessa query in READ COMMITTED nell'istanza primaria Aurora MySQL o in RDS per MySQL. Potresti utilizzare l'impostazione aurora_read_replica_read_committed per questi casi d'uso, come un report completo che esegue la scansione di un database molto grande. Potresti invece evitarla per brevi query con piccoli set di risultati, dove la precisione e la ripetibilità sono importanti.

Il livello di isolamento READ COMMITTED non è disponibile per le sessioni all'interno di un cluster secondario in un Aurora Global Database che utilizzano la funzionalità di inoltro di scrittura. Per informazioni sull'inoltro di scrittura, consulta Utilizzo dell'inoltro di scrittura in un database globale Amazon Aurora.

Utilizzo di READ COMMITTED per le letture

Per usare il livello di isolamento READ COMMITTED per le repliche di Aurora, configura l'impostazione aurora_read_replica_read_committed su ON. Usa questa impostazione a livello di sessione mentre è connessa a una replica di Aurora specifica. Per farlo, esegui i comandi SQL seguenti.

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

Puoi usare temporaneamente questa impostazione di configurazione per eseguire query interattive una tantum. Potresti anche voler eseguire un'applicazione per la creazione di report o l'analisi dei dati che beneficia del livello di isolamento READ COMMITTED, lasciando invariata l'impostazione predefinita per altre applicazioni.

Quando l'impostazione aurora_read_replica_read_committed è attivata, usa il comando SET TRANSACTION ISOLATION LEVEL per specificare il livello di isolamento per le transazioni appropriate.

set transaction isolation level read committed;

Differenze nel comportamento di READ COMMITTED nelle repliche di Aurora

L'impostazione aurora_read_replica_read_committed rende il livello di isolamento READ COMMITTED disponibile per una replica di Aurora con un comportamento coerente che viene ottimizzato per le transazioni a esecuzione prolungata. Il livello di isolamento READ COMMITTED nelle repliche di Aurora ha un isolamento meno restrittivo di quello nelle istanze primarie di Aurora. Per questo motivo, abilita l'impostazione solo in repliche di Aurora in cui sai che le query possono accettare la possibilità di determinati tipi di risultati incoerenti.

Le tue query possono riscontrare alcuni tipi di anomalie di lettura quando l'impostazione aurora_read_replica_read_committed è attivata. Due tipi di anomalie sono particolarmente importanti per comprendere e gestire il codice dell'applicazione. Una lettura non ripetibile si verifica quando viene eseguita un'altra transazione durante l'esecuzione della query. Una query a esecuzione prolungata può visualizzare dati diversi all'inizio della query rispetto a quelli visualizzati alla fine. Una lettura fantasma si verifica quando altre transazioni causano la riorganizzazione delle righe esistenti mentre la query è in esecuzione e una o più righe vengono lette due volte dalla query.

Le query potrebbero presentare conteggi di riga incoerenti a seguito delle letture fantasma. Le tue query potrebbero anche restituire risultati incompleti o incoerenti a causa delle letture non ripetibili. Ad esempio, supponiamo che un'operazione di join si riferisca alle tabelle che sono modificate contemporaneamente dalle istruzioni SQL come INSERT o DELETE. In questo caso, la query di join potrebbe leggere una riga da una tabella ma non la riga corrispondente da un'altra tabella.

Lo standard ANSI SQL consente entrambi questi comportamenti per il livello di isolamento READ COMMITTED. Tuttavia, questi comportamenti sono diversi dalla tipica implementazione di MySQL di READ COMMITTED. Pertanto, prima di abilitare l'impostazione aurora_read_replica_read_committed, controlla qualsiasi codice SQL esistente per verificare se funziona come previsto nel modello di coerenza più flessibile.

Il conteggio delle righe e altri risultati potrebbero non essere propriamente coerenti nel livello di isolamento READ COMMITTED mentre questa impostazione è abilitata. Pertanto, in genere abiliti l'impostazione solo durante l'esecuzione di query analitiche che aggregano grandi quantità di dati e non richiedono la precisione assoluta. Se non hai questo tipo di query a esecuzione prolungata insieme a un carico di lavoro ad alta intensità di scrittura, probabilmente non è necessaria l'impostazione aurora_read_replica_read_committed. Senza la combinazione di query a esecuzione prolungata e carico di lavoro ad alta intensità di scrittura, è improbabile che si verifichino problemi con la lunghezza dell'elenco della cronologia.

Esempio Query che mostrano il comportamento di isolamento per READ COMMITTED nelle repliche di Aurora

L'esempio seguente mostra come le query READ COMMITTED in una replica di Aurora potrebbero restituire risultati non ripetibili se le transazioni modificano contemporaneamente le tabelle associate. La tabella BIG_TABLE contiene 1 milione di righe prima dell'inizio di qualsiasi query. Altre istruzioni DML (Data Manipulation Language) aggiungono, rimuovono o modificano le righe mentre sono in esecuzione.

Le query nell'istanza primaria Aurora nel livello di isolamento READ COMMITTED producono risultati prevedibili. Tuttavia, il sovraccarico di mantenere la visualizzazione di lettura coerente per la durata di ogni query a esecuzione prolungata può portare a costose operazioni di garbage collection in un secondo momento.

Le query nella replica di Aurora nel livello di isolamento READ COMMITTED sono ottimizzate per ridurre al minimo questo sovraccarico di garbage collection. Il compromesso sta nei risultati che possono variare a seconda che le query recuperino o meno le righe aggiunte, rimosse o riorganizzate dalle transazioni che eseguono il commit mentre la query è in esecuzione. Le query possono prendere in considerazione queste righe, ma non sono obbligate a farlo. A scopo dimostrativo, le query controllano solo il numero di righe nella tabella utilizzando la funzione COUNT(*).

Orario	Istruzione DML nell'istanza primaria Aurora	Query nell'istanza primaria Aurora con READ COMMITTED	Query nella replica di Aurora con 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 finisce ora, il risultato è 1.000.000.	Se Q2 finisce ora, il risultato è 1.000.000 o 1.000.001.
T5	DELETE FROM big_table LIMIT 2; COMMIT;
T6		Se Q1 finisce ora, il risultato è 1.000.000.	Se Q2 finisce ora, il risultato è 1.000.000 o 1.000.001 o 999.999 o 999.998.
T7	UPDATE big_table SET c2 = CONCAT(c2,c2,c2); COMMIT;
T8		Se Q1 finisce ora, il risultato è 1.000.000.	Se Q2 finisce ora, il risultato è 1.000.000 o 1.000.001 o 999.999 oppure possibilmente un numero più alto.
T9		Q3: SELECT COUNT(*) FROM big_table;	Q4: SELECT COUNT(*) FROM big_table;
T10		Se Q3 finisce ora, il risultato è 999.999.	Se Q4 finisce ora, il risultato è 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 finisce ora, il risultato è 0.	Se Q6 finisce ora, il risultato è 0 o 1.

Se le query finiscono rapidamente, prima che qualsiasi altra transazione esegua istruzioni e commit DML, i risultati sono prevedibili e uguali tra l'istanza primaria e la replica di Aurora. Esaminiamo in dettaglio le differenze di comportamento, a partire dalla prima query.

I risultati di Q1 sono prevedibili perché READ COMMITTED nell'istanza primaria usa un modello di consistenza elevata simile al livello di isolamento REPEATABLE READ.

I risultati per Q2 possono variare a seconda delle transazioni sottoposte al commit durante l'esecuzione della query. Ad esempio, supponiamo che altre transazioni eseguano istruzioni DML ed eseguano il commit mentre le query sono in esecuzione. In questo caso, la query nella replica di Aurora con il livello di isolamento READ COMMITTED potrebbe o meno prendere in considerazione le modifiche. I conteggi delle righe non sono prevedibili come nel livello di isolamento REPEATABLE READ. Inoltre, non sono prevedibili come le query in esecuzione sotto il livello di isolamento READ COMMITTED nell'istanza primaria o in un'istanza RDS for MySQL.

L'istruzione UPDATE a T7 in effetti non cambia il numero di righe della tabella. Tuttavia, modificando la lunghezza di una colonna di lunghezza variabile, questa istruzione può causare la riorganizzazione interna delle righe. Una transazione READ COMMITTED a esecuzione prolungata potrebbe visualizzare la versione precedente di una riga e, successivamente, all'interno della stessa query, visualizzare la nuova versione della stessa riga. La query può anche ignorare sia la versione precedente che quella nuova della riga, quindi il conteggio delle righe potrebbe essere diverso da quello previsto.

I risultati di Q5 e Q6 potrebbero essere identici o leggermente diversi. La query Q6 nella replica di Aurora in READ COMMITTED è in grado di vedere, ma non è obbligata a vedere, le nuove righe che vengono confermate mentre la query è in esecuzione. Potrebbe anche vedere la riga di una tabella, ma non dell'altra tabella. Se la query di join non trova una riga corrispondente in entrambe le tabelle, restituisce un conteggio pari a zero. Se la query trova entrambe le nuove righe in PARENT_TABLE e CHILD_TABLE, la query restituisce un conteggio pari a uno. In una query a esecuzione prolungata, le ricerche dalle tabelle unite potrebbero avvenire in momenti ampiamente separati.

Nota

Queste differenze di comportamento dipendono dal momento in cui vengono eseguite le transazioni e vengono elaborate dalle query le righe della tabella sottostante. Pertanto, è molto probabile che si notino tali differenze nelle query di report che richiedono minuti o ore e che vengono eseguite in cluster Aurora che elaborano contemporaneamente transazioni OLTP. Questi sono i tipi di carichi di lavoro misti che beneficiano maggiormente del livello di isolamento READ COMMITTED nelle repliche di Aurora.