Locking Mechanisms & Deadlocks in MySQL

When multiple users access and modify data at the same time, MySQL must ensure that the data remains accurate and consistent.

To manage this safely, MySQL uses locking mechanisms.

Locks control how transactions interact with data and prevent conflicts during concurrent execution. Locking works together with isolation levels to maintain proper transaction behavior.

1. What Is a Lock?

A lock is a control mechanism that restricts access to data while a transaction is using it.

When a transaction reads or modifies data:

  • MySQL may place a lock on that data.
  • Other transactions must follow specific rules before accessing them.

Locks help maintain:

  • Data integrity
  • Consistency
  • Proper isolation between transactions

In most cases, MySQL automatically handles locking when you use INSERT, UPDATE, or DELETE inside transactions.

2. Why Locking Is Necessary

Consider this example:

Two users try to update the same account balance at the same time.

Without locking:

  1. Both read the same balance.
  2. Both update it.
  3. One update overwrites the other.

This is called the lost update problem.

Locking ensures that only one transaction modifies the data at a time, preventing data corruption.

3. Types of Locks in MySQL (InnoDB)

MySQL’s InnoDB storage engine uses different types of locks to manage concurrency.

1. Shared Lock (Read Lock)

A shared lock allows multiple transactions to read the same data.

However:

  • No transaction can modify the locked rows until the lock is released.

Example:

SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;

This allows:

  • Multiple reads
  • No updates until the transaction completes

2. Exclusive Lock (Write Lock)

An exclusive lock is used when a transaction intends to modify data.

It prevents:

  • Other transactions involve modifying the locked rows.

Depending on the isolation level, other transactions may still read previously committed versions of the data, but they cannot update the locked rows.

Example:

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

This locks the selected rows for update until the transaction commits or rolls back.

4. Row-Level Locking vs Table-Level Locking

Locking behavior depends on the storage engine.

InnoDB

  • Uses row-level locking.
  • Locks only the specific rows being modified.
  • Better performance in high-concurrency systems.
  • Suitable for multi-user applications.

MyISAM

  • Uses table-level locking.
  • Locks the entire table during write operations.
  • Simpler but less efficient when many users access the table simultaneously.

This is one of the main reasons InnoDB is the default and preferred storage engine in modern MySQL systems.

5. Intention Locks (Basic Concept)

InnoDB also uses intention locks internally.

These locks indicate that a transaction intends to place row-level locks inside a table.

They help MySQL manage locking efficiently without locking the entire table.

For beginners, it is enough to understand that intention locks support row-level locking and help improve performance.

6. What Is a Deadlock?

A deadlock occurs when two or more transactions wait for each other indefinitely.

Example:

Transaction A:

  • Locks Row 1
  • Attempts to lock Row 2

Transaction B:

  • Locks Row 2
  • Attempts to lock Row 1

Now:

  • Transaction A waits for Transaction B
  • Transaction B waits for Transaction A

Neither can continue. This situation is called a deadlock.

7. How MySQL Handles Deadlocks

InnoDB automatically detects deadlocks.

When a deadlock is detected:

  • One transaction is rolled back.
  • The other transaction continues.

The application should be designed to retry the rolled-back transaction if necessary.

8. Best Practices to Avoid Deadlocks

  1. Access tables and rows in a consistent order.
  2. Keep transactions short.
  3. Avoid unnecessary locks.
  4. Commit or rollback quickly.
  5. Use proper indexing to reduce the time rows remain locked.

9. Real-World Example

In an online shopping system:

  • Two customers attempt to purchase the last available product.
  • MySQL places locks to ensure only one transaction updates the stock.
  • The other transaction must wait or fail safely.

Without locking:

  • Both purchases might succeed.
  • The inventory would become incorrect.

Summary

Locking mechanisms allow MySQL to manage concurrent transactions safely.

Key concepts include:

  • Shared Lock (read lock)
  • Exclusive Lock (write lock)
  • Row-level locking (InnoDB)
  • Table-level locking (MyISAM)
  • Deadlocks and automatic resolution

Locking works together with transactions and isolation levels to maintain data consistency in multi-user environments.

Understanding locking is essential for building reliable and scalable database-driven applications.