Concurrency Control 2PL, Deadlock, Isolation

Concurrency is a practical DBMS topic that becomes clear when you connect the definition to a small working example.

Use this page to understand what happens, why it happens, how to verify it, and what mistake usually breaks the concept.

After reading, practice Concurrency with a normal case, a boundary case, and a broken case so the idea becomes usable instead of memorized.

Concurrency Control 2PL Deadlock Isolation should be studied as a practical database design lesson, not as a label. Start by naming the input, the rule that changes the input, and the result a learner should be able to predict after reading the page.

In the dbms > concurrency-control page, the notes should connect the definition with a working scenario, a mistake that beginners actually make, and the exact check that proves the fix. That makes the topic useful for coding, debugging, and interview revision.

Concurrency Control in DBMS

Concurrency control is the DBMS mechanism that keeps database results correct when multiple transactions execute at the same time. Without concurrency control, users may read temporary data, overwrite each other's updates, or produce reports from inconsistent intermediate states.

The goal is to get the performance benefit of concurrent execution while preserving the correctness of serial execution. In simple words, the DBMS should allow many users to work together without letting their transactions damage each other.

Why Concurrency Control is Needed

It improves database throughput by allowing many transactions to run together.
It prevents lost updates, dirty reads, unrepeatable reads, phantom reads, and incorrect summaries.
It helps preserve isolation, one of the ACID properties of transactions.
It ensures that concurrent schedules are equivalent to correct serial schedules.
It supports multi-user applications such as banking, ticket booking, inventory, and e-commerce.

Concurrency Example

Suppose two transactions try to update the same account balance at the same time. If both read the old value and write a new value independently, one update may be lost.

lost-update-schedule.txt

Initial balance A = 1000

T1: Read A = 1000
T2: Read A = 1000
T1: A = A + 200, Write A = 1200
T2: A = A - 100, Write A = 900

Final value becomes 900.
T1's update is lost because T2 overwrote it.

Important Terms

Term	Meaning
Transaction	A logical unit of work that should commit completely or roll back completely.
Schedule	The order in which operations of transactions are executed.
Serial Schedule	Transactions run one after another without interleaving.
Concurrent Schedule	Operations of multiple transactions are interleaved.
Conflict	Two operations from different transactions access the same item and at least one operation is a write.
Serializable Schedule	A concurrent schedule that gives the same result as some serial schedule.

Problems Without Concurrency Control

Problem	Description	Example
Lost Update	One transaction overwrites another transaction's update.	Two users update the same stock quantity and the later write removes the earlier change.
Dirty Read	A transaction reads data written by another uncommitted transaction.	T2 reads a salary change by T1, but T1 later rolls back.
Unrepeatable Read	A transaction reads the same row twice and gets different values.	T1 reads balance 5000; T2 updates it; T1 reads balance again and sees 4500.
Phantom Read	A repeated condition-based query returns a different set of rows.	T1 counts orders above 1000; T2 inserts a matching order; T1 counts again and sees one extra row.
Incorrect Summary	An aggregate reads some old values and some new values while updates are happening.	A report computes total account balance while a transfer is halfway complete.

Serializability

Serializability is the main correctness condition in concurrency control. A schedule is serializable if it produces the same final result as a serial schedule.

Two operations conflict if they are from different transactions, access the same data item, and at least one operation writes that item.

Type	Meaning	How It Is Checked
Conflict Serializability	The schedule can be transformed into a serial schedule by swapping non-conflicting operations.	Use a precedence graph. If the graph has no cycle, the schedule is conflict serializable.
View Serializability	The schedule has the same read-from relationships and final writes as a serial schedule.	Harder to test; it is more general than conflict serializability.

precedence-graph-example.txt

Schedule:
R1(A), W1(A), R2(A), W2(A), R1(B), W2(B), C1, C2

Conflicts:
W1(A) before R2(A) gives edge T1 -> T2
W1(A) before W2(A) gives edge T1 -> T2
R1(B) before W2(B) gives edge T1 -> T2

Graph has no cycle.
So the schedule is conflict serializable in order T1, T2.

Lock-Based Protocols

A lock is a permission to access a data item. Before a transaction reads or writes an item, it may need to acquire a lock. Locking is one of the most widely used concurrency control techniques.

