sql 标准定义了4种事务的隔离级别，各个数据库厂商提供了不同的实现，甚至实现的标准都有所差别

关于ACID 避免了哪些问题

如果精准的理解这些隔离级别是针对于当前事务对其他事务的逻辑，也就是当前事务读取其他事务的逻辑区别。

1. 未提交读 ：在当前事务可以读取到其他事务未提交但是已经更改的数据如下图
   
   

   图1

• 查询1：在未修改时会读取到数据

• 查询2：事务A修改但是未提交的数据会被读取到
  那么不得不提如果不开启事务是怎么样的情况，如下图

  
  
  

  图2
• 查询1：事务A未修改数据，读取到的是事务A数据的旧值
• 查询2：事务A修改了数据，但是未提交，当时查询看不到数据变化依然读取旧值
• 查询3：事务A提交后当前查询看到了数据变化读取到事务A的新值
  看到了上面连个案例是否有所理解，无事务时的查询是看不到事务内的操作，需要提交了之后才能看到。而未提交读是故意提供一个查看范围更大的逻辑，可以读取到任何事务未提交的更改(例如innodb相当于读取最新的数据快照)
  任何情况都不能避免

2. 已提交读：在当前事务内可以读取到其他事务的已提交的数据，这个读取的逻辑和未开启事务一致，相当于开启了事务的默认读取规则。如下图3。
   
   

   图3

• 查询1：读取到事务A旧值
• 查询2：事务A修改了数据，读取到事务A旧值
• 查询3：事务A提交，读取到事务A新值
  避免了脏读：如果使用未提交读的级别，如图1，查询2读取到了事务A的未提交的更改后的数据，但是在这之后事务A进行了回滚，那么图1中事务B的查询2则是脏读

3. 可重复读（常见的默认级别）：当前事务内在开启事务后，读取其他数据时第一次的读取，无论后续其他更改数据的事务是否提交都一致保持第一次读取的值(例如innodb是让当前事务读取的一直是第一次读取数据的快照)
   
   

   图4
   
   
   

   图5
   
   如图4

• 查询1：查询到修改前的旧值
• 查询2：依然旧值
• 查询3：依然旧值
  如图5
• 查询2：因为隔离级别越高，会包含前面级别的隔离定义，所以未提交的数据看不到，读取的是事务A的旧值
• 查询3：依然旧值
  这里要说一下，如果直接从查询3读取则会读取新值，符合隔离级别2读已提交
  避免不可重复读：那么这个隔离级别避免了事务内多次读取数据不一致的情况，第一次读取的值在当前事务内一定一致。

除了串行化有3种情况避免避免不了

写写并发操作引起的数据异常：

lost update ：丢失更新

图6

如图6：

• 事务A先update(name = a)：获取到当前数据的锁，进行操作
• 事务Bupdate(name = b)：需要等待事务A释放锁
• 事务A提交：这时数据name = a
• 事务B获取到事务A释放的锁：进行更新 name = b
• 事务B提交： 数据变化成功name = b
  这两个update的并发事务都会成功，那么对于事务B来说他要达到的数据变化要求，但是对于事务A来说的它的更新就已经丢失了。这么看来也没什么问题啊？是吗。
  我们如果将name = a，name = b 换成依赖于之前的值的变化，可以是取原先值的数字叠加，例如 变为set count = count + 1,那么两个事务都是 set count = count + 1，如果不是利用sql语句叠加，而是在应用程序种先查询出来原始count，那么两个并发事务都查询出0(假设原始值为0)，然后都去更新count = 1，对于应用来说两次更新只成功了一次。后面还有添加了where条件的更新:写偏序也属于这种情况之一。

所谓脏写：本人对这个逻辑持怀疑态度，但是各种论坛甚至腾讯数据库大佬的书《数据库事务处理的艺术-事务管理与并发控制》都有提到这个脏写问题

关于《数据库事务处理的艺术-事务管理与并发控制》第一章1.1.4节的脏写问题 · Issue #3 · bluesea2DB/DB-MyBooks (github.com)
还有论坛知乎等这样也都是这样描述：
大白话讲解脏写、脏读、不可重复读和幻读 - 知乎 (zhihu.com)
如上可直接看脏写部分的描述，连个并发事务，前一个的提交会被后一个提交的回滚覆盖，这一点我不太认同，理论上innodb无论什么事务级别，在update时会获取排他锁，那么只需要看谁先update，那么后面的update一定要等前一个update提交后才能操作，也就不存在如果有一个事务回滚覆盖另一个事务了，因为你就算回滚一定是回滚自己的版本，另一个事务一定是提交后了才能让你获取到被释放的锁进行update。

语义约束引起的异常：

