Tenormis's Blog

Appearance

MySQL 锁 ​

1. 全局锁 ​

FLUSH TABLES WITH READ LOCK; 命令会获取一个全局读锁（锁定整个 MySQL 服务器实例，而不仅仅是当前会话所在的数据库），这意味着在锁定期间，其他会话不能修改任何表的数据。它们可以读取数据，但不能执行写操作。

在执行 FLUSH TABLES WITH READ LOCK 后，通常会使用工具（如 mysqldump）来备份数据库。备份完成后，可以使用 UNLOCK TABLES 命令释放读锁，允许其他会话继续修改数据。

1.1. 一致性数据库备份 ​

  1. 获取全局锁

    SQL
    FLUSH TABLES WITH READ LOCK;

  2. 备份数据库

    Bash
    mysqldump -uyour_username -pyour_password your_database > /path/to/save.sql

    Note：mysqldump 是 Bash 命令。

  3. 释放全局锁

    SQL
    UNLOCK TABLES;

上述操作需要谨慎在生产环境中操作：

  • 如果在主库上执行，那么全局锁定、备份期间都不能执行任何写操作，业务基本就得停摆；
  • 如果在从库上执行，那么全局锁定、备份期间从库不能执行主库同步操作，会导致主从延迟；

1.2. 不加锁的一致性数据库备份 ​

在 InnoDB 引擎中，我们可以在备份时加上参数 --single-transaction 参数来实现不加锁的一致性数据库备份：

Bash
mysqldump --single-transaction -uyour_username -pyour_password your_database > /path/to/save.sql

--single-transaction 选项仅适用于支持事务的存储引擎。如果你的表使用 MyISAM 等不支持事务的引擎，这个选项将不起作用。在执行备份时，数据库仍然可以被读取和写入，但备份过程可能会对数据库性能产生一定影响。

它利用了 InnoDB 存储引擎的事务特性来实现不加锁的一致性数据库备份。InnoDB 支持多版本并发控制（MVCC），这意味着在事务启动时，它会创建一个一致性视图。这个视图包含了事务启动时刻的数据库快照。即使在备份过程中数据库发生了变化（如插入、更新或删除操作），备份仍然会基于这个一致性视图来读取数据。

2. 表级锁 ​

在 MySQL 中，表级锁（Table-level Lock）主要包括以下几种类型：

2.1. 表锁（Table Lock） ​

针对整个表加锁，不同线程对同一张表的读写操作会产生冲突。

表锁分类：

  • 读锁（Read Lock）/共享锁（Shared Lock）

    多个线程可以同时读取，但不能写入。加读锁期间，其他线程可以继续加读锁，但不能加写锁。

  • 写锁（Write Lock）/排他锁（Exclusive Lock）

    加锁线程可以进行增删改操作，其他线程无法再加任何锁（读锁或写锁）。写锁释放后，其他线程才能继续操作。

SQL 语法如下：

  • 加锁

    SQL
    LOCK {TABLE | TABLES}
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | WRITE
}

  • 释放锁

    SQL
    UNLOCK {TABLE | TABLES}

2.2. 元数据锁（Metadata Lock，MDL） ​

元数据锁在 MySQL 5.5 及以上版本引入，用于保护表结构元数据的一致性，由 MySQL 自动控制。在执行 ALTER TABLE、DROP TABLE、CREATE INDEX 等操作时，会加 MDL 锁。会阻塞其他线程的 DML 操作（如 SELECT、INSERT、UPDATE、DELETE）。

元数据锁分类：

  • MDL 读锁（共享）

    多个线程可以同时加 MDL 读锁，但不能加写锁；

    • SHARED_READ：在执行 SELECT、SELECT ... FOR SHARE 等查询操作时；
    • SHARED_WRITE：在执行 INSERT、UPDATE、DELETE、SELECT ... FOR UPDATE 等修改操作时；

  • MDL 写锁（排他）

    加写锁期间，其他线程不能加任何 MDL 锁；

    • EXCLUSIVE：在修改表结构时（如 ALTER TABLE）；

查看元数据锁：

SQL
SELECT * FROM performance_schema.metadata_locks;

2.3. 意向锁（Intention Lock） ​

InnoDB 引入的一种表级锁，用于标识事务中是否打算在表内某些行上加行锁（优化表锁检查性能，使得表锁不用逐行检查数据是否加锁）。不会阻塞其他事务的行级锁，但会与表级锁发生冲突。

