Basic snapshot isolation
Each transaction reads from a consistent snapshot of the database taken at the moment the transaction begins. SI prevents many issues like dirty reads or lost updates by ensuring each transaction’s snapshot is isolated.
However, SI’s commit rule only checks for write-write conflicts – it allows concurrent transactions to proceed as long as they aren’t trying to modify the exact same data. This means read-write conflicts (where one transaction’s decision is based on data another transaction is concurrently altering) can slip through, leading to anomalies such as write skew (What Fekete’s Anomaly Can Teach Us About Isolation - Marc’s Blog).
A typical example is the “no doctor on call(write skew)” scenario, where two concurrent transactions, one per doctor, decide to take the night off:
- The first transaction (doctor Alice) checks that Doctor Bob is on call and takes the night off
- The second transaction (doctor Bob) checks that Doctor Alice is on all and takes the night off
Each transaction sees a snapshot with “the other doctor on call,” so both think it’s safe to go off duty (Learning About Transaction Isolation Levels in Databases).
Direct write conflicts
They each update different rows (Alice updates her status, Bob updates his), so from SI’s perspective there is no direct write conflict – the writes are to separate records. Outcome: Both transactions commit, leaving zero doctors on call for the night, violating the hospital’s rule (an inconsistent state). Only a concurrent write of the same record cause a transaction to abort in snapshot isolation
Optimistic locking
SI is a form of optimistic locking, since transactions are not blocked and allowed to proceeed, but they could be aborted at commit time
Strong Snapshot Isolation (SSI)
Strong Snapshot Isolation, more commonly known as Serializable Snapshot Isolation (SSI), extends SI by detecting and preventing those dangerous concurrent patterns that SI would allow.
In SSI, the database identifies “conflicting transactions” as those that have overlapping data accesses in a way that could lead to a serialization anomaly. Precisely, two concurrent transactions conflict if at least one of them writes to a data item that the other reads or writes. In other words, a read-write overlap on the same data is treated as a conflict (not just a write-write overlap).
SSI versus Strong SSI
Under SSI (serializable isolation), Dr. Alice’s and Dr. Bob’s transactions would no longer both be allowed to commit: each transaction read the other doctor’s on-call status and then wrote an update to its own row. SSI detects this read-write conflict pattern: recognizing that if both committed the result wouldn’t be serializable, SSI intervenes.
SSI + Global Ordering (Aurora DSQL) – Global Consistency with Snapshot Isolation
Aurora (Distributed SQL edition) implements strong snapshot isolation with a global commit ordering across its multi-region clusters. In practice, this means Aurora DSQL uses the SSI model (no blocking reads, optimistic commits) but augments it with tightly synchronized clocks and a global coordination at commit time to order transactions across regions (What is DSQL?).
Every transaction’s updates are replicated and checked against others’ in a unified commit sequence. The global ordering ensures that all nodes (regions) apply commits in the same sequence, giving a consistent timeline of the database state.
Important
This improvement addresses anomalies that arise in distributed environments due to unsynchronized snapshots – for example, it prevents a transaction in one region from missing an update that committed slightly earlier in another region. In effect, Aurora DSQL aims to make snapshot isolation behave consistently across regions by making each transaction see the most recent global snapshot and by resolving commit conflicts through a global first-commit-win rule. (This is similar in spirit to Google Spanner’s TrueTime approach, which provides a linearizable global order of transactions (What is DSQL?), though Aurora stops short of full serializability.)
Global read consistentency
In a multi-region user profile system, Alice could update her profile in the U.S. (Transaction A commits an update to record X), and moments later Bob, in Europe, attempts to read Alice’s profile (Transaction B). In a generic distributed SI setup without global ordering, it’s possible Bob’s transaction takes a snapshot that does not include Alice’s recent update (if the update hasn’t been seen or applied in Europe yet).
How global ordering help
Aurora DSQL’s global ordering prevents this: if one transaction commits before another begins (anywhere in the cluster), the later transaction’s snapshot reflects the earlier one’s effects. This eliminates anomalies where a transaction in one region might otherwise observe a “partial order” of commits (seeing some updates but missing others that logically occurred earlier in real time).
SSI + Ordering vs Serializable
Aurora DSQL does not perform full serializable conflict checking for all read-write dependencies – it primarily coordinates concurrent writes. This means that classic SI anomalies like write skew can still occur within the globally ordered schedule. The global sequence prevents blatant timing inconsistencies, but it doesn’t magically enforce all transactions to be serializable. Global ordering fixes “when” transactions see each other’s effects, but not which concurrent behaviors are allowed*.
Full Serializability – The Strongest Isolation
Serializability is the strictest isolation level, guaranteeing that the outcome of all transactions is equivalent to some serial (one-at-a-time) execution. Any schedule that cannot be rearranged into a serial order is disallowed and achieving this typically requires detecting any read-write conflict cycle and breaking it (by aborting a transaction or by stricter locking).
In practice, databases offering full serializable isolation (like PostgreSQL’s Serializable mode or CockroachDB’s default) will sacrifice some concurrency: if a set of concurrent operations would result in an inconsistency, one or more transactions are rolled back to ensure consistency. The benefit is that all integrity constraints and invariants can rely on the database alone; the database won’t let concurrent activity violate them.
Example – An Anomaly Aurora Allows vs. Serializability Prevents: Recall the earlier scenarios (doctors on call or distributed inventory). Under Amazon Aurora’s snapshot isolation, those write skew anomalies are possible: Aurora would happily commit both transactions in the “no doctor on call” scenario, or both warehouse shipments, because it doesn’t see a direct conflict on the same row or key (What Fekete’s Anomaly Can Teach Us About Isolation - Marc’s Blog). A fully serializable system would not allow that outcome. For instance, in the hospital case, one doctor’s transaction would be aborted as soon as the other doctor’s off-duty update was detected, preserving the rule that someone stays on call. In the inventory case, a serializable database would recognize the dangerous pattern (each transaction read stale stock info) and stop one shipment from committing. To illustrate, consider a banking scenario: A customer has a savings and a checking account with a rule that the combined balance must never drop below $100. Account A has $150, Account B has $150 to start (total $300). Two concurrent transactions execute: Transaction X debits $250 from Account A (planning to leave $50 total, checking that Account B had $150); Transaction Y simultaneously debits $250 from Account B (checking that Account A had $150). Under Aurora’s SI, each transaction reads the other account’s balance (both see $150) and withdraws from the other account. They update different accounts, so Aurora’s OCC check sees no direct conflict – both commits succeed. The end state is Account A = $-100, Account B = $-100 (total $-200), clearly violating the $100 minimum balance rule. Serializability would prevent this – when the second transaction tries to commit, it would be aborted because the account it read ($150 in the other account) was modified by the first transaction (What Fekete’s Anomaly Can Teach Us About Isolation - Marc’s Blog). In a serializable schedule, these two huge withdrawals could not both happen; effectively, one transaction’s withdrawal would occur “first,” reducing the total balance seen by the other and causing it to fail the minimum balance check. This example highlights that serializable isolation is strictly stronger: it would catch the cross-account dependency and disallow the non-serial outcome that Aurora’s “strong snapshot” mode still lets through.
In summary, full serializability eliminates all phenomena that weaker levels (even globally ordered SI) might exhibit. If Amazon Aurora DSQL (SSI + global ordering) is not fully serializable, it will allow certain anomalies (like the write skew above) that a true serializable database forbids (What is DSQL?). Serializability is the ironclad guarantee that no matter how transactions interleave, the result will always be as if they ran one by one – ensuring the highest level of correctness for concurrent transactions, at the cost of more aborts and stricter scheduling. (Learning About Transaction Isolation Levels in Databases) (What is DSQL?)
Snapshot Isolation (SI) is a concurrency control method that ensures each transaction operates on a consistent snapshot of the database, avoiding dirty reads, non-repeatable reads, and phantom reads. However, it does not guarantee a total order of transactions and allows anomalies such as write skew.
SI operates using Multi-Version Concurrency Control (MVCC), where each transaction sees a past consistent version of the database at its start time. This allows high concurrency but introduces subtle anomalies when multiple transactions make decisions based on stale data.
Write Skew Example in Traditional SI
Consider a hotel booking system where only one booking is allowed per room:
-- Transaction T1 reads room availability
SELECT COUNT(*) FROM bookings WHERE room_id = 1;
-- Returns 0 (room available)-- Transaction T2 also reads room availability
SELECT COUNT(*) FROM bookings WHERE room_id = 1;
-- Returns 0 (room available)Both transactions see the same snapshot and assume the room is free.
-- T1 books the room
INSERT INTO bookings VALUES (1, 'Alice');
COMMIT;-- T2 books the room
INSERT INTO bookings VALUES (1, 'Bob');
COMMIT;After both transactions commit, the system now has two bookings for the same room, violating the constraint. SI allows this anomaly because each transaction reads an outdated snapshot without detecting concurrent conflicting writes.
Strong Snapshot Isolation (SSI)
Strong Snapshot Isolation (SSI) improves SI by detecting and aborting transactions that would cause write skew. It tracks read-write conflicts and enforces a commit-time validation step, preventing transactions from making decisions based on outdated snapshots.
If the sequence of operations described in the previous example where executed under SSI, a detection system would abort one of the two transactions, realizing that if the other one has committed, it means that the read data is stale:
- T1 starts, reads snapshot (sees 0 bookings).
- T2 starts, reads snapshot (sees 0 bookings).
- T1 attempts to commit its insert (
INSERT INTO bookings VALUES (1, 'Alice')). - T2 attempts to commit its insert (
INSERT INTO bookings VALUES (1, 'Bob')). - SSI detects that T2 read stale data and aborts it.
Read skews in SSI
A transaction can still read an old snapshot even if another transcation has committed,
What Can Still Happen with SSI (Without Global Ordering)
- Read skew anomalies remain: A transaction can still read an old snapshot even if another transaction has committed.
- No real-time order: If T1 and T2 are concurrent, their order of execution may not reflect real-world time order.
For example:
- T1 inserts a booking and commits.
- T2 starts after T1 commits but still reads an old snapshot where the booking is missing.
- T2 makes decisions based on outdated data.
This is allowed under SSI because it only prevents write skew, not stale reads.
SSI + Global Ordering
Aurora DSQL introduces an enhanced version of SSI, which includes global transaction ordering and linearizability. It achieves this by using:
- Optimistic Concurrency Control (OCC) to validate conflicts at commit time.
- A distributed adjudicator service that assigns a global commit order to transactions, ensuring real-time consistency.
Example: What Global Ordering Prevents Beyond Traditional SSI
- T1 commits first.
- T2 starts after T1 commits but sees the updated state (not a stale snapshot).
- T2 makes decisions based on the latest committed data.
In contrast, traditional SSI would have allowed T2 to still read the old snapshot before T1’s changes were visible.
Tradi
The key difference is that with Global Ordering, we can make T2 wait until the results of T1 are visible globally. There is a global commit order which allows this to happen
What Aurora SSI + Global Ordering Still Allows (But Serializability Prevents)
A better example would focus on an anomaly that still occurs even though stale reads and write skew are prevented. Let’s consider a non-serializable interleaving of transactions that Aurora allows but Serializability prevents.
Example: Non-Serializable Interleaving in Aurora SSI
Consider a bank account transfer system:
- T1 transfers $500 from Alice to Bob.
- T2 transfers $300 from Bob to Charlie.
- T3 checks the total balance of Alice, Bob, and Charlie to verify system integrity.
Execution in Aurora SSI + Global Ordering
- T1 starts, subtracts 500 to Bob.
- T1 commits.
- T2 starts after T1 commits, so it sees Bob’s updated balance.
- T2 subtracts $300 from Bob and adds it to Charlie.
- T2 commits.
- T3 starts after T2 commits and sums up all account balances.
- T3 calculates the total balance as if T1 happened before T2—which is correct.
This seems correct at first glance, but T3’s execution could still reflect a non-serializable order in a distributed system if we have a race condition where another concurrent transaction sees partial updates from T1 and T2.
What Serializability Would Do Differently
In a fully serializable system, the database would enforce a strict global order of execution such that:
- T3 must be scheduled to observe the system in a state where either T1 or T2 has fully completed, not an interleaving.
- If T3 started at the wrong moment, the system would force it to wait or abort to ensure that the results are consistent with a fully sequential execution.
Key Fix
❌ Aggregation Anomaly was incorrect because Aurora DSQL prevents stale reads.
✅ Aurora SSI still allows anomalies when transactions interleave in ways that would not occur in a strictly serializable schedule.
✅ Serializability forces a strict, fully sequential order of execution—something Aurora does not guarantee.
Would you like a more in-depth example based on real-world Aurora DSQL documentation? I can do a deeper search for verified cases where Aurora’s Global Ordering does not provide full Serializability.