Topic
transactions
12 articles across 5 sub-topics
Sub-topic
5 articles
Read Skew Explained: Inconsistent Snapshots Across Multiple Objects
TLDR: Read skew occurs when a transaction reads two logically related objects at different points in time — one before and one after a concurrent transaction commits — producing a view that never existed as a committed whole. Read Committed isolation...
Phantom Read Explained: When New Rows Appear Mid-Transaction
TLDR: A phantom read occurs when a transaction runs the same range query twice and gets a different set of rows — because a concurrent transaction inserted or deleted matching rows and committed in between. Row locks cannot stop this because the phan...
Write Skew Explained: The Anomaly That Requires Serializable Isolation
TLDR: Write skew is the hardest concurrency anomaly to reason about: two concurrent transactions each read a shared condition, decide they can safely proceed, and then write to different rows. No individual operation is wrong. No row was overwritten....
Dirty Read Explained: How Uncommitted Data Corrupts Transactions
TLDR: A dirty read occurs when Transaction B reads data written by Transaction A before A has committed. If A rolls back, B has made decisions on data that — from the database's perspective — never existed. Read Committed isolation (the default in Po...
Non-Repeatable Read Explained: When the Same Query Returns Different Results
TLDR: A non-repeatable read happens when the same SELECT returns different results within a single transaction because a concurrent transaction committed an update between the two reads. Read Committed isolation — the default in PostgreSQL, MySQL, an...
Sub-topic
3 articles
ACID Properties Explained: How SQL Databases Guarantee Atomicity, Consistency, Isolation, and Durability
TLDR: ACID is four orthogonal guarantees that every SQL transaction must provide. Atomicity says all-or-nothing: PostgreSQL implements it via WAL rollback; MySQL InnoDB via undo logs. Consistency says constraints always hold: the database rejects any...
Isolation Levels in Databases: Read Committed, Repeatable Read, Snapshot, and Serializable Explained
TLDR: Isolation levels control which concurrency anomalies a transaction can see. Read Committed (PostgreSQL and Oracle's default) prevents dirty reads but still silently allows non-repeatable reads, write skew, and lost updates. Repeatable Read adds...
ACID Transactions in Distributed Databases: DynamoDB, Cosmos DB, and Spanner Compared
TLDR: ACID transactions in distributed databases are not equal. DynamoDB provides multi-item atomicity scoped to 25 items using two-phase commit with a coordinator item, but only within a single region. Cosmos DB wraps partition-scoped operations ins...
Sub-topic
2 articles
Dirty Write Explained: When Uncommitted Data Gets Overwritten
TLDR: A dirty write occurs when Transaction B overwrites data that Transaction A has written but not yet committed. The result is not a rollback or an error — it is silently inconsistent committed data: one table reflects Transaction B's intent, anot...
Lost Update Explained: When Two Writes Become One
TLDR: A lost update occurs when two concurrent read-modify-write transactions both read the same committed value, both compute a new value from it, and both write back — with the second write silently discarding the first. No error is raised. Both tr...
Sub-topic
1 article
Database Anomalies: How SQL and NoSQL Handle Dirty Reads, Phantom Reads, and Write Skew
TLDR: Database anomalies are the predictable side-effects of concurrent transactions — dirty reads, phantom reads, write skew, and lost updates. SQL databases use MVCC and isolation levels to prevent them; PostgreSQL's Serializable Snapshot Isolation...
Sub-topic
1 article
Distributed Transactions: 2PC, Saga, and XA Explained
TLDR: Distributed transactions require you to choose a consistency model before choosing a protocol. 2PC and XA give atomic all-or-nothing commits but block all participants on coordinator failure. Saga gives eventual consistency with explicit compen...