MysqlInnodb中的锁

本文主要涉及以下三个部分：

1. 为什么要加锁

2. 锁的分类

3. 常见语句的加锁分析

4. 如何分析死锁

5. 如何预防死锁

先列出我本地的运行环境

数据库版本是5.7，隔离级别是Repeatable-Read(可重复读)，不同的数据库版本和隔离级别对语句的执行结果影响很大。讨论锁的时候不指明版本和隔离级别，都是耍流氓。

一、为什么要加锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

锁是用于管理对公共资源的并发控制。也就是说并发的情况下，会出现资源竞争，所以需要加锁。

举个例子，转账操作。简单来说，张三给李四转账x元，可以分为三步：


1，先查询张三的账户余额y是否大于x

2，张三的余额 y = y &＃8211; x元

3，李四的余额 x = z + x元

假设张三账户余额有1000元，李四余额也有1000元，如果不加锁的话，同时有两个请求，A要求转500元，B要求转600元，第一步查询余额都是足够的，第2步和第3步也能执行成功，但是最终结果却是错误，第二个请求可能会覆盖掉第一个请求。

这种问题叫做 丢失更新： 多个事务操作同一行，后面的更新覆盖了前面的更新值。需要在应用级别加锁来避免。

数据库有ACID原则，其中I是隔离性，标准SQL规范中定义了四种隔离级别：

越往下，隔离级别越高，问题越少，同时并发度也越低。隔离级别和并发度成反比的。

1. 脏读：事务A读取了事务B未提交的数据
2. 不可重复读：对于一条记录，事务A两次读取的数据变了
3. 幻读：事务A按照相同的查询条件，读取到了新增的数据

MySQL中的隔离级别如下：

和标准SQL规范相比，MySQL中可重复读解决了幻读，实现了串行化隔离级别的功能，同时没有严重影响并发。是通过加锁、阻止插入新数据，来解决幻读的。

二、锁的分类

我们听说过读锁、写锁、共享锁、互斥锁、行锁等等各种名词，根据自己的理解，简单对这些锁进行了分类。

加锁机制：

1、乐观锁：先修改，保存时判断是够被更新过，应用级别

2、悲观锁：先获取锁，再操作修改，数据库级别

锁粒度：

表级锁：开销小，加锁快，粒度大，锁冲突概率大，并发度低，适用于读多写少的情况。

页级锁：BDB存储引擎

行级锁：Innodb存储引擎，默认选项

兼容性：

S锁，也叫做读锁、共享锁，对应于我们常用的 select * from users where id =1 lock in share mode


X锁，也叫做写锁、排它锁、独占锁、互斥锁，对应对于select * from users where id =1 for update

下面这个表格是锁冲突矩阵，可以看到只有读锁和读锁之间兼容的，写锁和读锁、写锁都是冲突的。

冲突的时候会阻塞当前会话，直到拿到锁或者超时

这里要提到的一点是，S锁 和 X锁是可以是表锁，也可以是行锁

索引组织表

先理解下索引组织表。

辅助索引

聚集索引

Innodb中的索引数据结构是 B+ 树，数据是有序排列的，从根节点到叶子节点一层层找到对应的数据。普通索引，也叫做辅助索引，叶子节点存放的是主键值。主键上的索引叫做聚集索引，表里的每一条记录都存放在主键的叶子节点上。当通过辅助索引select 查询数据的时候，会先在辅助索引中找到对应的主键值，然后用主键值在聚集索引中找到该条记录。举个例子，用name=Alice来查询的时候，会先找到对应的主键值是18 ，然后用18在下面的聚集索引中找到name=Alice的记录内容是 77 和 Alice。

表中每一行的数据，是组织存放在聚集索引中的，所以叫做索引组织表。

了解索引数据结构的目的是为了说明，行锁是加在索引上的。

1.select * from user where id=10 for update

一条简单的SQL。在user表中查找id为10的记录，并用for update加X锁。

这里User表中，有3个字段， 主键id 和 另外一个字段name。下面的表格是B+树索引的简化表达。第一行id是索引的节点，第二行和第三行是这行记录，包含了姓名和性别。