Lock Type	Also Called	Allows	Blocks
Shared Lock (S)	Read lock	Many transactions can read the same item.	Writing by other transactions.
Exclusive Lock (X)	Write lock	One transaction can read and write the item.	All other reads and writes on the item.
Update Lock (U)	Conversion lock	A transaction plans to update after reading.	Some competing update attempts, depending on DBMS.
Intent Lock	Hierarchy lock	A transaction plans to lock lower-level objects such as rows.	Conflicting table-level locks.

locking-example.sql

START TRANSACTION;

SELECT balance
FROM accounts
WHERE account_id = 101
FOR UPDATE;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;

COMMIT;

Two-Phase Locking (2PL)

Two-Phase Locking is a locking protocol that guarantees conflict serializability. A transaction has two phases: first it only acquires locks, then it only releases locks.

The point where a transaction gets its last lock is called the lock point. Under 2PL, transactions are serialized according to their lock points.

Phase	Meaning
Growing Phase	The transaction may acquire new locks but cannot release any lock.
Shrinking Phase	The transaction may release locks but cannot acquire any new lock.

Lock Granularity

Lock granularity means the size of the object being locked. A DBMS may lock a database, table, page, row, or even a field.

Smaller locks allow more concurrency but require the DBMS to manage more lock entries. Larger locks are cheaper to manage but block more transactions.

Granularity	Example	Concurrency	Overhead
Table Lock	Lock the whole employees table.	Low	Low
Page Lock	Lock a disk page containing many rows.	Medium	Medium
Row Lock	Lock one account row.	High	High

Deadlock

A deadlock occurs when two or more transactions wait for each other in a cycle. Because each transaction is waiting for a lock held by another transaction, none can continue.

In a wait-for graph, transactions are nodes and waiting relationships are edges. A cycle in the graph indicates a deadlock.

Method	How It Works
Prevention	Use rules that make deadlocks impossible, such as lock ordering or timestamp-based policies.
Detection	Periodically build a wait-for graph and check for cycles.
Recovery	Choose a victim transaction, abort it, release its locks, and restart it later.
Timeout	Abort a transaction if it waits longer than a configured time.

deadlock-schedule.txt

T1: lock-X(A)
T2: lock-X(B)
T1: request lock-X(B) and wait
T2: request lock-X(A) and wait

T1 waits for T2.
T2 waits for T1.
Cycle found, so deadlock exists.

Timestamp-Based Concurrency Control

In timestamp ordering, each transaction gets a unique timestamp when it starts. The DBMS uses timestamps to decide whether an operation is allowed or whether the transaction must restart.

Operation	Rule	Result
Read X	If TS(T) < WTS(X), the transaction is too old.	Abort and restart T.
Read X	If TS(T) >= WTS(X), the read is safe.	Read X and update RTS(X).
Write X	If TS(T) < RTS(X) or TS(T) < WTS(X), the write is too old.	Abort and restart T.
Write X	If timestamp checks pass, the write is safe.	Write X and update WTS(X).

TS(T) is the timestamp of transaction T.
RTS(X) is the largest timestamp of any transaction that successfully read item X.
WTS(X) is the largest timestamp of any transaction that successfully wrote item X.

Thomas Write Rule

Thomas Write Rule is an optimization for timestamp ordering. If a transaction tries to write an old value that has already been overwritten by a newer transaction, the DBMS can ignore that obsolete write instead of aborting the transaction.

thomas-write-rule.txt

If transaction T wants to write X:

1. If TS(T) < RTS(X), abort T.
2. If TS(T) < WTS(X), ignore the write because it is obsolete.
3. Otherwise, perform the write and set WTS(X) = TS(T).

Optimistic Concurrency Control

Optimistic concurrency control assumes conflicts are rare. Transactions execute without locking first, then the DBMS validates them before commit.

Phase	Meaning
Read Phase	The transaction reads database values and makes changes in a private workspace.
Validation Phase	The DBMS checks whether the transaction conflicts with other committed transactions.
Write Phase	If validation succeeds, changes are written to the database. If validation fails, the transaction restarts.

MVCC

Multiversion Concurrency Control (MVCC) keeps multiple versions of a row. Readers can view a consistent snapshot while writers create newer versions. This reduces blocking between reads and writes.

Readers usually do not block writers.
Writers usually do not block readers.
Each transaction sees a consistent snapshot based on its start time or statement time.
Old row versions must be cleaned up by the DBMS later.

mvcc-version-example.txt

Account row versions:

