Niveles de aislamiento de Aurora MySQL
Aprenda cómo las instancias de base de datos en un clúster de Aurora MySQL implementan la propiedad de aislamiento de la base de datos. Este tema le ayuda a comprender cómo el comportamiento predeterminado de Aurora MySQL encuentra un equilibrio entre la consistencia estricta y el alto rendimiento. Puede utilizar esta información para decidir cuándo cambiar la configuración predeterminada en función de las características de su carga de trabajo.
Niveles de aislamiento disponibles para las instancias de escrituras
Puede utilizar los niveles de aislamiento REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED y SERIALIZABLE en la instancia principal de un clúster de base de datos de Aurora MySQL. Estos niveles de aislamiento funcionan de la misma manera en Aurora MySQL que en RDS for MySQL.
Nivel de aislamiento REPEATABLE READ para las instancias del lector
De manera predeterminada, las instancias de base de datos de Aurora MySQL que están configuradas como réplicas de Aurora de solo lectura siempre utilizan el nivel de aislamiento REPEATABLE
READ. Estas instancias de base de datos ignoran cualquier instrucción SET TRANSACTION ISOLATION LEVEL y continúan utilizando el nivel de aislamiento REPEATABLE READ.
No puede establecer el nivel de aislamiento de las instancias de base de datos del lector mediante parámetros de base de datos o parámetros de clúster de base de datos.
Nivel de aislamiento READ COMMITTED para las instancias del lector
Si su aplicación incluye una carga de trabajo de escritura intensiva en la instancia principal y solicitudes de larga ejecución en las réplicas de Aurora, puede experimentar lag de purgado considerable. El lag de purgado se produce cuando la recogida de elementos no utilizados interna se bloquea debido a consultas de ejecución prolongada. El síntoma que ve es un valor alto para history list length en el resultado del comando SHOW ENGINE INNODB STATUS. Puede supervisar este valor utilizando la métrica RollbackSegmentHistoryListLength en CloudWatch. Un lag de purgado considerable puede reducir la eficacia de los índices secundarios, disminuir el rendimiento general de las consultas y provocar un desperdicio de espacio de almacenamiento.
Si experimenta esos problemas, puede utilizar una opción de configuración de nivel de sesión de Aurora MySQL, aurora_read_replica_read_committed, para utilizar el nivel de aislamiento de READ COMMITTED en réplicas de Aurora. Cuando utilice esta configuración, puede ayudar a reducir las ralentizaciones y el espacio desaprovechado que pueda derivarse de la realización de consultas de ejecución prolongada al mismo tiempo que las transacciones que modifican sus tablas.
Recomendamos que se asegure de que comprende el comportamiento específico de Aurora MySQL del aislamiento READ COMMITTED antes de utilizar esta configuración. La réplica de Aurora del comportamiento de READ COMMITTED cumple con el estándar ANSI SQL. Sin embargo, el aislamiento es menos estricto que el comportamiento READ COMMITTED de MySQL típico con el que puede que esté más familiarizado. Por lo tanto, puede ser que vea resultados de consultas diferentes en READ COMMITTED en una réplica de lectura de Aurora MySQL que para la misma consulta en READ COMMITTED en la instancia principal de Aurora MySQL o en RDS para MySQL. Puede considerar utilizar la configuración de aurora_read_replica_read_committed para esos casos de uso como un informe completo que analiza una base de datos muy grande. Por el contrario, puede evitarla para consultas breves con conjuntos de resultados pequeños, donde la precisión y la repetibilidad son importantes.
El nivel de aislamiento READ COMMITTED no está disponible para las sesiones de un clúster secundario de una base de datos global de Aurora que utilicen la función de reenvío de escritura. Para obtener información sobre el reenvío de escritura, consulte Uso del reenvío de escritura en una base de datos Amazon Aurora global.
Uso de READ COMMITTED para lectores
Para utilizar el nivel de aislamiento READ COMMITTED para las réplicas de Aurora, establezca la opción de configuración aurora_read_replica_read_committed en ON. Utilice esta configuración en el nivel de sesión mientras esté conectado a una réplica de Aurora específica. Para ello, ejecute los comandos de SQL siguientes.
set session aurora_read_replica_read_committed = ON; set session transaction isolation level read committed;
Puede utilizar esta opción de configuración de manera temporal para realizar consultas ad hoc únicas e interactivas. Puede que también desee ejecutar una aplicación de informes o de análisis de datos que se beneficie del nivel de aislamiento READ COMMITTED, mientras deja el predeterminado sin cambiar para otras aplicaciones.
Cuando la configuración aurora_read_replica_read_committed está activada, utilice el comando SET TRANSACTION ISOLATION
LEVEL para especificar el nivel de aislamiento para las transacciones adecuadas.
set transaction isolation level read committed;
Diferencias en el comportamiento READ COMMITTED en las réplicas de Aurora
La configuración aurora_read_replica_read_committed hace que el nivel de aislamiento READ COMMITTED esté disponible para una réplica de Aurora, con un comportamiento de consistencia que está optimizado para las transacciones de ejecución prolongada. El nivel de aislamiento READ
COMMITTED en las réplicas de Aurora tiene un aislamiento menos estricto que en las instancias principales de Aurora. Por ese motivo, habilite esta configuración solo en las réplicas de Aurora en las que sabe que sus consultas pueden aceptar la posibilidad de ciertos tipos de resultados inconsistentes.
Sus consultas pueden experimentar ciertos tipos de anomalías de lectura cuando se enciende la configuración aurora_read_replica_read_committed. Dos tipos de anomalías son especialmente importantes para entender y manejar su código de aplicación. Una lectura no repetible se produce cuando otra transacción se confirma mientras su consulta está en ejecución. Una consulta de ejecución prolongada puede ver diferentes datos al principio de la consulta que los que ve al final. Una lectura fantasma se produce cuando otras transacciones hacen que las filas existentes se reorganicen mientras su consulta se ejecuta y su consulta lee dos veces una o más filas.
Sus consultas pueden experimentar recuentos de filas inconsistentes como resultado de las lecturas fantasmas. Puede que sus consultas también devuelvan resultados incompletos o inconsistentes debido a las lecturas no repetibles. Por ejemplo, suponga que una operación conjunta hace referencia a tablas que las instrucciones SQL modifican de forma simultánea como INSERT o DELETE. En este caso, la consulta conjunta puede leer una fila de una tabla pero no la fila correspondiente de otra tabla.
El estándar ANSI SQL permite a estos comportamientos el nivel de aislamiento READ COMMITTED. Sin embargo, estos comportamientos son diferentes de la implementación típica de MySQL de READ COMMITTED. Por lo tanto, antes de habilitar la configuración aurora_read_replica_read_committed, compruebe cualquier código existente de SQL para verificar si opera según lo esperado en el modelo de consistencia secundario.
Puede que los recuentos de filas y otros resultados no sean altamente consistentes en el nivel de aislamiento READ COMMITTED mientras se habilita este nivel de aislamiento. Por lo tanto, típicamente habilita la configuración solo mientras ejecuta consultas analíticas que agregan grandes cantidades de datos y no precisan una precisión absoluta. SI no tiene estos tipos de consultas de ejecución prolongada junto a una carga de trabajo de escritura intensiva, probablemente no necesite la configuración aurora_read_replica_read_committed. Sin la combinación de solicitudes de ejecución prolongada y una carga de trabajo de escritura intensiva, no es probable que tenga problemas con extensión de la lista del historial.
ejemplo Consultas que muestran un comportamiento de aislamiento para READ COMMITTED en las réplicas de Aurora
El siguiente ejemplo le muestra cómo las consultas READ COMMITTED en una réplica de Aurora puede devolver resultados no repetibles si las transacciones modifican las tablas asociadas al mismo tiempo. La tabla BIG_TABLE contiene 1 millón de filas antes de que comience cualquier consulta. Otras instrucciones del lenguaje de manipulación de datos (DML) añaden, eliminan o cambian filas mientras se ejecutan.
Las consultas en la instancia principal de Aurora en el nivel de aislamiento READ COMMITTED producen resultados predecibles. Sin embargo, la sobrecarga de mantener la vista de lectura consistente durante la vida útil de cada consulta de ejecución prolongada puede llevar a una recopilación de elementos no utilizados cara más adelante.
Las consultas en la réplica de Aurora en el nivel de aislamiento READ COMMITTED se optimizan para minimizar esta sobrecarga de recopilación de elementos no utilizados. La desventaja es que los resultados pueden variar dependiendo de si las consultas recuperan filas que se han añadido, eliminado o reorganizado por transacciones que se confirman cuando la consulta se ejecuta. Las consultas pueden tener en cuenta estas filas pero no están obligadas a ello. Para fines demostrativos, las consultas comprueban solo el número de filas en la tabla utilizando la función COUNT(*).
| Time | Instrucción DML en la instancia principal de Aurora | Consulta en la instancia principal de Aurora con READ COMMITTED | Consulta en la réplica de Aurora con READ COMMITTED |
|---|---|---|---|
| T1 |
INSERT INTO big_table SELECT * FROM other_table LIMIT 1000000; COMMIT;
|
||
| T2 | C1: SELECT COUNT(*) FROM big_table; |
C2: SELECT COUNT(*) FROM big_table; |
|
| T3 |
INSERT INTO big_table (c1, c2) VALUES (1, 'one more row'); COMMIT;
|
||
| T4 | Si C1 termina ahora, el resultado es 1 000 000. | Si C2 termina ahora, el resultado es 1 000 000 o 1 000 001. | |
| T5 |
DELETE FROM big_table LIMIT 2; COMMIT;
|
||
| T6 | Si C1 termina ahora, el resultado es 1 000 000. | Si C2 termina ahora, el resultado es 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 | Si C1 termina ahora, el resultado es 1 000 000. | Si C2 termina ahora, el resultado es 1 000 000 o 1 000 001, o 999 999 o posiblemente algún número mayor. | |
| T9 | C3: SELECT COUNT(*) FROM big_table; |
C4: SELECT COUNT(*) FROM big_table; |
|
| T10 | Si C3 termina ahora, el resultado es 999 999. | Si C4 termina ahora, el resultado es 999 999. | |
| T11 | C5: SELECT COUNT(*) FROM parent_table p JOIN child_table c ON (p.id = c.id) WHERE p.id = 1000; |
C6: 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 C5 termina ahora, el resultado es 0. | Si C6 termina ahora, el resultado es 0 o 1. |
Si las consultas terminan rápidamente, antes de que cualquier otra transacción realice instrucciones DML y los envíe, los resultados son predecibles y lo mismo ocurre entre la instancia primaria y la réplica de Aurora. Examinemos las diferencias de comportamiento en detalle, empezando por la primera consulta.
Los resultados para C1 son muy predecibles, ya que READ COMMITTED en la instancia principal utiliza un modelo de coherencia alta similar al del nivel de aislamiento REPEATABLE READ.
Los resultados para C2 puede variar dependiendo de las transacciones que se confirman mientras esa consulta se ejecuta. Por ejemplo, suponga que otras transacciones realizan instrucciones DML y las confirman mientras las consultas se ejecutan. En este caso, la consulta en la réplica de Aurora con el nivel de aislamiento READ COMMITTED puede o no tener en cuenta los cambios, Los recuentos de filas no se pueden predecir de la misma manera que en el nivel de aislamiento REPEATABLE READ. Tampoco se pueden predecir como consultas que se ejecutan en el nivel de aislamiento READ COMMITTED en la instancia principal o en una instancia de RDS for MySQL.
El estándar UPDATE en T7 no cambia en realidad el número de filas en la tabla. Sin embargo, al cambiar la extensión de una columna de extensión variable, esta instrucción puede hacer que las filas se reorganicen de manera interna. Una transacción READ COMMITTED de ejecución prolongada puede observar la versión antigua de una fila y más adelante, en la misma consulta, observar la nueva versión de la misma fila. La consulta también puede omitir las versiones nueva y antigua de la fila, por lo que el recuento de filas puede ser diferente de lo esperado.
Los resultados de C5 y C6 pueden ser idénticos o ligeramente diferentes. La consulta C6 en la réplica de Aurora en READ
COMMITTED puede observar, pero no está obligada a ello, las filas nuevas se confirman mientras que la consulta se ejecuta. También podría ver la fila de una tabla, pero no la de la otra. Si la consulta conjunta no encuentra una fila que coincida en ambas tablas, devuelve un recuento de cero. Si la consulta encuentra las dos filas en PARENT_TABLE y CHILD_TABLE, la consulta devuelve un recuento de uno. En una consulta de ejecución prolongada, las búsquedas de las tablas combinadas puede producirse en momentos muy separados.
nota
Estas diferencias de comportamiento depende del momento en el que se confirmen las transacciones y el momento en el que las consultas procesan las filas subyacentes de la tabla. Por lo tanto, es muy probable que vea esas diferencias en consultas de informes que tardan minutos u horas y que se ejecutan en clústeres de Aurora que procesan transacciones de OLTP al mismo tiempo. Estos son los tipos de cargas de trabajo mixtas que más se benefician del nivel de aislamiento READ COMMITTED en las réplicas de Aurora.
