Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.
# Niveaux d’isolement Aurora MySQL
Découvrez comment les instances de base de données d’un cluster Aurora MySQL implémentent la propriété d’isolement d’une base de données. Cette rubrique explique comment le comportement par défaut d’Aurora MySQL cherche l’équilibre entre une cohérence stricte et des performances élevées. Vous pouvez utiliser ces informations pour décider quand modifier les paramètres par défaut en fonction des caractéristiques de votre charge de travail.
## Niveaux d’isolement disponibles pour les instances d’enregistreur
Vous pouvez utiliser les niveaux d’isolement `REPEATABLE READ`, `READ COMMITTED`, `READ UNCOMMITTED` et `SERIALIZABLE` sur l’instance principale d’un cluster de bases de données Aurora MySQL. Ces niveaux d’isolement fonctionnent de la même façon dans Aurora MySQL que dans RDS for MySQL.
## Niveaux d’isolement REPEATABLE READ pour les instances de lecteur
Par défaut, les instances de base de données Aurora MySQL configurées comme réplicas Aurora en lecture seule utilisent toujours le niveau d’isolement `REPEATABLE READ`. Ces instances de bases de données ignorent toutes les instructions `SET TRANSACTION ISOLATION LEVEL` et continuent à utiliser le niveau d’isolement `REPEATABLE READ`.
## Niveaux d’isolement READ COMMITTED pour les instances en lecture
Si votre application inclut une charge de travail gourmande en écriture sur l’instance principale et des requêtes de longue durée sur les réplicas Aurora, il se peut que vous soyez confronté à un retard de purge conséquent. Le *retard de purge* se produit quand le nettoyage interne de la mémoire est bloqué par les requêtes de longue durée. Le symptôme que vous voyez est une valeur élevée pour `history list length` dans la sortie de la commande `SHOW ENGINE INNODB STATUS`. Vous pouvez superviser cette valeur dans CloudWatch à l'aide de la métrique `RollbackSegmentHistoryListLength`. Un retard de purge substantiel peut réduire l’efficacité des index secondaires, réduire les performances globales des requêtes et conduire à un gaspillage de l’espace de stockage.
Si vous rencontrez de tels problèmes, vous pouvez définir un paramètre de configuration de niveau session Aurora MySQL, `aurora_read_replica_read_committed`, pour utiliser le niveau d’isolement `READ COMMITTED` sur les réplicas Aurora. Quand vous appliquez ce paramètre, vous pouvez aider à diminuer les ralentissements et l’espace gaspillé qui peuvent résulter de l’exécution de requêtes de longue durée simultanément aux transactions qui modifient vos tables.
Nous vous recommandons de bien comprendre le comportement spécifique d’Aurora MySQL de l’isolement `READ COMMITTED` avant d’utiliser ce paramètre. Le comportement `READ COMMITTED` du réplica Aurora est conforme à la norme ANSI SQL. Cependant, le niveau d’isolement est moins strict que le comportement `READ COMMITTED` MySQL classique que vous connaissez sans doute. Par conséquent, vous pouvez voir des résultats de requête sous `READ COMMITTED` sur un réplica en lecture Aurora MySQL différents de ceux de la même requête sous `READ COMMITTED` sur l’instance principale Aurora MySQL ou sur RDS for MySQL. Vous pouvez envisager d’utiliser le paramètre `aurora_read_replica_read_committed` pour ces cas d’utilisation en tant que rapport exhaustif qui couvre une base de données très volumineuse. En revanche, vous pouvez l’éviter dans le cas de courtes requêtes avec des ensembles de résultats réduits, où la précision et la reproductibilité sont importantes.
Le niveau d’isolement `READ COMMITTED` n’est pas disponible pour les sessions dans un cluster secondaire dans une base de données Aurora globale qui utilisent la fonction de transfert d’écriture. Pour plus d’informations sur le transfert d’écriture, consultez [Utilisation du transfert d'écriture dans une base de données globale Amazon Aurora](aurora-global-database-write-forwarding.md).
### Utilisation de READ COMMITTED pour les lecteurs
Pour utiliser le niveau d’isolement `READ COMMITTED` pour les réplicas Aurora, définissez le paramètre de configuration `aurora_read_replica_read_committed` sur `ON`. Utilisez ce paramètre au niveau de la session tout en étant connecté à un réplica Aurora spécifique. Pour ce faire, exécutez les commandes SQL suivantes :
```
set session aurora_read_replica_read_committed = ON;
set session transaction isolation level read committed;
```
Vous pouvez utiliser ce paramètre de configuration temporairement pour exécuter des requêtes uniques interactives. Il se peut aussi que vous souhaitiez exécuter un rapport ou une application d’analyse des données qui tire profit du niveau d’isolement `READ COMMITTED`, tout en conservant le paramètre par défaut inchangé pour les autres applications.
Quand le paramètre `aurora_read_replica_read_committed` est activé, utilisez la commande `SET TRANSACTION ISOLATION LEVEL` pour spécifier le niveau d’isolement pour les transactions appropriées.
```
set transaction isolation level read committed;
```
### Différences de comportement READ COMMITTED sur les réplicas Aurora
Le paramètre `aurora_read_replica_read_committed` garantit la disponibilité du niveau d’isolement `READ COMMITTED` pour un réplica Aurora, avec une cohérence optimisée pour les transactions de longue durée. Le niveau d’isolement `READ COMMITTED` sur les réplicas Aurora offre un isolement moins strict que sur les instances principales Aurora. Pour cette raison, l’activation de ce paramètre uniquement sur les réplicas Aurora où vous savez que vos requêtes peuvent accepter la possibilité de certains types de résultats incohérents.
Vos requêtes peuvent expérimenter certains types d’anomalies en lecture quand le paramètre `aurora_read_replica_read_committed` est activé. Deux types d’anomalies sont particulièrement importants à comprendre et à gérer dans le code de votre application. Une *lecture non reproductible* se produit quand une autre transaction est validée tandis que votre requête est en cours d’exécution. Une requête de longue durée peut voir des données au démarrage de la requête différentes de celles qu’il voit à la fin. Une *lecture fantôme* se produit quand d’autres transactions entraînent une réorganisation des lignes existantes tandis que votre requête est en cours d’exécution, et qu’une ou plusieurs lignes sont lues deux fois par votre requête.
Vos requêtes peuvent expérimenter des nombres de lignes incohérents comme résultat des lectures fantômes. Vos requêtes peuvent aussi retourner des résultats incomplets ou incohérents suite à des lectures non reproductibles. Par exemple, supposons qu’une opération de jointure fasse référence à des tables modifiées simultanément par des instructions SQL, telles que `INSERT` ou `DELETE`. Dans ce cas, la requête de jointure peut lire une ligne d’une autre table, mais pas la ligne correspondante d’une autre table.
La norme ANSI SQL permet les deux comportements pour le niveau d’isolement `READ COMMITTED`. Cependant, ces comportements diffèrent de l’implémentation MySQL typique de `READ COMMITTED`. Ainsi, avant d’activer le paramètre `aurora_read_replica_read_committed`, vérifiez le code SQL existant pour vous assurer qu’il fonctionne comme attendu selon le modèle de cohérence le moins strict.
Les nombres de lignes et autres résultats peuvent ne pas offrir une cohérence forte sous le niveau d’isolement `READ COMMITTED` lorsque ce paramètre est activé. Ainsi, vous n’activez généralement le paramètre que lors de l’exécution de requêtes analytiques qui agrègent d’importantes quantités de données et ne nécessitent pas une précision absolue. Si vous n’avez pas ces types de requêtes de longue durée en même temps qu’une charge de travail gourmande en écriture, vous n’avez probablement pas besoin du paramètre `aurora_read_replica_read_committed`. Sans la combinaison de requêtes de longue durée et une charge de travail gourmande en écriture, il est peu probable que vous rencontriez des problèmes avec la longueur de la liste de l’historique.
**Example Requêtes illustrant le comportement d’isolement pour READ COMMITTED sur les réplicas Aurora**
L’exemple suivant montre comment les requêtes `READ COMMITTED` sur un réplica Aurora peuvent retourner des résultats non reproductibles si les transactions modifient en même temps les tables associées. La table `BIG_TABLE` contient 1 million de lignes avant le démarrage des requêtes. D’autres instructions en langage de manipulation de données (DML) ajoutent, suppriment ou modifient des lignes tandis qu’elles s’exécutent.
Les requêtes sur l’instance principale Aurora sous le niveau d’isolement `READ COMMITTED` produisent des résultats prévisibles. Cependant, la surcharge qu’entraîne la conservation d’une vue cohérente en lecture pendant la durée de vie de chaque requête de longue durée peut conduire par la suite à un nettoyage de la mémoire onéreux.
Les requêtes sur le réplica Aurora sous le niveau d’isolement `READ COMMITTED` sont optimisées pour réduire cette surcharge du nettoyage de la mémoire. Le compromis est que les résultats peuvent varier selon que les requêtes récupèrent ou non les lignes qui sont ajoutées, supprimées ou réorganisées par les transactions qui sont validées pendant que la requête est en cours d’exécution. Les requêtes sont autorisées à prendre en compte ces lignes, mais elles n’y sont pas obligées. À des fins de démonstration, les requêtes vérifient uniquement le nombre de lignes de la table à l’aide de la fonction `COUNT(*)`.
| Heure | Instruction DML sur une instance principale Aurora | Requête sur une instance principale Aurora avec READ COMMITTED | Requête sur un réplica Aurora avec READ COMMITTED |
| --- | --- | --- | --- |
| T1 | INSERT INTO big\$1table SELECT \$1 FROM other\$1table LIMIT 1000000; COMMIT; | | |
| T2 | | Q1: SELECT COUNT(\$1) FROM big\$1table; | Q2: SELECT COUNT(\$1) FROM big\$1table; |
| T3 | INSERT INTO big\$1table (c1, c2) VALUES (1, 'one more row'); COMMIT; | | |
| T4 | | Si Q1 se termine maintenant, le résultat est 1 000 000. | Si Q2 se termine maintenant, le résultat est 1 000 000 ou 1 000 001. |
| T5 | DELETE FROM big\$1table LIMIT 2; COMMIT; | | |
| T6 | | Si Q1 se termine maintenant, le résultat est 1 000 000. | Si Q2 se termine maintenant, le résultat est 1 000 000, 1 000 001, 999 999 ou 999 998. |
| T7 | UPDATE big\$1table SET c2 = CONCAT(c2,c2,c2); COMMIT; | | |
| T8 | | Si Q1 se termine maintenant, le résultat est 1 000 000. | Si Q2 se termine maintenant, le résultat est 1 000 000 ou 1 000 001 ou 999 999, ou même un nombre supérieur. |
| T9 | | Q3: SELECT COUNT(\$1) FROM big\$1table; | Q4: SELECT COUNT(\$1) FROM big\$1table; |
| T10 | | Si Q3 se termine maintenant, le résultat est 999 999. | Si Q4 se termine maintenant, le résultat est 999 999. |
| T11 | | Q5: SELECT COUNT(\$1) FROM parent\$1table p JOIN child\$1table c ON (p.id = c.id) WHERE p.id = 1000; | Q6: SELECT COUNT(\$1) FROM parent\$1table p JOIN child\$1table c ON (p.id = c.id) WHERE p.id = 1000; |
| T12 | INSERT INTO parent\$1table (id, s) VALUES (1000, 'hello'); INSERT INTO child\$1table (id, s) VALUES (1000, 'world'); COMMIT; | | |
| T13 | | Si Q5 se termine maintenant, le résultat est 0. | Si Q6 se termine maintenant, le résultat est 0 ou 1. |
Si les requêtes se finissent rapidement avant que d’autres transactions n’exécutent les instructions DML et les validations, les résultats sont prévisibles et identiques entre l’instance principale et le réplica Aurora. Examinons les différences de comportement en détail, en commençant par la première requête.
Les résultats de Q1 sont hautement prévisibles, car `READ COMMITTED` sur l’instance principale utilise un modèle de cohérence forte similaire au niveau d’isolement `REPEATABLE READ`.
Les résultats de Q2 peuvent varier en fonction des transactions qui sont validées pendant que la requête est en cours d’exécution. Par exemple, supposons que d’autres transactions exécutent des instructions DML et soient validées tandis que les requêtes sont en cours d’exécution. Dans ce cas, la requête sur le réplica Aurora avec le niveau d’isolement `READ COMMITTED` peut ou non prendre en compte les modifications. Les nombres de lignes ne sont pas prévisibles de la même façon que sous le niveau d’isolement `REPEATABLE READ`. De même, ils ne sont pas aussi prévisibles que les requêtes s’exécutant sous le niveau d’isolement `READ COMMITTED` sur l’instance principale ou sur une instance RDS for MySQL.
L’instruction `UPDATE` à T7 ne modifie pas réellement le nombre de lignes de la table. Cependant, en modifiant la longueur d’une colonne de longueur variable, cette instruction peut entraîner une réorganisation interne des lignes. Une transaction `READ COMMITTED` de longue durée peut afficher l’ancienne version d’une ligne, et, par la suite, au sein de la même requête, afficher la nouvelle version de la même ligne. La requête peut également ignorer l’ancienne et la nouvelle version de la ligne, de sorte que le nombre de lignes peut être différent de ce qui est attendu.
Les résultats de Q5 et Q6 peuvent être identiques ou légèrement différents. La requête Q6 sur le réplica Aurora sous `READ COMMITTED` peut afficher, mais elle n’est pas obligée à la faire, les nouvelles lignes validées pendant que la requête est en cours d’exécution. Elle peut également afficher la ligne d’une table, mais pas de l’autre table. Si la requête de jointure ne trouve pas de ligne correspondante dans les deux tables, elle retourne un nombre égal à 0 (zéro). Si la requête retrouve bel et bien les nouvelles lignes dans `PARENT_TABLE` et dans `CHILD_TABLE`, la requête retourne un nombre égal à 1 (un). Dans une requête de longue durée, les recherches à partir des tables jointes peuvent se produire à d’importants intervalles de temps.
Ces différences de comportement dépendent du moment où les transactions sont validées et de celui où les requêtes traitent les lignes de la table sous-jacente. Ainsi, il est fort probable que vous rencontriez de telles différences dans les requêtes sur les rapports qui nécessitent plusieurs minutes ou heures, et qui s’exécutent sur des clusters Aurora traitant simultanément les transactions OLTP. Ce sont les types de charges de travail mixtes qui tirent le meilleur parti du niveau d’isolement `READ COMMITTED` sur les réplicas Aurora.