Transaction Isolation on AWS Databases - Same Level Name, Different Anomalies, and the Statement That Changes Nothing

First Published: 2026-08-27
Last Updated: 2026-08-27

After migrating an application to a different database engine, you may find that while the row counts match, validation tasks pass, and acceptance testing shows green, data integrity still breaks in production. What broke is not the data. It is the agreement between transactions that run at the same time.

During the migration process, most of what you can verify is data at rest. You compare rows, data types, and key formats. However, none of these checks reveal what happens when two transactions attempt to access the same row simultaneously. Transaction isolation levels are not observable when examining static data alone.

This article is not a comprehensive guide to transaction isolation levels. You can find the definitions in upstream documentation. What this article covers is which AWS engine fails to prevent which anomaly, and what is left for the application to do when it does not.

One point deserves to be set down first. The most dangerous thing about a migration is not that the level name changes. It is that the name stays the same while the range it protects changes. Worse still, on some engines the statement that selects a level runs, succeeds, and does nothing. No error is raised. Nothing is written to the log.

This article checked its technical statements against the Amazon Aurora User Guide, Amazon Aurora DSQL User Guide, Amazon DynamoDB Developer Guide, Amazon Redshift Database Developer Guide, AWS Database Blog, the official PostgreSQL documentation, and the official MySQL documentation, as well as the original paper from ACM SIGMOD 95, as of August 27, 2026. Default values and availability move. Look them up again at the time you read this. This article does not cover pricing.

1. What Breaks After the Move Is Not the Data
2. Fixing the Vocabulary Before the Comparison
3. write skew Is Not in the Standard's Vocabulary
4. What Each Engine Actually Guarantees
5. Three Paths the Same Statement Takes
6. SELECT ... FOR UPDATE Means Four Different Things
7. What Optimistic Concurrency Control in Aurora DSQL Requires of the Application
8. In DynamoDB the Pair of Operations Decides the Isolation
9. Where the Primary Sources Are Silent and Where They Disagree
10. What to Inventory Before You Move
11. Failures That Show Up After the Move
12. Frequently Asked Questions
13. Summary
14. References

1. What Breaks After the Move Is Not the Data

1.1 The Situation This Article Assumes

This article assumes the perspective of someone who has already migrated, or is planning to migrate, an application to a different database engine. It does not address the merits of migration itself, but rather focuses on the stage after the decision to migrate has been made, or after the migration is complete.

The initial challenge from this perspective isn't the lack of general information about isolation levels. That information is readily available in the official documentation for PostgreSQL and MySQL. The difficulty is working out which of the guarantees you relied on you have lost, and one reading of the destination's documentation will not tell you. AWS documentation states that the engine is compatible. It does not lay out the incompatible parts along the isolation level axis.

Therefore, verification requires consulting multiple documents. The engine names and default settings are found in the AWS user guides. The upstream documentation, PostgreSQL's or MySQL's, says what each level prevents. And for an anomaly that appears in neither the upstream documentation nor the standard, the trail runs back to the original paper from 1995. This article gathers those three layers into one place.

1.2 The Boundary With the Existing Articles

This article does not cover the following topics.

Published	Focus
Heterogeneous Database Migration on AWS - Schema Conversion, Full Load with CDC, Data Validation, and a Cutover You Can Reverse	Details the steps for database migration, including schema conversion, full load with Change Data Capture (CDC), validation tasks, and a reversible cutover. That article states outright that it does not provide a cross-engine comparison of isolation levels, and this article takes that slot.
Amazon Aurora DSQL Design Decision Guide - Distributed SQL Between Amazon DynamoDB and Aurora PostgreSQL	A guide to help determine whether to choose Aurora DSQL. It discusses the internal workings of a distributed architecture, compatibility audits, and the implementation of retry layers. This article does not cover the selection criteria.
Event-Driven Serverless Architecture on AWS - Building Resilient Workflows with API Gateway, Lambda, EventBridge, Step Functions, and DynamoDB	Covers idempotency, Outbox patterns, and sagas. It details the design for ensuring safe retries. This article goes as far as why a retry becomes necessary and hands the design itself over.
Zero-Downtime Database Change on RDS and Aurora - Blue/Green Deployments, Upgrades, and Rollback Design	Covers schema changes and version upgrades inside one database engine. This article does not cover schema changes.
Summary of Differences and Commonalities in AWS Database Services using the Quorum Model - Comparison Charts of Amazon Aurora, Amazon DocumentDB, and Amazon Neptune	Examines replication at the storage layer and quorum models. It discusses how many copies of a write must be delivered before it is considered confirmed, a layer distinct from isolation levels.
How Amazon S3 Achieves Strong Consistency and Durability - The Architecture Behind the Guarantees	Focuses on consistency, specifically when written data becomes visible. Isolation, in contrast, concerns how multiple concurrent transactions appear to each other; it is a separate concept.
What AWS Transform Automates and What Stays Human - Agentic and Self-Directed Paths, the Handoff Points That Are Designed In, and What Functional Equivalence Testing Actually Covers	Covers the move itself: where the agent finishes the conversion and where people pick it up. This article sits after that stage and covers only what changes in the meaning of the SQL on the destination.
Load Testing on AWS - Which Policy Actually Applies, Why the Load Generator Runs Out First, and What a One-Minute Metric Hides	Details issues that only emerge under load. Because anomalies related to isolation levels are proportional to the frequency of concurrent operations, this article is closely linked to test design.

1.3 Three Questions This Article Answers

First, when the destination system has a level with the same name as the level used in the source system, does it provide the same level of protection? The answer is no. §4 details the specific areas where the protection differs.

Second, do statements that specify a level always take effect? The answer is no. There are engines that ignore them, engines that accept them syntactically but do not produce any effect, and engines that only allow a single value within a specific context. §5 lines up all three.

Third, what remains on the application side when protection is not provided? Primarily, two options remain: explicit row reservations and retrying failed transactions. Sections 6 and 7 cover them.

2. Fixing the Vocabulary Before the Comparison

The primary reason comparisons of isolation levels can be difficult to read is that the names used for anomalies vary across different documents. Fixing them first solves that. This article uses only the following five terms, and these terms will not be altered throughout the text.

2.1 The Three Phenomena the Standard Names

ANSI SQL-92 defines isolation levels based on phenomena. The beginning of the original paper lists the phenomena referenced by the standard.

ANSI SQL-92 [MS, ANSI] defines Isolation Levels in terms of phenomena:
Dirty Reads, Non-Repeatable Reads, and Phantoms.

There are three such phenomena. That is easy to confuse with the number of isolation levels, which is four. The number of phenomena is three.

Term	What Happens
`dirty read`	You read changes that have not yet been committed. If the transaction that wrote them rolls back, you are holding a value that never existed.
`non-repeatable read`	When you read the same row twice within a single transaction, the value is different. This is because another transaction has updated and committed changes to that row in the interim.
`phantom`	When you read with the same search criteria twice, the set of rows returned is different. This is because another transaction has inserted or deleted rows that match the criteria and then committed those changes.

