事务隔离

最新推荐文章于 2021-12-23 16:34:38 发布

a...Z

最新推荐文章于 2021-12-23 16:34:38 发布

阅读量162

点赞数

分类专栏： MYSQL骑马的路

本文链接：https://blog.csdn.net/qq_39787367/article/details/103072222

版权

MYSQL骑马的路专栏收录该内容

47 篇文章 3 订阅

订阅专栏

总结

掌握程度:

事务的概念是什么?
mysql的事务隔离级别读未提交, 读已提交, 可重复读, 串行各是什么意思?
读已提交, 可重复读是怎么通过视图构建实现的?
可重复读的使用场景举例? 对账的时候应该很有用?
事务隔离是怎么通过read-view(读视图)实现的?
并发版本控制(MCVV)的概念是什么, 是怎么实现的?
使用长事务的弊病? 为什么使用常事务可能拖垮整个库?
事务的启动方式有哪几种?
commit work and chain的语法是做什么用的?
怎么查询各个表中的长事务?
如何避免长事务的出现?

隔离性与隔离级别

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

当数据库有多个事务同时执行的时候,就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念

隔离级别及产生影响

读未提交（read uncommitted）
产生脏读
读未提交是指：一个事务还没提交时，它做的变更就能被别的事务看到。

读提交（read committed）
产生不可重复读的问题
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到

可重复读（repeatable read）默认隔离级别
产生幻读问题
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

和串行化（serializable ）
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

示例

mysql> create table T(c int) engine=InnoDB;

insert into T(c) values(1);

在这里插入图片描述 读未提交
V1 是2 V2是2 V3是2

这时候事务B虽然没有提交，但是结果已经被事务A看到了，因此V2、V3也是2
造成了脏读

读提交
V1 是1 V2是2 V3是2
事务B更新提交后被事务A看到
造成了事务A开启式事务和结束事务后同一行的数据不一致，也就是不可重复读

可重复读
V1是1 V2是1 V3是2
之所以V2还是1，遵循的就是：事务在执行期间看到的数据前后必须是一致的
如果事务1在为提交过程，事务B此时插入一条数据，事务A没提交事务是看不到事务B插入的这条数据，当事务A提交事务，看到事务B插入的这条数据，此时发现多了一条数据，造成幻读

串行化
V1是1 V2是1 V3是2
事务A开始事务查询到的值为1，此时提交事务事务A查询到值为1，此时事务B要等待事务A commit后才能开启事务，将值1改为2，提交事务B，事务A再查就是2了

事务逻辑为准基础

在可重复读
实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图，因为可重复读的前提就是两个事务未提交前提下只能看到自己事务内部的信息
在事务内第一次查询时被创建，整个事务存在期间都用这个视图

读提交
读提交隔离级别下,这个视图是在每个SQL语句开始执行的时候创建的

读未提交
读未提交隔离级别是直接返回行记录上最新值,没有视图概念

串行化
串行化隔离级别下直接用加锁的方式来避免并行的访问

隔离级别查看

在这里插入图片描述

事务隔离的实现

可重复读
在mysql中,实际上每条记录在更新的时候都会记录一条回滚日志,记录上的的最新值,通过回滚操作,都可以得到前一个状态的值

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录

在这里插入图片描述当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

回滚日志删除条件

在不需要的时候删除,系统会判断,当没有事务在需要这些回滚事务时,回滚事务会被删除也就是当系统里面没有比这个回滚日志更早的read-view的时候

长事务造成的影响

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有 200GB 的库。最终只好为了清理回滚段，重建整个库。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

事务的启动方式

显示启动事务语句

begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务

多事务提交

对于一个需要频繁使用事务的业务，第二种方式每个事务在开始时都不需要主动执行一次 “begin”，减少了语句的交互次数。如果你也有这个顾虑，我建议你使用 commit work and chain 语法。

在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

查找时间超过60秒的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

read committed 导致主从事务不一致

Statement记录的是事务更新语句的顺序
MySQL5.0之前为啥事务的隔离机制是RC（'read-committed）并且存储格式是Statement时会出现主从复制数据不一致问题。

