Practical MySQL & MariaDB

MySQL 锁机制详解：检查锁状态、安全释放锁并防止死锁（5.6–8.0）

伺服器設定與管理
目次

1. 介绍

MySQL 被广泛用作数据库管理系统，但当多个查询尝试访问相同的数据时，会触发锁机制。锁对于保持数据一致性至关重要；然而，管理不当可能导致死锁和性能下降。

在本文中，我们将解释 MySQL 锁的基本概念，并提供关于 如何检查锁状态、如何释放锁以及如何防止死锁 的详细指南。

本文您将学到的内容

  • MySQL 锁的类型及其影响
  • 如何按 MySQL 版本检查锁
  • 安全的释放锁的步骤
  • 防止死锁的实用建议

让我们从解释 MySQL 锁的基本概念 开始。

2. MySQL 锁的基本概念

在数据库中，“锁”是一种限制访问的机制，用于在多个事务同时尝试修改数据时保持数据完整性。如果锁管理不当，可能导致性能下降和死锁。

2.1 锁的主要类型

在 MySQL 中，根据所需的数据保护级别，存在 多种锁类型。

行锁

  • 仅锁定特定行，最大限度地减少对其他事务的影响。
  • 仅由 InnoDB 存储引擎支持。
  • 在使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 时触发。

表锁

  • 锁定整个表，阻止多个查询同时执行。
  • 通常与 MyISAM 存储引擎一起使用。
  • 在使用 LOCK TABLES 语句时触发。

意向锁

  • 用于协调行锁和表锁以避免冲突的锁。
  • 仅在 InnoDB 中使用，且自动管理。

死锁

  • 多个事务相互等待对方的锁的状态。
  • 如果事务设计不当，处理可能会完全停止。

2.2 锁发生的示例

让我们查看具体的 SQL 查询，以了解锁是如何产生的。

行锁示例

执行以下 SQL 将锁定特定行。

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

如果另一个会话尝试更新同一行，它将进入锁等待状态（锁争用）。

表锁示例

要锁定整个表，请使用以下命令。

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

在此锁被释放之前，其他用户无法修改 products 表中的数据。

死锁示例

以下展示了一个典型的死锁场景。

-- 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

在这种情况下，每个事务都在等待对方释放锁，导致死锁。

3. 如何检查 MySQL 锁状态（按版本）

要确定是否存在锁，需要执行适用于您 MySQL 版本的命令。

3.1 如何在 MySQL 5.6 及更早版本中检查锁

在 MySQL 5.6 及更早版本中，您可以使用 SHOW ENGINE INNODB STATUS\G; 来检查锁信息。

SHOW ENGINE INNODB STATUS\G;

执行此命令会显示当前活动锁的详细信息。

3.2 如何在 MySQL 5.7 中检查锁

在 MySQL 5.7 及以后版本中，最简便的方法是使用 sys.innodb_lock_waits 表。

SELECT * FROM sys.innodb_lock_waits;

通过查询此表，您可以识别哪些事务正在等待锁。

3.3 如何在 MySQL 8.0 及更高版本中检查锁

在 MySQL 8.0 及更高版本中，您可以使用 performance_schema.data_locks 获取更详细的锁信息。

SELECT * FROM performance_schema.data_locks;

要识别持有锁的会话，请使用以下 SQL：

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

这使您能够定位导致锁的进程。

4. 如何释放 MySQL 锁（风险说明）

如果 MySQL 中出现锁且未得到妥善处理，处理可能会停滞，数据库性能可能下降。
在本节中，我们将解释如何释放锁以及涉及的风险。

4.1 识别持有锁的会话

在释放锁之前，必须识别哪个会话持有锁。使用以下 SQL 检查正在等待锁的会话：

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

此查询列出正在等待锁的会话。

在 MySQL 8.0 及更高版本中，您可以使用以下方式获取详细的锁信息：

SELECT * FROM performance_schema.data_locks;

4.2 使用 KILL 命令释放锁

一旦识别出持有锁的会话，您可以通过强制终止进程来释放锁。

1. 检查持有锁的进程

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

2. 使用 KILL 命令终止会话

KILL <process_id>;

例如，要终止 ID=12345 的进程，执行以下操作：

KILL 12345;

⚠️ KILL 命令的风险

  • 被强制终止的事务会回滚
  • 例如，被中断的 UPDATE 语句所做的更改可能会被丢弃。
  • 可能导致应用程序错误
  • 如果您经常需要使用 KILL，应审查您的应用程序设计。

4.3 使用 ROLLBACK 释放锁（更安全的方法）

在使用 KILL 命令之前，若可能，请尝试 手动结束导致锁的事务。

1. 首先，检查当前事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. 如果发现有问题的事务，执行 ROLLBACK

ROLLBACK;

此方法可在保持数据一致性的同时释放锁。

4.4 使用 SET innodb_lock_wait_timeout 自动处理锁

您可以配置锁等待超时，而不是手动释放锁，使 如果锁在指定时间内未释放，事务会自动超时。

SET innodb_lock_wait_timeout = 10;

使用此设置后，如果锁在 10 秒内未释放，MySQL 将返回错误并自动结束事务。

5. MySQL 锁的重要注意事项和最佳实践

适当的锁管理有助于 降低死锁和性能下降的风险。以下是最小化锁并高效管理锁的最佳实践。

5.1 如何防止死锁

为防止死锁，请牢记以下要点：

1. 统一事务执行顺序

  • 例如，在更新多个表时，始终以相同的顺序更新它们。
  • 示例：
    -- 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：不同的执行顺序可能导致死锁

-- 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