MySQL中的锁：从行锁、表锁到元数据锁（MDL）的冲突排查与复杂死锁分析

MySQL中的锁：从行锁、表锁到元数据锁（MDL）的冲突排查与复杂死锁分析

大家好，今天我们来深入探讨MySQL中的各种锁机制，以及如何排查锁冲突，特别是那些复杂的死锁情况。锁是数据库并发控制的核心，理解锁的工作原理对于编写高效、稳定的应用程序至关重要。

1. MySQL锁概览

MySQL为了保证数据的一致性和完整性，在并发环境下使用了多种锁机制。主要分为以下几类：

• 行锁 (Row Lock)：锁定表中的特定行。InnoDB存储引擎支持行锁。
• 表锁 (Table Lock)：锁定整个表。MyISAM存储引擎支持表锁，InnoDB也支持表锁，但通常用于特定的场景。
• 页锁 (Page Lock)：锁定数据页。页锁的粒度介于行锁和表锁之间，但MySQL的InnoDB引擎不直接使用页锁，而是通过行锁来实现类似的功能。
• 元数据锁 (Metadata Lock, MDL)：锁定表的元数据，例如表结构。

2. 行锁

行锁是InnoDB存储引擎的关键特性，可以最大程度地提高并发性能。InnoDB支持两种类型的行锁：

• 共享锁 (Shared Lock, S Lock)：允许事务读取一行数据。多个事务可以同时持有同一行数据的共享锁。
• 排他锁 (Exclusive Lock, X Lock)：允许事务修改或删除一行数据。同一时刻，只能有一个事务持有某一行的排他锁。

行锁的获取方式：

• 隐式锁： 当事务执行更新操作（如UPDATE、DELETE、INSERT）时，会自动对涉及的行加上排他锁。
• 显式锁： 可以使用 SELECT ... FOR SHARE 获取共享锁，使用 SELECT ... FOR UPDATE 获取排他锁。

示例：

-- 事务1
START TRANSACTION;
SELECT * FROM products WHERE id = 1 FOR UPDATE; -- 获取id=1行的排他锁
-- ... 执行更新操作 ...
COMMIT;

-- 事务2
START TRANSACTION;
SELECT * FROM products WHERE id = 1 FOR SHARE; -- 尝试获取id=1行的共享锁 (会被阻塞，直到事务1释放排他锁)
-- ... 执行读取操作 ...
COMMIT;

行锁的冲突与排查：

行锁冲突通常表现为事务长时间等待。可以使用以下方法排查：

• 查看当前锁等待情况：

SHOW ENGINE INNODB STATUS;

在输出结果的 LATEST DETECTED DEADLOCK 或 TRANSACTIONS 部分，可以找到锁等待的信息，包括持有锁的事务ID，等待锁的事务ID，以及相关的SQL语句。

• 使用 performance_schema：

SELECT
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_started AS waiting_since,
    r.trx_query AS waiting_query,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_started AS blocking_since,
    b.trx_query AS blocking_query
FROM
    performance_schema.threads t
    INNER JOIN performance_schema.events_statements_current e ON t.thread_id = e.thread_id
    INNER JOIN information_schema.innodb_lock_waits w ON e.event_id = w.requesting_event_id
    INNER JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
    INNER JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;

这条SQL语句可以清晰地展示等待锁的事务和持有锁的事务的信息，以及相关的SQL语句，便于定位问题。

3. 表锁

表锁会阻塞对整个表的并发访问，因此应该尽量避免使用。MyISAM存储引擎主要使用表锁，InnoDB也支持表锁，但通常只在某些特定的情况下使用，例如：

• LOCK TABLES 语句： 显式地锁定表。
• ALTER TABLE 语句： 修改表结构时，会自动加上表锁。
• 某些优化操作： 某些优化操作可能会使用表锁来提高效率。

示例：

-- 事务1
LOCK TABLES products WRITE; -- 获取products表的写锁 (排他锁)
-- ... 执行更新操作 ...
UNLOCK TABLES;

-- 事务2
LOCK TABLES products READ; -- 尝试获取products表的读锁 (共享锁) (会被阻塞，直到事务1释放写锁)
-- ... 执行读取操作 ...
UNLOCK TABLES;

表锁的冲突与排查：

表锁冲突会导致整个表上的操作被阻塞。排查方法如下：

• 查看当前锁等待情况：

SHOW OPEN TABLES WHERE In_use > 0;

这条语句可以查看当前正在被使用的表，以及被使用的次数。

• 使用 performance_schema：

虽然 performance_schema 主要用于InnoDB，但也可以用来查看表锁相关的等待事件。