写偏序: 我的理解是，在除了串行化事务级别下，先需要查询根据某些查询出来的<条件>再进行更新时，会产生覆盖或者非预期情况，这是因为事务下查询除了串行化隔离级别，当前事务去查询其他事务未提交的更新是读取不到的，然后你的这次更新又依赖了对其他未提交事务更新的值的查询，会造成后面的事务感知不到其他并发事务已经将值修改了

举例：一共两条数据如下图7

图7

两个并发的事务，如下图，两个事务都会成功（前提是后面的事务B不是未提交读级别）

图8

结果如下：原因其实就是事务隔离造成的并发事务下的已提交读

图9

那么我们把事务级别更改为 未提交读则只有第一个事务A会成功，那么再变换一下，如果事务B的查询时机在事务A的提交之前那么结果也还是等同于 已提交读，可重复读

幻读：其实逻辑和上面的写偏序类似，就是在非串行化的隔离级别下，因为读是共享锁，不会读取到其他事务未提交数据（幻读一般特指where条件写读取，可以理解为专门读取其他事务未提交的更改），但是这里又分为3种情况。

• 如果是未提交读：可以避免幻读，可以读取到其他事务未提交的更改，但是又会因为回滚引起脏读
• 如果是已提交读：在第一次读取，未读取到其他事务的提交，如果其他事务提交了，但是后面再次查询就会读取到，但是这种逻辑本身就是不可重复读的异常，会对当前事务的数据读取一致性产生影响
• 如果是可重复读（默认级别）：则会真正产生幻读，读取不到其他并发事务更改。如写偏序，就会对当前事务的逻辑有所影响
  幻读因为读取其他事务的数据时，还有插入数据的影响，如果其他事务插入新数据那么你的查询也是感知不到。对于可重复读级别可以利用已经读取到的数据进行快照隔离，幻读会在对应查询条件上产生新数据，可重复读无法避免。

4. 串行化：避免了一切，每个事务串行执行包括读取。也需要等待前一个事务提交后才可以读取。性能很差。

事务模型

不同的数据库厂商对于事务的实现是不同的。引用《数据库事务处理的艺术-事务管理与并发控制》1.3章节

• 平板事务：事务块种的所有sql。构成一个逻辑单元，要么都成功，要么都失败。
• 带有保存点的平板事务：在平板事务逻辑上添加了保存点，让一整个逻辑单元可以拆分为多个小的逻辑单元进行成功，回滚。例如posstgresql，innodb，informix都在平板事务基础上支持了保存点逻辑。
• 链式事务：嵌套事务如同一颗树，树有子叉，每个子叉可以嵌套子事务也可以是平板事务。但叶子节点的事务是平板事务。根节点事务提交，整个事务的数据修改才生效。否则只是事务内局部有效。这里不太容易理解。也没有找到很好的案例。
• 多层次事务：多层事务也如同一棵树，树根是事务的总节点，下层是对象操作作为子事务的存在，对象操作还可以带有子对象操作节点，或带有一个或多个叶子节点。也不太理解也没有很好的案例。

并发事务的技术实现

这里阐述了一些并发控制的实现方式，但是往往各个数据库厂商是通过多个实现结合来进行并发控制

时间戳：基于时间戳对事务提交顺序排序的并发控制

通过事务时间戳+数据项时间戳，多个事务按照事件戳顺序来访问数据项，同时对提交进行时间戳排序。

串行化图形检测： 也称为优先图/ 冲突图/ 串行化图检测：

通过有向图检测环形事务依赖

两阶段封锁：

为了提高并发度分为共享锁（innodb中的读），排他锁（innodb中的写）
默认隔离级别下，读读，会阻塞，其他都需要阻塞等待释放资源。那为什么我们实际开发过程中innodb是允许 读写，写读在默认隔离级别可重复读是可以并发的呢。如下多版本并发控制。

多版本并发控制：

事务管理器为写操作生成一个数据项的新版本；当有读操作所在事务开始阶段获得的活动事务的快照，找出应该读取的该数据项的某个版本、这样读写，写读也不会产生阻塞，只有写写会产生阻塞。innodb就是这样做的。例如可重复读的隔离级别(默认级别)，在第一次读时就是已经确定了读取的版本，那么其他事务就算已经提交了，当前事务还是不会读取到。后续会讲到我实际业务中产生的问题以及解决方案。

基于索引的并发控制：

在索引树上对索引页采取封锁手段，以维护索引树的一致性，同时可以避免幻想异常，如mysql的innodb存储引擎以B+树作为存储的基本结构。可以在索引树上直接施加next-key locking 进行范围锁定，以避免谓词限定内的数据insert，update，delete等。这里也有很典型的案例。
一个表 id ， name ， parent_id , level id为主键，parent_id 为索引
假如有4条数据
100 ， a ， 8，1
101， b ， 8，2
102， c， 8，3
103， d， 8，4
有两个并发事务同时要修改这几条数据，

