Database Locking Interview Questions

Master Database Locking with interview-focused questions covering Shared Locks, Exclusive Locks, Row Locks, Table Locks, Intent Locks, Optimistic Locking, Pessimistic Locking, Lock Escalation, Lock Timeout, MVCC, and enterprise production best practices.

Introduction

When multiple users access the same data simultaneously,

the database must ensure that

Data remains consistent
Transactions do not overwrite each other
Concurrent updates do not corrupt data

To achieve this,

databases use Locks.

Locking is one of the most important concurrency control mechanisms and is heavily used in

Banking
E-Commerce
Airline Reservation
Stock Trading
Healthcare

It is one of the most frequently asked database interview topics.

Locking Architecture

flowchart LR

ApplicationA["Application A"] --> TransactionLockmanagerlockManager["Transaction --> LockManager["Lock Manager"]"]

ApplicationB["Application B"] --> TransactionLockmanagerlockManager["Transaction --> LockManager["Lock Manager"]"]

LockManager["Lock Manager"] --> Database

1. What is Database Locking?

Answer

Database Locking is a mechanism that temporarily restricts access to data while a transaction is reading or modifying it.

Its purpose is to prevent

Lost Updates
Dirty Reads
Data Corruption
Concurrent Modification Issues

Locking Workflow

Transaction Starts

↓

Acquire Lock

↓

Read / Update Data

↓

Commit

↓

Release Lock

2. Why is Locking required?

Locking provides

Data Consistency
Transaction Isolation
Concurrent Access Control
Safe Updates
Reliable Transactions

3. What are the types of Locks?

Common lock types

Shared Lock
Exclusive Lock
Intent Lock
Row Lock
Page Lock
Table Lock

Lock Hierarchy

Database

↓

Table Lock

↓

Page Lock

↓

Row Lock

4. What is a Shared Lock (S Lock)?

A Shared Lock allows

multiple transactions

to read the same data simultaneously.

However,

updates are blocked until the shared locks are released.

Shared Lock Example

Transaction A

SELECT *
FROM Employee
WHERE id=1;

Transaction B

SELECT *
FROM Employee
WHERE id=1;

Both succeed.

Transaction C

UPDATE Employee
SET salary=90000
WHERE id=1;

Must wait.

Shared Lock Workflow

flowchart LR

ReadA["Read A"] --> SharedLock["Shared Lock"]

ReadB["Read B"] --> SharedLock["Shared Lock"]

SharedLock["Shared Lock"] --> Database

5. What is an Exclusive Lock (X Lock)?

An Exclusive Lock allows

only one transaction

to modify data.

No other transaction can

Read (database dependent)
Update

until the lock is released.

Exclusive Lock Example

UPDATE Employee
SET salary=95000
WHERE id=1;

Other UPDATE operations must wait.

Exclusive Lock

Update

↓

Exclusive Lock

↓

Commit

↓

Release

6. Difference between Shared and Exclusive Lock?

Shared Lock	Exclusive Lock
Read Only	Read + Write
Multiple Allowed	Single Transaction
Blocks Updates	Blocks Reads/Writes (depends on DB)
High Concurrency	Low Concurrency

7. What is a Row Lock?

A Row Lock locks

only

one row.

Other rows remain available.

This provides

high concurrency.

Row Lock

Table

↓

Row 5 Locked

↓

Other Rows Accessible

8. What is a Table Lock?

A Table Lock locks

the entire table.

All other transactions

must wait.

Table Lock

Employee Table

↓

Entire Table Locked

9. Row Lock vs Table Lock

Row Lock	Table Lock
Fine-Grained	Coarse-Grained
High Concurrency	Lower Concurrency
Better for OLTP	Better for Bulk Operations
Less Blocking	More Blocking

10. What is a Page Lock?

A Page Lock locks

one database page,

which contains multiple rows.

It balances

Row Lock

and

Table Lock.

11. What is an Intent Lock?

Intent Locks indicate

that a transaction

plans to acquire

lower-level locks.

Common types

Intent Shared (IS)
Intent Exclusive (IX)

Intent Lock Workflow

Table

↓

Intent Lock

↓

Row Lock

12. What is Lock Escalation?

Lock Escalation converts

many row locks

into

one table lock

to reduce lock management overhead.

Lock Escalation

1000 Row Locks

↓

One Table Lock

13. What is Lock Timeout?

Lock Timeout is

the maximum time

a transaction waits

before failing

to acquire a lock.

14. What is Blocking?

Blocking occurs

when one transaction waits

for another transaction

to release a lock.

Blocking Example

Transaction A

↓

Exclusive Lock

↓

Transaction B

↓

Waiting...

15. What is Optimistic Locking?

Optimistic Locking assumes

conflicts are rare.

No database lock is held while reading.

Instead,

the application verifies

whether the data changed

before updating.

Commonly implemented using

Version Number
Timestamp

Optimistic Locking Example

UPDATE Employee
SET salary=90000,
version=version+1
WHERE id=1
AND version=5;

If zero rows are updated,

another transaction modified the row.

Optimistic Lock Workflow

flowchart LR

ReadVersion["Read Version"] --> UpdateVersionCheckSuccessretrysuccess["Update --> Version Check --> SuccessRetry["Success / Retry"]"]