SELECT
    event_name,
    object_schema,
    object_name,
    lock_type,
    lock_mode,
    source,
    timer_wait
FROM
    performance_schema.events_waits_summary_global_by_event_name
WHERE
    event_name LIKE 'wait/lock/table%';

这条语句可以查看表锁等待事件的统计信息。

4. 元数据锁 (MDL)

元数据锁 (MDL) 是MySQL 5.5引入的一种锁机制，用于保护表的元数据信息，例如表结构。MDL锁的目的是保证在执行DDL操作（如ALTER TABLE）时，不会出现数据不一致的情况。

MDL的类型：

• MDL_SHARED： 共享锁，用于读操作。
• MDL_SHARED_READ： 共享读锁，用于读操作。
• MDL_SHARED_WRITE： 共享写锁，用于读操作，允许并发的读写操作。
• MDL_EXCLUSIVE： 排他锁，用于写操作，例如ALTER TABLE。
• MDL_SHARED_UPGRADABLE： 共享可升级锁，用于某些特殊操作。

MDL的获取方式：

MDL锁由MySQL服务器自动管理，不需要手动获取。当执行SQL语句时，MySQL会自动根据语句的类型和操作的对象，获取相应的MDL锁。

• SELECT： 获取 MDL_SHARED_READ 锁。
• INSERT、UPDATE、DELETE： 获取 MDL_SHARED_WRITE 锁。
• ALTER TABLE、DROP TABLE： 获取 MDL_EXCLUSIVE 锁。

MDL的冲突与排查：

MDL锁冲突通常发生在执行DDL操作时，由于长时间运行的查询语句持有表的共享锁，导致DDL操作无法获取排他锁而被阻塞。

示例：

1. 一个长时间运行的 SELECT 查询正在执行，持有 MDL_SHARED_READ 锁。
2. 另一个事务尝试执行 ALTER TABLE 操作，需要获取 MDL_EXCLUSIVE 锁。
3. 由于 MDL_SHARED_READ 锁的存在，ALTER TABLE 操作会被阻塞，直到 SELECT 查询结束。

排查方法：

• 查看当前锁等待情况：

SELECT
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_started AS waiting_since,
    r.trx_query AS waiting_query,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_started AS blocking_since,
    b.trx_query AS blocking_query
FROM
    performance_schema.threads t
    INNER JOIN performance_schema.events_statements_current e ON t.thread_id = e.thread_id
    INNER JOIN information_schema.innodb_lock_waits w ON e.event_id = w.requesting_event_id
    INNER JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
    INNER JOIN information_schema.metadata_locks l ON l.object_name = r.trx_tables_in_use
    INNER JOIN information_schema.innodb_trx b ON l.lock_id = b.trx_id; -- 修正了连接条件

这个SQL语句尝试将等待锁的事务与持有MDL锁的事务关联起来，从而找出阻塞DDL操作的查询。 注意：这个SQL语句的有效性依赖于MySQL版本和配置，可能需要调整。

• 查看 processlist：

SHOW PROCESSLIST;

在 processlist 中，可以找到处于 Waiting for table metadata lock 状态的线程，这些线程正在等待MDL锁。

解决MDL锁冲突：

1. Kill 长时间运行的查询： 如果确定长时间运行的查询是导致MDL锁冲突的原因，可以尝试终止该查询。
2. 优化查询： 优化查询，使其运行时间更短，减少持有MDL锁的时间。
3. 分批执行DDL操作： 如果DDL操作涉及大量数据，可以尝试分批执行，避免长时间持有MDL锁。
4. 使用Online DDL： MySQL 5.6及更高版本支持Online DDL，可以在执行DDL操作时，尽量减少对现有查询的影响。
5. 缩短lock_wait_timeout： 可以设置lock_wait_timeout参数，控制事务等待锁的最长时间。超过这个时间，事务会被回滚。但是需要谨慎设置，避免误杀正常事务。

5. 死锁分析与避免

死锁是指两个或多个事务相互等待对方释放锁，导致所有事务都无法继续执行的情况。MySQL会自动检测死锁，并选择回滚其中一个事务来打破死锁。

死锁的常见场景：

• 循环依赖： 事务1持有资源A的锁，并尝试获取资源B的锁；事务2持有资源B的锁，并尝试获取资源A的锁。
• 锁升级： 事务先获取共享锁，然后尝试升级为排他锁，但此时有其他事务持有共享锁。

示例：

-- 事务1
START TRANSACTION;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
SELECT * FROM orders WHERE product_id = 1 FOR UPDATE;
COMMIT;

-- 事务2
START TRANSACTION;
SELECT * FROM orders WHERE product_id = 1 FOR UPDATE;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
COMMIT;

