mysql事务隔离级别和锁

事务的四种隔离级别

image.png

锁的使用注意点

1、未开启事务时，InnoDB默认为增/删/改开启事务（开启事务可以认为有对应锁模式）；
2、未开启事务时，InnoDB不会为查开启事务（开启事务可以认为有对应锁模式）。

1. A会话设置隔离级别为串行化SE，修改单条记录（这条记录加写/排它锁）
   update sms_scene set scene_code="ocr_scene1" where id=1032;
2. B会话设置隔离级别为串行化SE(读时需要读/共享锁)，未开启事务时可以查询，开启事务时查询超时：select * from sms_scene where id=1032;
3. B会话设置隔离级别为可重复读RR(读时不需要读/共享锁)，未开启事务时可以查询，开启事务时也可以查询：select * from sms_scene where id=1032;

InnoDB锁类型概述

image.png

乐观锁和悲观锁

1、乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题。
2、乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务；
3、悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁；

行锁详解

1、InnoDB支持多种锁粒度，默认使用行锁，锁粒度最小，锁冲突发生的概率最低，支持的并发度也最高，但系统消耗成本也相对较高；下面我们的讲解都只会围绕着行锁来展开。
2、共享锁与排他锁是InnoDB实现的两种标准的行锁；
3、InnoDB有三种锁算法——记录锁、gap间隙锁、还有结合了记录锁与间隙锁的next-key锁，InnoDB对于行的查询加锁是使用的是next-key locking这种算法，一定程度上解决了幻读问题；意向锁不在本次讨论范围之内。
4、InnoDB所有的行锁算法都是基于索引实现的，锁定的也都是索引或索引区间（这一点会在后面详细说到）；

image.png

• 注意细节点

共享锁（读锁/S锁）可以跟其他共享锁兼容，不能跟排他锁兼容；而排他锁（写锁/X锁）不能跟共享锁兼容，也不能跟排它锁兼容。

只有在串行化（SE）才会默认为读加共享锁；通过下面方式实地验证：

1. A事务设置串行化（SE）模式开启事务，读某行记录（获取到读共享锁）：
   select * from sms_scene where id=1032;
2. B事务设置为串行化（SE）模式，去修改这行记录（需要获取写排它锁），会超时：
   update sms_scene set scene_code="ocr_scene1" where id=1032;
3. 当A事务设置为可重复读（RR）模式，重复上述动作则不会超时。

在所有模式读未提交（RU）/读已提交（RC）/可重复读（RR）/串行化（SE）都会给增/删/改操作加排它锁。
通过下面两种方式实地验证：

1. A事务设置读RU/RC/RR/SE模式开启事务，修改某行记录（获取到写排它锁）：
   update sms_scene set scene_code="ocr_scene2" where id=1032;
2. B事务设置为串行化（SE）模式，开启事务去读取这行记录（需要获取读共享锁），会超时：
   select * from sms_scene where id=1032;

1. A事务设置串行化（SE）模式开启事务，读某行记录（获取到读共享锁）：
   select * from sms_scene where id=1032;
2. B事务设置为RU/RC/RR/SE模式，去修改这行记录（需要获取写排它锁），会超时：
   update sms_scene set scene_code="ocr_scene1" where id=1032;

MVCC多版本并发

1、MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议MVCC；与MVCC相对的，是基于锁的并发控制。
2、MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。
3、在RU隔离级别下，直接读取版本的最新记录就 OK；对于SERIALIZABLE隔离级别，则是通过加锁互斥来访问数据；都不需要MVCC的帮助。因此MVCC运行在RC和RR这两个隔离级别下.

• 实现原理
  InnoDB中MVCC的实现方式为：每一行记录都有两个隐藏列：
  DATA_TRX_ID：记录最近更新这条行记录的事务ID
  DATA_ROLL_PTR：表示指向该行回滚段的指针
  image.png

事务A对值x进行更新之后，该行即产生一个新版本和旧版本。假设之前插入该行的事务ID为100 ，事务A的ID为200，该行的隐藏主键为1。

image.png

那么INSERT和DELETE会怎么做呢？其实相比UPDATE这二者很简单，INSERT会产生一条新纪录，它的DATA_TRX_ID为当前插入记录的事务ID ；
DELETE某条记录时可看成是一种特殊的UPDATE，其实是软删，真正执行删除操作会在commit时，DATA_TRX_ID则记录下删除该记录的事务ID 。

• 当前读（未加锁）

读未提交模式下RU简单的select是不加锁的，直接读取最新数据；除非特殊的读操作，插入/更新/删除操作属于当前读，或者显示加锁和for update，需要加锁。
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 ?;

示例一

image.png

示例二

image.png

• 当前读（加锁）

串行化模式下SE简单的select也是需要加读共享锁的，当它在读取某行记录时，其他事务不能修改这行记录。

image.png

• 快照读（RR）

在RR隔离级别下使用MVCC时，每个事务touch first read时（本质上就是执行第一个SELECT语句时，后续所有的SELECT都是复用这个ReadView

示例一

image.png

示例二

image.png

示例三

image.png

• 快照读（RC）

