Understanding and Managing MySQL Locks: Types, Detection, and Deadlock Prevention

1. Introduction

MySQL is widely used as a database management system, but when multiple queries access the same data, a locking mechanism is triggered. Locks are essential for maintaining data consistency, but incorrect management can lead to deadlocks and performance degradation.

This article explains the fundamental concepts of locks in MySQL, detailing how to check lock status, release locks, and prevent deadlocks.

What You’ll Learn

• Types of MySQL locks and their effects
• Version-specific methods for checking locks
• Safe procedures for releasing locks
• Practical strategies to prevent deadlocks

Let’s begin with the basic concept of MySQL locks.

2. Basic Concepts of MySQL Locks

In databases, a “lock” is a mechanism that restricts access to maintain data integrity when multiple transactions attempt to modify data simultaneously. Improper lock management may cause performance issues or deadlocks.

2.1 Main Types of Locks

MySQL provides several lock types depending on the level of data protection required.

Row Lock

• Locks only specific rows, minimizing impact on other transactions.
• Supported only by the InnoDB engine.
• Occurs when using SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE.

Table Lock

• Locks the entire table, preventing multiple queries from executing simultaneously.
• Commonly used by the MyISAM engine.
• Triggered by the LOCK TABLES statement.

Intention Lock

• Coordinates row and table locks to avoid conflicts.
• Used only in InnoDB and managed automatically.

Deadlock

• Occurs when multiple transactions wait for each other’s locks indefinitely.
• Improper transaction design can cause the process to halt.

2.2 Examples of Lock Occurrence

The following examples show how locks occur in actual SQL queries.

Row Lock Example

Executing the SQL below locks specific rows.

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- Other sessions cannot update this row until COMMIT or ROLLBACK is executed

If another session tries to update the same row, it will enter a waiting state (lock contention).

Table Lock Example

To lock an entire table, use the following command:

LOCK TABLES products WRITE;
-- Prevents other sessions from modifying the products table until the lock is released

Until this lock is released, no other user can modify data in the products table.

Deadlock Example

A typical deadlock scenario looks like this:

-- Session 1
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
-- Waiting for Session 2...

-- Session 2
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
-- Waiting for Session 1...

-- Session 1 (executes next)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here

In this case, each transaction is waiting for the other to release a lock, resulting in a deadlock.

3. Checking MySQL Lock Status (by Version)

To determine whether locks are active, use commands appropriate for your MySQL version.

3.1 MySQL 5.6 and Earlier

In MySQL 5.6 and earlier, use SHOW ENGINE INNODB STATUSG; to check lock details.

SHOW ENGINE INNODB STATUSG;

This command displays detailed information about current locks.

3.2 MySQL 5.7

From MySQL 5.7, it’s easier to use the sys.innodb_lock_waits table.

SELECT * FROM sys.innodb_lock_waits;

This query shows which transactions are currently waiting for locks.

3.3 MySQL 8.0 and Later

In MySQL 8.0 and newer, you can obtain more detailed information using performance_schema.data_locks.

SELECT * FROM performance_schema.data_locks;

To identify which session is holding the lock:

SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = <process_id>;

This helps pinpoint the process responsible for the lock.

4. How to Release Locks in MySQL (With Risks Explained)

If a lock occurs in MySQL and is not released properly, it can stall processes and reduce database performance.
This section explains how to release locks safely and the potential risks involved.

4.1 Identifying Sessions Holding Locks

Before releasing a lock, identify which session is holding it. Use the following SQL to check sessions waiting for locks:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE='Waiting for table metadata lock';

This query lists sessions currently waiting for table metadata locks.

In MySQL 8.0 and later, you can get detailed lock data with:

SELECT * FROM performance_schema.data_locks;

4.2 Releasing Locks with the KILL Command

Once you identify the session holding a lock, you can terminate the process to release it.

1. Check processes holding locks

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;

2. End the session using the KILL command

KILL <process_id>;

Example: To terminate process ID=12345, execute:

KILL 12345;

⚠️ Risks of Using KILL

• Terminated transactions will ROLLBACK automatically
• For instance, unfinished UPDATE operations may discard pending changes.
• May cause application errors
• If frequent KILL operations are needed, review your application’s transaction design.

4.3 Releasing Locks Safely with ROLLBACK

Before using KILL, try to end the transaction manually when possible.

1. Check current sessions

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. Identify the problematic transaction and execute ROLLBACK

ROLLBACK;

This method safely releases locks while maintaining data consistency.

4.4 Automating Lock Release with SET innodb_lock_wait_timeout

Instead of manually releasing locks, you can configure a timeout so that locks automatically expire after a certain period.

SET innodb_lock_wait_timeout = 10;

This setting causes a transaction to terminate automatically if a lock is not released within 10 seconds, preventing long stalls.

5. Key Points and Best Practices for MySQL Locks

Proper lock management minimizes deadlocks and performance degradation. Below are best practices for efficient lock handling.

5.1 Preventing Deadlocks

To prevent deadlocks, follow these principles:

1. Keep a consistent transaction order

• Always update multiple tables in the same sequence.
• Example:

-- OK: Always update orders → customers
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

× Wrong: Inconsistent order causes deadlocks

-- Session 1
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;

-- Session 2 (executed in reverse order → possible deadlock)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

2. Keep transactions short

• Always COMMIT or ROLLBACK promptly
• Long-running transactions block others and increase deadlock risk.

3. Use proper indexing

• Indexes reduce the scope of locked rows, minimizing unnecessary locks.
• Example: Adding an index to customer_id in the orders table ensures only relevant rows are locked.

CREATE INDEX idx_customer_id ON orders (customer_id);

6. Summary

• MySQL locks include row, table, and intention locks. Mismanagement can cause deadlocks and slow performance.
• Lock-checking methods differ across MySQL versions.
• Be cautious when releasing locks!
• Try ROLLBACK before using KILL.
• Use SET innodb_lock_wait_timeout to automate lock release.
• Prevent deadlocks by keeping consistent transaction order and short transaction times.

7. FAQ

Q1. What’s the easiest way to check MySQL lock status?

• A1. In MySQL 8.0+, use SELECT * FROM performance_schema.data_locks; to view lock status easily.

Q2. How should I handle deadlocks?

• A2. Run SHOW ENGINE INNODB STATUSG; to identify the cause, then adjust transaction order to prevent recurrence.

Q3. Can the KILL command corrupt data?

• A3. Forced termination triggers ROLLBACK for unfinished transactions, which may affect consistency. Use with caution.

Q4. How can I prevent deadlocks?

• A4. Apply these rules:
• Keep consistent transaction order
• Use short transactions
• Set proper indexes

Q5. How can I improve MySQL performance by reducing locks?

• A5.
• Design efficient indexes to minimize locks
• Use short transactions to reduce lock time
• Avoid full-table locks (LOCK TABLES)
• Leverage read replicas to distribute read loads