Mysql 锁和锁算法

相关命令：

  • show engines;  查看数据库支持的引擎
  • show variables like '%storage_engine%';   查看数据库默认的引擎
  • select @@global.tx_isolation; 查询数据库的隔离级别
  • show variables like 'innodb_autoinc_lock_mode';  获取到当前自增长锁的模式
  • select * from information_schema.INNODB_LOCKS;  获取到当前加锁情况
  • show InnoDB status;  查看最近死锁的日志。

InnoDB 锁类型

S or X (共享锁&排他锁)

在 InnoDB 中实现了两个标准的行级锁，可以简单的看为两个读写锁：

  • S 共享锁：又叫读锁，其他事务可以继续加共享锁，但是不能继续加排他锁。

  • X 排他锁：又叫写锁，一旦加了写锁之后，其他事务就不能加锁了。

IS or IX (共享&排他)意向锁

意向锁在 InnoDB 中是表级锁，意向锁分为：

  • 意向共享锁：表达一个事务想要获取一张表中某几行的共享锁。

  • 意向排他锁：表达一个事务想要获取一张表中某几行的排他锁。

这个锁有什么用呢？为什么需要这个锁呢？

首先说一下如果没有这个锁，要给这个表加上表锁，一般的做法是去遍历每一行看看它是否有行锁，这样的话效率太低。而我们有意向锁，只需要判断是否有意向锁即可，不需要再去一行行的去扫描。

兼容性：是指事务 A 获得一个某行某种锁之后，事务 B 同样的在这个行上尝试获取某种锁，如果能立即获取，则称锁兼容，反之叫冲突。 

纵轴是代表已有的锁，横轴是代表尝试获取的锁。

自增长锁

自增长锁是一种特殊的表锁机制，提升并发插入性能。对于这个锁有几个特点：

  • 在 SQL 执行完就释放锁，并不是事务执行完。

  • 对于 insert...select 大数据量插入会影响插入性能，因为会阻塞另外一个事务执行。

  • 自增算法可以配置。

在 MySQL 中 innodb_auto_inclock_mode 有 3 种配置模式 0、1、2，分别对应：

  • 传统模式：使用表锁。

  • 连续模式：对于插入的时候可以确定行数的使用互斥量，对于不能确定行数的使用表锁的模式。

  • 交错模式：所有的都使用互斥量，为什么叫交错模式呢，有可能在批量插入时自增值不是连续的，当然一般来说如果不看重自增值连续一般选择这个模式，性能是最好的。

InnoDB 锁算法 

记录锁(Record-Lock)

记录锁是锁住记录的，锁住的是索引记录，而不是我们真正的数据记录：

  • 如果锁的是非主键索引，会在自己的索引上面加锁之后然后再去主键上面加锁锁住。

  • 如果表上没有索引(包括没有主键)，则会使用隐藏的主键索引进行加锁。

  • 如果要锁的没有索引，则会进行全表记录加锁。 

间隙锁

间隙锁顾名思义锁间隙，不锁记录。锁间隙的意思就是锁定某一个范围，间隙锁又叫 gap 锁，其不会阻塞其他的 gap 锁，但是会阻塞插入间隙锁，这也是用来防止幻读的关键。

next-key 锁 

这个锁本质是记录锁加上 gap 锁。在 RR 隔离级别下(InnoDB 默认)，InnoDB 对于行的扫描锁定都是使用此算法，但是如果查询扫描中有唯一索引会退化成只使用记录锁。

因为唯一索引能确定行数，而其他索引不能确定行数，需要使用间隙锁防止其他事务中再次添加这个索引的数据造成幻读。RR 隔离级别下，InnoDB 使用 Next-Key Lock 算法避免了幻读。

插入意向锁

插入意向锁是在插入的时候产生的，在多个事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。 

假设有一个记录索引包含键值 4 和 7，不同的事务分别插入 5 和 6，每个事务都会产生一个加在 4-7 之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

这里要说明的是如果有间隙锁了，插入意向锁会被阻塞。

MVCC 

MVCC 参考：https://www.cnblogs.com/wade-luffy/p/8686883.html

MVCC，多版本并发控制技术。在 InnoDB 中，在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号。通过版本号和行锁，从而提高数据库系统并发性能。 

在 MVCC 中，对于读操作可以分为两种读：

  • 快照读：读取的历史数据，简单的 select 语句，不加锁，MVCC 实现可重复读，使用的是 MVCC 机制读取 undo 中的已经提交的数据。所以它的读取是非阻塞的。

  • 当前读：需要加锁的语句，update，insert，delete，select...for update 等等都是当前读。

