MySQL 锁的使用

发表于 阅读次数： 评论数： 本文字数： 5.4k 阅读时长 ≈ 5 分钟

大纲

MySQL 锁的介绍

锁的类型

在 MySQL 中，锁机制可以用来解决事务并发问题。MySQL 的锁有以下几种类型：

  • 按锁的粒度可以分为：
    • 行锁：锁住某行数据，锁粒度最小，并发度高。开销大，加锁慢，会出现死锁。
    • 表锁：锁住整张表，锁粒度较大，并发度低。开销小，加锁快，不会出现死锁。
    • 间隙锁：锁住的是一个区间。
    • 全局锁：锁住整个数据库，锁粒度最大。

  • 按锁的排它性可以分为：

    • 共享锁：也就是读锁，一个事务给某行数据加了读锁，其他事务也可以读，但是不能写。
    • 排它锁：也就是写锁，一个事务给某行数据加了写锁，其他事务不能读，也不能写。

  • 按锁的逻辑可以分为：

    • 乐观锁：并不会真正的去锁住某行记录，而是通过一个版本号来控制数据的并发访问。
    • 悲观锁：比如行锁、表锁等都是悲观锁。

锁的分类

MySQL 中常用的锁主要分为以下几类：

  • (1) 全局锁（Global Lock）

    • 全局锁（Global Lock）：用于在整个数据库级别上进行锁定操作，通常用于数据库备份和维护操作。
      • 整库加读锁：将整个数据库置于只读模式，阻止所有写操作（包括数据插入、更新和删除），但允许读操作。常用于数据库备份、数据导出等场景，以保证数据的一致性。加锁可以使用命令 FLUSH TABLES WITH READ LOCK;，解锁可以使用命令 UNLOCK TABLES;。
      • 整库加写锁：将整个数据库置于写锁控制状态，阻止其他会话对数据库中的表进行读写操作，仅持有锁的会话可以对数据库中的表查询和修改。常用于批量数据修改、数据迁移、数据修复等需要保证操作期间数据一致性的场景。由于 MySQL 不支持直接对整个数据库加写锁，通常需要使用命令 LOCK TABLES table1 WRITE, table2 WRITE, ...; 对数据库中的所有业务表加写锁，完成后使用命令 UNLOCK TABLES; 统一释放锁。特别注意，MySQL 不提供类似 FLUSH TABLES WITH WRITE LOCK; 的全局加写锁命令。

  • (2) 表级锁（Table Locks）

    • 表锁（Table Lock）：将整张表锁定。
      • 表共享锁（读锁）：允许多个事务同时读取同一个表的数据，但不允许任何事务对表进行写操作。加锁可以使用命令 LOCK TABLES table1 READ;，解锁可以使用命令 UNLOCK TABLES;。
      • 表排它锁（写锁）：只允许持有锁的事务对表进行读和写操作，不允许其他事务访问被锁定的表，既不允许读操作，也不允许写操作。加锁可以使用命令 LOCK TABLES table1 WRITE;，解锁可以使用命令 UNLOCK TABLES;。

  • (3) 行级锁（Record Locks）

    • 行锁（Record Lock）：锁定表中的特定行。行锁的粒度更小，允许更高的并发性。主要用于 InnoDB 存储引擎。
      • 行共享锁（读锁）：允许多个事务读取同一行数据，但不能修改。其他事务可以继续加共享锁，但不能加排它锁。加锁可以使用命令 SELECT ... LOCK IN SHARE;。
      • 行排它锁（写锁）：一个事务获取排它锁后，其他事务不能再读取或修改该行。排它锁会阻塞其他事务的共享锁和排它锁请求。加锁可以使用命令 SELECT ... FOR UPDATE;。

  • (4) 意向锁（Intention Locks）

    • 意向锁（Intention Lock）：在表级别上设置的锁，用于表示某事务准备对某些行加共享锁或排它锁。
      • 意向共享锁（IS Lock，Intention Shared Lock）：事务准备加共享锁时，先在表上加一个意向共享锁。
      • 意向排它锁（IX Lock，Intention Exclusive Lock）：事务准备加排它锁时，先在表上加一个意向排它锁。

  • (5) 自增锁（AUTO-INC Locks）

    • 自增锁（AUTO-INC Lock）：用于处理包含 AUTO_INCREMENT 列的插入操作，确保自增列的值唯一性，主要用于 InnoDB 存储引擎。如果有事务回滚操作，数据会回滚，但自增序列不会回滚。

  • (6) 间隙锁（Gap Locks）

    • 间隙锁（Gap Lock）：锁定一个范围，而不是锁定记录本身，主要用于防止幻读现象。间隙锁通常只有在可重复读（REPEATABLE READ）和串行化（SERIALIZABLE）隔离级别下才会使用到。

  • (7) 临键锁（Next-Key Locks）

    • 临键锁（Next-Key Lock）：是行锁和间隙锁的组合，锁定记录以及它前面的间隙，主要用于防止幻读现象。

  • (8) 插入意向锁（Insert Intention Locks）

    • 插入意向锁（Insert Intention Lock）：是一种特殊的间隙锁，多个事务在同一个间隙中插入记录时，不会互相阻塞。