The Amazon Redshift Database Developer Guide describes these three phenomena in the same order. The guide's definition of phantom is as follows:

Phantoms - A phantom is a row that matches the search criteria but is
not initially seen.

2.2 The Two Anomalies the Standard Does Not Name

Two of the anomalies that bite in practice are missing from the standard's three phenomena.

Term	What Happens
`lost update`	Two transactions read the same row, each perform calculations, and then attempt to write the updated values back. The transaction that writes last overwrites the previous update, effectively losing the earlier change.
`write skew`	Two transactions read the same two data items, and each transaction writes a different value to those items. Although each transaction individually adheres to its constraints, a violation occurs when both transactions execute simultaneously.

Neither of these anomalies is included in the three phenomena defined in ANSI SQL-92. The original paper from 1995 redefined them as P4 and A5B, respectively, to describe behavior the standard's three phenomena cannot tell apart. These two, particularly write skew, are most likely to cause subtle failures during migrations. The reasons for this are explained in §3.

2.3 Four Level Names Do Not Mean Four Implementations

The level names are as follows, and will always be written in code spans in this article:

READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE

In addition to these standard four, two further names are included. One is SNAPSHOT, which Amazon Redshift provides as a database-level setting. The other is snapshot isolation, which is more of a description of an implementation method rather than a level name. It is what Aurora DSQL actually publishes under the name REPEATABLE READ.

Four names does not mean four implementations. The official PostgreSQL documentation explicitly states this.

In PostgreSQL, you can request any of the four standard transaction
isolation levels, but internally only three distinct isolation levels
are implemented, i.e., PostgreSQL's Read Uncommitted mode behaves like
Read Committed.

Even when READ UNCOMMITTED is specified, it may function as READ COMMITTED. The statement is accepted. No error is raised. This is the smallest example illustrating the core topic discussed in this article.

3. `write skew` Is Not in the Standard's Vocabulary

3.1 A5B, as the Original Paper Defines It

The write skew anomaly comes from "A Critique of ANSI SQL Isolation Levels," presented at ACM SIGMOD in 1995. ⚠ ANSI SQL does not define it. The paper identifies this as an anomaly, introduced to demonstrate a scenario where the standard's three phenomena fail to distinguish between different outcomes. The paper gives this anomaly the symbol A5B.

A5B Write Skew Suppose T1 reads x and y, which are consistent with C(),
and then a T2 reads x and y, writes x, and commits. Then T1 writes y.
If there were a constraint between x and y, it might be violated.
In terms of histories:

A5B: r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur)    (Write Skew)

The paper uses the example of bank accounts. Consider a scenario where the sum of balances for jointly held accounts must be non-negative, but individual balances are permitted to be negative. If two transactions each attempt to withdraw funds from different accounts, and each verifies that the total remains non-negative before it withdraws, but both transactions commit, the resulting total could become negative.

Crucially, neither transaction is writing to the same row. Because they are not modifying the same row, row-level conflict detection will not trigger.

3.2 Snapshot Isolation and `REPEATABLE READ` Are Incomparable

The conclusion most relevant in the context of migration is Remark 9. First, it is demonstrated that snapshot isolation allows anomalies that are not permitted by REPEATABLE READ.

However, Write Skew (A5B) obviously can occur in a Snapshot Isolation
history (e.g., H5), and in the Single Valued history interpretation
we've been reasoning about, forbidding P2 also precludes A5B.
Therefore Snapshot Isolation admits history anomalies that
REPEATABLE READ does not.

Next, it is shown that the reverse is also true.

Snapshot Isolation cannot experience the A3 anomaly. A transaction
rereading a predicate after an update by another will always see the
same old set of data items. But the REPEATABLE READ isolation level can
experience A3 anomalies. Snapshot Isolation histories prohibit histories
with anomaly A3, but allow A5B, while REPEATABLE READ does the opposite.

And then, the conclusion is reached.

Remark 9.  REPEATABLE READ  »« Snapshot Isolation.

The symbol »« signifies that the two isolation levels are not comparable – that is, neither is strictly superior to the other. One is not a superset of the other.

⚠ In this paper, the term phantom is used with two different symbols. A3 refers to a narrow definition, where re-reading the same predicate returns a different set of rows. P3 is a broader definition that includes A3. Therefore, preventing A3 and preventing P3 are not the same thing. The term phantom in the following table refers to P3.

Table 4 in the paper lists the anomalies that can occur at each isolation level. Only the rows this article needs are extracted below.

Level	`lost update` (P4)	`phantom` (P3)	`write skew` (A5B)
`READ UNCOMMITTED`	Possible	Possible	Possible
`READ COMMITTED`	Possible	Possible	Possible
`REPEATABLE READ`	Not Possible	Possible	Not Possible
Snapshot	Not Possible	Sometimes Possible	Possible
`SERIALIZABLE`	Not Possible	Not Possible	Not Possible

⚠ This table describes theoretical isolation levels. The actual behavior of a specific database engine using the same names may vary and needs to be determined on a case-by-case basis. This is discussed in §4.

Anomaly Map: What the Standard Names and What It Does Not

3.3 Why This Matters in a Migration

Many of the engines selected as destinations rely on reading from snapshots as their core functionality. In exchange for the advantage of not having reads wait for writes, many implementations do not perform conflict checks on the rows read by default. Some, like Aurora DSQL, explicitly state in their official documentation that they do not perform these checks (see §4.3). The absence of checks on read rows means that write skew can persist.

And write skew, because it is not in the standard's vocabulary, does not make it onto the pre-migration checklist. If a migration proceeds without addressing this, the issue will only surface when the following conditions are met:

The constraint is not closed within a single row; it spans multiple rows or tables.
The application confirms the constraint through a SELECT query before writing the data.
Concurrent operations are occurring.

The third condition is crucial; the issue doesn't appear under low load. It won't manifest during initial testing or trial runs, but will only emerge under peak production load.

4. What Each Engine Actually Guarantees

This is the core of the article. For each engine, the available levels and the default come first. Then comes what the default fails to prevent.

4.1 Aurora MySQL and RDS for MySQL

All four levels are usable on the writer instance. The Amazon Aurora User Guide states:

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.

The default is REPEATABLE READ, which is also the default for InnoDB and aligns with the MySQL official documentation.

InnoDB's behavior in REPEATABLE READ differs between non-locking reads and locking reads. Regarding non-locking SELECT statements, the official documentation states:

Consistent reads within the same transaction read the snapshot
established by the first read.

For locking reads, such as SELECT ... FOR UPDATE and SELECT ... FOR SHARE, as well as UPDATE and DELETE operations, gap locks or next-key locks are applied to the range of the scanned index. This prevents other sessions from inserting rows within that range.

Lowering the isolation level to READ COMMITTED removes this behavior. The official documentation is clear on this point.

