mysql隔离级别和mysql的加锁情况分析

mysql在并发比较大的时候，锁等待，死锁的情况的可能会出出现。锁等待和事物特性也有一定的关系，故这篇文章希望从锁的隔离级别和加锁的情况来介绍mysql。

一、事务特性

1.原子性
事务是一个原子操作单元，事务中包含的所有操作要么都做，要么都不做，没有第三种情况。

2.一致性
事务操作前和操作后都必须满足业务规则约束，比如说A向B转账，转账前和转账后AB两个账户的总金额必须是一致的。

3.隔离性
隔离性是数据库允许多个并发事务同时对数据进行读写的能力，隔离性可以防止事务并发执行时由于交叉执行导致数据不一致的问题。

4.持久性
事务完成后，对数据的修改是永久的，即使出现系统故障也不会丢失。

二、常见出现的并发问题

1.脏读
已知有两个事务A和B, A读取了已经被B更新但还没有被提交的数据，之后，B回滚事务，A读取的数据就是脏数据。

2、不可重复读
在一个事务的两次查询之中数据不一致，一个事务在进行中读取到了其他事务对旧数据的修改结果。比如A开启一个事物，先查询到数据是1000，B开启事物把数据改成2000，A再次查询发现数据已经改成2000了。

3、.幻读

一个事务中，读取到了其他事务新增的数据，仿佛出现了幻象。（幻读与不可重复读类似，不可重复读是读到了其他事务update/delete的结果，幻读是读到了其他事务insert的结果）
比如A开启一个事物，查询到只有A,B两条信息。B开启事物新增了C信息，A再次查询发现数据有ABC三条信息。

三、隔离级别
1.未提交读（read-uncommitted）
在一个事务中，可以读取到其他事务未提交的数据变化，这种读取其他会话还没提交的事务，叫做脏读现象。

2.读提交（read-committed）
在一个事务中，可以读取到其他事务已经提交的数据变化，这种情况会出现不可重复读问题，可以避免脏读。oracle,sql server默认是这种隔离级别。

3、重复读（Repeatable read）
可以避免脏读和不可重复读，但可能出现幻读。注意：①、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；②、如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。mysql默认的隔离级别。

4、Serializable（序列化）
可以避免脏读、不可重复读和幻读，但是并发性极低，一般很少使用。注意：该隔离级别在读写数据时会锁住整张表。

四、mysql加锁

1、对mysql隔离级别、锁表、锁行次数的查询。

（1） 查询mysql的隔离级别
select @@global.tx_isolation,@@tx_isolation;

（2）表锁的查询

show status like ‘table%’;

Table_locks_immediate ： 产生表级锁定的次数
Table_locks_waited ：出现表级锁定争定发生等待的次数

（3）行锁情况的查询
show STATUS like ‘innodbrowlock%’;

Innodb_row_lock_current_waits

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；（重要）
Innodb_row_lock_time_avg：每次等待所花平均时间；（重要）
Innodb_row_lock_time_max：从系统启动到现在等待最长一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；（重要）

尤其是当等待次数很高，而且每次等待时长也不低的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

（4） 2PL原则

两阶段锁传统RDBMS加锁的一个原则，就是2PL (Two-Phase Locking，两阶段锁)。相对而言，2PL比较容易理
解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下面，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现。

从上图可以看出，2PL就是将加锁/解锁分为两个完全不相交的阶段。
加锁阶段：只加锁，不放锁。
解锁阶段：只放锁，不加锁。

五、mysql各种加锁的说明

下面的演示都是基于 innodb 和 重复读 的隔离级别演示

建表语句

CREATE TABLE `t_lock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pubtime` varchar(100) DEFAULT '',
  `name` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `pubtime` (`pubtime`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

表中数据

锁的类型，按着范围：

全局锁：整个数据库实例。service层实现的
表级锁：锁的是一张表。service层实现的
行锁：锁定的是表中的数据。存储引擎实现的，InnoDB实现行锁。
读锁
select * from t1 where … lock in share mode;
写锁：
select * from t1 where … for update;
增 删 该 。
行锁： 记录锁，间隙锁，临键锁。

（1）未使用索引的情况

由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。

1、session1: begin;–开启事务未提交
session1：select * from t_lock where name =‘bbb’ lock in share mode; – 给session2加读锁
2、session2：select * from t_lock where name =‘hdc’ for update; – 修改name=‘hdc’ （阻塞）

3、session2：insert into t_lock values(9,‘aa’,20)（堵塞）
4、session2：select * from t_lock where id =8 for update;（堵塞）
5、session2：select * from t_lock where name =‘hdcr’ for update;（堵塞）

（2）、使用主键索引

（2、1）等值查询，命中了一条记录时，对主键加记录锁
1、session1: begin;–开启事务未提交
session1: select * from t_lock where id =‘4’ for update; 对id=4加记录锁
2、session2：elect * from t_lock where id =‘6’ for update;（不堵塞）
3、session2：elect * from t_lock where id =‘5’ for update;（不堵塞）
4、session2：elect * from t_lock where id =‘4’ for update;（堵塞）

（2、2）等值/范围查询，没有命中任何一条记录时，这时包含where条件的间隙区间会被加间隙锁。
begin;

1、session1: begin;–开启事务未提交
session1: sselect * from t_lock where id =3 for update; 对（1,4）加间隙锁
2、session2：insert into t_lock values(2,‘aa’,20)（堵塞）

（2、3）范围查询，命中以1条或多条记录时，加的临键锁。包含我们where条件的临键区间会加临键锁。

1、session1: begin;–开启事务未提交
session1: select * from t_lock where id >3 and id<5 for update; 对（1,6]加临界锁