MySQL 表锁的使用注意事项

  • 当 MySQL 客户端断开连接后，MySQL 会直接结束客户端会话，服务器会自动释放该会话持有的全部表锁。
  • LOCK TABLES 会隐式释放当前会话之前持有的所有表锁，并隐式提交所有未提交的事务（如果开启了事务）。
  • 当锁了多张表（例如 LOCK TABLES table1 WRITE, table2 READ;），执行一次 UNLOCK TABLES 会释放全部表锁，MySQL 不支持单独释放某一张表的锁。
  • 当前 MySQL 会话开始一个事务：执行 START TRANSACTION 或 BEGIN 也会隐式地执行 UNLOCK TABLES，释放当前会话持有的全部表锁。这会导致你失去所有表锁，通常用于你打算彻底释放全部锁的场景。

MySQL 行锁的使用注意事项

  • 行锁只在事务中生效，并且会在事务提交（COMMIT）或回滚（ROLLBACK）时一次性释放所有行锁。因此，执行加锁语句前，务必先执行 BEGIN 或 START TRANSACTION。在自动提交模式下，锁会在语句执行后立即释放，这会让行锁无效。
  • InnoDB 的行锁是加在索引项上的。如果你的查询条件（WHERE 子句）无法有效命中索引，InnoDB 会进行全表扫描，这会导致对所有记录加锁，实际效果等同于锁表，严重降低并发性能。因此，确保 WHERE 条件使用了高效的索引是加行锁的前提。
  • UPDATE、DELETE 和 INSERT 语句会自动对它们操作的行加上排他锁（写锁），无需手动使用 SELECT ... FOR UPDATE。这里的 SELECT ... FOR UPDATE 主要用于需要先 "查询并保护" 某些行，以备后续更新或防止其他事务修改的场景。
  • 在默认的可重复读（REPEATABLE READ）隔离级别下，InnoDB 为了防止出现 "幻读"，会在锁定的记录及其前后的 "间隙"（Gap）上添加临键锁（Next-Key Lock），这会锁定一个范围，阻止其他事务在该范围内插入新数据。这与只锁定确切记录的 "记录锁（Record Lock）" 不同。
  • MySQL 中的行锁（Record Lock）、间隙锁（Gap Lock）、临键锁（Next-Key Lock）主要依赖索引实现，是 InnoDB 存储引擎支持的锁机制；意向锁（Intention Lock）属于表级锁，由 InnoDB 自动维护，用于协调表锁与行锁，并不依赖具体索引。

间隙锁是什么