Because gap locking is disabled, phantom row problems may occur, as
other sessions can insert new rows into the gaps.

This is a key point of divergence. PostgreSQL's default is READ COMMITTED, while MySQL's default is REPEATABLE READ. Depending on the direction of the migration, the default isolation level will either increase or decrease if no action is taken.

⚠ Furthermore, a reader instance behaves differently. §5.1 covers that separately.

4.2 Aurora PostgreSQL and RDS for PostgreSQL

AWS documentation does not have a dedicated chapter on the isolation levels for Aurora PostgreSQL; it simply states that it is PostgreSQL compatible. Therefore the upstream documentation is the canonical source for the definitions of the anomalies. The table below is from the official PostgreSQL documentation.

Level	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read uncommitted	Allowed, but not in PG	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible	Possible
Repeatable read	Not possible	Not possible	Allowed, but not in PG	Possible
Serializable	Not possible	Not possible	Not possible	Not possible

There are two points to note regarding interpretation.

First, look at the Phantom Read column on the Repeatable read row. The standard allows phantom reads under REPEATABLE READ, but PostgreSQL does not. The official documentation provides further explanation on this.

The table also shows that PostgreSQL's Repeatable Read implementation
does not allow phantom reads. This is acceptable under the SQL standard
because the standard specifies which anomalies must not occur at certain
isolation levels; higher guarantees are acceptable.

In short, PostgreSQL's REPEATABLE READ is stronger than the standard requires. MySQL's REPEATABLE READ and PostgreSQL's REPEATABLE READ carry the same name and are not the same thing.

Second, look at the rightmost column, Serialization Anomaly. It is a column the standard three phenomena do not have, and it is where write skew lives. The Repeatable read row reads Possible. The official documentation even carries a worked example.

Suppose that serializable transaction A computes:

SELECT SUM(value) FROM mytab WHERE class = 1;

and then inserts the result (30) as the value in a new row with
class = 2. Concurrently, serializable transaction B computes:

SELECT SUM(value) FROM mytab WHERE class = 2;

and obtains the result 300, which it inserts in a new row with class = 1.
Then both transactions try to commit. If either transaction were running
at the Repeatable Read isolation level, both would be allowed to commit;
but since there is no serial order of execution consistent with the
result, using Serializable transactions will allow one transaction to
commit and will roll the other back with this message:

ERROR:  could not serialize access due to read/write dependencies among transactions

Choosing SERIALIZABLE prevents it. Transactions will fail, though, so a retry path is required. The official PostgreSQL documentation says to make that retry a mechanism rather than a per-caller concern.

When using this technique, it will avoid creating an unnecessary burden
for application programmers if the application software goes through a
framework which automatically retries transactions which are rolled back
with a serialization failure.

⚠ And one limitation involving read replicas sits upstream. §9.2 covers it separately.

4.3 Aurora DSQL

Aurora DSQL gives you nothing to choose from. The migration guide in the user guide settles it in one sentence.

The transaction isolation level is fixed at PostgreSQL Repeatable Read.

What it is underneath is snapshot isolation, and the concurrency control page says so.

It maintains full ACID compliance through snapshot isolation, ensuring
data consistency and reliability.

When reviewing the list of supported SQL commands, it becomes clear that there is only one option for transaction control statements. Both BEGIN and START TRANSACTION only allow specifying the ISOLATION LEVEL REPEATABLE READ.

⚠ While the concurrency control page for Aurora DSQL mentions snapshot isolation, it does not list the specific anomalies it prevents. The AWS Database Blog describes snapshot isolation in general terms, stating that it prevents dirty reads, non-repeatable reads, and phantom reads. However, this is a general description of snapshot isolation and not specific to Aurora DSQL. Furthermore, Table 4 in the original paper lists phantom (P3) as Sometimes Possible. ⛔ This article does not say that Aurora DSQL prevents phantom (§9.4).

On the other hand, AWS clearly states the behavior regarding write skew. This is where §3.2, Remark 9, applies. Snapshot isolation permits write skew. An article on the AWS Database Blog that discusses transaction processing in Aurora DSQL even provides solutions for this issue.

SELECT FOR UPDATE is a special kind of SQL statement in Amazon Aurora
DSQL. For read-write transactions, you can utilize SELECT FOR UPDATE for
managing write skew as Aurora DSQL doesn't perform concurrency checks on
read records.

The reason for this lies in the final point. It does not perform concurrency checks on rows that have been read. Therefore, if you need to protect rows that have been read, the reading side must explicitly declare this. The mechanism for this declaration is SELECT ... FOR UPDATE. Details are provided in §6.3.

4.4 Amazon DynamoDB

DynamoDB does not provide syntax for specifying isolation levels. Isolation is decided by which operation ran concurrently with which. The developer guide lists combinations of operations rather than a list of isolation levels.

It is not possible to state that DynamoDB is SERIALIZABLE in a single sentence. This is because the documentation lists combinations of operations that do not achieve serializability. See §8 for more details.

4.5 Amazon Redshift

Redshift has two isolation levels. This is described in the database developer guide.

SNAPSHOT and SERIALIZABLE isolation are the two serializable isolation
levels available in Amazon Redshift.

SNAPSHOT isolation is the default isolation level when creating
provisioned clusters and serverless workgroups, letting you process
larger volumes of data than SERIALIZABLE isolation in less time.

SERIALIZABLE isolation takes more time, but implements stricter
constraints on concurrent transactions. This isolation level prevents
problems such as write-skew anomalies by only allowing one transaction
to commit, while canceling all other concurrent transaction with an
serializable isolation violation error.

⚠ The default is SNAPSHOT, and the condition attached to that is when you create a provisioned cluster or a serverless workgroup. Migrate on the belief that Redshift is serializable and you are already one step off. Furthermore, the third paragraph is one of the few places in AWS's official documentation where the term write skew is used. It explicitly states that only SERIALIZABLE can prevent this.

⇒ Given the conditions, don't assume the default; instead, query the current setting.

To change the default, specify it at the database level. Both CREATE DATABASE and ALTER DATABASE include a clause for ISOLATION LEVEL, where you can specify either SNAPSHOT or SERIALIZABLE. Applying ALTER DATABASE comes with operational constraints:

You must be a superuser or have the CREATE DATABASE privilege for the target database.
The isolation level of the dev database cannot be changed.
It cannot be changed within a transaction block.
It will fail if other users are connected to the database.

The current setting can be verified using the STV_DB_ISOLATION_LEVEL catalog view.

When a conflict is detected in SERIALIZABLE, the following error occurs:

ERROR:1023 DETAIL: Serializable isolation violation on table in Redshift

⚠ In Redshift, in addition to the isolation level, long-running transactions can prevent storage reclamation. VACUUM cannot be executed within a transaction block, and if a transaction that began before a deletion is still open, VACUUM cannot reclaim the deleted rows. Keeping a connection open for an analytical workload therefore bites regardless of the isolation level setting. Open transactions can be viewed using the SVV_TRANSACTIONS view.