如图所示，通过锁住聚集索引中的节点来锁住这条记录。

聚集索引上的锁，比较好理解，锁住id=10的索引，即锁住了这条记录。

2. select * from user where name=‘b’ for update

查询user表中name为d的记录，并用for update加X锁

这里的name上加了唯一索引，唯一索引本质上是辅助索引，加了唯一约束。所以会先在辅助索引上找到name为d的索引记录，在辅助索引中加锁，然后查找聚集索引，锁住对应索引记录。

为什么聚簇索引上的记录也要加锁？试想一下，如果有并发的另外一个SQL，是直接通过主键索引id=30来更新，会先在聚集索引中请求加锁。如果只在辅助索引中加锁的话，两个并发SQL之间是互相感知不到的。

3. select * from user where name=‘b’ for update

查询user表中name为b的记录，并用for update加X锁。这里name上加了普通的索引，不是唯一索引。普通索引的值是可以重复的。会先在辅助索引中找到name为b的两条记录，加X锁，然后得到主键值7和30，到聚集索引中加X锁。

事情并没有那么简单，如果这时有另一个事务，插入了name=b,id=40的记录，却发现是可以插入的。

位置在途中红色线条标注的间隙内，这样就会出现幻读，两次查询得到的结果是不一致的，第一次查到两条数据，插入之后得到三条数据。

为了防止这种情况，出现了另一种锁，gap lcok 间隙锁。锁住的是索引的间隙。

即图中，红色线条标识的空隙。因为新插入name=b的记录，可能出现在这三个间隙内。

这张图里出现了三种锁

记录锁：单行记录上的锁

间隙锁：锁定记录之间的范围，但不包含记录本身。

Next Key Lock: 记录锁+ 间隙锁，锁定一个范围，包含记录本身。

4. 意向锁( Intention Locks )

InnoDB为了支持多粒度(表锁与行锁)的锁并存，引入意向锁。意向锁是表级锁，

IS: 意向共享锁
IX: 意向排他锁

事务在请求某一行的S锁和X锁前，需要先获得对应表的IS、IX锁。

意向锁产生的主要目的是为了处理行锁和表锁之间的冲突，用于表明“某个事务正在某一行上持有了锁，或者准备去持有锁”。比如，表中的某一行上加了X锁，就不能对这张表加X锁。

如果不在表上加意向锁，对表加锁的时候，都要去检查表中的某一行上是否加有行锁，多麻烦。

意向锁的兼容性矩阵

5. 插入意向锁（Insert Intention Lock）

Gap Lock中存在一种插入意向锁，在insert操作时产生。

有两个作用：

• 和next-key互斥，阻塞next-key 锁，防止插入数据，这样就不会幻读。
• 插入意向锁互相是兼容的，允许相同间隙、不同数据的并发插入

三、常见语句的加锁分析