Version 1: balance = 1000, visible to transactions started before T2 commits
Version 2: balance = 1200, created by T2 after update

Old readers can continue seeing Version 1.
New readers can see Version 2 after T2 commits.

Isolation Levels

Isolation levels define how strongly one transaction is separated from other concurrent transactions. Stronger isolation prevents more anomalies but can reduce performance.

Isolation Level	Dirty Read	Unrepeatable Read	Phantom Read	Typical Performance
READ UNCOMMITTED	Possible	Possible	Possible	Highest
READ COMMITTED	Prevented	Possible	Possible	High
REPEATABLE READ	Prevented	Prevented	May be possible depending on DBMS.	Medium
SERIALIZABLE	Prevented	Prevented	Prevented	Lowest

isolation-levels.sql

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;

SELECT stock
FROM products
WHERE product_id = 10;

UPDATE products
SET stock = stock - 1
WHERE product_id = 10;

COMMIT;

Recoverable, Cascadeless, and Strict Schedules

Correct concurrency control must also support recovery. A schedule should avoid situations where one rollback forces many other transactions to roll back.

Schedule Type	Meaning	Quality
Recoverable	If T2 reads a value written by T1, T2 commits only after T1 commits.	Minimum requirement for safe recovery.
Cascadeless	Transactions read only committed data.	Avoids cascading rollback.
Strict	A transaction cannot read or write an item until the last transaction that wrote it commits or aborts.	Best for simple recovery.

Comparison of Concurrency Control Methods

Method	Main Idea	Advantages	Disadvantages
Locking	Transactions acquire locks before accessing data.	Easy to understand and widely used.	May cause deadlocks and blocking.
Timestamp Ordering	Transactions are ordered by timestamps.	No deadlock.	May cause frequent restarts.
Optimistic Control	Transactions run first and are validated before commit.	Good when conflicts are rare.	Costly when conflicts are common.
MVCC	Maintain multiple row versions for snapshots.	Great read concurrency.	Requires version cleanup and more storage management.

Best Practices

Keep transactions short so locks are held for less time.
Access tables and rows in a consistent order to reduce deadlocks.
Use indexes on search conditions so updates lock fewer rows.
Choose the lowest isolation level that still preserves correctness.
Retry transactions safely when deadlocks or serialization failures occur.
Avoid long user interactions while a transaction is open.
Use database constraints to catch invalid concurrent updates.

Common Mistakes

Mistake	Risk	Better Approach
Assuming concurrent transactions behave like serial transactions automatically	Lost updates and inconsistent reads may occur.	Use proper isolation, locks, constraints, or retry logic.
Using SERIALIZABLE for every operation	Unnecessary blocking and slower throughput.	Use it only for operations that truly require strict isolation.
Holding locks during slow application work	Other transactions wait longer and deadlock risk increases.	Keep only database work inside the transaction.
No retry logic for deadlocks	User operations fail even though retry would succeed.	Catch deadlock or serialization errors and retry safely.

Interview and Exam Questions

Concurrency control is the process of coordinating simultaneous transactions so the database remains correct and isolated.

The main goal is to make concurrent execution equivalent to a correct serial execution while still improving performance.

A schedule is conflict serializable if it can be converted into a serial schedule by swapping non-conflicting operations.

Two-phase locking is a protocol where a transaction first acquires locks in the growing phase and then releases locks in the shrinking phase.

A deadlock occurs when transactions wait for each other in a cycle and none can proceed.

Timestamp ordering usually restarts transactions instead of making them wait in cycles, so circular waiting does not occur.

MVCC is a concurrency control method that keeps multiple versions of rows so readers can see a consistent snapshot while writers continue.

Quick Revision Notes

Concurrency control protects correctness during simultaneous transaction execution.
Serializability is the main correctness goal.
Conflicting operations access the same item, belong to different transactions, and include at least one write.
Shared locks allow reads; exclusive locks allow writes and block other access.
2PL guarantees conflict serializability but can cause deadlock.
A wait-for graph cycle means deadlock.
Timestamp ordering avoids deadlock but may restart transactions.
MVCC improves read concurrency using multiple row versions.
Isolation levels trade correctness strength for concurrency and performance.

Deep Study Notes for Concurrency

Concurrency should be learned as a practical DBMS skill, not only as a definition. Start by asking what problem the topic solves, what input or state it receives, what rule it applies, and what visible result proves it worked.