4.6 The Comparison

First, the levels you can choose and the default.

Engine and Endpoint	Levels You Can Choose	Default	How to Change It
Aurora MySQL writer / RDS for MySQL	`READ UNCOMMITTED`, `READ COMMITTED`, `REPEATABLE READ`, `SERIALIZABLE`	`REPEATABLE READ`	`SET [SESSION] TRANSACTION ISOLATION LEVEL`. ⚠ Aurora MySQL does not accept `GLOBAL`.
Aurora MySQL reader	`REPEATABLE READ`. With `aurora_read_replica_read_committed` on, `READ COMMITTED`	`REPEATABLE READ`	Set the session-level setting first. The statement alone changes nothing (§5.1)
Aurora PostgreSQL / RDS for PostgreSQL	`READ COMMITTED`, `REPEATABLE READ`, `SERIALIZABLE`. `READ UNCOMMITTED` is accepted but behaves as `READ COMMITTED`	`READ COMMITTED`	`SET TRANSACTION` / `SET SESSION CHARACTERISTICS` / `default_transaction_isolation` in a parameter group
Aurora DSQL	`REPEATABLE READ` only. What it is underneath is snapshot isolation	`REPEATABLE READ`	None
Amazon DynamoDB	No syntax for specifying an isolation level	—	—
Amazon Redshift	`SNAPSHOT`, `SERIALIZABLE`, per database	`SNAPSHOT`	`CREATE DATABASE` / `ALTER DATABASE ... ISOLATION LEVEL`

Next, here is what each level prevents and what it does not. The cells use only three values: Prevented, Not prevented, and Not documented. Not documented means that the engine's official documentation says nothing about that combination, and this article does not fill it in by inference. The one exception is the DynamoDB row, which reads Not expressible because that engine has no isolation level syntax at all (§8).

Engine and Level	`dirty read`	`non-repeatable read`	`phantom`	`lost update`	`write skew`
Aurora MySQL writer, `REPEATABLE READ` (default)	Prevented	Prevented	Prevented (gap locks for locking reads, the snapshot for non-locking reads)	Not documented	Not documented
Aurora MySQL writer, `READ COMMITTED`	Prevented	Not prevented	Not prevented (gap locking is disabled)	Not documented	Not documented
Aurora MySQL reader with `READ COMMITTED` enabled	Prevented	Not prevented	Not prevented	Not documented	Not documented
Aurora PostgreSQL, `READ COMMITTED` (default)	Prevented	Not prevented	Not prevented	Not documented	Not prevented
Aurora PostgreSQL, `REPEATABLE READ`	Prevented	Prevented	Prevented (stronger than the standard)	Not documented	Not prevented
Aurora PostgreSQL, `SERIALIZABLE`	Prevented	Prevented	Prevented	Not documented	Prevented
Aurora DSQL, `REPEATABLE READ` (snapshot isolation)	Prevented	Prevented	Not documented (§9.4)	Prevented (of two concurrent updates to the same row, one fails)	Not prevented
Amazon Redshift, `SNAPSHOT` (default)	Prevented	Prevented	Prevented	Prevented (protected against update and delete conflicts)	Not prevented
Amazon Redshift, `SERIALIZABLE`	Prevented	Prevented	Prevented	Prevented	Prevented
Amazon DynamoDB	Not expressible (§8)	Not expressible (§8)	Not expressible (§8)	Not expressible (§8)	Not expressible (§8)

Look at the write skew column. Only two cells in that column say Prevented, and neither of them is a default: Aurora PostgreSQL at SERIALIZABLE and Amazon Redshift at SERIALIZABLE. Aurora DSQL offers no setting to choose from, so the remedy there is an explicit declaration with SELECT ... FOR UPDATE. Not one engine in this table has official documentation saying that its default setting prevents write skew.

The lost update column reading Not documented on every MySQL and PostgreSQL row also means something. The official MySQL and PostgreSQL documentation carries no lost update column in its description of isolation levels, because in multiversion concurrency control what happens when the same row is updated concurrently is settled by a mechanism separate from the level. A missing column and a guarantee are not the same thing. An update that reads a value, computes in the application, and writes it back is not necessarily protected by the database at any level.

What Each Engine Prevents and What It Does Not

5. Three Paths the Same Statement Takes

A statement written to change the isolation level does not always take effect. What is more, it fails to take effect in three different ways across three engines.

Where the Isolation Level Statement Goes

5.1 An Aurora MySQL Reader Ignores the Statement

This excerpt is from the Amazon Aurora User Guide.

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.

⚠ Statements are ignored. This does not result in an error. The same application code will function correctly when connected to the writer endpoint, but will not function when connected to the reader endpoint. In a configuration that routes reads to the reader, the same code may operate with different isolation levels depending on the connection endpoint.

If you want to use READ COMMITTED on the reader, you must first configure a session-level setting.

set session aurora_read_replica_read_committed = ON;
set session transaction isolation level read committed;

⛔ However, the READ COMMITTED achieved through this setting is distinct from the READ COMMITTED used by the writer, as explicitly stated in the user guide.

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.

Within the same engine, the same cluster, and with the same name, different results may be returned depending on the connection endpoint. The same guide names two anomalies this setting admits: non-repeatable read and phantom read. A long-running aggregate query sees changes committed while it runs, and row reorganization makes it read the same row twice. The symptom is a row count that does not add up. ⚠ However, the definition of phantom read as used here differs from the standard definition (see §9.3).

The guide limits the use of this setting. Enable it only for analytical queries that aggregate large volumes and do not need absolute precision. Conversely, it advises against using it for short queries where accuracy and reproducibility are critical.

⚠ Furthermore, this READ COMMITTED setting cannot be used in sessions that utilize write forwarding on secondary clusters within an Aurora Global Database.

5.2 Redshift Accepts the Syntax and Does Nothing With It

Redshift's BEGIN syntax accepts all four standard levels. However, a note is attached:

Note: READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ have no
operational impact and map to SERIALIZABLE in Amazon Redshift.

⚠ no operational impact. When migrating an application from PostgreSQL to Redshift, any SET TRANSACTION ISOLATION LEVEL READ COMMITTED commands that remain in the code will run. They will run and do nothing. §9.1 covers the fact that this note itself no longer lines up with the current default.

In Redshift, a database attribute decides the isolation level, not a session-level statement. That difference is one instance of a wider pattern: Redshift shares elements with PostgreSQL while giving them different meanings. The database developer guide itself warns about it.

Do not assume that the semantics of elements that Amazon Redshift and
PostgreSQL have in common are identical.

5.3 Aurora DSQL Lists Only One Value as a Supported Clause

The list of supported SQL in Aurora DSQL specifies only ISOLATION LEVEL REPEATABLE READ for the clauses associated with BEGIN and START TRANSACTION. No other values are listed.