在 RR 隔离级别下的快照读，不是以 begin 事务开始的时间点作为 snapshot 建立时间点，而是以第一条 select 语句的时间点作为 snapshot 建立的时间点。以后的 select 都会读取当前时间点的快照值。

在 RC 隔离级别下总是读取被锁定行最新的快照数据。

具体的原理是通过每行会有两个隐藏的字段一个是用来记录当前事务，一个是用来记录回滚的指向 Undolog。利用 Undolog 就可以读取到之前的快照，不需要单独开辟空间记录。

死锁检测 

死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。说明有等待才会有死锁，解决死锁可以通过去掉等待，比如回滚事务。

解决死锁的两个办法：

  • 等待超时：当某一个事务等待超时之后回滚该事务，另外一个事务就可以执行了。

    但是这样做效率较低，会出现等待时间，还有个问题是如果这个事务所占的权重较大，已经更新了很多数据了，但是被回滚了，就会导致资源浪费。

  • 等待图(wait-for-graph)：等待图用来描述事务之间的等待关系，当这个图如果出现回路如下：事务就出现回滚，通常来说 InnoDB 会选择回滚权重较小的事务，也就是 undo 较小的事务。

 

如何防止死锁：

  • 以固定的顺序访问表和行。交叉访问更容易造成事务等待回路。

  • 尽量避免大事务，占有的资源锁越多，越容易出现死锁。建议拆成小事务。

  • 降低隔离级别。如果业务允许(上面也分析了，某些业务并不能允许)，将隔离级别调低也是较好的选择，比如将隔离级别从 RR 调整为 RC，可以避免掉很多因为 gap 锁造成的死锁。

  • 为表添加合理的索引。防止没有索引出现表锁，出现死锁的概率会突增。

实例分析

数据库事务隔离选择了 RR。

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(11) CHARACTER SET utf8mb4 DEFAULT NULL,
  `comment` varchar(11) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


insert user select 20,333,333;
insert user select 25,555,555;
insert user select 20,999,999;

demo 1 select 普通索引 锁竞争

发现在事务 A 中给 555 加了 next-key 锁，事务 B 插入的时候会首先进行插入意向锁的插入。

事务 B 由于间隙锁和插入意向锁的冲突，导致了阻塞。

demo 2 select 唯一索引

上面查询条件用的是普通的非唯一索引，现在试下主键索引：

非唯一索引加 next-key 锁由于不能确定明确的行数有可能其他事务在你查询的过程中，再次添加这个索引的数据，导致隔离性遭到破坏，也就是幻读。

唯一索引由于明确了唯一的数据行，所以不需要添加间隙锁解决幻读。

demo 3 select 没有索引 锁竞争

上面测试了主键索引，非唯一索引，再试下一个没有索引

如果用没有索引的数据，其会对所有聚簇索引上都加上 next-key 锁。

平常开发的时候如果对查询条件没有索引的，一定进行一致性读，也就是加锁读，会导致全表加上索引，会导致其他事务全部阻塞，数据库基本会处于不可用状态。

demo 4 delete 死锁

时间点 2：事务 A 删除 name = '777' 的数据，需要对 777 这个索引加上 next-key 锁，但是其不存在。

所以只对 555-999 之间加间隙锁，同理事务 B 也对 555-999 之间加间隙锁。间隙锁之间是兼容的。

时间点 3：事务 A，执行 insert 操作，首先插入意向锁，但是 555-999 之间有间隙锁。

由于插入意向锁和间隙锁冲突，事务 A 阻塞，等待事务 B 释放间隙锁。事务 B 同理，等待事务 A 释放间隙锁。于是出现了 A->B，B->A 回路等待。

时间点 4：事务管理器选择回滚事务 A，事务 B 插入操作执行成功。

间隙锁带来的死锁问题解决思路

方案一：隔离级别降级为 RC，在 RC 级别下不会加入间隙锁，所以就不会出现毛病了，但是在 RC 级别下会出现幻读，所以这个方案不行。

方案二：隔离级别升级为可序列化，不会出现这个问题，但是在可序列化级别下，性能会较低，会出现较多的锁等待，同样的也不考虑。

方案三：修改代码逻辑，不要直接删(因为删除会加next-key锁)，改成每个数据由业务逻辑去判断哪些是更新，哪些是删除，那些是添加(减少锁时间)，这个工作量稍大，所以这个方案可选。

方案四：较少的修改代码逻辑，在删除之前，可以通过快照查询(不加锁)，如果查询没有结果，则直接插入；如果有通过主键进行删除，通过唯一索引会降级为记录锁，所以不存在间隙锁。

 

posted @ 2018-09-22 13:41  wade&luffy  阅读(2994)  评论(0)  编辑  收藏  举报