Aurora MySQL isolation levels
Learn how DB instances in an Aurora MySQL cluster implement the database property of isolation. This topic explains how the Aurora MySQL default behavior balances between strict consistency and high performance. You can use this information to help you decide when to change the default settings based on the traits of your workload.
Available isolation levels for writer instances
You can use the isolation levels REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED, and
SERIALIZABLE on the primary instance of an Aurora MySQL DB cluster. These isolation levels work the same in
Aurora MySQL as in RDS for MySQL.
REPEATABLE READ isolation level for reader instances
By default, Aurora MySQL DB instances that are configured as read-only Aurora Replicas always use the REPEATABLE
READ isolation level. These DB instances ignore any SET TRANSACTION ISOLATION LEVEL statements and
continue using the REPEATABLE READ isolation level.
You can't set the isolation level for reader DB instances using DB parameters or DB cluster parameters.
READ COMMITTED isolation level for reader instances
If your application includes a write-intensive workload on the primary instance and long-running queries on the Aurora
Replicas, you might experience substantial purge lag. Purge lag happens when internal garbage
collection is blocked by long-running queries. The symptom that you see is a high value for history list length in
the output from the SHOW ENGINE INNODB STATUS command. You can monitor this value using the
RollbackSegmentHistoryListLength metric in CloudWatch. Substantial purge lag can reduce the effectiveness of
secondary indexes, decrease overall query performance, and lead to wasted storage space.
If you experience such issues, you can set an Aurora MySQL session-level configuration setting,
aurora_read_replica_read_committed, to use the READ COMMITTED isolation level on Aurora Replicas.
When you apply this setting, you can help reduce slowdowns and wasted space that can result from performing long-running queries
at the same time as transactions that modify your tables.
We recommend making sure that you understand the specific Aurora MySQL behavior of the READ COMMITTED isolation
before using this setting. The Aurora Replica READ COMMITTED behavior complies with the ANSI SQL standard. However,
the isolation is less strict than typical MySQL READ COMMITTED behavior that you might be familiar with. Therefore,
you might see different query results under READ COMMITTED on an Aurora MySQL read replica than you might see for the
same query under READ COMMITTED on the Aurora MySQL primary instance or on RDS for MySQL. You might consider using the
aurora_read_replica_read_committed setting for such cases as a comprehensive report that scans a very large
database. In contrast, you might avoid it for short queries with small result sets, where precision and repeatability are
important.
The READ COMMITTED isolation level isn't available for sessions within a secondary cluster in an Aurora
global database that use the write forwarding feature. For information about write forwarding, see Using write forwarding in an Amazon Aurora global database.
Using READ COMMITTED for readers
To use the READ COMMITTED isolation level for Aurora Replicas, set the
aurora_read_replica_read_committed configuration setting to ON. Use this setting at the
session level while connected to a specific Aurora Replica. To do so, run the following SQL commands.
set session aurora_read_replica_read_committed = ON; set session transaction isolation level read committed;
You might use this configuration setting temporarily to perform interactive, one-time queries. You might also want to run
a reporting or data analysis application that benefits from the READ COMMITTED isolation level, while leaving
the default setting unchanged for other applications.
When the aurora_read_replica_read_committed setting is turned on, use the SET TRANSACTION ISOLATION
LEVEL command to specify the isolation level for the appropriate transactions.
set transaction isolation level read committed;
Differences in READ COMMITTED behavior on Aurora replicas
The aurora_read_replica_read_committed setting makes the READ COMMITTED isolation level
available for an Aurora Replica, with consistency behavior that is optimized for long-running transactions. The READ
COMMITTED isolation level on Aurora Replicas has less strict isolation than on Aurora primary instances. For that
reason, enable this setting only on Aurora Replicas where you know that your queries can accept the possibility of certain
types of inconsistent results.
Your queries can experience certain kinds of read anomalies when the aurora_read_replica_read_committed
setting is turned on. Two kinds of anomalies are especially important to understand and handle in your application code. A
non-repeatable read occurs when another transaction commits while your query is running. A
long-running query can see different data at the start of the query than it sees at the end. A phantom
read occurs when other transactions cause existing rows to be reorganized while your query is running, and
one or more rows are read twice by your query.
Your queries might experience inconsistent row counts as a result of phantom reads. Your queries might also return
incomplete or inconsistent results due to non-repeatable reads. For example, suppose that a join operation refers to tables
that are concurrently modified by SQL statements such as INSERT or DELETE. In this case, the join
query might read a row from one table but not the corresponding row from another table.
The ANSI SQL standard allows both of these behaviors for the READ COMMITTED isolation level. However, those
behaviors are different than the typical MySQL implementation of READ COMMITTED. Thus, before enabling the
aurora_read_replica_read_committed setting, check any existing SQL code to verify if it operates as
expected under the looser consistency model.
Row counts and other results might not be strongly consistent under the READ COMMITTED isolation level while
this setting is enabled. Thus, you typically enable the setting only while running analytic queries that aggregate large
amounts of data and don't require absolute precision. If you don't have these kinds of long-running queries
alongside a write-intensive workload, you probably don't need the aurora_read_replica_read_committed
setting. Without the combination of long-running queries and a write-intensive workload, you're unlikely to encounter
issues with the length of the history list.
Example Queries showing isolation behavior for READ COMMITTED on Aurora Replicas
The following example shows how READ COMMITTED queries on an Aurora Replica might return non-repeatable
results if transactions modify the associated tables at the same time. The table BIG_TABLE contains 1
million rows before any queries start. Other data manipulation language (DML) statements add, remove, or change rows
while they are running.
The queries on the Aurora primary instance under the READ COMMITTED isolation level produce predictable
results. However, the overhead of keeping the consistent read view for the lifetime of every long-running query can lead
to expensive garbage collection later.
The queries on the Aurora Replica under the READ COMMITTED isolation level are optimized to minimize this
garbage collection overhead. The tradeoff is that the results might vary depending on whether the queries retrieve rows
that are added, removed, or reorganized by transactions that are committed while the query is running. The queries are
allowed to consider these rows, but aren't required to. For demonstration purposes, the queries check only the
number of rows in the table by using the COUNT(*) function.
| Time | DML statement on Aurora primary instance | Query on Aurora primary instance with READ COMMITTED | Query on Aurora Replica with 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 | If Q1 finishes now, result is 1,000,000. | If Q2 finishes now, result is 1,000,000 or 1,000,001. | |
| T5 |
DELETE FROM big_table LIMIT 2; COMMIT;
|
||
| T6 | If Q1 finishes now, result is 1,000,000. | If Q2 finishes now, result is 1,000,000 or 1,000,001 or 999,999 or 999,998. | |
| T7 |
UPDATE big_table SET c2 = CONCAT(c2,c2,c2); COMMIT;
|
||
| T8 | If Q1 finishes now, result is 1,000,000. | If Q2 finishes now, result is 1,000,000 or 1,000,001 or 999,999, or possibly some higher number. | |
| T9 |
Q3:
SELECT COUNT(*) FROM big_table;
|
Q4:
SELECT COUNT(*) FROM big_table;
|
|
| T10 | If Q3 finishes now, result is 999,999. | If Q4 finishes now, result is 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 | If Q5 finishes now, result is 0. | If Q6 finishes now, result is 0 or 1. |
If the queries finish quickly, before any other transactions perform DML statements and commit, the results are predictable and the same between the primary instance and the Aurora Replica. Let's examine the differences in behavior in detail, starting with the first query.
The results for Q1 are highly predictable because READ COMMITTED on the primary instance uses a strong
consistency model similar to the REPEATABLE READ isolation level.
The results for Q2 might vary depending on what transactions are committed while that query is running. For example,
suppose that other transactions perform DML statements and commit while the queries are running. In this case, the query
on the Aurora Replica with the READ COMMITTED isolation level might or might not take the changes into
account. The row counts aren't predictable in the same way as under the REPEATABLE READ isolation level.
They also aren't as predictable as queries running under the READ COMMITTED isolation level on the
primary instance, or on an RDS for MySQL instance.
The UPDATE statement at T7 doesn't actually change the number of rows in the table. However, by
changing the length of a variable-length column, this statement can cause rows to be reorganized internally. A
long-running READ COMMITTED transaction might see the old version of a row, and later within the same query
see the new version of the same row. The query can also skip both the old and new versions of the row, so the row count
might be different than expected.
The results of Q5 and Q6 might be identical or slightly different. Query Q6 on the Aurora Replica under READ
COMMITTED is able to see, but is not required to see, the new rows that are committed while the query is
running. It might also see the row from one table, but not from the other table. If the join query doesn't find a
matching row in both tables, it returns a count of zero. If the query does find both the new rows in
PARENT_TABLE and CHILD_TABLE, the query returns a count of one. In a long-running query,
the lookups from the joined tables might happen at widely separated times.
Note
These differences in behavior depend on the timing of when transactions are committed and when the queries process
the underlying table rows. Thus, you're most likely to see such differences in report queries that take minutes
or hours and that run on Aurora clusters processing OLTP transactions at the same time. These are the kinds of mixed
workloads that benefit the most from the READ COMMITTED isolation level on Aurora Replicas.