后面会有多个SQL语句，先说明一下表结构

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`id_no` varchar(255) DEFAULT NULL COMMENT '身份证号',
`name` varchar(255) DEFAULT NULL COMMENT '姓名',
`mobile` varchar(255) DEFAULT NULL COMMENT '手机号',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`address` varchar(255) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_id_no` (`id_no`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=utf8 COMMENT='用户表';

这里有一个user表，5个字段，其中id是主键，id_no是身份证号，加了唯一索引，name是用户姓名，可以重复的，加了普通索引，手机号、年龄、地址都没有索引。

1. 普通select

select * from user where id =1;
begin;
select * from user where id =1;
commit:

普通的select 语句是不加锁的。select包裹在事务中，同样也是不加锁的。where后面的条件不管多少，普通的select是不加锁的。

2. 显式加锁

select * from user where id =1 lock in share mode;
select * from user where id =1 for update;

显式指出要加什么样的锁。上面一个加的是共享锁，下面的是互斥锁。

这里需要强调的一点，需要明确在事务中是用这些锁，不在事务中是没有意义的。

3. 隐式加锁

update user set address '北京' where id=1;
delete from user where id=1;

update和delete也会对查询出的记录加X锁，隐式加互斥锁。加锁类型和for update 类似

后面只按照显式加锁的select for update 举例子，更新和删除的加锁方式是一样的。

4. 按索引类型

elect * from user where id =1 for update;
select * from user where id_no ='a22' for update;
select * from user where name ='王二' for update;
select * from user where address ='杭州' for update;

四条SQL，区别在于where条件的过滤列，分别是主键、唯一索引、普通索引、无索引。

主键：之前提到过索引组织表，这里会在聚集索引上对查询出的记录，加X锁

唯一索引：会在辅助索引上，把在对应的id_no=a22的索引加X锁，因为是唯一的，所以不是next-key锁。然后在主键上，也会在这条记录上加X锁。

普通索引：因为不是唯一的，会在辅助索引上，把对应的id_no=a22的索引加next-key锁。然后在主键加X锁。

无索引：首先，是不推荐这种写法，没有索引的话，因为会全表扫描，数据量大的话查询会很慢。这里讨论的是，这种情况下，会加什么锁？ 答案： 首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。这是一个很恐怖的事情，请注意。

5. 记录不存在的情况

前面几个例子中，都是可以查到结果的。如果对应记录不存在会怎样？答案是锁住间隙，不允许插入。mysql要保证没有其他人可以插入，所以锁住间隙。

6. 普通 insert 语句

在插入之前，会先在插入记录所在的间隙加上一个插入意向锁。

insert会对插入成功的行加上排它锁，这个排它锁是个记录锁，而非next-key锁（当然更不是gap锁了），不会阻止其他并发的事务往这条记录之前插入 。

7. 先查询后插入

类似于这样的insert

insert into target_table select * from source_table ...
create target_table select * from source_table ...

将select查询的结果集，插入到另一张表中，或者使用结果集，创建一个新表。

和之前简单插入的情况类似，已插入成功的数据加X锁，间隙加上一个插入意向锁。

对于select的源表中的记录，会加共享的 next-key 锁。这是为了防止主从同步出问题。

举个例子：

session1 先开启事务，然后查询user2表的结果集，插入到user表中，session2开启事务，在插入user2中插入数据，所插入的数据刚好是session1能查询到的数据，如果不加锁的话，session2可以插入成功，然后session2提交事务，接着session1提交数据。这样看起来是没问题的，但是session2先提交的，所以bin log中会这样记录，先在user2表中插入数据，然后在user中插入数据，这样的bin log在从库执行的时候，就会出问题。

主库： user2插入一条数据，user 插入一条数据

从库： user2插入一条数据，user 插入两条数据

user表会比主库多一条数据。所以需要锁住select查询表中加next-key锁，不允许user2表中新增数据。

四、分析当前锁的情况

先说一下死锁的定义，死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。这个定义适用于数据库，有几个重点，两个或两个以上的事务，一个事务是不会出现死锁的。争夺的资源一般都是表或者记录。

出现死锁了会怎样，正常情况下，mysql会检查出死锁，并回滚某一个事务，让另一个事务正常运行。

Mysql 会回滚副作用小的事务，判定的标准是执行的时间以及影响的范围。

1.如何知道系统有没有发生过死锁，如何去查看发生过的锁

show status like ‘innodb_row_lock%&＃8217;; 从系统启动到现在的数据

Innodb_row_lock_current_waits：当前正在等待锁的数量；

Innodb_row_lock_time ：锁定的总时间长度，单位ms；

Innodb_row_lock_time_avg ：每次等待所花平均时间；

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；

Innodb_row_lock_waits ：从系统启动到现在总共等待的次数。

平均时间和锁等待次数比较大的话，说明可能会存在锁争用情况

2. show engine innodb status

展示innodb存储引擎的运行状态

通过这个命令显示的内容比较多，其中有一项lasted detected deadlock 显示最近发生的死锁。

图中红色线条标注的是执行的SQL，以及加了什么锁，可以看出是在这行记录上加了X锁，没有gap锁。

3. 错误日志中查看历史发生过的死锁

set global innodb_print_all_deadlocks=1;

上一个命令，只能看到最近发生的锁，如果我想看历史发生的锁怎么办？ 执行这一句，更改innodb 的一个配置，innodb_print_all_deadlocks，打印所有的死锁。会将死锁的信息输出到mysql的错误日志中，默认是不输出，格式和show engine innodb status 是差不多的。

4. information_schema.innodb_locks

information_schema 数据库是mysql自带的，保存着关于MySQL服务器所维护的所有其他数据库的信息。其中innodb_locks表，记录了事务请求但是还没获得的锁，即等待获得的锁。

lock_id：锁的id，由锁住的空间id编号、页编号、行编号组成

lock_trx_id：锁的事务id。

lock_mode：锁的模式。S[,GAP], X[,GAP], IS[,GAP], IX[,GAP]

lock_type：锁的类型，表锁还是行锁

lock_table：要加锁的表。

lock_index：锁住的索引。

lock_space：innodb存储引擎表空间的id号码

lock_page：被锁住的页的数量，如果是表锁，则为null值。

lock_rec：被锁住的行的数量，如果表锁，则为null值。

lock_data：被锁住的行的主键值，如果表锁，则为null值。

5. information_schema.innodb_lock_waits

查看等待中的锁

requesting_trx_id：申请锁资源的事务id。

requested_lock_id：申请的锁的id。

blocking_trx_id：阻塞的事务id，当前拥有锁的事务ID。

blocking_lock_id：阻塞的锁的id，当前拥有锁的锁ID

6. information_schema.innodb_trx

查看已开启的事务

trx_id：innodb存储引擎内部事务唯一的事务id。

trx_state：当前事务的状态。

trx_started：事务开始的时间。

trx_requested_lock_id：等待事务的锁id，如trx_state的状态为LOCK WAIT，那么该值代表当前事务之前占用锁资源的id，如果trx_state不是LOCK WAIT的话，这个值为null。

trx_wait_started：事务等待开始的时间。

trx_weight：事务的权重，反映了一个事务修改和锁住的行数。在innodb的存储引擎中，当发生死锁需要回滚时，innodb存储引擎会选择该值最小的事务进行回滚。

trx_mysql_thread_id：正在运行的mysql中的线程id，show full processlist显示的记录中的thread_id。

trx_query：事务运行的sql语句

五、预防死锁

1. 以相同的顺序更新不同的表

这样执行的话，会出现锁等待，但不容易出现死锁。

假设有这么两个接口，增加老师和学生的幸运值、减少老师和学生的幸运值，这个需求是我造出来的，先别管需求是不是合理。

现在有两个请求，一个增加幸运值，一个降低幸运值，如果更新顺序不同的话，就是这样，第一个事务先给老师加幸运值，第二个接口给学生减幸运值，然后第一个事务给学生加幸运值，因为锁已经被第二个事务持有了，所以第一个事务等待。然后第二个事务给老师幸运值，这时就互相等待锁，出现了死锁。

2. 预先对数据进行排序

比如一个接口批量操作数据，如果乱序的话，并发的情况下，也是有可能出现死锁的。给学生批量加分的接口，按照表格中的执行顺序的话，第一个事务，持有A的锁，请求B的锁，第二个事务持有B的锁，请求A的锁，出现死锁。

3. 直接申请足够级别的锁，而非先共享锁，再申请排他锁。

比如这种情况，两个事务，先申请共享锁，共享锁是兼容的，然后申请互斥锁的时候，需要互相等待，就出现了死锁。

4. 事务的粒度及时间尽量保持小，这样锁冲突的概率就小了，也就不容易出现死锁。不建议在数据库的事务中执行API调用。

5. 正确加索引。没有索引会引起全表扫描，类似于锁表。

六：总结：

1，正确的加索引，尽量先查询，然后使用主键去加锁，等于操作来加锁，而尽量避免辅助索引，或者不是范围比较来加锁。

2，出现了锁的问题，根据数据库已有的信息，分析死锁。

3，举了几个例子，可能很多都是上线之后才发现的，最好能在开发阶段就避免死锁。