• 事务A update table set name = xxx where parent_id = 8 and level in (1,4)
• 事务B update table set name = xxx where parent_id = 8 and level in (2)
  那么这两个事务同时尝试锁住 4条数据，并发情况下可能会产生死锁！
  需要更换为 update by id即可，降低锁粒度到你真正想要更新的数据

日志技术与恢复子系统

redo/undo log进行数据变更前，后的存储，便于进行故障恢复。那么分布式事务seata框架中的AT模式，便是利用了原本大多用于故障恢复的日志技术，来实现了分布式事务的提交和回滚。通过TC分布式事务协调器来协调各个事务分支进行提交或者回滚，通过 undo log中的前后镜像实现。

那么innodb的并发控制是通过 mvcc + 严格两阶段锁 + 快照隔离 实现的并发控制

回过头来看 常见的事务异常原因

• 未提交读的脏读：postgresql不提供这个级别，那么可以读到其他事务未提交的数据，其实就是一直读取当前数据的最新快照数据。产生脏读。
• 已提交读的不可重复读现象：其实就是一直读取已经提交的快照，会产生多次读取不一致。
• 可重复读级别：产生幻读，写偏序等问题，因为为了保证读取的一致性，只要在第一次查询时，就确定了是读取哪个版本的快照，之后一直读取这个版本的快照，就算是其他事务的更改已经提交了也不会感知到，则产生了幻读，写偏序等问题。但是这个问题使用未提交读/已提交读 两个级别不会有，但是会引入别的问题。
  多版本控制 + 快照隔离 ：

1. 提供了可重复的数据一致性
2. 提供了 更高的并发度，可以使严格两阶段锁协议的 写读， 读写变为非阻塞。
   事务死锁的元凶：严格两阶段封锁协议
   虽然配合多版本控制+快照 提高了并发度，减少了阻塞，但是innodb严格两阶段锁协议不允许锁降级，只要是写操作都会升级为排他锁以及必须提交后释放锁，那么就可能存在因为写写操作带来的资源竞争，互相持有资源等待对方释放造成死锁。
   强两阶段封锁保证了数据得一致性。

关于innodb的一些锁(以锁作用范围的维度)

1. 记录锁 基于 = 号，in等查询方式出现
2. gap locks 间隙锁 通过 > < 的查询方式出现
   避免幻读
3. next-key locks 基于2的查询方式，并且作用范围没有命中到已有的记录上，例如id，1，10，20，我们使用 id > 10 的时候只有间隙锁，那么id > 5就会出现临键锁，本质上是由于锁作用域索引，需要一个真实的挂载点
   避免幻读 基于2， 左开右闭
4. insert intention locks 允许不冲突作用在间隙锁上的数据插入

innodb锁在存储上的维度

1. 行锁主要加在索引上，如果对非索引的字段设置条件进行更新，行锁可能会升级为表锁
2. InnoDB的行锁是针对索引加锁，不是针对记录加锁，并且加锁的索引不能失效，否则行锁可能升级为表锁。
3. 页级锁 介于行锁和 表级锁之间

间隙锁和临键锁的示例

间隙锁通过范围的查询，造成加锁，那么行锁的通过主表id的索引查询也会对索引下所有数据加锁。有点类似。
1 张三 300
2 李四 350
3 王五 500
15 赵六 100
20 田七 360
account 表中存在的id间隙为 (3,15] (15,20] (20,正无穷] 三个间隙
那么我们使用语句 update table set balance = balance + 100 where id > 5 and id < 16
那么锁住的是 (3, 20]
可重复读下生效
临键锁 ： next-key locks 是行锁和 间隙锁的组合，例如上面例子中的产生出来的 (3, 20]就称为临键锁
索引行级锁 的作用域和 间隙(临键锁)的产生方式不同，临键锁通过 < , > 等范围查询所致。

mysql的 原子性和持久性通过 redolog实现

redolog 刷盘规则

1. 开启事务，发出提交事务指令后是否刷新日志由变量innodb_flush_log_at_trx_commit决定。
2. 每秒刷新一次，由变量innodb_flush_log_at_timeout的值决定，默认1s（单单指这个时间间隔），刷新日志的频率和是否执行了commit操作无关。
3. 当Log buffer 中已经使用的内存超过一半时，也会触发刷盘操作。
4. 当事务中存在 checkpoint 时。在一定程度上代表了刷写到磁盘时日志所处的LSN的位置。LSN代表日志的逻辑序列号

innodb_flush_log_at_timeout：

变量0 ：系统崩溃可能丢失1s的数据无法恢复（都不会写到OS buffer中，通过单独的线程来做）
1：每次提交事务会强制刷盘，性能较差，默认值
2：每次提交事务 会将数据先存入 os buffer中，然后每1s由 os buffer中刷盘到磁盘

通过 undolog 进行回滚保证一致性，和mvcc

