Mysql事务隔离级别、锁

参考自 ：mysql 四种隔离级别、MySQL 中隔离级别 RC 与 RR 的区别、详解 MySql InnoDB 中的三种行锁（记录锁、间隙锁与临键锁）、

谈谈MySQL的锁 https://blog.csdn.net/BruceLeeNumberOne/article/details/81865045?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.vipsorttest&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-1.vipsorttest

【mysql性能优化】事务中隔离性的实现 https://blog.csdn.net/aiming66/article/details/95164488

【MySQL笔记】正确的理解MySQL的MVCC及实现原理 https://blog.csdn.net/SnailMann/article/details/94724197

 

一、事物

事务的 四个特征（ACID）

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

1 、原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

2 、一致性。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

3 、隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4 、持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

 

Mysql的四种隔离级别

查看隔离级别：

当前会话隔离级别 select @@transaction_isolation （mysql版本 8.0 以后）

当前会话隔离级别 select @@tx_isolation （mysql版本 8.0 之前）

查看系统当前隔离级别 select @@global.tx_isolation;（mysql版本 8.0 之前）

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。

低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

Read Uncommitted（读未提交）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果，就会产生脏读（Dirty Read）。本隔离级别很少用于实际应用，同时它的性能也不比其他级别好多少。

 

Read Committed（读已提交）

这是很多数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。

该隔离级别解决了脏读的问题，不过可能会面临另外两个问题：

（1）不可重复读（Nonrepeatable Read）：一个事务A中多次执行同一个select, 期间事物B对相应的数据进行了update并且提交，导致事物A 两次读取的数据不一致。

（2）幻读 （Phantom Read）问题，具体见下文。

 

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

Mysql的InnoDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。（MVCC见下文）

不过理论上，该隔离级别会面临另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行（或者删除某一行），当用户再读取该范围的数据行时，会发现有新的“幻影” 行 或者缺少了某一行。

国际标准要求RR级别达到数据可重复读的标准，没有要求解决幻读的问题。但是，Mysql的InnoDB存储引擎通过引入一种特殊的间隙锁（gap lock）----临键锁（next-key lock，innodb的默认行锁算法）解决了幻读的问题，具体见下文。

 

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁，在写数据时加排它锁。在这个级别，可能导致大量的超时现象和锁竞争。

 

这里区分一下 不可重复读 和 幻读：

不可重复读的重点是修改: 同样的条件的select, 你读取过的数据, 再次读取出来发现值不一样了

幻读的重点在于新增或者删除: 同样的条件的select, 第1次和第2次读出来的记录数不一样

从结果上来看, 两者都是为多次读取的结果不一致。但如果你从实现的角度来看, 它们的区别就比较大：
对于前者, 在RC下只需要锁住满足条件的记录，就可以避免被其它事务修改，也就是 select for update, select in share mode; RR隔离下使用MVCC实现可重复读；
对于后者, 要锁住满足条件的记录及所有这些记录之间的gap，也就是需要 间隙锁 gap lock、临键锁 next-key lock。

 

综上，隔离级别主要是通过锁和MVCC来控制的，可以用下图进行描述：

 

二、Mysql中的锁

MySQL中有着Lock和Latch的概念，在数据库中，这两者都可以被称为“锁”，但是两者有着截然不同的含义。

Latch一般称为闩锁（轻量级的锁），因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差，在InnoDB引擎中，Latch又可以分为mutex（互斥量）和rwlock（读写锁）。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

Lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。

关于Latch更详细的讲解可以参考：关于MySQL latch争用深入分析与判断，本文主要关注的是Lock锁。

锁的类型

对数据的操作其实只有两种，也就是读和写，而数据库在实现锁时，也会对这两种操作使用不同的锁；InnoDB 实现了标准的行级锁，也就是共享锁（Shared Lock）和互斥锁（Exclusive Lock）。

　　共享锁（shared locks，又称为读锁，简称s锁），顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

　　加锁释锁方式：

select*from users WHERE id=1  LOCK IN SHARE MODE;
commit/rollback

　　排他锁（exclusive locks，又称为写锁，简称×锁），排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有获取了该排他锁的事务才可以对数据行进行读取和修改，（其他事务要读取数据可来自于快照）。

　　加锁释锁方式：

delete/update/insert   --默认加上×锁
SELECT* FROM table_name WHERE … FOR UPDATE
commit/rollback

锁的粒度

Lock锁根据粒度主要分为表锁、页锁和行锁。不同的存储引擎拥有的锁粒度都不同。

表锁

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。
使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