在RC隔离级别下，每个SELECT语句开始时，都会重新将当前系统中的所有的活跃事务拷贝到一个列表生成 ReadView 。二者的区别就在于生成ReadView的时间点不同，一个是事务之后第一个SELECT语句开始、一个是事务中每条SELECT语句开始。

image.png

锁算法详解

InnoDB存储引擎加锁算法有Record lock（记录锁），Gap lock（间隙锁）和Next-key锁。
这些加锁算法锁住的是索引，而不是记录行，但是锁住了索引也就锁住了对应的记录行。

• Record lock（记录锁）

1、单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身
2、即使该表上没有任何索引，也会在创建一个隐藏的聚集主键索引，锁住的就是这个隐藏的聚集主键索引
3、innodb在RC（读提交隔离级别）使用的就是Record lock（记录锁）

主键索引字段a	唯一索引字段b	普通索引字段c	非索引字段d
1	3	5	7
3	5	7	9
5	7	9	11
7	9	11	13
9	11	13	15

image.png

1. 在主键上使用=，>, >=, <, <=锁住的是满足条件的主键，比如上表查询a>=5 for update，锁住的是a=[5] + [7] +[9]这三个主键索引；
2. 在唯一健上使用=，>, >=, <, <=锁住的是满足条件的主键，比如上表查询b>=7 for update，锁住的是a=[5] + [7] +[9]这三个主键索引；并没有锁住b=[7] + [9] + [11]这三个非唯一索引
3. 在非唯一健上使用=，>, >=, <, <=锁住的是满足条件的的主键，比如上表查询c>=9 for update，锁住的是a=[5] + [7] +[9]这三个主键索引，并没有锁住c=[9] + [11] + [13]这三个非唯一索引；插入(6, 8, 9, 11)还是能成功;
4. 在非索引上使用=，>, >=, <, <=锁住的是满足条件的主键，比如上表查询d>=11 for update，锁住的是a=[5] + [7] +[9]这三个主键索引。插入(6, 8, 9, 11)还是能成功;（实质上是先给所有的主键聚集索引加锁，在释放不满足条件的索引锁）。
5. 锁住了索引也就是锁住了对应的记录，不能在已有的记录上删除/修改，上面的示例不能对a=[5] + [7] +[9]这三行记录的所有字段做where修改或删除（where a=7或b=9或c=11或d=13）。
6. 上面这些所有的例子（主键索引、唯一索引、非唯一索引、非索引）查询如果没有满足条件的记录，就不会给任何记录加锁。

• Gap lock（间隙锁）和Next-key锁

1、Gap lock（间隙锁）是在索引记录之间的间隙中加锁，但并不包括该索引记录本身
2、gap lock的机制主要是解决可重复读模式下的幻读问题（并不能解决所有的幻读问题）
3、Next-key锁 = Record lock（记录锁） + Gap lock（间隙锁），既包含索引间隙，也包含索引本身。
4、innodb在RR（读提交隔离级别）使用的就是Record lock（记录锁）

1. 对于下表的主键a，记录锁就是已存在记录a=[1] + [3] + [5] + [7] + [9]；间隙锁就是根据现有记录分隔的开区间(不包含记录本身)，(-∞, 1); (1,, 3); (3, 5); (5, 7); (7, 9); (9, ∞)

主键索引字段a	唯一索引字段b	普通索引字段c	非索引字段d
1	3	5	7
3	5	7	9
5	7	9	11
7	9	11	13
9	11	13	15

2. 在主键上使用=，>, >=, <, <=锁住的是满足条件的主键；比如上表查询a>=5 for update，锁住的是a=[5] + (5, ∞)这些索引记录；不能对现有满足条件的记录改或删，也不能对a>=5的记录做insert。

   
   image.png

3. 在唯一索引上使用=查询，锁住的是对应的唯一索引+主键索引；例如b=7 for update锁住的是b=[7]和a=[5] ;
   在唯一索引上使用>或<查询，锁住的是对应的唯一索引+主键索引；例如b>7 for update锁住的是b=(7, ∞) 不包括7和a=[7] + [9];

   
   image.png

4. 在唯一索引上使用>=或<=查询，锁住的是对应的唯一索引+主键索引；例如b>=7 for update锁住的是b=(5, ∞) 不包括5(间隙锁往前移了一步)和a=[5] + [7] + [9]；
   但实际表现看起来像是锁表一样！！！没搞懂，可能有别的自己不知道的原因！！！

   
   image.png

5. 在非唯一索引上使用=查询，锁住的是对应的非唯一索引+主键索引；例如c=9 for update锁住的是c=(7, 11)往前往后各一个间隙但不包括7\9和a=[5] ;

   
   image.png

6. 在非唯一索引上使用>或<查询，锁住的是对应的非唯一索引+主键索引；例如c>9 for update锁住的是c=(9, ∞) 不包括9和a=[7] + [9];

   
   image.png

7、在非唯一索引上使用>=或<=查询，锁住的是对应的非唯一索引+主键索引；例如c>=9 for update锁住的是c=(7, ∞) 不包括7(间隙锁往前移了一步)和a=[5] + [7] + [9]；
但实际表现看起来像是锁表一样！！！没搞懂，可能有别的自己不知道的原因！！！

image.png

8、在非索引上使用=，>, >=, <, <=查询会锁住整张表，即使没有匹配的记录也会锁住整张表。

image.png