意向锁分类：

  • 意向共享锁（Intention Shared Lock, IS）

    与表读锁（Read Lock）兼容，与表写锁（Write Lock）互斥。

    事务打算对表中的某些行加共享锁时，先申请 IS 锁。

    例如：在执行 SELECT、SELECT ... FOR SHARE 等查询操作时；

  • 意向排他锁（Intention Exclusive Lock, IX）

    与表读锁（Read Lock）及表写锁（Write Lock）都互斥。

    事务打算对表中的某些行加排他锁时，先申请 IX 锁。

    例如：在执行 INSERT、UPDATE、DELETE、SELECT ... FOR UPDATE 等修改操作时；

Note：意向锁之间不互斥。

查看意向锁及行锁：

SQL
SELECT * FROM performance_schema.data_locks;

3. 行级锁 ​

根据行锁的范围，行级锁分为以下三类：

  • 记录锁（Record Lock）
  • 间隙锁（Gap Lock）
  • 临键锁（Next-Key Lock）

如果根据锁的读写类型分为：

  • 共享锁（S）
  • 排他锁（X）

Tip：对于普通的 SELECT 语句，是不加任何锁的。可以根据需要，手动为 select 语句附加共享锁（SELECT ... FOR SHARE/SELECT ... LOCK IN SHARE MODE）或排他锁（SELECT ... FOR UPDATE）。

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用临键锁（Next-Key Lock）进行搜索和索引扫描：

  • 针对唯一索引进行检索时：

    • 对已存在的记录进行等值匹配时，将会自动优化为记录锁；
    • 对不存在的记录进行等值匹配时，将会自动优化为间隙锁；

  • 针对普通索引进行等值匹配时，向右遍历时最后一个值不满足查询需求时，将退化为间隙锁。

  • InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁。

查看意向锁及行锁：

SQL
SELECT * FROM performance_schema.data_locks;

3.1. 记录锁（Record Lock） ​

记录锁（Record Lock）是作用于索引记录本身的锁，确保事务在读取或修改某条具体记录时，其他事务无法对其进行更改。Record Lock 仅锁定索引上的具体行，而不是范围。

假设有一个 users 表：

SQL
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

INSERT INTO users VALUES (1, 'Alice', 25), (2, 'Bob', 30), (3, 'Charlie', 35);

事务 A 执行：

SQL
START TRANSACTION;
SELECT * FROM users WHERE id = 2 FOR UPDATE;

此时事务 A 对 id = 2 的行加了 Record Lock，事务 B 不能更新 id = 2 的记录，必须等待事务 A 提交或回滚后才能执行。但事务 B 仍然可以插入 id = 4，因为 Record Lock 只锁定已有的记录，而不影响间隙。

3.2. 间隙锁（Gap Lock） ​

间隙锁（Gap Lock）锁定的是索引中的一个范围，而不包含记录本身，即锁住某个索引范围之间的 “空隙”，防止其他事务在该范围内插入数据。主要用于防止幻读（Phantom Read）。仅在 REPEATABLE READ 级别下的 Next-Key Lock 机制中生效（Next-Key Lock = Record Lock + Gap Lock）。

假设 users 表已有如下数据：

Text
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | Alice   | 25  |
|  3 | Bob     | 30  |
|  5 | Charlie | 35  |
+----+---------+-----+

事务 A 执行：

SQL
START TRANSACTION;
SELECT * FROM users WHERE id > 1 AND id < 5 FOR UPDATE;

事务 A 可能加上的锁：

  • Record Lock：锁定 id = 3；
  • Gap Lock：锁定 (1,3) 和 (3,5) 之间的范围；

影响：

  • 事务 B 不能插入 id = 2 或 id = 4，因为 (1,3) 和 (3,5) 之间的间隙被锁住；
  • 但事务 B 仍然可以更新 id = 1 或 id = 5，因为它们不在锁定的间隙范围内；

Tip：在实际应用中，如果你不想让 Gap Lock 影响插入操作，可以使用 READ COMMITTED 隔离级别，因为它不会使用 Gap Lock。

3.3. 范围锁（Range Lock） ​

Range Lock（范围锁）是 SERIALIZABLE 隔离级别下的一种锁机制，它能彻底防止幻读问题。它的作用类似于 Gap Lock，但更加严格。它不仅锁住索引范围，还锁住整个查询范围，使得其他事务不能在该范围内进行任何增删改操作（包括 DELETE + INSERT 并发问题）；

假设 users 表：

Text
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | Alice   | 25  |
|  3 | Bob     | 30  |
|  5 | Charlie | 35  |
+----+---------+-----+

事务 A 在 SERIALIZABLE 级别执行：

SQL
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM users WHERE age BETWEEN 26 AND 34;

事务 A 使用 Range Lock 锁定整个范围 age∈[26,34]。事务 B 无法插入 (id=4, age=28)，因为 Range Lock 锁住了查询范围。同时，事务 B 无法删除 id = 3，因为 Range Lock 也锁住了原始数据。

转载请注明作者和出处

Copyright © 2025-至今 Tenormis