Practical MySQL & MariaDB

MySQL Locking Explained: Check Lock Status, Release Locks Safely, and Prevent Deadlocks (5.6–8.0)

Server Management
目次

1. Introduction

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

In this article, we will explain the fundamental concepts of locking in MySQL and provide detailed guidance on how to check lock status, how to release locks, and how to prevent deadlocks.

What You Will Learn in This Article

  • The types of MySQL locks and their impact
  • How to check locks by MySQL version
  • Safe procedures for releasing locks
  • Practical advice to prevent deadlocks

Let’s begin by explaining the basic concepts of MySQL locking.

2. Basic Concepts of MySQL Locking

In a database, a “lock” is a mechanism that restricts access in order to maintain data integrity when multiple transactions attempt to modify data simultaneously. If locks are not managed properly, performance degradation and deadlocks may occur.

2.1 Main Types of Locks

In MySQL, there are several types of locks depending on the level of data protection required.

Row Lock

  • Locks only specific rows, minimizing the impact on other transactions.
  • Supported only by the InnoDB storage engine.
  • Triggered 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 with the MyISAM storage engine.
  • Triggered when using the LOCK TABLES statement.

Intention Lock

  • A lock used to coordinate row locks and table locks to avoid conflicts.
  • Used only in InnoDB and managed automatically.

Deadlock

  • A state where multiple transactions are waiting for each other’s locks.
  • If transactions are not designed properly, processing may stop completely.

2.2 Examples of Lock Occurrence

Let’s look at specific SQL queries to understand how locks occur.

Row Lock Example

Executing the following SQL will lock a specific row.

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

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

Table Lock Example

To lock an entire table, use the following command.

LOCK TABLES products WRITE;
-- Other sessions cannot modify the products table until all read/write operations are complete

Until this lock is released, other users cannot modify data in the products table.

Deadlock Example

The following shows a typical deadlock scenario.

-- 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 another statement)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here

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

3. How to Check MySQL Lock Status (By Version)

To determine whether locks are occurring, you need to execute commands appropriate for your MySQL version.

3.1 How to Check Locks in MySQL 5.6 and Earlier

In MySQL 5.6 and earlier, you can check lock information using SHOW ENGINE INNODB STATUS\G;.

SHOW ENGINE INNODB STATUS\G;

Executing this command displays detailed information about currently active locks.

3.2 How to Check Locks in MySQL 5.7

In MySQL 5.7 and later, the easiest method is to use the sys.innodb_lock_waits table.

SELECT * FROM sys.innodb_lock_waits;

By querying this table, you can identify which transactions are waiting for locks.

3.3 How to Check Locks in MySQL 8.0 and Later

In MySQL 8.0 and later, you can retrieve more detailed lock information using performance_schema.data_locks.

SELECT * FROM performance_schema.data_locks;

To identify the session holding the lock, use the following SQL:

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

This allows you to pinpoint the process responsible for the lock.

4. How to Release MySQL Locks (Risks Explained)

If a lock occurs in MySQL and is not handled properly, processing may stall and database performance may degrade.
In this section, we will explain how to release locks and the risks involved.

4.1 Identifying the Session Holding the Lock

Before releasing a lock, you must identify which session is holding it. Use the following SQL to check sessions experiencing lock waits:

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

This query lists sessions that are waiting for a lock.

In MySQL 8.0 and later, you can obtain detailed lock information using:

SELECT * FROM performance_schema.data_locks;

4.2 Releasing Locks Using the KILL Command

Once you identify the session holding the lock, you can release it by forcibly terminating the process.

1. Check the process holding the lock

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

2. Terminate the session using the KILL command

KILL <process_id>;

For example, to terminate a process with ID=12345, execute:

KILL 12345;

⚠️ Risks of the KILL Command

  • Forcefully terminated transactions are rolled back
  • For example, changes made by an interrupted UPDATE statement may be discarded.
  • It may cause application errors
  • If you frequently need to use KILL, you should review your application design.

4.3 Releasing Locks with ROLLBACK (Safer Method)

Before using the KILL command, if possible, try manually ending the transaction causing the lock.

1. First, check the current transactions

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. If you find the problematic transaction, execute ROLLBACK

ROLLBACK;

This method allows you to release the lock while maintaining data consistency.

4.4 Automating Lock Handling with SET innodb_lock_wait_timeout

Instead of manually releasing locks, you can configure a lock wait timeout so that the transaction automatically times out if the lock is not released within a specified time.

SET innodb_lock_wait_timeout = 10;

With this setting, if the lock is not released within 10 seconds, MySQL returns an error and automatically ends the transaction.

5. Important Notes and Best Practices for MySQL Locks

Proper lock management helps reduce the risk of deadlocks and performance degradation. Below are best practices to minimize locks and manage them efficiently.

5.1 How to Prevent Deadlocks

To prevent deadlocks, keep the following points in mind:

1. Standardize the Transaction Execution Order

  • For example, when updating multiple tables, always update them in the same order.
  • Example:
-- OK: Always update in the order orders → customers
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

× NG: Different execution orders can cause 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 (deadlock may occur if executed in reverse order)
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

  • Commit or roll back as quickly as possible
  • Avoid long-running transactions, as they can negatively impact other processes.

3. Set Appropriate Indexes

  • Creating proper indexes helps avoid unnecessary locks.
  • Example: Adding an index on customer_id in the orders table ensures that only specific rows are locked.
CREATE INDEX idx_customer_id ON orders (customer_id);

6. Summary

  • MySQL locks include row locks, table locks, and intention locks. Improper management can lead to deadlocks and performance issues.
  • The method for checking lock status varies depending on the MySQL version, so choose the appropriate approach for your environment.
  • Be cautious when releasing locks!
  • Try ROLLBACK before using the KILL command.
  • Use SET innodb_lock_wait_timeout to automatically handle lock timeouts.
  • To prevent deadlocks, standardize transaction execution order and keep transactions short.

7. FAQ (Frequently Asked Questions)

Q1. What is the easiest command to check MySQL lock status?

  • A1. In MySQL 8.0 and later, use SELECT * FROM performance_schema.data_locks; to easily check lock status.

Q2. What should I do if a deadlock occurs?

  • A2. First, run SHOW ENGINE INNODB STATUS\G; to identify the cause of the deadlock. Then review and standardize the transaction execution order to prevent recurrence.

Q3. Can using the KILL command corrupt data?

  • A3. When forcibly terminating a session, unfinished transactions are rolled back, which may affect data consistency. Use it with caution.

Q4. How can I prevent deadlocks?

  • A4. The following methods are effective:
  • Standardize transaction execution order
  • Keep transactions short
  • Set appropriate indexes

Q5. How can I reduce locks and improve MySQL performance?

  • A5.
  • Design proper indexes to reduce unnecessary locks
  • Keep transactions short to minimize lock duration
  • Avoid full table locks (LOCK TABLES)
  • Use read replicas to distribute read workloads