⚠ AWS documentation does not describe what happens when other values are specified. This article does not definitively state the behavior. What is certain is that a design resting on READ COMMITTED or SERIALIZABLE does not hold on Aurora DSQL.

5.4 What the Three Have in Common

What happens on the three engines differs: an ignored statement, a clause with no effect, and a value that is not in the supported list. Seen from the practice of migration, though, they share a shape. The isolation level specified in the code may not always reflect the actual isolation level being enforced by the database.

So what to check after a migration is not what the code says. It is what level the database is running at right now. How you ask differs by engine.

Engine	Method for Verifying Actual Isolation Level
Aurora MySQL / RDS for MySQL	`transaction_isolation` system variable
Aurora PostgreSQL / RDS for PostgreSQL	Session: `SELECT CURRENT_SETTING('TRANSACTION_ISOLATION')`. Instance default: `SHOW DEFAULT_TRANSACTION_ISOLATION`
Amazon Redshift	`STV_DB_ISOLATION_LEVEL` catalog view
Aurora DSQL	Only one level exists, so there is nothing to query

⚠ MySQL's SET TRANSACTION command also has another potential pitfall. If written without specifying GLOBAL or SESSION, the setting only applies to the subsequent transaction, according to the official documentation.

The statement applies only to the next single transaction performed
within the session. Subsequent transactions revert to using the session
value of the named characteristics.

In environments utilizing connection pools, a common implementation runs the statement exactly once, right after checking out a connection. In that shape, the second and every later transaction falls back to the session value.

6. `SELECT ... FOR UPDATE` Means Four Different Things

A traditional approach to handling write skew involves reserving rows while reading. This is achieved using the SELECT ... FOR UPDATE syntax in SQL. ⚠ The syntax survives the migration. What does not survive is a single meaning: it splits four ways. And within PostgreSQL it splits again, into two, depending on the level you chose.

6.1 InnoDB Locks the Row and the Index Range

According to the official MySQL documentation, when a read operation acquires a lock under the REPEATABLE READ isolation level, if the search condition uses a unique index, only the index records found are locked. For other search conditions, InnoDB locks the range of the index being scanned using gap locks or next-key locks.

So it waits. Other transactions wait until the lock is released.

The documentation also advises against mixing read operations that acquire locks with SELECT statements that do not acquire locks within a single REPEATABLE READ transaction. It suggests that if you find yourself wanting to do this, you likely need the SERIALIZABLE isolation level instead.

6.2 In PostgreSQL the Level Decides Whether You Wait or Fail

In PostgreSQL, the same query can produce different results depending on the isolation level.

In READ COMMITTED, the system waits for preceding transactions to complete before re-evaluating the search conditions.

The search condition of the command (the WHERE clause) is re-evaluated
to see if the updated version of the row still matches the search
condition. If so, the second updater proceeds with its operation using
the updated version of the row. In the case of SELECT FOR UPDATE and
SELECT FOR SHARE, this means it is the updated version of the row that
is locked and returned to the client.

In REPEATABLE READ, if the wait results in updated data, the transaction attempting to read the data will fail.

But if the first updater commits (and actually updated or deleted the
row, not just locked it) then the repeatable read transaction will be
rolled back with the message

ERROR:  could not serialize access due to concurrent update

because a repeatable read transaction cannot modify or lock rows changed
by other transactions after the repeatable read transaction began.

The same line of code might return a new version of the data in READ COMMITTED, but result in an error in REPEATABLE READ. As the default isolation level changes during migration, this difference becomes a difference in the application's branching.

6.3 Aurora DSQL Takes No Lock and Decides at Commit Time

Aurora DSQL does not acquire locks with SELECT ... FOR UPDATE. The concurrency control page says so.

Because of the Aurora DSQL concurrency control mechanism, the
SELECT ... FOR UPDATE and SELECT ... FOR KEY SHARE clauses produce
results through optimistic conflict detection at commit time rather than
locking.

The meaning is that it declares an intention to write to a particular row. If another transaction commits changes to that same row before the declaring transaction commits, the declaring transaction will fail at commit time.

The same section presents a matrix illustrating which combinations of operations result in conflicts. Within this matrix, the term key column is defined. A key column is a column that belongs to a unique index that is neither partial nor expression-based. Every other column is a non-key column.

One Operation	`INSERT` / `DELETE` / `UPDATE` (of key columns) / `SELECT ... FOR UPDATE`	`UPDATE` (of non-key columns only)	`SELECT ... FOR KEY SHARE`
`INSERT` / `DELETE` / `UPDATE` (of key columns) / `SELECT ... FOR UPDATE`	Conflict	Conflict	Conflict
`UPDATE` (of non-key columns only)	Conflict	Conflict	No Conflict
`SELECT ... FOR KEY SHARE`	Conflict	No Conflict	No Conflict

SELECT ... FOR KEY SHARE only declares a dependency on the key columns of a row. It does not conflict with other transactions that update only non-key columns. This provides a fine-grained option for applications that maintain referential integrity.

⚠ Aurora DSQL does not support the NO KEY UPDATE and FOR SHARE clauses. However, DML operations internally utilize a mechanism similar to NO KEY UPDATE.

6.4 DynamoDB Has No Equivalent Syntax

DynamoDB does not have syntax for reserving rows. If you want to protect read items, you'll need to add a conditional expression on the write operation to verify that the item's state has not changed since it was read. The ConditionCheck action within TransactWriteItems provides a mechanism for this. If the condition is not met, the entire transaction will be rolled back.

⚠ However, this only protects items that match the condition specified in the expression. Unlike SQL's SELECT ... FOR UPDATE, which protects the entire range of items scanned, the condition only protects the specifically designated items. As part of the migration, the responsibility for determining which items to protect shifts from the database to the application.

7. What Optimistic Concurrency Control in Aurora DSQL Requires of the Application

7.1 `40001` and the Two OCC Codes

Aurora DSQL does not use locks and evaluates conflicts at commit time. The user guide calls this commit-time evaluation adjudication. On a conflict it returns SQLSTATE 40001, the PostgreSQL serialization failure. PostgreSQL's error code list gives 40001 the condition name serialization_failure and files it under Class 40, transaction rollback. Because Aurora DSQL returns the same value as PostgreSQL, a PostgreSQL driver's serialization-failure exception type works unchanged. ⚠ However, Amazon Redshift handles serialization violations differently, returning error 1023 (see §4.5). Do not assume that serialization failures always result in 40001.

In addition to 40001, Aurora DSQL returns two codes that indicate the type of conflict.

Code	Meaning	Error Message
`OC000`	Data conflict. Two transactions attempted to modify the same row. The transaction with the earlier commit time succeeds.	`ERROR: change conflicts with another transaction (OC000) (SQLSTATE 40001)`
`OC001`	Schema conflict. The session's cached schema catalog is out of date.	`ERROR: schema has been updated by another transaction (OC001) (SQLSTATE 40001)`

