Concurrency control is the set of techniques a DBMS uses to let many transactions run at the same time without producing incorrect results. Locking is one of the most important methods. Shared locks protect reads, exclusive locks protect writes, Two-Phase Locking (2PL) controls when locks are acquired and released, and multiple-granularity locking lets the DBMS lock large or small parts of the database using intention locks such as IS, IX and SIX. This chapter explains all of these ideas from zero with compatibility tables, solved schedules, a lock simulator and simple examples.
What Is Concurrency Control in DBMS?
Concurrency control manages simultaneous transactions so that the database remains correct even when transaction operations are interleaved. Its goal is to get the speed advantage of concurrent execution without problems such as lost updates, dirty reads and inconsistent results.
Concurrency control is closely connected to Transactions & ACID Properties, Serializability in DBMS and Deadlock in DBMS.
Problems Caused by Uncontrolled Concurrency
What Is a Lock in DBMS?
A lock is a control placed on a database item that restricts how other transactions can access that item while the lock is held.
A transaction normally requests an appropriate lock before reading or writing protected data. The lock manager checks whether that request is compatible with existing locks.
T1 requests lock on X
DBMS checks current lock table
If compatible -> grant
If incompatible -> T1 waits or another policy is appliedShared Lock and Exclusive Lock
Shared Lock (S)
Used mainly for reading.
Several transactions can hold shared locks on the same item at the same time.
T1: S-lock(X)
T2: S-lock(X)
AllowedExclusive Lock (X)
Used when a transaction needs to modify an item.
An X lock conflicts with both S and X locks held by other transactions.
T1: X-lock(X)
T2: S-lock(X)
T2 must waitS and X Lock Compatibility Matrix
| Existing Lock | Requested S | Requested X |
|---|---|---|
| S | YES | NO |
| X | NO | NO |
Two readers can safely share the same item, but a writer needs exclusive access.
Lock Compatibility Console
Select the lock already held and the lock another transaction wants. The tool shows whether the pair is compatible.
Existing lock
Requested lock
Lock Upgrade and Lock Downgrade
Upgrade
Change a weaker lock to a stronger lock.
S(X) -> X(X)This is needed when a transaction first reads X and later decides to update X.
Downgrade
Change a stronger lock to a weaker lock.
X(X) -> S(X)The transaction no longer needs exclusive write access but may still need to read X.
Under Two-Phase Locking, lock upgrades belong to the growing phase, while downgrades belong to the shrinking phase.
What Is Two-Phase Locking (2PL)?
Two-Phase Locking is a concurrency-control protocol that divides a transaction's locking activity into a growing phase and a shrinking phase. It guarantees conflict serializability when transactions follow the protocol correctly.
Growing Phase
The transaction may acquire new locks and upgrade locks.
It must not release locks yet.
Shrinking Phase
The transaction may release locks and downgrade locks.
It must not acquire any new lock.
What Is the Lock Point in 2PL?
The lock point is the moment when a transaction obtains its final lock. After this point, it eventually enters the shrinking phase and cannot acquire any new lock once unlocking begins.
Example:
LOCK-S(A)
LOCK-X(B)
READ(A)
WRITE(B)
Lock point occurs after LOCK-X(B)
UNLOCK(A)
UNLOCK(B)The ordering of lock points is related to an equivalent serial order in 2PL-based schedules.
Basic, Conservative, Strict and Rigorous 2PL
Growing phase followed by shrinking phase.
Guarantees conflict serializability, but deadlocks can occur and cascading rollback is still possible.
The transaction obtains all required locks before it starts its main execution.
This prevents deadlock caused by lock waiting, but can reduce concurrency and requires knowing required locks in advance.
Follows 2PL and keeps all exclusive (X) locks until commit or abort.
This produces strict schedules and simplifies recovery by preventing other transactions from reading or overwriting uncommitted writes.
Keeps both shared and exclusive locks until commit or abort.
It is stronger than strict 2PL in lock-release timing.
Basic 2PL vs Strict 2PL vs Rigorous 2PL
| Point | Basic 2PL | Strict 2PL | Rigorous 2PL |
|---|---|---|---|
| Growing / shrinking rule | Yes | Yes | Yes |
| Hold X locks until commit/abort | Not required | Yes | Yes |
| Hold S locks until commit/abort | Not required | Not necessarily | Yes |
| Conflict serializable | Yes | Yes | Yes |
| Deadlock possible | Yes | Yes | Yes |
| Recovery friendliness | Lower | Higher | Higher |
2PL Schedule Example
Transaction T1 needs to read A and update B.
T1:
LOCK-S(A)
READ(A)
LOCK-X(B)
READ(B)
WRITE(B)
UNLOCK(A)
UNLOCK(B)
COMMITT1 acquires both locks before releasing its first lock. After UNLOCK(A), it does not request any new lock. So it follows Basic 2PL.
If T1 keeps its X lock on B until commit or abort, it can satisfy Strict 2PL.
Why Does 2PL Guarantee Conflict Serializability?
2PL prevents arbitrary lock acquisition after a transaction has begun releasing locks. This creates an ordering through transaction lock points.
The resulting precedence relationships cannot form a cycle if all transactions obey 2PL in the normal theoretical model. Therefore the schedule is conflict-serializable.
For precedence-graph logic, revise Conflict & View Serializability.
Can Two-Phase Locking Cause Deadlock?
Yes. Normal 2PL can create deadlocks because one transaction may hold a lock and wait for another lock while another transaction does the opposite.
T1 holds X, waits for Y
T2 holds Y, waits for XThis is why DBMSs need deadlock detection, prevention or timeout policies even when 2PL is used.
Revise Deadlock Detection & Prevention in DBMS for wait-for graphs, wait-die and wound-wait.
What Is Multiple Granularity Locking?
Multiple granularity locking allows the DBMS to lock data at different levels of a hierarchy, such as database, table, page and row. It balances lock-management cost with concurrency.
Coarse-Grained vs Fine-Grained Locks
Coarse-Grained Lock
Locks a large object such as a whole table.
Advantage: fewer locks and lower lock-management overhead.
Disadvantage: less concurrency.
Fine-Grained Lock
Locks a smaller object such as a row.
Advantage: more transactions can work on different rows.
Disadvantage: more locks and more lock-management overhead.
What Are Intention Locks?
Suppose T1 wants an X lock on one row of a table. Before another transaction locks the whole table, the DBMS needs a quick way to know that some lower-level object is already going to be locked.
Intention locks place information on higher levels of the hierarchy.
Multiple Granularity Lock Compatibility Matrix
| Existing / Requested | IS | IX | S | SIX | X |
|---|---|---|---|---|---|
| IS | YES | YES | YES | YES | NO |
| IX | YES | YES | NO | NO | NO |
| S | YES | NO | YES | NO | NO |
| SIX | YES | NO | NO | NO | NO |
| X | NO | NO | NO | NO | NO |
This matrix is one of the most important things to revise for multiple-granularity locking questions.
Multiple Granularity Locking Protocol Rules
Example: Updating One Row Using Multiple Granularity
Suppose T1 wants to update Row 101 inside Table STUDENT.
Hierarchy:
Database
-> STUDENT table
-> Page 1
-> Row 101A simplified lock path can be:
Database: IX
STUDENT table: IX
Page 1: IX
Row 101: XThe IX locks on higher levels tell other transactions that T1 intends to obtain an exclusive lock somewhere below them.
Example: Reading One Row
If T2 wants only a shared lock on Row 102:
Database: IS
STUDENT table: IS
Page 1: IS
Row 102: SThe IS locks announce that T2 intends to take shared locks lower in the hierarchy.
When Is SIX Lock Useful?
Suppose a transaction needs to read an entire table but update only a few rows inside that table.
Instead of separately placing S on the table and IX on the same table, the DBMS can use:
SIX on the tableThis means:
What Is Lock Escalation?
Lock escalation replaces many fine-grained locks with one coarser lock when the DBMS decides that managing a large number of small locks is too expensive.
Example:
Before:
X locks on 5,000 individual rows
After escalation:
one X lock on the tableBenefit
Lower lock-table memory and lock-management overhead.
Cost
Lower concurrency because other transactions lose access to unrelated rows in the same table.
Can Row Locks Prevent Every Concurrency Problem?
No. Row locks alone may not prevent a phantom problem.
Suppose T1 runs:
SELECT *
FROM students
WHERE marks > 90;Another transaction may insert a new row with marks 95. If T1 repeats the query, a new matching row appears.
DBMSs can use techniques such as predicate locking, index-range locking or serializable isolation mechanisms to control this kind of problem.
Is Locking the Only Concurrency-Control Method?
No. Locking is one major method, but modern DBMSs can also use timestamp ordering, optimistic methods and Multi-Version Concurrency Control (MVCC).
Why Locking Can Create Deadlock
Locks protect correctness, but waiting for locks creates a new risk.
T1:
X-lock(A)
wait for B
T2:
X-lock(B)
wait for AThe two transactions are now in a deadlock cycle.
Why Strict 2PL Helps Database Recovery
Strict 2PL keeps exclusive locks until commit or abort. Therefore another transaction cannot read or overwrite the uncommitted value written by the current transaction.
This reduces cascading rollback and gives the DBMS a simpler recovery situation.
For logs, checkpoints, UNDO and REDO, revise Recoverability & Database Recovery in DBMS.
Solved Lock Compatibility Example
Suppose Table STUDENT already has an IX lock held by T1.
Can T2 request IS on STUDENT?
Yes. IX and IS are compatible because T1 may update some descendants while T2 intends only to read some descendants.
Can T2 request S on STUDENT?
No. A full shared table lock would conflict with T1's intention to acquire exclusive descendant locks.
Can T2 request IX on STUDENT?
Yes. Both transactions can intend to update different lower-level descendants, although they may later conflict on the same child item.
Concurrency Control Mistakes to Avoid
How to Write Concurrency Control in Semester Exams
Definition
Define concurrency control, S/X lock or Two-Phase Locking in two clear lines.
Protocol answer
Explain S/X compatibility and growing/shrinking phases, or explain IS, IX and SIX.
Full answer
Cover locking, compatibility matrix, 2PL variants, deadlocks, multiple granularity, hierarchy rules and one solved example.
For answer presentation, use How to Write High-Scoring Answers in University Semester Exams. For revision priority, use the previous-year-paper method.
Practice Questions With Answers
1. What is concurrency control in DBMS?
Concurrency control coordinates simultaneous transactions so that interleaved execution does not produce incorrect database results.
2. What is a shared lock?
A shared lock is mainly used for reading. Multiple transactions can hold shared locks on the same item at the same time.
3. What is an exclusive lock?
An exclusive lock is used for updating data. It conflicts with shared and exclusive locks held by other transactions on the same item.
4. What is Two-Phase Locking?
2PL divides locking into a growing phase, where locks are acquired, and a shrinking phase, where locks are released and no new locks may be acquired.
5. Does 2PL guarantee serializability?
Yes. Proper Two-Phase Locking guarantees conflict serializability.
6. Does 2PL prevent deadlocks?
No. Basic, strict and rigorous 2PL can still produce deadlocks. Conservative 2PL avoids deadlock by acquiring all required locks before execution.
7. What is strict 2PL?
Strict 2PL follows the two-phase rule and keeps all exclusive locks until the transaction commits or aborts.
8. What is multiple granularity locking?
It is a locking approach that allows data to be locked at different hierarchy levels such as database, table, page and row.
9. What are intention locks?
Intention locks are higher-level locks that announce plans to acquire locks on lower-level descendants. Common modes are IS, IX and SIX.
10. What should I study after locking and 2PL?
Study timestamp ordering, optimistic concurrency control, MVCC, isolation levels and advanced deadlock handling.
Concurrency Control One-Minute Revision Sheet
Ultimate memory line
Locks control access · 2PL controls timing · intention locks control hierarchyFrequently Asked Questions
What is concurrency control in DBMS in simple words?
Concurrency control is the set of rules a DBMS uses to let multiple transactions run together without corrupting or incorrectly mixing their results.
What is the difference between shared and exclusive locks?
A shared lock allows multiple readers, while an exclusive lock is used for updates and conflicts with other shared or exclusive locks on the same item.
What is 2PL in DBMS?
Two-Phase Locking is a protocol with a growing phase for acquiring locks and a shrinking phase for releasing locks.
What is strict 2PL?
Strict 2PL keeps all exclusive locks until commit or abort, helping produce strict schedules and simplifying recovery.
What is multiple granularity locking?
Multiple granularity locking lets the DBMS lock data at hierarchy levels such as database, table, page and row while coordinating those levels with intention locks.
What are IS, IX and SIX locks?
IS means intention shared, IX means intention exclusive, and SIX means shared on the current node plus intention exclusive on lower-level descendants.
What Should You Learn Next?
Next: Timestamp Ordering, MVCC & Isolation Levels
You now understand lock-based concurrency control. The next step is learning timestamp ordering protocols, validation-based concurrency control, Multi-Version Concurrency Control (MVCC), Read Committed, Repeatable Read and Serializable isolation.
For video revision after reading, use the DBMS YouTube study guide only as a secondary resource.
