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'isolation Aurora MySQL
Découvrez comment les instances de base de données d'un cluster Aurora MySQL implémentent la propriété d'isolation 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'isolation disponibles pour les instances en écriture.
Vous pouvez utiliser les niveaux d'isolation REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED et SERIALIZABLE sur l'instance principale d'un cluster de base de données Aurora MySQL. Ces niveaux d'isolation fonctionnent de la même façon dans Aurora MySQL que dans RDS pour MySQL.
REPEATABLE READ Niveaux d'isolation pour les instances en lecture
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'isolation REPEATABLE
READ. Ces instances de bases de données ignorent toutes les instructions SET TRANSACTION ISOLATION LEVEL et continuent à utiliser le niveau d'isolation REPEATABLE READ.
READ COMMITTED Niveaux d'isolation 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'isolation 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'isolation 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'isolation 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 pour 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 de plus amples informations sur le transfert d'écriture, veuillez consulter Utilisation du transfert d'écriture dans une base de données globale Amazon Aurora.
Utilisation de READ COMMITTED pour les lecteurs
Pour utiliser le niveau d'isolation 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'isolation 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'isolation 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'isolation READ COMMITTED pour un réplica Aurora, avec une cohérence optimisée pour les transactions de longue durée. Le niveau d'isolation READ
COMMITTED sur les réplicas Aurora offre une isolation moins stricte 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'isolation 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'isolation 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.
Exemple Requêtes illustrant le comportement d'isolation 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'isolation 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'isolation 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_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 | 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_table 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_table 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(*) FROM big_table; |
Q4: SELECT COUNT(*) FROM big_table; |
|
| 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(*) 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 | 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'isolation 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'isolation 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'isolation REPEATABLE READ. De même, ils ne sont pas aussi prévisibles que les requêtes s'exécutant sous le niveau d'isolation READ COMMITTED sur l'instance principale ou sur une instance RDS pour 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.
Note
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'isolation READ COMMITTED sur les réplicas Aurora.