2、session2： begin；
session2：insert into t_lock values(2,‘aa’,20)（堵塞）
3、session2：insert into t_lock values(5,‘aa’,20)（堵塞）
4、session2：select * from t_lock where id =6 for update;（堵塞）
5、session2：select * from t_lock where id =1 for update;（不堵塞）

（3）使用辅助索引

要理解辅助索引的加锁，需要了解，创建辅助索引，索引的叶子节点是主键索引值，这个也是造成辅助索引与主键索引的区别。

（3、1） 等值查询
命中一条记录，命中一条记录。命中记录的辅助索引项和主键索引项加记录锁，记录的两边加间隙锁。
1、session 1:
begin;
select * from t_lock where pubtime=3 for UPDATE;
2、session 2:
begin;
select * from t_lock where pubtime=3 for update;阻塞
select * from t_lock where id=4 for update;阻塞
select * from t_lock where pubtime=1 for update;不阻塞
select * from t_lock where pubtime=5 for update;不阻塞

（3、2）范围查询
命中了一条或者多条记录，临键锁。 包含我们where条件临键区间会被加临键锁。
8<pubtime<12 .辅助索引项的临键区间(5,10] (10,20] 加临键锁
命中记录的id主键索引项加的是记录锁。

1、session 1:
begin;
select * from t_lock where pubtime>8 and pubtime<12 for UPDATE;
2、session 2:
begin;
select * from t_lock where pubtime=5 for update;不阻塞
select * from t_lock where pubtime=10 for update;**阻塞 **
select * from t_lock where pubtime=20 for update;**阻塞 **
select * from t_lock where id=1 for update;**阻塞 **
insert into t_lock (pubtime,id,name) value(8,12,‘aa’);阻塞
insert into t_lock (pubtime,id,name) value(12,12,‘bb’);阻塞

（3、3）未命中任何记录的情况，加临键锁。
11<pubtime<19 ， (10,20] 加临键锁。

1、session 1:
begin;
select pubtime,id from t_lock where pubtime>11 and pubtime<19 for UPDATE;
2、session 2:
begin;
select * from t_lock where pubtime=10 for update;不阻塞
select * from t_lock where pubtime=20 for update;阻塞
insert into t_lock (pubtime,id) value(11,11);阻塞

需要注意的是，本文所有测试都是基于 重复读（Repeatable read）的情况，如果调整mysql到 读提交 事物级别。由于不用考虑幻读 ，并不会出现间隙锁和临界锁。
总结
主键索引
1.等值查询
（1）命中记录，加记录锁。
（2）未命中记录，加间隙锁
2.范围查询
（1）没有命中任何一条记录时，加间隙锁。
（2）命中1条或者多条，包含where条件的临键区间，加临键辅助索引

辅助索引

1.等值查询
（1）命中记录，命中记录的辅助索引项+主键索引项 加记录锁，辅助索引项加间隙锁。
（2）未命中记录，加间隙锁
2.范围查询
（1）没有命中任何一条记录时，加间隙锁
（2）命中1条或者多条，包含where条件的临键区间，加临键锁。命中记录的id索引项要加记录锁。

死锁一：

session 1	session 2
begin(开启事物)
select pubtime,id from t_lock where id=1 for UPDATE;
	begin(开启事物)
	select pubtime,id from t_lock where id=10 for UPDATE;
select pubtime,id from t_lock where id=10 for UPDATE;
	select pubtime,id from t_lock where id=1 for UPDATE;

这是很容易发现的死锁

死锁二：

session 1	session 2
begin(开启事物)
sselect pubtime,id from t_lock where pubtime=3 for UPDATE;
	begin(开启事物)
	select pubtime,id from t_lock where pubtime=20 for UPDATE;
insert into t_lock(id,pubtime,name) values (9,30,‘bbb’)
	insert into t_lock(id,pubtime,name) values (2,5,‘bbb’)

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