A strong explanation of Concurrency includes the normal case, a boundary case, and a failure case. When you practice, write down the before-state, the operation, the after-state, and the reason the result changed.

This lesson was expanded because the audit reported: limited checklist/practice/mistake/FAQ notes . The added notes below focus on clearer explanation, more examples, and concrete practice so the topic is easier to understand from the page itself.

Define the exact problem solved by Concurrency before looking at syntax.
Trace one small example by hand and describe every step in plain language.
Identify what changes when the input is empty, repeated, invalid, delayed, or larger than expected.
Connect the topic to a realistic project scenario instead of treating it as isolated theory.
Verify your answer with output, logs, query results, browser behavior, compiler feedback, or a state table.

Worked Explanation: Using Concurrency Correctly

Imagine you are adding Concurrency to a small learning project. The first step is to choose the smallest scenario that still shows the main idea. Avoid starting with a large production design; it hides the concept behind too many details.

Next, isolate the moving parts. Name the input, the rule, the output, and the possible error. This habit makes the topic easier to debug because you can see whether the problem is caused by bad data, wrong configuration, incorrect syntax, timing, permissions, or misunderstanding of the rule.

Finally, compare two versions: one correct version and one intentionally broken version. The broken version is valuable because it teaches you how the topic fails in real work, which is usually what interviews and debugging tasks test.

Normal case: show the expected behavior with simple, valid input.
Boundary case: test the smallest, largest, empty, repeated, or unusual value that still belongs to the topic.
Failure case: introduce one realistic mistake and explain the symptom it creates.
Repair step: change one thing at a time so you know exactly what fixed the problem.

Concurrency SQL lab setup

CREATE TABLE lesson_concurrency (
    id INT PRIMARY KEY,
    description VARCHAR(120),
    amount DECIMAL(10,2),
    status VARCHAR(20)
);

INSERT INTO lesson_concurrency VALUES
(1, 'Concurrency normal case', 1000.00, 'active'),
(2, 'Concurrency boundary case', 0.00, 'review');

SELECT * FROM lesson_concurrency;

Concurrency reasoning query

BEGIN;
UPDATE lesson_concurrency
SET status = 'checked'
WHERE amount >= 0;

SELECT status, COUNT(*) AS rows_seen
FROM lesson_concurrency
GROUP BY status;
ROLLBACK;

-- Explanation: ROLLBACK lets you test the concept safely before committing changes.

Key Takeaways

State the purpose of Concurrency in one sentence before using it.
Create a tiny DBMS example that demonstrates the topic without unrelated code.
Test one normal input, one edge input, and one incorrect input for Concurrency.
Explain the result using before-state, operation, and after-state.
Add a verification step such as output, logs, query results, browser behavior, or compiler feedback.

Common Mistakes to Avoid

WRONG Memorizing Concurrency as a definition only.

RIGHT Pair the definition with a small working example and a failure example.

The fastest way to remember the topic is to explain why the output changes.

WRONG Copying syntax without checking the state before and after.

RIGHT Write the input state, apply the rule, then inspect the output state.

State tracing turns confusing behavior into a visible sequence.

WRONG Ignoring the error path for Concurrency.

RIGHT Create one intentionally broken version and document the symptom and fix.

A page is much easier to learn from when it explains both success and failure.

WRONG Memorizing Concurrency Control 2PL Deadlock Isolation without the situation where it is useful.

RIGHT Connect Concurrency Control 2PL Deadlock Isolation to a concrete database design task.

Purpose makes syntax easier to recall.

Practice Tasks

Build the smallest working demo for Concurrency and write what each line does.
Change one input or setting and predict the result before running it.
Break the example in a realistic way, then fix it and describe the repair.
Create a two-column note comparing when to use Concurrency and when another approach is better.
Explain Concurrency aloud as if teaching a beginner who knows basic DBMS only.

Frequently Asked Questions

What should I understand first in Concurrency?

Understand the problem it solves, the input or state it works on, and the visible result that proves the concept is working.

How should I practice Concurrency?

Use one tiny correct example, one boundary example, and one broken example. Compare the output or state after each change.

Why do beginners struggle with Concurrency?

They often memorize the term without tracing the behavior. Tracing makes the rule easier to remember and debug.

What should I remember first about Concurrency Control 2PL Deadlock Isolation?

Remember the problem it solves in database design, then attach the syntax or steps to that problem.

Previous Next

Concurrency Control 2PL, Deadlock, Isolation