Introduction

When multiple users access the same data simultaneously, MySQL must ensure data consistency while maintaining high concurrency.

Locking is the mechanism that prevents multiple transactions from modifying the same data in conflicting ways.

Without locking, applications would suffer from

• Lost Updates
• Dirty Reads
• Data Corruption
• Inconsistent Results

MySQL's InnoDB Storage Engine provides sophisticated locking mechanisms that allow high concurrency while preserving ACID guarantees.

---

MySQL Locking Architecture

flowchart LR

Application --> Transaction --> LockManager

LockManager --> RowLocks

LockManager --> TableLocks

LockManager --> GapLocks

LockManager --> MVCC

MVCC --> StorageEngine

---

1. What is Locking?

Answer

Locking is a mechanism that controls concurrent access to database resources.

It ensures

• Data Consistency
• Isolation
• Concurrency Control
• Transaction Safety

---

2. Why is Locking required?

Without locking

User A

↓

Update Balance

User B

↓

Update Balance

↓

Incorrect Balance

Locking prevents concurrent modification conflicts.

---

3. What types of locks are available in MySQL?

Major lock types

• Shared Lock
• Exclusive Lock
• Row Lock
• Table Lock
• Gap Lock
• Next-Key Lock
• Intention Lock
• Metadata Lock

---

Lock Hierarchy

flowchart TD

Lock --> SharedLock

Lock --> ExclusiveLock

Lock --> RowLock

Lock --> TableLock

Lock --> GapLock

Lock --> NextKeyLock

---

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

A Shared Lock allows

• Multiple Reads
• No Writes

Example

SELECT *

FROM Employee

WHERE id=100

LOCK IN SHARE MODE;

(MySQL 8 also supports FOR SHARE.)

---

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

Exclusive Lock allows

• Read by owner
• Write by owner

Other transactions cannot

• Read (depending on isolation level)
• Write

Example

SELECT *

FROM Employee

WHERE id=100

FOR UPDATE;

---

Shared vs Exclusive Lock

flowchart LR

SharedLock --> MultipleReaders

ExclusiveLock --> SingleWriter

---

6. What is a Row Lock?

Only one row is locked.

Other rows remain accessible.

Provides

• High Concurrency
• Better Performance

Used by InnoDB.

---

7. What is a Table Lock?

Entire table becomes locked.

All rows become unavailable for conflicting operations.

Used mainly by MyISAM.

---

Row Lock vs Table Lock

Row Lock	Table Lock
Single Row	Entire Table
High Concurrency	Low Concurrency
InnoDB	MyISAM

---

8. What is a Gap Lock?

Gap Lock locks

the space

between index records.

Purpose

Prevent phantom inserts.

---

Gap Lock

10      20      30      40

-------- GAP --------

No insert allowed here

---

9. What is a Next-Key Lock?

Next-Key Lock

=

Row Lock

Gap Lock

It locks

• Current Row
• Gap After Row

Used in

REPEATABLE READ

---

Next-Key Lock

20

↓

Current Row Locked

↓

Gap Locked

↓

30

---

10. Why does InnoDB use Next-Key Locks?

To prevent

Phantom Reads.

---

11. What is an Intention Lock?

Intention Locks tell MySQL

that a transaction intends

to acquire row-level locks.

Types

• IS (Intent Shared)
• IX (Intent Exclusive)

---

12. Why are Intention Locks needed?

They improve locking efficiency.

MySQL doesn't need to inspect every row before applying a table lock.

---

13. What is a Metadata Lock (MDL)?

Metadata Locks protect database objects during DDL operations.

Example

ALTER TABLE

DROP TABLE

RENAME TABLE

Other conflicting operations wait until the lock is released.

---

14. What is Lock Escalation?

Unlike SQL Server,

MySQL InnoDB does not automatically escalate row locks to table locks.

It continues using row-level locks.

---

15. What is Lock Wait?

A transaction waits because another transaction already holds a conflicting lock.

---

Lock Wait

flowchart LR

Transaction1 --> Lock

Transaction2 --> Waiting

---

16. What is Lock Wait Timeout?

If waiting exceeds

innodb_lock_wait_timeout

MySQL aborts the waiting transaction.

Example

ERROR 1205

Lock wait timeout exceeded

---

17. What is a Deadlock?

Two or more transactions wait for each other forever.

Example

Transaction A

locks Row 1

needs Row 2

Transaction B

locks Row 2

needs Row 1

Deadlock occurs.

---

Deadlock

flowchart LR

TransactionA --> Row1