OC001 can occur not only with DDL operations like CREATE TABLE and ALTER TABLE, but also with GRANT and REVOKE. ⚠ In environments where permissions are frequently changed, you may encounter OC001 even if you don't believe the schema has been modified.

7.2 Retrying Is Not Exception Handling

The user guide explicitly states that retries should be incorporated into the design.

Design your applications to implement retry logic to handle these
responses. The ideal design pattern is idempotent, enabling transaction
retry as a first recourse whenever possible. The recommended logic is
similar to the abort and retry logic in a standard PostgreSQL lock
timeout or deadlock situation. However, OCC requires your applications
to exercise this logic more frequently.

The key point is this: You will be performing the same retry process, but at a higher frequency. This is because what previously waited due to locks will now fail. Conflicts that previously manifested as wait times during the transition will now appear as failure rates after the transition.

To ensure retries are safe, transactions must be idempotent. This design consideration is outside the scope of this article. Event-Driven Serverless Architecture on AWS - Building Resilient Workflows with API Gateway, Lambda, EventBridge, Step Functions, and DynamoDB addresses this. Amazon Aurora DSQL Design Decision Guide - Distributed SQL Between Amazon DynamoDB and Aurora PostgreSQL covers whether to adopt Aurora DSQL at all.

7.3 The Transaction Limits Shape the Design

Aurora DSQL has several limits that come into play during migration. ⚠ The values move. Look them up again before you start.

Item	Limit (as of August 27, 2026)	Error Message
Number of rows that can be modified in a single transaction	3,000 rows	`ERROR: transaction row limit exceeded`
Total size of data modified in a single write transaction	10 MiB	`ERROR: transaction size limit 10mb exceeded`
Maximum transaction duration	5 minutes	`ERROR: transaction age limit of 300s exceeded`
Maximum connection duration	60 minutes	Not documented

The limits on row counts and duration are related to handling write skew. Widening the range that SELECT ... FOR UPDATE protects puts more rows into adjudication and raises the chance of a conflict. Narrowing it leaves write skew in place. Using batch processing to wrap long transactions is not a viable workaround, as it is restricted by the 5-minute limit.

7.4 Reducing Contention Is a Design Decision

The user guide suggests minimizing contention, specifically by reducing conflicts associated with single keys or narrow key ranges. This includes selecting random values for table primary keys and avoiding patterns that increase contention on a single key.

In the context of migration, sequential primary keys and rows that experience concentrated updates can become sources of conflict if migrated directly. What previously resulted in delays due to row locking may now result in failures when using optimistic concurrency control.

8. In DynamoDB the Pair of Operations Decides the Isolation

8.1 The Nine Rows AWS Publishes

The DynamoDB developer guide gives the isolation level between a transactional operation, meaning TransactWriteItems or TransactGetItems, and each other operation. It gives it as a table.

Operation	Isolation Level
`DeleteItem`	`Serializable`
`PutItem`	`Serializable`
`UpdateItem`	`Serializable`
`GetItem`	`Serializable`
`BatchGetItem`	`Read-committed` (*)
`BatchWriteItem`	`NOT Serializable` (*)
`Query`	`Read-committed`
`Scan`	`Read-committed`
Other transactional operation	`Serializable`

8.2 What the Asterisk Means

The guide adds a note about the two rows carrying an asterisk.

Levels marked with an asterisk (*) apply to the operation as a unit.
However, individual actions within those operations have a serializable
isolation level.

⚠ How you take the unit changes the answer. Individual write operations included within BatchWriteItem are SERIALIZABLE with respect to a transactional operation. However, when viewed as a single unit, BatchWriteItem is not SERIALIZABLE. The guide reiterates this point elsewhere in the document.

Although there is serializable isolation between transactional
operations, and each individual standard write in a BatchWriteItem
operation, there is no serializable isolation between the transaction
and the BatchWriteItem operation as a unit.

The same structure applies to the read side. A single GetItem operation is SERIALIZABLE with respect to TransactWriteItems, but the results of running multiple GetItem operations concurrently are read-committed.

A single GetItem request is serializable with respect to a
TransactWriteItems request in one of two ways, either before or after
the TransactWriteItems request. Multiple GetItem requests, against keys
in a concurrent TransactWriteItems requests can be run in any order, and
therefore the results are read-committed.

⇒ If you require a consistent read across multiple items, use TransactGetItems instead of running multiple GetItem operations in parallel. The guide recommends this approach.

8.3 The Transaction Constraints

Here are the constraints that apply to migration design:

Item	Description
Number of write actions for `TransactWriteItems`	Maximum 100
Number of `Get` actions for `TransactGetItems`	Maximum 100
Items you can target	Up to 100 distinct items, in one or more tables within the same AWS account and the same Region
Total size of items within a transaction	Must not exceed 4 MB.
Duplication of items within a single transaction	The same item cannot be the target of multiple operations within a single transaction.
Indexes	Transactions cannot be executed against indexes.

⚠ Mechanisms for idempotency are also provided. TransactWriteItems supports client tokens. These tokens are valid for 10 minutes after a request completes. Resending a request with the same token will return a success response without making any changes. If you resend a request with the same token within that 10-minute window but with different parameters, an IdempotentParameterMismatch error will be returned.

⚠ After a transaction completes, propagation to global secondary indexes, streams, and backups occurs gradually. This guide explicitly states that stream consumers should not assume the atomicity or order of transactions.

9. Where the Primary Sources Are Silent and Where They Disagree

An investigation into isolation levels runs into both places where primary sources fail to line up and places where the primary sources provide no information whatsoever. Filling in these gaps with speculation can lead to readers designing solutions based on incorrect assumptions, ultimately leading to failure. Four such places are recorded here as they stand.

9.1 The Note on Redshift's `BEGIN` Does Not Line Up With the Default

§5.2 states that the BEGIN isolation level maps to SERIALIZABLE when the standard three levels are used. However, the page on isolation levels referenced in §4.5 indicates that the default isolation level for newly created provisioned clusters and serverless workgroups is SNAPSHOT.

Reading these two sections directly suggests that the default is SNAPSHOT, but specifying the isolation level in a BEGIN statement maps to SERIALIZABLE. ⚠ The core issue is that the isolation level clause in the BEGIN statement has no operational effect. The statement mapping to SERIALIZABLE likely reflects a previous state before SNAPSHOT was introduced. ⛔ However, this article does not definitively state that. No documentation from AWS explicitly states this.

⇒ The practical conclusion remains unchanged. To confirm the actual isolation level in Redshift, check the STV_DB_ISOLATION_LEVEL view. Do not rely on inferences based on the wording of the documentation.

9.2 AWS Says Nothing About Aurora PostgreSQL Readers

The official PostgreSQL documentation notes a limitation: SERIALIZABLE isolation is not extended to read-only standby instances.

This level of integrity protection using Serializable transactions does
not yet extend to hot standby mode (Section 26.4) or logical replicas.
Because of that, those using hot standby or logical replication may want
to use Repeatable Read and explicit locking on the primary.

