AWS re:Invent 2025 - A tale of two transactions (DAT455)

Transactions and Isolation Levels: Balancing Correctness and Performance

Understanding Isolation Levels

• Isolation levels are a tradeoff between complexity for application developers and performance/scalability for the database.
• Stronger isolation (e.g. serializability) makes it easier for applications to be correct, but can hurt performance.
• Weaker isolation (e.g. read uncommitted) provides better performance, but requires more complex application logic to handle concurrency anomalies.

Implementing Isolation with Locking

• Two-phase locking (2PL) is a classic algorithm for implementing serializable isolation.
  • Transactions acquire locks on rows as they read/write, and hold those locks until commit or rollback.
  • This can lead to deadlocks that require aborting one of the conflicting transactions.
• Optimistic Concurrency Control (OCC) is an alternative approach.
  • Transactions don't acquire locks, but check for conflicts at commit time.
  • This can lead to "weird" behaviors where transactions see inconsistent data.

Snapshot Isolation

• Snapshot isolation (SI) is a stronger isolation level than OCC, but weaker than serializability.
• Under SI, transactions see a consistent snapshot of the database as of their start time.
  • Writes from concurrent transactions are rejected if they conflict.
  • Reads are repeatable and don't see uncommitted changes.
• SI coordination scales with writes, while serializability scales with both reads and writes.

Implementing Snapshot Isolation

• PostgreSQL's "repeatable read" isolation is actually SI, not true repeatable read.
• Aurora DSQL implements SI using a multi-versioned storage engine.
  • Each row has a linked list of versions, and transactions read the version corresponding to their start time.
  • This avoids the need for read locks and allows concurrent readers and writers.

Consistency vs. Availability

• Strong consistency (e.g. linearizability) is often preferable to eventual consistency for most applications.
  • Eventual consistency can lead to surprising and hard-to-reason-about application behavior.
• Database systems can be both highly available and strongly consistent, even in the face of network partitions.
  • The key tradeoff is increased latency, especially for writes.

Performance Tradeoffs

• Strongly consistent systems incur additional latency, especially for cross-region operations.
  • Aurora DSQL can provide strong consistency with local reads/writes and 1-2 cross-region hops at commit.
  • DynamoDB global tables trade off strong consistency for lower latency.

Key Takeaways

• Isolation levels involve complex tradeoffs between correctness and performance.
• Snapshot isolation can provide a good balance, scaling better than serializability.
• Strong consistency is often preferable to eventual consistency, but comes at a latency cost.
• Understanding these tradeoffs is crucial for building high-performance, reliable distributed applications.