Row1 --> TransactionB

TransactionB --> Row2

Row2 --> TransactionA

---

18. How does MySQL handle Deadlocks?

InnoDB automatically detects deadlocks.

One transaction becomes the

Deadlock Victim

and is rolled back.

---

19. How do you detect Deadlocks?

Useful command

SHOW ENGINE INNODB STATUS;

Look for

LATEST DETECTED DEADLOCK

---

20. What causes Deadlocks?

• Different update order
• Long transactions
• Missing indexes
• Large batch updates

---

21. What is Optimistic Locking?

Assumes conflicts are rare.

Uses

• Version Number
• Timestamp

Update succeeds only if the version matches.

---

Optimistic Locking

Version = 5

↓

Update

↓

Version = 6

---

22. What is Pessimistic Locking?

Assumes conflicts are likely.

Locks data before modification.

Example

SELECT *

FROM Account

WHERE id=1

FOR UPDATE;

---

Optimistic vs Pessimistic

Optimistic	Pessimistic
No Immediate Lock	Immediate Lock
Better Concurrency	Better Consistency
Uses Version	Uses Database Lock

---

23. What is MVCC?

MVCC

Multi-Version Concurrency Control

Allows

Readers

and

Writers

to work simultaneously.

Reduces lock contention.

---

24. Does every SELECT acquire locks?

No.

Normal SELECT uses MVCC and does not block writers.

Locking reads use

• FOR UPDATE
• FOR SHARE

---

25. Banking Example

Money Transfer

Lock Sender Account

↓

Debit

↓

Credit

↓

Commit

---

26. Inventory Example

Reserve Product

↓

Lock Product Row

↓

Reduce Quantity

↓

Commit

---

27. Ticket Booking Example

Seat Reservation

↓

Lock Seat

↓

Book Seat

↓

Commit

Prevents double booking.

---

28. HR Example

Salary Update

↓

FOR UPDATE

↓

Modify Salary

↓

Commit

---

29. Common Locking Problems

• Deadlocks
• Lock Wait Timeout
• Long Transactions
• Missing Indexes
• Full Table Updates
• Hot Rows

---

30. How do indexes reduce locking?

Without indexes

↓

Many rows scanned

↓

Many rows locked

With indexes

↓

Only matching rows locked

↓

Higher concurrency

---

Locking Workflow

flowchart LR

Transaction --> AcquireLock --> ExecuteSQL --> Commit

Commit --> ReleaseLock

---

Enterprise Best Practices

• Keep transactions short.
• Commit quickly.
• Access tables in the same order.
• Use indexes to minimize locked rows.
• Avoid unnecessary SELECT ... FOR UPDATE.
• Handle deadlock retries in the application.
• Monitor lock waits regularly.
• Avoid user interaction inside transactions.
• Batch updates carefully.
• Review SHOW ENGINE INNODB STATUS during troubleshooting.

---

Quick Revision

Topic	Key Point
Shared Lock	Multiple Readers
Exclusive Lock	Single Writer
Row Lock	High Concurrency
Table Lock	Entire Table
Gap Lock	Prevent Phantom Inserts
Next-Key Lock	Row + Gap
Intention Lock	Lock Intent
Metadata Lock	Protect DDL
Deadlock	Circular Waiting
Optimistic Lock	Version-Based
Pessimistic Lock	Database Lock
MVCC	Readers & Writers Together

---

Interview Tips

Interviewers frequently ask

• What is locking?
• Shared Lock vs Exclusive Lock.
• Row Lock vs Table Lock.
• Explain Gap Lock.
• Explain Next-Key Lock.
• What is an Intention Lock?
• What is a Deadlock?
• How does MySQL resolve deadlocks?
• Optimistic vs Pessimistic Locking.
• Why does MVCC improve concurrency?

Always explain that InnoDB uses row-level locking together with MVCC, allowing multiple transactions to execute concurrently while maintaining consistency. Mention that Gap Locks and Next-Key Locks are specifically designed to prevent phantom reads under the REPEATABLE READ isolation level.

---

Summary

MySQL locking mechanisms ensure safe concurrent access to data by coordinating reads and writes across transactions. InnoDB provides advanced locking features such as Row Locks, Gap Locks, Next-Key Locks, Intention Locks, and MVCC, enabling high-performance transaction processing with strong consistency guarantees.

Understanding lock types, deadlock handling, optimistic and pessimistic locking, and production troubleshooting techniques is essential for Backend Developers, Database Engineers, and Solution Architects working with enterprise MySQL systems.