While AWS clearly outlines limitations regarding the reader instance for Aurora MySQL (see §5.1), AWS does not provide similar information for Aurora PostgreSQL. Furthermore, Aurora reader instances sit on shared storage, which differs from the structure of a standard PostgreSQL hot standby.

⛔ Therefore, this article does not state that the limitations described earlier necessarily apply to Aurora PostgreSQL's reader instance. While RDS for PostgreSQL read replicas utilize physical replication and have a structure closer to the original, this article does not assert it either, because AWS does not state it.

⇒ If your design relies on directing reads to the reader instance and assumes SERIALIZABLE isolation, it is essential to verify this within your own cluster before assuming it will work. The sole recommendation of this section is to avoid making assumptions without first verifying.

9.3 `phantom read` on an Aurora MySQL Reader Has a Different Definition

The phenomenon described as phantom read in the Amazon Aurora User Guide, as mentioned in §5.1, differs from the definition established in §2.1.

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.

⚠ A phantom in the usual sense is the appearance of new rows that match the search criteria. However, what is described here is the situation where existing rows are reorganized, resulting in them being read twice. The guide attributes this phenomenon to internal row reconfigurations caused by changes in the length of variable-length columns.

⇒ Even though the term is the same, the meaning is different. When incorporating terminology from primary source materials into internal documentation, it's crucial to include the definition used in those materials as well.

9.4 Whether Aurora DSQL Prevents `phantom` Depends on Which Source You Read

Aurora DSQL's documentation on concurrency control states that it uses snapshot isolation, but does not list the specific anomalies it prevents. In contrast, the AWS Database Blog describes snapshot isolation in general terms, noting that this applies to snapshot isolation itself, not specifically to Aurora DSQL.

Snapshot isolation - Allows transactions to view a consistent snapshot
of the database as it was at the beginning of the transaction,
preventing dirty reads, non-repeatable reads, and phantom reads without
the overhead of full serializability.

Meanwhile, Table 4 in the original paper marks phantom (P3) for Snapshot as Sometimes Possible. ⚠ The blog does not define what it means by phantom read. The paper's P3 refers to a broader, predicate-based definition, which is wider in scope than the definition of Phantom Read found in PostgreSQL's official documentation (§3.2). Because different sources use the same terminology with varying degrees of scope, simply listing them together can appear contradictory.

⛔ Therefore, this article states neither that Aurora DSQL prevents phantom nor that it does not. The table in §4.6 marks that cell Not documented.

⇒ In practical terms, rather than asking whether Aurora DSQL prevents phantom, it is more useful to ask whether a condition confirmed by a SELECT still holds at commit time. Aurora DSQL's behavior is that it does not guarantee this, as it does not inspect the rows read. If you need this guarantee, you should declare the SELECT statement using SELECT ... FOR UPDATE (§6.3).

9.5 The General Rule These Four Yield

What the four have in common is that there is no single canonical source for statements about isolation levels. While AWS documentation provides the names and default values for these levels, the documentation for upstream systems like PostgreSQL and MySQL is the authoritative source for understanding what those levels protect against. In cases of anomalies not covered in either upstream or standard documentation, it may be necessary to refer back to the original research paper.

So separate a claim by the layer it belongs to before you check it. When AWS states that a system is PostgreSQL-compatible, that means you should consult the upstream documentation; it doesn't mean that the upstream documentation's descriptions automatically apply.

10. What to Inventory Before You Move

10.1 Count It on the Application Side

Before migration, extract all instances from the code. Instead of verifying that the count is zero, decide, one by one, how each behaves on the destination.

Locations where isolation levels are set. This includes SET TRANSACTION ISOLATION LEVEL, SET SESSION CHARACTERISTICS, and any settings being applied during connection establishment by connection pools or frameworks.
SELECT ... FOR UPDATE, SELECT ... FOR SHARE, and SELECT ... FOR KEY SHARE.
Updates that read a value, compute in the application, and write it back.
Places where a constraint that does not close within one row is confirmed with a SELECT before the write. Totals, counts, ceilings, exclusivity conditions. These are the write skew candidates
Locations where serialization failures are caught. If failures are not caught, determine where to place a retry layer.
Areas where reads are routed to a reader. If the unit of routing is finer than a transaction, the level can change inside one logical operation.

10.2 Confirm It on the Database Side

Query the level actually in effect on both the source and the destination. Consult the database itself, rather than relying on configuration files (see the table in §5.4).
If the level names on the destination database are the same as those on the source, review the destination's official documentation to confirm what each level protects against. Do not assume that levels with the same name provide the same protection.
If the destination does not prevent write skew, determine whether the mitigation strategy involves configuration settings or explicit declarations.
If the destination database has limits on transaction count, size, and duration, measure whether existing, largest transactions fall within those limits.

10.3 What to Add to Acceptance Testing

A test that counts rows will not surface a difference in isolation level. Tests that include concurrency have to be added.

For the write skew candidates identified in §10.1, write tests that run concurrently from two sessions. One session finishes reading, the other then reads, and each writes a different row, in that order.
Verify that the application correctly retries and ultimately succeeds when a serialization failure occurs.
Measure the number of retries and the failure rate. The failure rate is proportional to the level of concurrency, so it does not appear until you apply a production-equivalent load. Load Testing on AWS covers how to apply that load.
If reads are being routed to a reader, create separate tests that specifically utilize the reader.

⚠ Before migrating, document the isolation level that the application is currently relying on. If this assumption is not documented, no one will notice if the application's behavior changes after the migration.

11. Failures That Show Up After the Move

Failure	What Happens	What to Do
Names are the same, so nothing was changed.	When migrating from MySQL to PostgreSQL, the default isolation level changes from `REPEATABLE READ` to `READ COMMITTED`. Going the other way, it rises. Neither direction produces a syntax error.	Measure and compare the default isolation levels of both the source and destination databases. Compare the default values, not just the names.
Results changed when routing to the reader.	Aurora MySQL's reader ignores `SET TRANSACTION ISOLATION LEVEL`. `READ COMMITTED` when `aurora_read_replica_read_committed` is enabled is less strict than that of the writer.	Verify the unit of routing. For operations requiring high precision, route them to the writer.
`SET TRANSACTION ISOLATION LEVEL` is not taking effect.	It has no operational effect in Redshift. If written without specifying a scope in MySQL, it only applies to the next transaction.	Query the database to confirm the current isolation level.
Constraints on totals or counts are violated.	This is `write skew`. No AWS engine has official documentation saying that its default setting prevents it. It typically doesn't occur until concurrency increases.	Use `SERIALIZABLE` or declare rows read with `SELECT ... FOR UPDATE`. The former will increase the number of failed transactions on any engine. The latter will either wait or fail, depending on the engine (see §6).
`SELECT ... FOR UPDATE` is no longer waiting.	In Aurora DSQL, it doesn't acquire locks and returns a failure at commit. Logic that used it as a queue no longer holds.	Shift from a waiting design to one that handles failures and retries.
Serialization failures are presented to the user as errors.	There is no retry layer. Under optimistic concurrency control a conflict is a normal event.	Place retries in a shared data access layer, rather than scattering them across calling components.
`OC001` is occurring, but the schema hasn't been changed.	Changes to `GRANT` and `REVOKE` also modify the schema catalog.	Do not include permission changes in regular operational processes. If you do, make them subject to retries.
Designed with the assumption that DynamoDB is safe because it's `SERIALIZABLE`.	When considering `BatchWriteItem` as a single unit, it is not `SERIALIZABLE`. `Query`, `Scan`, and `BatchGetItem` are `read-committed`.	Look the pair of operations up in the table. For a consistent read across multiple items, use `TransactGetItems`.
Analytical processing migrated to Redshift is not reducing storage.	It's not the isolation level, but rather long-running transactions that remain open that are preventing `VACUUM` from reclaiming space.	Use `SVV_TRANSACTIONS` to check for open transactions. Avoid leaving analytical connections open.
Passed validation tests, so assumed it was safe.	Validation tests compare static data. They do not cover behavior under concurrent conditions.	Create separate acceptance tests that specifically include concurrent scenarios.

