ACID

Transactions and Locking

Understand how SQL Server groups statements into atomic transactions, uses locks to enforce isolation, and how deadlocks arise and get resolved.

ACID and Isolation Levels

How PostgreSQL guarantees Atomicity, Consistency, Isolation, and Durability, and how the four SQL isolation levels trade off correctness against concurrency.

ACID Properties

The four guarantees—Atomicity, Consistency, Isolation, Durability—that make database transactions reliable.

What is a Transaction in a Database?

A transaction is a sequence of one or more database operations that executes as a single, indivisible unit of work — either every operation succeeds and commit…

What is the Read Uncommitted Isolation Level?

Read Uncommitted is the weakest transaction isolation level, allowing a transaction to see changes made by other transactions even before those changes are com…

What is the Read Committed Isolation Level?

Read Committed is an isolation level that guarantees a transaction only ever sees data that has been committed by other transactions, eliminating dirty reads w…

What is the Repeatable Read Isolation Level?

Repeatable Read is an isolation level that guarantees any row a transaction has read will return the exact same values if read again later in that same transac…

What is the Serializable Isolation Level?

Serializable is the strictest transaction isolation level, guaranteeing that the outcome of running transactions concurrently is identical to running them one…

What is a Phantom Read in Database Transactions?

A phantom read is a concurrency anomaly where a transaction re-runs a range query and finds a different set of rows than the first time, because another transa…

Dirty Read vs Non-Repeatable Read: What is the Difference?

A dirty read happens when a transaction reads data written by another transaction that has not yet committed, so it can read a value that later gets rolled bac…

What is MVCC (Multi-Version Concurrency Control)?

MVCC (Multi-Version Concurrency Control) is a concurrency-control technique where the database keeps multiple versions of a row so readers see a consistent sna…

What is the Write Skew Anomaly?

Write skew is a concurrency anomaly where two transactions each read overlapping data, independently decide their own write is safe based on what they read, an…

What is the Lost Update Problem in Databases?

The lost update problem occurs when two transactions read the same row, both compute a new value based on that same original read, and the second transaction’s…

What is the Two-Phase Locking (2PL) Protocol?

Two-Phase Locking (2PL) is a concurrency-control protocol that guarantees serializable execution by splitting every transaction into a growing phase, where it…

Wide-Column Store vs Relational Database: What is the Difference?

A wide-column store lets each row hold millions of dynamically named, sparse columns grouped by family and distributed by row key across many nodes, while a re…

What is a Write-Ahead Log (WAL) and Why Do Databases Use One?

A write-ahead log (WAL) is a durability mechanism where the database records every change as a sequential log entry on stable storage before it modifies the ac…

How Does the Write-Ahead Log Enable Crash Recovery?

The write-ahead log enables crash recovery because, on restart, the database replays log records to redo committed changes that never made it to data pages, th…

Redo Log vs Undo Log: What is the Difference?

A redo log records the new value produced by a change so committed effects can be reapplied after a crash, while an undo log records the old value that existed…

What is Two-Phase Commit for Distributed Transactions?

Two-phase commit (2PC) is a protocol that lets a coordinator get multiple independent database participants to agree on committing or aborting a single distrib…

SQL vs NoSQL for Scale: Which Should You Choose?

SQL databases scale best when data is relational and consistency matters, typically via vertical scaling or read replicas, while NoSQL databases are built to s…

ACID vs BASE: What’s the Difference and When Does Each Apply?

ACID (Atomicity, Consistency, Isolation, Durability) describes transactions that are all-or-nothing, always valid, isolated from each other, and permanently sa…