mysql复杂语句加锁,Mysql innodb SQL语句加锁方式

mysql innodb存储引擎支持事务，是mysql的默认存储引擎。

数据库事务

事务是由一组sql语句组成的逻辑单元，完成特定的任务。事务有四个基本属性，通常称为ACID：

Atomic：原子性，组成事务的sql语句要么全部执行，要么全部不执行

Consitensy：事务完成后，数据库处于一致性状态。事务需经过良好的设计，才能让数据保持在一致状态

Isolation：隔离性，并发事务不受彼此干扰，事务处理的中间状态对外部不可见

durable：持久性，一旦事务执行完成，对于数据的修改是永久性的，即使出现故障也能够保持

innodb支持事务，是innodb区别于其他存储引擎最重要的一点，除此之外还有行锁，这也是mysql如此流行的原因。

并发事务的常见问题

对于并发事务来说，能大大提高数据库的性能以及吞吐量，但是并发事务处理，通常存在如下问题：

Lost Update: 当两个事务并发修改同一行时，如果是基于原值基础上的更新，有可能后写入的覆盖了之前一次的写入

脏读： 某个事务读取到了其他事务未提交的值，称之为脏读

不可重复读：在同一个事务里，针对相同的查询语句，返回的数据已经发生了改变，或者被删除，称为不可重复读

幻读：在同一个事务里，按照相同的查询条件，后一次查询返回了比前一次查询更多的结果，发现其他事务插入的新数据，称之为幻读

数据库隔离级别

为了解决并发事务的问题，就有了数据库的隔离级别，不同的隔离级别，解决不同的问题。通常有RU，RC，RR，Serializable四种

RU： read uncommited，读未提交

RC：read commited 读已提交

RR：repeatable read 可重复读

serializable: 串行化

事务的隔离性是通过锁来实现的，mysql事务的默认隔离级别是RR

InnoDB使用的锁

为了支持事务，以及不同的隔离级别，innodb提供了如下类型的锁：

1.共享锁，排他锁(S锁，X锁)S通常指的是读锁，X是写锁。有一点需要提出的是，S锁，X锁不是具体的锁，而是锁的模式，用来'修饰'其他的锁

2.意向锁 意向锁是表锁，Intention Lock有两种模式，意向共享锁和意向排他锁 简称IS和IX

3.Record Lock，记录锁，记录锁也分为S锁和X锁

4.Gap Locks 间隙锁，锁住记录的间隙，解决幻读问题

5.Next-key Locks，邻键锁 = 行锁+间隙锁

6.插入意向锁

7.自增锁 自增列的锁

共享锁排他锁

共享锁(S锁)和排他锁(X锁)的概念在许多编程语言中都出现过。先来描述一下这两种锁在MySQL中的影响结果：

如果一个事务对某一行数据加了S锁，另一个事务还可以对相应的行加S锁，但是不能对相应的行加X锁。

如果一个事务对某一行数据加了X锁，另一个事务既不能对相应的行加S锁也不能加X锁。

行锁，间隙锁，邻键锁

这三种锁描述的都是锁定范围，mysql官方文档的定义如下：

记录锁：记录锁锁定索引的一条记录，对于unique索引，锁住唯一的一行，对于二级索引，锁定索引以及对应的聚集索引

间隙锁：间隙索引要么锁住索引记录中间的值，要么锁住索引第一个索引记录之前的值，或者是最后一个索引之后的值

邻键锁：是索引记录上的锁和在索引记录之前的间隙锁的组合

在定义中，都提到了索引记录，为什么？行锁和索引有什么关系呢？其实，innodb是通过扫描索引来进行加锁操作，innodb会为他

遇到的每一个索引增加共享锁或者排他锁，因此记录锁也称为索引记录锁。row-level lock其实就是index-record lock。行锁其实是加到

相应的索引记录上的

这三种锁的锁定范围不同，逐渐扩大，我们举个例子来说明，假设表test中有索引列3，5，8，9，四个数组值，那对应的锁的锁定范围如下：

记录锁：锁定范围是单独的索引记录，也就是3,5,8,9这四行