MySQL 的间隙锁（Gap Lock）是 InnoDB 存储引擎实现的锁机制之一，用于避免幻读现象并确保一致性。在可重复读（REPEATABLE READ）隔离级别下，间隙锁是必不可少的。间隙锁的主要特点和作用如下：

  • (1) 锁定范围：间隙锁锁定的是记录之间的空隙。例如，在表中有两条记录 ID 分别为 10 和 20，间隙锁可以锁定 ID 在 10 和 20 之间的范围，使得该范围内不能插入新的记录。
  • (2) 防止幻读：通过锁定记录之间的空隙，间隙锁防止了其他事务在这个空隙内插入新记录，从而避免了幻读现象。在幻读现象中，一个事务在执行多次相同查询时，看到的结果集不同，这是因为其他事务插入了新记录。
  • (3) 实现方式：在 InnoDB 中，间隙锁通常与临键锁（Next-Key Lock）结合使用。临键锁是行锁和间隙锁的组合，用于锁定记录以及它前面的间隙，这样可以确保查询范围内的所有记录和间隙都被锁定。
  • (4) 性能影响：间隙锁在提高一致性的同时，也会影响并发性能，因为它会锁定较大的范围，导致更多的等待和锁冲突。因此，在某些高并发应用中，需要权衡一致性和性能。
  • (5) 事务隔离级别：间隙锁主要在可重复读（REPEATABLE READ）和串行化（SERIALIZABLE）隔离级别下使用。对于读已提交（READ COMMITTED）和读未提交（READ UNCOMMITTED）隔离级别，MySQL 通常不会使用间隙锁，因为这些隔离级别允许一定程度的并发和数据不一致。

总结

间隙锁通过锁定记录之间的空隙，可以防止其他事务在这个空隙（范围）内插入新记录，从而避免幻读现象。

比如，有一个带有索引的表 students，其中包含两条记录 ID 分别为 10 和 20。一个事务执行以下查询：

1
SELECT * FROM students WHERE id between 10 and 20 FOR UPDATE;

此查询会通过间隙锁来锁定 ID 在 10 和 20 之间的间隙，所以其他事务不能在 ID 为 15 的位置插入新记录。

MySQL 锁的使用

行锁的使用

使用语法

行共享锁（读锁）允许多个事务读取同一行数据，但不能修改。其他事务可以继续加共享锁，但不能加排它锁。

1
2
-- 加行共享锁
SELECT * FROM table_name WHERE condition FOR SHARE;

行排它锁（写锁）只允许持有锁的事务对一行数据进行读和写操作，其他事务不能再读取或修改该行。排它锁会阻塞其他事务的共享锁和排它锁请求。

1
2
-- 加行排它锁
SELECT * FROM table_name WHERE condition FOR UPDATE;

使用案例

假设有一张 accounts 表，其表结构如下：

1
2
3
4
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

如果希望防止脏读、不可重复读和幻读，可以使用事务和行锁来实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 事务 1
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- 事务 2
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- 事务 1 可以修改 balance
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 事务 2 在事务 1 提交之前无法继续执行

表锁的使用

使用语法

表共享锁（读锁）允许多个事务同时读取同一个表的数据，但不允许任何事务对表进行写操作。

1
2
3
4
5
6
7
8
-- 使用表共享锁
LOCK TABLES your_table_name READ;

-- 进行读取操作
SELECT * FROM your_table_name;

-- 释放锁
UNLOCK TABLES;

表排它锁（写锁）只允许持有锁的事务对表进行读和写操作，不允许其他事务访问被锁定的表，既不允许读操作，也不允许写操作。

1
2
3
4
5
6
7
8
9
10
11
-- 使用表排它锁
LOCK TABLES your_table_name WRITE;

-- 进行写操作
INSERT INTO your_table_name (column1, column2) VALUES ('value1', 'value2');

-- 或者进行读操作（只有当前事务可以读）
SELECT * FROM your_table_name;

-- 释放锁
UNLOCK TABLES;

使用案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 锁定表以进行写操作
LOCK TABLES employees WRITE;

-- 插入新数据
INSERT INTO employees (name, position) VALUES ('John Doe', 'Manager');

-- 更新数据
UPDATE employees SET position = 'Senior Manager' WHERE name = 'John Doe';

-- 读取表数据（只有当前事务可以读）
SELECT * FROM employees;

-- 释放锁
UNLOCK TABLES;

MySQL 锁的注意事项

