Does Amazon Aurora offer serializable isolation for read-only transaction running on slave nodes?

user_185646 0
Viewed 2693

From what I understand from reading Amazon aurora documentation, even if Aurora master node synchronously write the WAL log to 4 of 6 storage nodes. Unless there is switch of master, the Aurora slave are only kept in sync using asynchronous log shipping directly from the master node.

If this is true, I would assume that it's possible for a client to write and commit a value to master node and then immediately send a read only query to one of the slave and observe the old value instead of the latest value that was just written.

this would mean it can only support snapshot isolation mode on the slave.

this seem like a very big limitation! And I wanted to make sure this is correct.

transaction-isolation amazon-aurora amazon-web-services isolation-level mysql
2 Answers

Serializable isolation is a difficult problem for clusters. I don't know of any that do truly support it. And if there was one that did, I don't know if I would be willing to accept the performance implications that would come with it.

Running the following on an aurora instance seems to indicate that only REPEATABLE-READ is supported.

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

Due to eventual consistency from replication lag; if it is critical that you receive real time data, you should be reading against the master.

user_8165840

Replica lag for Aurora is very small relative to non-Aurora read replicas, but is still a non-zero value which you can monitor with CloudWatch metric AuroraBinlogReplicaLag and AuroraReplicaLag - documented more extensively at https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Monitoring.html. Specific to your question, Aurora doesn't write to all 6 copies of the storage syncronously - only 4. A 4-part blog super deep dive on how this storage system works can be found at https://aws.amazon.com/blogs/database/amazon-aurora-under-the-hood-quorum-and-correlated-failure, and I encourage everyone to read it. You can also read more about Aurora Replication at https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Replication.html. but Steve Buzonas is correct - if you need guaranteed read-after-write SERIALIZABLE reads, then you need to read from the writer instance endpoint: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.Endpoints.html

user_106124580
Related