16. What is Pessimistic Locking?

Pessimistic Locking assumes

conflicts are likely.

The database locks data

before modification.

Pessimistic Lock Example

SELECT *
FROM Employee
WHERE id=1
FOR UPDATE;

Other transactions cannot update

until COMMIT.

17. Optimistic vs Pessimistic Locking

Optimistic	Pessimistic
No Lock During Read	Lock Immediately
Version Check	Database Lock
Better Read Performance	Better Conflict Prevention
Retry Required	Waiting Possible

18. What is MVCC?

MVCC (Multi-Version Concurrency Control)

allows readers

to access

older committed versions

instead of waiting.

Used by

PostgreSQL
MySQL InnoDB
Oracle

to reduce read locks.

MVCC Workflow

flowchart LR

Writer --> NewVersion["New Version"]

Reader --> OldVersion["Old Version"]

19. Banking Example

Money Transfer

Debit

↓

Exclusive Lock

↓

Credit

↓

Commit

20. E-Commerce Example

Inventory Update

Product Row

↓

Row Lock

↓

Update Quantity

↓

Commit

21. Airline Booking Example

Seat Reservation

Seat

↓

Exclusive Lock

↓

Payment

↓

Commit

Prevents double booking.

22. Healthcare Example

Patient Record

Doctor Updates

↓

Exclusive Lock

↓

Commit

23. Stock Trading Example

Portfolio Update

Read

↓

Optimistic Lock

↓

Version Check

↓

Commit

24. Production Example

Salary Processing

Read Employees

↓

Row Locks

↓

Update Salaries

↓

Commit

25. Common Locking Problems

Blocking
Deadlocks
Lock Contention
Long Transactions
Lock Escalation

26. Advantages of Locking

Data Integrity
Safe Concurrent Updates
Prevents Lost Updates
ACID Compliance
Reliable Transactions

27. Disadvantages of Locking

Blocking
Deadlocks
Reduced Concurrency
Longer Wait Time
Performance Overhead

28. Performance Tips

Keep transactions short.
Use Row Locks whenever possible.
Avoid unnecessary Table Locks.
Create indexes to reduce lock duration.
Commit quickly.
Monitor lock waits.
Avoid user interaction inside transactions.

29. Best Practices

Prefer Row Locks for OLTP systems.
Use Optimistic Locking for high-read applications.
Use Pessimistic Locking when conflicts are frequent.
Keep transactions small.
Monitor blocking sessions.
Tune lock timeout values.
Avoid lock escalation.
Index frequently updated rows.
Use MVCC-enabled databases.
Continuously monitor deadlocks.

30. Lock Lifecycle

flowchart LR

TransactionBegins["Transaction Begins"] --> AcquireLockReadWrite["Acquire Lock --> Read / Write --> Commit --> ReleaseLock["Release Lock"]"]

Enterprise Best Practices

Design transactions to minimize lock duration.
Choose Row Locks over Table Locks whenever possible.
Use Optimistic Locking for REST APIs and web applications.
Use Pessimistic Locking for banking and inventory systems.
Monitor lock contention using database monitoring tools.
Review execution plans to reduce unnecessary locking.
Avoid long-running transactions.
Configure lock timeout appropriately.
Regularly analyze blocking and deadlock reports.
Combine MVCC with proper indexing for maximum concurrency.

Quick Revision

Topic	Key Point
Lock	Controls Concurrent Access
Shared Lock	Multiple Readers
Exclusive Lock	Single Writer
Row Lock	One Row
Table Lock	Entire Table
Page Lock	One Database Page
Intent Lock	Future Lock Indicator
Optimistic Lock	Version-Based
Pessimistic Lock	Immediate Database Lock
Lock Escalation	Row → Table Lock
Lock Timeout	Maximum Wait Time
MVCC	Multiple Row Versions

Interview Tips

Interviewers frequently ask

What is Database Locking?
Shared Lock vs Exclusive Lock.
Row Lock vs Table Lock.
What is Lock Escalation?
What is Blocking?
Optimistic vs Pessimistic Locking.
What is MVCC?
Why does Locking cause Deadlocks?
How do you reduce lock contention?
Which locking strategy is used in Spring Boot JPA?

A strong interview explanation is:

"Database locking is a concurrency control mechanism that protects data consistency during concurrent transactions. Shared locks allow multiple readers, while exclusive locks allow only one writer. Row locks provide high concurrency, whereas table locks are suitable for bulk operations but reduce concurrency. Optimistic locking uses version checking to detect conflicts without holding locks, while pessimistic locking acquires database locks immediately to prevent concurrent modifications. Modern databases also use MVCC to reduce reader-writer blocking and improve throughput."

Summary

Database Locking is a core mechanism for ensuring transaction isolation and data consistency in concurrent environments. Understanding Shared Locks, Exclusive Locks, Row Locks, Table Locks, Intent Locks, Optimistic Locking, Pessimistic Locking, Lock Escalation, Blocking, and MVCC is essential for building scalable enterprise applications.

Mastering locking concepts provides the foundation for advanced topics such as Deadlocks, Isolation Levels, MVCC Internals, Distributed Transactions, Spring Transaction Management, and High-Performance Database Design.