使用注意案例一

  • 创建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `t_customer` (
  `id` INT(20) NOT NULL AUTO_INCREMENT,
  `cname` VARCHAR(50) NOT NULL,
  `age` INT(10) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `sex` TINYINT(4) NOT NULL,
  `birth` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

create index idx_cname on t_customer(cname);

INSERT INTO t_customer(cname,age,phone,sex,birth) VALUES('z3',22,'13811112222',1,NOW());
INSERT INTO t_customer(cname,age,phone,sex,birth) VALUES('z4',24,'13811112223',0,NOW());
INSERT INTO t_customer(cname,age,phone,sex,birth) VALUES('z5',25,'13811112224',1,NOW());

  • 操作步骤一（使用聚簇索引 - 主键索引）
MySQL 客户端一 MySQL 客户端二
BEGIN;
UPDATE t_customer SET age=55 WHERE id=4;
UPDATE t_customer SET age=55 WHERE id=5; # 正常执行
或者
UPDATE t_customer SET age=33 WHERE id=6; # 正常执行
或者
UPDATE t_customer SET age=11 WHERE id=4; # 转圈等待
COMMIT;
OK
注意：id 字段建立了主键索引

为什么上面的 MySQL 客户端二在执行第 3 个 UPDATE 操作时，会一直转圈等待呢？这是因为在 t_customer 表中，id 字段建立了主键索引，这导致了 MySQL 客户端一执行 UPDATE 操作时使用了行锁，间接导致 MySQL 客户端二的 UPDATE 操作要等待 MySQL 客户端一执行完成后才能执行。换言之，索引生效后，UPDATE 操作使用的就是行锁。

  • 操作步骤二（使用非聚簇索引 - 普通索引）
MySQL 客户端一 MySQL 客户端二
BEGIN;
UPDATE t_customer SET age=1 WHERE cname=’z3’;
UPDATE t_customer SET age=44 WHERE cNAME=’z4’; #正在执行
或者
UPDATE t_customer SET age=55 WHERE cNAME=’z5’; #正在执行
或者
UPDATE t_customer SET age=11 WHERE cNAME=’z3’ # 转圈等待
或者
UPDATE t_customer SET age=11 WHERE id=4 # 转圈等待
COMMIT;
OK
注意：id 和 cname 字段都建立了普通索引

为什么上面的 MySQL 客户端二在执行第 3 和第 4 个 UPDATE 操作时，会一直转圈等待呢？这是因为在 t_customer 表中，cname 和 id 字段都建立了索引，这导致了 MySQL 客户端一执行 UPDATE 操作时使用了行锁，间接导致 MySQL 客户端二的 UPDATE 操作要等待 MySQL 客户端一执行完成后才能执行。换言之，索引生效后，UPDATE 操作使用的就是行锁。

提示

更多关于聚簇索引与非聚簇索引的介绍，请看 这里 的教程。

使用注意案例二

  • 创建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `t_customer` (
  `id` INT(20) NOT NULL AUTO_INCREMENT,
  `cname` VARCHAR(50) NOT NULL,
  `age` INT(10) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `sex` TINYINT(4) NOT NULL,
  `birth` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

create index idx_cname on t_customer(cname);

INSERT INTO t_customer(cname,age,phone,sex,birth) VALUES('z3',22,'13811112222',1,NOW());
INSERT INTO t_customer(cname,age,phone,sex,birth) VALUES('z4',24,'13811112223',0,NOW());
INSERT INTO t_customer(cname,age,phone,sex,birth) VALUES('z5',25,'13811112224',1,NOW());

  • 操作步骤
MySQL 客户端一 MySQL 客户端二
BEGIN;
UPDATE t_customer SET age=55 WHERE phone=’13811112222’;
UPDATE t_customer SET age=55 WHERE id=5; # 转圈等待。
或者
UPDATE t_customer SET age=44 WHERE id=6; # 转圈等待。
COMMIT;
OK
注意： phone 字段没有建立索引

为什么上面的 MySQL 客户端二在执行 UPDATE 操作时，会一直转圈等待呢？这是因为在 t_customer 表中，并没有给 phone 字段加索引，这导致了 MySQL 客户端一执行 UPDATE 操作时使用了表锁（全表扫描），间接导致 MySQL 客户端二的 UPDATE 操作要等待 MySQL 客户端一执行完成后才能执行。换言之，MySQL 客户端二使用的行锁变成了表锁。

特别注意

在 UPDATE 操作中，如果 WHERE 后面的字段没有建立索引或者索引不生效，那么就会导致其他事务的 UPDATE 操作中的行锁变成表锁，查询性能大大降低。

使用注意案例的总结

InnoDB 的行锁，是通过锁住索引来实现的。如果加锁执行查询或者更新的时候，没有使用到索引，那么就会将整个聚簇索引（B+ 树）都锁住，这相当于锁表了，会大大降低数据库表的性能。