间隙锁：间隙锁的锁定范围为行中间隙，为(-∞,3),(3,5),(5,8)(8,9)

邻键锁：索引记录和索引记录前的间隙锁，为(-∞,3],(3,5],(5,8](8,9]

对于间隙锁，需要额外补充如下几点：

1.间隙锁阻止其他事务对间隙的并发插入，这样能有效的解决幻读问题，因此不是所有事务隔离级别都支持间隙锁

2.间隙锁的作用只是阻止其他事务在间隙插入数据，不会阻止其他事务拥有想用的间隙锁，除了insert语句，其他事务对同样的行加

间隙锁不会被阻塞

3.对于唯一索引的加锁行为，间隙锁会失效，只有指定的行锁

mysql 5.7及之前，可以通过information_schema.innodb_locks查看事务的锁情况，但是只能看到阻塞事务的锁，如果事务并未阻塞，在

该表中看不到事务的锁情况。mysql 8.0删除了information_schema.innodb_locks，添加了performance_schema.data_locks,  支持查看未阻塞场景下的对事务持有的锁

innodb常用SQL语句的加锁方式

上面已经介绍了innodb的各种类型的锁，那么不同的sql语句分别加了什么锁呢？

锁的作用是为了解决并发事务的控制问题的。即两个并发执行的事务，如果T1正在修改某些行，那么T2要并发读取、修改、删除，插入满足T1查询条件的行时，T2就必须被阻塞。这就是通过锁来实现的。通常，要么是T1在已存在的行上加index record lock排他锁是的T2无法触碰已存在的行，以及T1在不存在的行上加gap lock是的T2无法插入新的满足条件的行

加什么样的锁，与以下因素相关：

1.数据库的事务隔离级别

2.SQL是一致性非锁定读，还是DML，或锁定读

3.SQL执行是是否用到的索引，以及索引类型(主键索引，唯一索引，普通索引)

下面我们来分析下，不同的隔离级别下，使用不同的索引时，分别加什么锁。

通常，普通SELECT使用(快照读)Snapshot Read，无需加锁。RU和Serializable两个事务隔离级别我们不会用到。

事务隔离级别为RR模式：

如果使用非唯一索引进行搜索或扫描，则在锁扫描的每一个索引记录上都设置next-key lock。这里锁扫描的每一个索引记录是指当扫描执行计划中所使用的的索引时，搜索遇到的每一条记录，where条件是否排除掉某个数据行并没有关系。innodb并不记得确切的where条件，innodb倔强的只认其扫描的索引范围。

这个有点难以理解，对于mysql的执行计划只会选择一个索引，使用一个索引来进行扫描。mysql执行sql语句的流程是先由innodb引擎执行索引扫描，然后把结果返回给mysql服务器，mysql服务器再对该索引条件之外的其他查询条件进行求值，从而得到最终结果集。而加锁时只考虑innodb扫描的索引，非索引的where条件并不考虑。当然，mysql使用index_merge优化时会同时使用多个索引的。

加的锁一般是next key lock，这种锁不仅锁住索引本身，还锁住了每一条索引记录前面的间隙，从而阻止其他事务向索引记录前面紧接着的间隙中插入记录

由于innodb聚集索引的特性，以及普通索引的实现，对普通索引加锁时，会隐含的锁住想用的聚集索引(主键索引)。

如果使用了唯一索引的唯一搜索条件，innodb只在满足条件的索引记录上设置行锁，不锁定前面的间隙。如果使用范围搜索条件，还是会加next key lock锁住间隙

insert在插入的索引记录上加X锁，以及insert intention lock，不会阻止其他事务在插入的记录前的间隙插入新的记录，不同的事务可以向同一索引的间隙插入记录而无需互相等待。一个事务中insert语句会在插入的行的索引记录上设置一把排他锁，如果有键重复的错误发生，则会在重复的索引记录上设置一把共享锁，在多个session同时插入一行，且另外的某个session已经持有了该索引记录的排他锁时，共享锁的使用可能导致死锁的出现。

事务隔离级别为RC模式

事务隔离级别为RC模式时，默认Gap锁被禁用，next key lock也被禁用，只剩下行锁了。