12. Frequently Asked Questions

Does `REPEATABLE READ` prevent `phantom`?

It depends on the database engine. PostgreSQL's REPEATABLE READ prevents phantom. The official documentation says outright that it gives a stronger guarantee than the standard requires. In MySQL's REPEATABLE READ, a locking read takes gap locks that block insertions into the range, while a non-locking SELECT reads the snapshot. Downgrading to READ COMMITTED disables gap locks, and the official documentation notes that phantom row problems may occur. Regarding Aurora DSQL's REPEATABLE READ, AWS documentation doesn't mention phantom reads at all (§9.4). ⚠ Check it against the engine's own documentation, not against the name.

Is `write skew` an anomaly defined by ANSI SQL?

No. ANSI SQL-92 names three phenomena: dirty read, non-repeatable read, and phantom. write skew was defined as A5B in a paper published at ACM SIGMOD in 1995, titled "A Critique of ANSI SQL Isolation Levels." Because it is not part of the standard terminology, it is easily overlooked in pre-migration checklists.

Is snapshot isolation stronger than `REPEATABLE READ`?

Neither is stronger. The paper states in Remark 9 that the two isolation levels cannot be directly compared, as the sets of anomalies they prevent are different. Snapshot isolation allows write skew and REPEATABLE READ does not. The other way round, REPEATABLE READ is the weaker of the two against the anomaly where the same predicate returns a different set of rows on a second read. ⚠ That is the theoretical level, though. What a given engine does under the same name still has to be checked separately.

Is there an AWS engine that prevents `write skew` at its default setting?

Not within the range this article examined. No engine has official documentation saying that its default setting prevents it. The settings the documentation does name as preventing write skew are SERIALIZABLE for Aurora PostgreSQL and SERIALIZABLE for Amazon Redshift, but neither of these are enabled by default. In Aurora DSQL, the remedy is not a setting but an explicit declaration with SELECT ... FOR UPDATE.

Can the isolation level be changed in Aurora DSQL?

No. The user guide states that The transaction isolation level is fixed at PostgreSQL Repeatable Read. Furthermore, the list of supported SQL commands shows that BEGIN and START TRANSACTION only accept the ISOLATION LEVEL REPEATABLE READ clause. ⚠ There is no documentation from AWS describing the behavior when specifying other values.

Is DynamoDB `SERIALIZABLE`?

There is no one-line answer. It depends on the combination of operations. Between a transactional operation and PutItem, UpdateItem, DeleteItem, or GetItem, the isolation is SERIALIZABLE. However, seen as a single unit, BatchWriteItem is not SERIALIZABLE. Similarly, when treating BatchGetItem as a single unit, it operates under read-committed consistency. Both Query and Scan also operate under read-committed consistency.

Is the Redshift default `SERIALIZABLE`?

No. For a newly created provisioned cluster or serverless workgroup, the default is SNAPSHOT. While SNAPSHOT protects against update and delete conflicts, the database developer guide says it is SERIALIZABLE that prevents write-skew anomalies. The current setting can be checked using STV_DB_ISOLATION_LEVEL.

If every migration validation task passes, has the isolation level been confirmed?

No. The validation tasks compare static data and do not observe the behavior of concurrently running transactions. That is where the pattern comes from: the row counts match, the validation passes, and the break shows up first in production. Separate acceptance testing, specifically including concurrency testing, is necessary. Heterogeneous Database Migration on AWS - Schema Conversion, Full Load with CDC, Data Validation, and a Cutover You Can Reverse covers the migration process itself.

Is `SELECT ... FOR UPDATE` equally safe on every engine?

No. InnoDB locks the scanned index range, and other transactions wait. In PostgreSQL's READ COMMITTED it waits and then re-evaluates the search condition. In REPEATABLE READ it fails with could not serialize access due to concurrent update. Aurora DSQL takes no lock and returns the conflict at commit time. DynamoDB does not support this syntax. ⚠ The syntax ports. The behavior does not.

Do isolation level differences relate to the replication quorum?

No, they are distinct concepts. Replication quorum refers to how many copies of a write need to be acknowledged for it to be considered finalized, from the storage system's perspective. Isolation, on the other hand, concerns how multiple concurrent transactions appear to each other. Summary of Differences and Commonalities in AWS Database Services using the Quorum Model - Comparison Charts of Amazon Aurora, Amazon DocumentDB, and Amazon Neptune compares the quorum models.

13. Summary

What breaks quietly after a migration is not the data. It is the agreement between transactions that run at the same time. It does not show up in a validation that counts rows. It shows up only when there is concurrency.

This article has confirmed three key points. First, an isolation level with the same name guards a different range on different engines. PostgreSQL's REPEATABLE READ prevents phantom, MySQL's REPEATABLE READ behaves differently depending on how the read takes locks, and Aurora DSQL's REPEATABLE READ is simply another name for snapshot isolation.

Second, a statement that selects a level does not always take effect. An Aurora MySQL reader ignores the statement, the clause has no operational impact in Redshift, and Aurora DSQL lists only one value as a supported clause. No error is raised in any of the three. Trust the level reported by the database itself, rather than the level specified in your code.

Third, no AWS engine has official documentation saying that its default setting prevents write skew. This anomaly isn't part of standard terminology, so it drops off the pre-migration checklist. Migrate with it still off the list, and the logic whose constraint does not close within a single row breaks the first time the load rises.

It comes down to two things. Query the level actually in effect on both the source and the destination, and record it. And find every place that confirms a constraint spanning more than one row with a SELECT and then writes. With those two done, the rest is choosing among options you already know.

14. References

References:
Tech Blog with curated related content

Written by Hidekazu Konishi