CREATE TABLE tem_table(
	b1 int,
b2 int
)

在这里插入图片描述此时查看会话1的提交结果：

dba> select * from t1;

±-----±-----+

| b1 | b2 |

±-----±-----+

| 1 | 4 |

| 2 | 8 |

| 3 | 4 |

| 4 | 8 |

| 5 | 4 |

±-----±-----+

5 rows in set (0.00 sec)

这个结果不会有任何问题。

假设在RC隔离级别下支持STATEMENT格式的binlog，并且binlog是打开的。binlog的记录顺序是按照事务commit顺序为序的。那么显而易见，binlog中的顺序为：

会话2：

dba> set tx_isolation=‘read-committed’;

dba> BEGIN;

dba> update t1 set b2=4 where b2=2;

dba> commit;

会话1：

dba> set tx_isolation=‘read-committed’;

dba> BEGIN;（开启事务）

dba> update t1 set b2=8 where b2=4;

#会话1进行提交

dba> commit;

那么此时在主从复制的从库上看到的结果应为：

dba> select * from t1;

±-----±-----+

| b1 | b2 |

±-----±-----+

| 1 | 8 |

| 2 | 8 |

| 3 | 8 |

| 4 | 8 |

| 5 | 4 |

±-----±-----+

5 rows in set (0.00 sec)

可见，在RC隔离级别下，如果支持STATEMENT格式的binlog，是有可能导致主从数据不一致的！

那么你可能会问，在RC隔离级别下，如果binlog格式为ROW或者MIXED，难道就不会有主从数据不一致的风险吗？答案是肯定的，如果binlog的格式是ROW或者MIXED，在RC隔离级别下，不会导致主从数据不一致。为什么呢？

因为ROW或者MIXED格式的binlog，是基于数据的变动。在进行update或者delete操作，记录到binlog，同时会把数据的原始记录写入到binlog。所以日志文件会比Statement大些，上述演示过程，binlog的记录顺序仍然是按照事务的commit顺序为序的，binlog的顺序仍然为：

会话2：

dba> set tx_isolation=‘read-committed’;

dba> BEGIN;

dba> update t1 set b2=4 where b2=2;

dba> commit;

会话1：

dba> set tx_isolation=‘read-committed’;

dba> BEGIN;（开启事务）

dba> update t1 set b2=8 where b2=4;

#会话1进行提交

dba> commit;

在从库仍然是按照这个binlog的执行时序，进行更新操作。但不同之处在于。会话2的update操作：

dba> update t1 set b2=4 where b2=2;

写入到binlog时，会把原始的记录也记录下来。它是这样记录的：

update dba.t1

where

b1=1

b2=2

set

b1=1

b2=4

update dba.t1

where

b1=3

b2=2

set

b1=3

b2=4

update dba.t1

where

b1=5

b2=2

set

b1=5

b2=4

从库上会话2的更新操作完成之后，接着执行会话1的更新操作：

dba> update t1 set b2=8 where b2=4;

binlog中的记录为：

update dba.t1

where

b1=2

b2=4

set

b1=2

b2=8

update dba.t1

where

b1=4

b2=4

set

b1=4

b2=8

这样从库看到的结果就是：

dba> select * from t1;

±-----±-----+

| b1 | b2 |

±-----±-----+

| 1 | 4 |

| 2 | 8 |

| 3 | 4 |

| 4 | 8 |

| 5 | 4 |

±-----±-----+

5 rows in set (0.00 sec)

这样主从就是一致的

a...Z

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
事务隔离

总结掌握程度:事务的概念是什么?mysql的事务隔离级别读未提交, 读已提交, 可重复读, 串行各是什么意思?读已提交, 可重复读是怎么通过视图构建实现的?可重复读的使用场景举例? 对账的时候应该很有用?事务隔离是怎么通过read-view(读视图)实现的?并发版本控制(MCVV)的概念是什么, 是怎么实现的?使用长事务的弊病? 为什么使用常事务可能拖垮整个库?事务的启动方式有...
复制链接

扫一扫