Concurrency Control

What is a Deadlock in Database?

A deadlock in a database happens when two or more transactions each hold a lock the other needs, so every transaction involved waits forever and none can proce…

Optimistic vs Pessimistic Locking: What is the Difference?

Pessimistic locking locks a row before reading it so no one else can change it, while optimistic locking allows concurrent reads and only checks for conflicts,…

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…

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…

How Do You Tune a Database Connection Pool?

A connection pool is tuned by sizing it to the database's actual concurrency capacity rather than the application's thread count, then adjusting timeouts and e…

How Do Databases Detect Deadlocks?

Databases detect deadlocks by building a wait-for graph, where each transaction is a node and an edge points from a waiting transaction to the transaction hold…

What is Lock Escalation in Databases?

Lock escalation is when a database engine converts many fine-grained row-level or page-level locks held by a single transaction into one coarser table-level lo…

Row-Level Locking vs Table-Level Locking: What is the Difference?

Row-level locking locks only the specific rows a transaction reads or modifies, allowing many transactions to work on different rows of the same table concurre…

What are Advisory Locks and When Should You Use Them?

An advisory lock is an application-defined lock that the database manages on behalf of an arbitrary key you choose, rather than automatically on a row or table…

How Does Optimistic Concurrency Control Work With Version Columns?

Optimistic concurrency control uses a version (or updated_at) column on each row so an UPDATE only succeeds if the version read by the client still matches the…

What Is the Banker’s Algorithm?

The Banker’s Algorithm is a deadlock-avoidance algorithm that grants a resource request only if the resulting system state is provably safe, meaning there stil…

What Is Deadlock Prevention?

Deadlock prevention is a design strategy that structurally rules out at least one of the four necessary conditions for deadlock — mutual exclusion, hold-and-wa…

What Is Deadlock Avoidance?

Deadlock avoidance is a dynamic strategy where the OS allows all four necessary conditions for deadlock to hold, but grants each resource request only after ch…

What Is Deadlock Detection?

Deadlock detection is a reactive strategy where the OS allows deadlock to potentially occur, periodically checks the system state for it using an algorithm ove…

What Is a Resource-Allocation Graph?

A resource-allocation graph is a directed graph model used to represent which processes hold which resources and which processes are waiting for which resource…

How Does Deadlock Recovery Work?

Deadlock recovery is the set of techniques an operating system uses after a deadlock has already been detected, and it works by forcibly breaking the circular…

What Are the Necessary Conditions for Deadlock?

Deadlock can only occur when four conditions hold simultaneously: mutual exclusion, hold and wait, no preemption, and circular wait — and breaking any single o…