六、MySQL锁机制与优化实践

玉衡子
62 阅读10分钟

MySQL锁机制与优化实践

锁是计算机协调多个进程或线程并发访问某一资源的机制。 在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类

  • 从性能上分为乐观锁(用版本对比或CAS机制)和悲观锁，乐观锁适合读操作较多的场景，悲观锁适合写操作较多的场景，如果在写操作较多的场景使用乐观锁会导致比对次数过多，影响性能
  • 从对数据操作的粒度分，分为表锁、页锁、行锁
  • 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)，还有意向锁

读锁（共享锁，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锁，对整个表加排他锁之前，需要先获取到意向排他锁。

表锁

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

基本操作

--建表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');

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

页锁

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

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。 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的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录。

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙，间隙锁在读未提交和读已提交级别下是不会生效的，mysql8中读已提交和串行化都生效。

上一个章节说过，Mysql默认级别是repeatable-read，有幻读问题，间隙锁是可以解决幻读问题的，主要是因为即使给所有的行都加上行锁，仍然无法解决幻读问题，因此引入了间隙锁的概念。

假设account表里数据如下： 转存失败，建议直接上传图片文件

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，在Session_1下面执行如下sql： select * from account where id = 18 for update; 则其他Session没法在这个(10,20)这个间隙范围里插入任何数据。

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

临键锁(Next-key Locks)

Next-Key Locks是行锁与间隙锁的组合。 可以理解为间隙锁锁如果锁的 (3,10)，那么next-key locks锁的就是 (3,10]。

加锁规则

MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。

原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。 原则 2：查找过程中访问到的对象才会加锁。 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

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

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

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

锁等待分析

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

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系统库锁相关数据表

-- 查看事务
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;

死锁问题分析

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锁机制是数据库并发控制的核心，本文主要介绍了以下内容：

  1. 锁的分类：

    • 从性能角度：乐观锁与悲观锁
    • 从粒度角度：表锁、页锁、行锁
    • 从操作类型：读锁(共享锁)、写锁(排他锁)、意向锁

  2. 不同粒度锁的特点：

    • 表锁：开销小，加锁快，不会死锁，但并发度低
    • 页锁：开销和并发度适中，会出现死锁
    • 行锁：开销大，加锁慢，会出现死锁，但并发度高

  3. InnoDB特有的锁机制：

    • 行锁是针对索引加的锁，索引失效会导致锁升级
    • 间隙锁(Gap Lock)解决幻读问题
    • 临键锁(Next-key Locks)是行锁与间隙锁的组合

  4. 锁等待与死锁分析：

    • 通过系统变量和信息表分析锁争用情况
    • 死锁的产生与处理方法

  5. 锁优化策略：

    • 合理使用索引避免锁升级
    • 控制事务大小和检索范围
    • 选择合适的隔离级别

通过合理使用MySQL的锁机制，可以在保证数据一致性的同时提高系统的并发处理能力。

avatar
文章
阅读
粉丝