如果事务1先获取了products表中id=1行的排他锁，然后事务2获取了orders表中product_id=1行的排他锁，此时事务1尝试获取orders表中product_id=1行的排他锁，而事务2尝试获取products表中id=1行的排他锁，就会发生死锁。

死锁的排查：

• 查看 SHOW ENGINE INNODB STATUS：

在 LATEST DETECTED DEADLOCK 部分，可以找到死锁的详细信息，包括涉及的事务ID、SQL语句、以及锁信息。

死锁的避免：

1. 保持事务简短： 尽量减少事务的执行时间，减少持有锁的时间。
2. 按固定顺序访问资源： 确保所有事务都按照相同的顺序访问资源，避免循环依赖。例如，在上面的例子中，可以强制所有事务先访问products表，再访问orders表。
3. 避免长时间运行的查询： 长时间运行的查询会持有锁，增加死锁的风险。
4. 使用较低的隔离级别： 如果业务允许，可以使用较低的隔离级别，减少锁的竞争。
5. 合理使用索引： 不合理的索引可能导致锁范围扩大，增加死锁的风险。
6. 避免在同一个事务中混合读写操作： 尽量将读操作和写操作分开，减少锁的竞争。
7. 使用乐观锁： 在某些场景下，可以使用乐观锁来避免死锁。乐观锁通过版本号或时间戳来控制并发，而不是使用锁机制。

6. 一个更复杂的死锁场景分析与解决

假设我们有两个表：accounts (账户表) 和 transactions (交易记录表)。

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    balance DECIMAL(10, 2) NOT NULL
);

CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    from_account_id INT NOT NULL,
    to_account_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (from_account_id) REFERENCES accounts(id),
    FOREIGN KEY (to_account_id) REFERENCES accounts(id)
);

现在，假设有两个并发的转账事务：

• 事务A： 从账户1转账100元到账户2。
• 事务B： 从账户2转账50元到账户1。

以下是可能导致死锁的代码：

-- 事务A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 锁定账户1
INSERT INTO transactions (from_account_id, to_account_id, amount) VALUES (1, 2, 100);
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 锁定账户2
COMMIT;

-- 事务B
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- 锁定账户2
INSERT INTO transactions (from_account_id, to_account_id, amount) VALUES (2, 1, 50);
UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- 锁定账户1
COMMIT;

死锁发生的过程：

1. 事务A 锁定了 accounts 表中 id = 1 的行。
2. 事务B 锁定了 accounts 表中 id = 2 的行。
3. 事务A 尝试锁定 accounts 表中 id = 2 的行，但被事务B阻塞。
4. 事务B 尝试锁定 accounts 表中 id = 1 的行，但被事务A阻塞。

此时，事务A和事务B相互等待对方释放锁，导致死锁。

解决方法：

关键在于统一访问资源的顺序。我们可以强制所有转账操作都按照账户ID的升序来锁定账户。

-- 事务A (改进后)
START TRANSACTION;
-- 按照账户ID升序锁定
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 锁定账户1
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 锁定账户2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
INSERT INTO transactions (from_account_id, to_account_id, amount) VALUES (1, 2, 100);
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 事务B (改进后)
START TRANSACTION;
-- 按照账户ID升序锁定
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 锁定账户1
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 锁定账户2
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
INSERT INTO transactions (from_account_id, to_account_id, amount) VALUES (2, 1, 50);
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

或者使用更简洁的方式：

-- 事务A （更简洁，推荐）
START TRANSACTION;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE; -- 锁定账户1和2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
INSERT INTO transactions (from_account_id, to_account_id, amount) VALUES (1, 2, 100);
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 事务B （更简洁，推荐）
START TRANSACTION;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE; -- 锁定账户1和2
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
INSERT INTO transactions (from_account_id, to_account_id, amount) VALUES (2, 1, 50);
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

解释：

1. SELECT ... FOR UPDATE： 显式地获取排他锁，确保在执行更新操作之前，锁定相关的行。
2. ORDER BY id： 强制按照账户ID升序锁定账户，避免循环依赖。

通过这种方式，无论事务A还是事务B，都会先锁定账户1，再锁定账户2，从而避免了死锁的发生。

总结

理解MySQL的锁机制，是构建高并发数据库应用的基础。行锁提供细粒度的并发控制，表锁则用于特定场景，而元数据锁保护表的元数据。排查锁冲突和死锁，需要结合SHOW ENGINE INNODB STATUS、performance_schema和processlist等工具。避免死锁的关键在于保持事务简短、按固定顺序访问资源，以及合理使用索引和隔离级别。通过深入理解锁的原理和应用，我们可以编写出更高效、更稳定的数据库应用。