暗夜零星
暗夜零星

聊聊Mysql锁机制

Eddie Eddie 收录于 性能调优 和 Mysql调优
 约 5000 字 预计阅读 10 分钟  - 次阅读  

Mysql 锁机制，读锁、写锁、意向锁。表锁、行锁、间隙锁、临键锁等。

锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，数据作为一种需要被多个用户共享的资源，其并发访问的一致性和有效性至关重要。因此，所有数据库系统都必须解决数据并发访问带来的问题，其中锁冲突是影响数据库性能的关键因素。

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁机制也有较大区别。

Mysql中锁的分类

  1. 按性能分类

    • 乐观锁：通过版本比对或CAS机制实现，适用于读操作较多的场景。
    • 悲观锁：更适用于写操作较多的场景，因为在写操作频繁的情况下，乐观锁会因比对次数过多而影响性能。

  2. 按数据操作粒度分类

    • 表锁
    • 页锁
    • 行锁

  3. 按数据库操作类型分类

    • 读锁（属于悲观锁）
    • 写锁（属于悲观锁）
    • 意向锁

读锁（共享锁，S锁(Shared)）：同一份数据，多个读操作可以同时进行，不会互相影响（和写锁是互斥的）。比如：

select * from T where id=1 lock in share mode

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁。数据修改操作都会加写锁，查询也可以通过for update加写锁。比如：

select * from T where id=1 for update

意向锁（Intention Lock）：又称I锁，针对表锁，为提高加表锁效率，MySQL会在事务对表中行数据加共享或排他锁时，给表设一个标识，表明已有行锁。其他事务加表锁时，直接读此标识，无需逐行检查冲突，从而快速决定是否可加表锁。此标识即意向锁，特别适用于记录多的表。

意向锁主要分为：

  • 意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。
  • 意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

基本操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 建表SQL
CREATE TABLE `mylock` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR (20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

-- 插入数据
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');
1
2
3
4
5
6

-- 手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
-- 查看表上加过的锁
show open tables;
-- 删除表锁
unlock tables;

页锁

只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

行锁可能变为临键锁

SELECT * FROM table WHERE id = 1 FOR UPDATE;

id 为 1 的记录行会被锁住。id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。

同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

InnoDB相对于MYISAM的最大不同有两点：

  • InnoDB支持事务（TRANSACTION）
  • InnoDB支持行级锁

注意：InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会升级为表锁) 比如我们在RR级别执行如下sql

select * from account where name = 'lilei' for update; --where条件里的name字段无索引

则其它Session对该表任意一行记录做修改操作都会被阻塞住。

PS：关于RR级别行锁升级为表锁的原因分析

因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录。

分析：聚簇索引与行锁

  1. 聚簇索引与行锁的关系：

    • InnoDB的聚簇索引保存了数据行的全部信息，并且每个表只能有一个聚簇索引。当表有聚簇索引时，数据行实际上存放在索引的叶子页中。
    • 行锁在InnoDB中是基于索引实现的，当执行更新操作时，InnoDB会尝试在涉及的索引上加锁，以确保数据的一致性。

  2. 更新操作与行锁：

    • 当更新操作通过聚簇索引进行时，行锁会直接加在聚簇索引上。
    • 如果更新操作是通过二级索引进行的，InnoDB会首先在二级索引上加锁，然后根据二级索引找到对应的聚簇索引记录，并在聚簇索引上加锁。这是因为二级索引不包含数据的全部信息，所以需要进一步访问聚簇索引来完成更新。

  3. 锁定的具体行为：

    • 如果更新条件能够通过索引（无论是聚簇索引还是二级索引）直接定位到具体的记录，InnoDB会在这些记录上加行锁。
    • 如果更新条件导致索引失效，或者没有合适的索引可用，InnoDB可能会退化为使用表锁，锁定整个表，而不是单独的行。

  4. 总结：

    • 更新操作所加的行锁，在大多数情况下，会涉及到聚簇索引。如果更新是通过聚簇索引进行的，锁会直接加在聚簇索引上；如果是通过二级索引进行的，锁会首先加在二级索引上，然后延伸到聚簇索引。
    • 锁的具体行为还受到查询条件、索引设计以及事务隔离级别等因素的影响。
SELECT … FOR UPDATE 一定会对聚簇索引加锁吗？