undolog记录逻辑日志，例如insert前记录一条 对应的delete 日志，update前记录一个反向update日志。
如果有系统崩溃先使用redolog，然后使用undolog恢复
在事务提交前将事务前镜像存入undolog，可以作为一个快照版本提供给其他事务读取，（可重复读需要利用这个快照）
undolog通过数据上的隐藏列（创建版本号, 删除版本号(回滚指针)）来实现多版本快照隔离
实际由 6字节事务id(创建版本号)(db_trx_id)，和7字节的回滚指针（db_roll_ptr）,和6字节的db_row_id字段组成
1）db_trx_id用来标识最近一次对本行记录做修改的事务id，如果是delete操作，在innodb也属于update。由一个标志位标记删除
2）db_roll_ptr 指向上一个版本的行记录。
3）db_row_id隐藏id。聚簇索引

binlog：innodb特有的日志。

一种记录所有mysql数据库表结构变更以及表数据变更的二进制日志。binlog中不会记录 select，show等查询。
1）主从复制
2）数据恢复

1. row模式 记录每一行修改情况，缺点如果有批量操作会产生大量日志
2. statement模式，记录sql语句，优点不记录修改细节，提升io。缺点数据可能不一致。例如sql中使用了now() last_insert_id()的函数会不一致、
3. Mixed 上面两种方式混用

mysql中 binlog优先于 redolog写入

最后欢迎阅读的网友指出不足和错误，共同进步。

【补充】 之 update，delete 或者是select for update 如果没有命中的到数据

如果没有命中到数据，而使用的条件又是带有索引的字段，会使用间隙锁+临键锁
并且极其容易产生死锁！
举个例子：
id name other_id node_id
55 啊 201 333
56 哈 210 333
57 哈 211 222
索引为联合 索引 other_id , node_id
如上述数据使用mysql默认可重复读隔离级别，使用如下语句
delete from table where other_id = 205 and node_id = 333
那么 现在获取排他锁，没有匹配到数据，并且使用索引字段作为条件，那么现在会利用这个索引的间隙锁锁住区间数据，并且产生了临键锁，遵循左开右闭并且联合索引锁住数据
这个时候会通过间隙锁+临建锁，虽然是联合索引，但是实际使用索引左值第一个other_id来检测数据锁的冲突。因为没有具体的数据可以排他，间隙锁大家都尝试去锁住一个区域，并不会互相排他等待，但是如果再insert这个间隙锁区域的数据时需要保证排他性。那么这个时候才会去互斥等待，如果前面有两个事务已经通过间隙锁尝试锁住这个区域了，两个事务会等待另一个间隙锁释放。则会产生死锁
上述delete语句会获取(201,210]的other_id的数据间隙锁，但是不会互斥，那么其他并发的事务如果同时获取这个区间的数据也会获取间隙锁，所以间隙锁是一个共享锁。待再insert table into (other_id, node_id) values(205,333) 会产生死锁。

具体例子如下

业务中我需要将原有的 other_id = 205 and node_id = 333 和 other_id = 206 and node_id = 666
删除，无论他有木有，再新建

1. 事务（1）delete from table where other_id = 205 and node_id = 333
   sleep(8s)
   insert table into (other_id, node_id) values (205,333)
2. 事务（2）delete from table where other_id = 207 and node_id = 666
   sleep(8s)
   insert table into (other_id, node_id) values (205,333)
   用两个接口测试，这个连个事务会死锁。（sleep是拉长事务时间，提高锁竞争概率，所以说有可能产生死锁的代码也是并发足够/事务时间长度两个因素暴露出来）
   那么如果使用 other_id = 300 则会获取 （211， 无穷大 所有大于211的间隙锁，更容易产生死锁。

解决方案，如果不是update，并且强制需要原有数据的状态判断的依赖，完全可以使用select(不要使用for update)获取共享锁，后续delete，update使用id，如果没有数据也不会去继续操作数据库了，这样就不会出现间隙锁啦！当然，如果没有这个数据你还用一个没有的id去delete，还是会以这个id生成间隙锁，但是没有代码会这样写吧 - -！，如果select 通过共享锁事务拿到数据，就算其他事务也同时进行了删除，那么这个时候delete语句就会有互斥性。并且不会产生间隙锁

select * from table where other_id = 205 and node_id = 333
如果不为null 再用 id delete
再 insert table into (other_id, node_id) values (205,333)
select 不会产生间隙锁

总结

一切排他锁 例如 update， delete 尽量使用 byId一定不会是间隙锁。如果是一定需要竞争查询到库里的数据，并依赖库里的数据状态值，需要用到select for update 尽量保证库里的数据存在，后面再继续操作，如果不存在，会产生间隙锁，如果继续操作极有可能死锁

如上操作如果你的where条件没有索引，那就是缩表了。。。百分百死锁，哈哈哈