mysql知识（锁和事务）

最新推荐文章于 2024-09-23 19:32:48 发布

jliang981

最新推荐文章于 2024-09-23 19:32:48 发布

阅读量1.1k

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/liang0000zai/article/details/80374919

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

mysql知识

几篇不错的博客 link
死锁分析
 mysql总体介绍

锁

mysql中的锁有好几种，按照作用范围分为行锁和表锁，gap锁，next-key lock，intention lock（意向锁）。按照作用性质，又分为共享锁和排它锁。还有虚拟的乐观锁与悲观锁。
首先要明白，mysql的锁其实是对索引加锁，如果查询语句中没有索引，则对表加锁。（讨论的是select for update语句）
现总结几个简单的规则：使用主键或者索引进行等值查询的时候，如果没有查出记录不加锁，否则对查出的数据加行锁。
如果进行like查询或者不等值值查询，不管有没有索引，都要加表锁（可能是这种查询设计的行比较多，直接加了表锁）。
如果一条sql语句涉及了多个索引，查询出的结果要对每个索引项都要加锁。这时如果表有多个索引，不同的查询走不同的索引，虽然访问的是不同记录，也是有可能冲突的。（实际项目中就碰到过一次）
锁表的情况：会对所有的记录加排它锁，也会在记录间加上gap锁。此时mysql也会做一些优化，详见[semi-consistent read原理]
更加详细的规则，可以参考这个博客link

如果一条语句涉及多条记录，这些记录是分开加锁的，即对一条记录加锁完成后，才处理下一条。并且一条记录涉及到对多个索引加锁时，也是一个索引一个索引的加。
在事务中加的锁，

Index Condition Pushdown(ICP) 若支持ICP，则不满足Index Filter的记录，无需加记录X锁，因为不满足条件的记录在存储引擎里就能过滤掉；

具体加不加锁，加什么锁，跟数据库当前的隔离级别也有密切的关系。

gap锁
可重复读隔离级别就是通过gap锁实现的。因为mysql的锁定是有序的，gap锁锁住了位置，就能保证其他数据不能插入进来。
对于使用唯一索引锁定行以搜索唯一行的语句，不需要使用间隙锁定。

next-key lock
索引记录上的下一个键锁定还会影响该索引记录之前的“间隙”。也就是说，下一个键锁定是索引记录锁定，并在索引记录之前的间隔上加上间隙锁定。
默认情况下，InnoDB在REPEATABLE READ事务隔离级别下运行。在这种情况下，InnoDB使用next-key锁进行搜索和索引扫描，这可以防止幻象行（Phantom Rows）；

意向锁
InnoDB支持多个粒度锁定，允许行锁和表锁共存。为了在多个粒度级别实现锁定，InnoDB使用意向锁。意向锁是表级锁，用于指示事务对表中某一行的事务需要哪种类型的锁（共享或排他）。
意向锁定协议如下：
在事务可以获取表中某一行的共享锁之前，它必须先获得一个IS锁或更强的表。
在一个事务可以获得一个表中某一行的排它锁之前，它必须首先在该表上获得一个IX锁

意向锁不会阻止除完整表请求之外的任何内容（例如，LOCK TABLES … WRITE）。意向锁的主要目的是显示某人正在锁定一行，或者要锁定表中的一行。

的人可能会对意向锁的目的并不是完全的理解，我们在这里可以举一个例子：如果没有意向锁，当已经有人使用行锁对表中的某一行进行修改时，如果另外一个请求要对全表进行修改，
那么就需要对所有的行是否被锁定进行扫描，在这种情况下，效率是非常低的；不过，在引入意向锁之后，当有人使用行锁对表中的某一行进行修改之前，会先为表添加意向互斥锁（IX），
再为行记录添加互斥锁（X），在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需要通过等待意向互斥锁被释放就可以了。

Two-Phase Locking
加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。MySQL为例，来简单看看2PL在MySQL中的实现。
在事务中加的锁，只有在commit之后才会统一释放掉。
link

死锁
死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，
分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。

MVCC Lock-Based CC（给予锁的并发控制）

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
当前读，读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

MySQL 就通过文章中提到的回滚日志（undo log）实现了 MVCC。[回滚日志并不能将数据库物理地恢复到执行语句或者事务之前的样子]；它是逻辑日志，当回滚日志被使用时，它只会按照日志逻辑地将数据库中的修改撤销掉看，
可以理解为，我们在事务中使用的每一条 INSERT 都对应了一条 DELETE，每一条 UPDATE 也都对应一条相反的 UPDATE 语句。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)
select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values ();
update table set ? where ?;
delete from table where ?;
所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。