页锁

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。
使用页级锁定的主要是BerkeleyDB存储引擎。

行锁

行级锁定最大的特点就是锁定对象的粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
使用行级锁定的主要是InnoDB存储引擎。

总结

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

意向锁 intention shared locks

InnoDB 支持多种粒度的锁，也就是行锁和表锁。为了支持多粒度锁定，InnoDB 存储引擎引入了意向锁（Intention Lock）。

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

意向锁也分为两种：

• 意向共享锁（intention shared locks，IS）：事务想要获得表中某些记录的共享锁，需要在表上先加意向共享锁，意向共享锁之间可以互相兼容。
• 意向互斥锁（intention exclusive locks，IX）：事务想要获得表中某些记录的互斥锁，需要在表上先加意向互斥锁，意向互斥锁之间可以互相兼容。

随着意向锁的加入，锁类型之间的兼容矩阵也变得愈加复杂：

意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

行锁的算法

InnoDB存储引擎有3种行锁的算法，其分别是：

• Record Locks：记录锁，锁住某一行记录。作用于唯一索引列或主键列，且查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。
• Gap Locks：间隙锁，锁定一个范围，但不包含记录本身。一般作用于非唯一索引。
• Next-Key Locks：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。一般作用于非唯一索引。

Record Locks 记录锁

select * from t where id =4 for update;

 

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;

 Record Locks 总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

如果也没有主键，会锁住整张表，变为表锁。

间隙锁 Gap Locks

间隙锁一般基于非唯一索引，它锁定一段范围内（左开右开）的索引记录。

对于表结构：id为主键，name 为非空。数据内容如下：

select * from t where id >4 and id <6 for update;
或者
select * from t where id =6 for update;

此时，sql的执行没有命中数据，即当记录不存在的时候，临键锁退化为gap锁。此时，innodb将数据划分为如下结果：这样之锁住了第三个Gap区间。

此时，sql会没有命中记录，按照定义，innodb会锁定一个区间，（7,10）区间。
如果在事务中执行以上sql语句，并且没有完成事务，这个情况下执行以下语句：

insert into t (id,name)values('9','9') --阻塞状态
insert into t (id,name)values('6','6') --阻塞状态

临键锁 Next-Key Locks

Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法，一般作用于非唯一索引，锁住记录+区间（左开右闭）。通过临建锁可以解决幻读的问题。

对于表结构：id为主键，name 为非空。数据内容如下：

为此，如果执行如下sql进行更新记录的时候：

select * from t where id >5 and id <9 for update;

innodb会将数据分为如下区间（左开右闭）：

此时，sql会命中id=7的记录，按照定义，innodb会锁定两个区间: 包含id=7的（4,7]区间 和 next-key临界区间（7,10]。

如果在事务中执行以上sql语句，并且没有完成事务，这个情况下执行以下语句：

select * from t where id =4 for update;--可以正常执行
select * from t where id =7 for update;--阻塞状态
select * from t where id =10 for update;--阻塞状态
insert into t (id,name)values('9','9'); --阻塞状态
update t set name='xx' where id=7; --阻塞状态

综上，innodb的行锁是通过给索引上索引项加锁来实现的。只有通过索引条件来检索的数据，innodb才能使用行级锁，否则innodb将使用表锁。

--例子一：通过非索引字段phoneNum 来修改记录，锁定的是整个表。
set session autocommit = off；
update user set lastUpdate=now（） where phoneNum =‘1234567’ --（这一个操作，只会修改数据库一条记录，本条记录id=1）

--此时不进行commit操作，直接进行以下操作。
update users set lastUpdate=now（） where id = 2 --提示等待执行，需要等上一个事务完成后才能进行
update users set lastUpdate=now（） where id = 1 --提示等待执行，需要等上一个事务完成后才能进行


--例子二：通过索引字段id 来修改记录，锁定的是一行记录
update user set lastUpdate=now（） where id = 1

--此时不进行commit操作，直接进行以下操作。
update users set lastUpdate=now（） where id = 2 --可以直接执行完成
update users set lastUpdate=now（） where id = 1 --提示等待执行，需要等上一个事务完成后才能进行

 

死锁的避免：
1）类似的业务逻辑以固定的顺序访问表和行。
2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
4）降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择
5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁（或者说是表锁）

三、MVCC

 可以参考：【MySQL笔记】正确的理解MySQL的MVCC及实现原理 https://blog.csdn.net/SnailMann/article/details/94724197

同时，参考下图：

 一句话：事务只能看到快照创建前已经提交的修改和该事务本身做的修改。

 

四、简单总结