在InnoDB存储引擎中，即使查询可以通过二级索引（如name_age联合索引）获取到所需的所有数据，当执行SELECT ... FOR UPDATE语句时，InnoDB仍然会在聚簇索引上对应的数据行上加锁。这是因为InnoDB的行锁是基于聚簇索引实现的，以确保数据的一致性。

  1. 是否会在聚簇索引上加锁？

    • 是的，即使查询可以通过二级索引完成，InnoDB仍然会在聚簇索引上对应的行上加锁。这是为了确保在事务期间对该行的任何更改（包括通过聚簇索引的更改）都会被阻塞，从而保持数据的一致性。

  2. 如果不加锁的话，其他UPDATE语句通过聚簇索引更新数据是不是就能成功？

    • 理论上，如果InnoDB不在聚簇索引上加锁，那么其他UPDATE语句通过聚簇索引更新同一条数据可能不会被阻塞。然而，由于InnoDB的行锁机制，这种情况实际上不会发生。InnoDB总是会在聚簇索引上加锁，以防止其他事务通过任何方式（包括通过聚簇索引）修改被锁定的行。

因此，即使查询可以通过name_age联合索引获取所需数据，执行SELECT ... FOR UPDATE时，InnoDB仍然会在聚簇索引上对应的数据行加锁，以确保数据的一致性和事务的隔离性。这意味着其他试图更新同一行的UPDATE语句（无论是通过聚簇索引还是其他索引）都将被阻塞，直到当前事务完成并释放锁。

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙，间隙锁是在可重复读隔离级别下才会生效。

Mysql默认级别是repeatable-read，有幻读问题，间隙锁是可以解决幻读问题的。

假设account表里数据如下：

id name balance
1 lilei 0
2 hanmei 10000
3 lucy 80000
10 zhuge 666
20 zhangsan 2000

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，在Session_1下面执行如下sql：

select * from account where id = 18 for update;

则其他Session没法在这个(10,20)这个间隙范围里插入任何数据。

如果执行下面这条sql：

select * from account where id = 25 for update;

则其他Session没法在这个(20,正无穷)这个间隙范围里插入任何数据。

也就是说，只要在间隙范围内锁了一条不存在的记录会锁住整个间隙范围，不锁边界记录，这样就能防止其它Session在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。

临键锁(Next-Key Locks)

MySQL中的临键锁（Next-Key Lock）是一种特殊的行锁，它结合了记录锁（Record Lock）和间隙锁（Gap Lock）的特性，用于解决并发事务中的数据一致性问题。

临键锁主要在可重复读（MySQL的默认隔离级别）和序列化隔离级别下被使用。在这些隔离级别下，为了维护数据的一致性和隔离性，MySQL会自动采用临键锁来锁定相关记录及其间隙。

  • 当执行INSERT操作时，如果存在唯一键约束，MySQL会自动添加临键锁来确保插入的数据不会违反这一约束。如果多个线程尝试插入具有相同唯一键值的记录，临键锁会阻止其中一个线程直到另一个线程完成插入操作为止。
  • 在可重复读（REPEATABLE READ）或序列化（SERIALIZABLE）事务隔离级别下，对索引进行范围查询（如使用>、<、BETWEEN等操作符）并命中索引时，会触发临键锁。这是因为范围查询不仅锁定了查询到的记录，还锁定了这些记录之间的间隙，以防止其他事务插入新的记录导致幻读。（这里的查询是指当前读）
  • 即使是等值查询，如果查询条件命中索引且存在多条记录，或者查询条件虽然等值但数据不存在（此时会锁定间隙），也可能触发临键锁。
注意
临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

小结

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。 另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

锁等待

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

show status like 'innodb_row_lock%';

对各个状态量的说明如下：
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：
Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

查看INFORMATION_SCHEMA系统库锁相关数据表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁，8.0之后需要换成这张表performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;  
-- 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  

-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

-- 查看锁等待详细信息
show engine innodb status;

死锁问题

1
2
3
4
5
6

set tx_isolation='repeatable-read';
Session_1执行：select * from account where id=1 for update;
Session_2执行：select * from account where id=2 for update;
Session_1执行：select * from account where id=2 for update;
Session_2执行：select * from account where id=1 for update;
查看近期死锁日志信息：show engine innodb status;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁，这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉。

锁优化

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能减少检索条件范围，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
  • 尽可能用低的事务隔离级别
收录于 合集・Mysql调优 9
更新于 2023-05-27 
查看源码编辑此页报告问题
MysqlLock
返回 | 主页
0%