对于使用MySql的人员来说,事务这个概念并不陌生,事务就是保证一组数据要么全部成功,要么全部失败,在数据库中,事务的实现是依靠引擎层的,MySql是一个支持多引擎的系统,并非所有的引擎都支持事务,例如MyISam就不支持事务,所以这就是被InnoDB取代的原因之一
首先说下,Mysql的事务隔离等级,可以分为读未提交,读已提交,可重复读和串行化
读未提交:一个事务没有提交前,其变更能被其他事务看到
读已提交:一个事务提交后,其改变才可变
可重复读:一个事务执行过程中会创建一个图,保证在此事务中的可见性
串行化:对一个记录,写加写锁,读加读锁,保证同一时刻只能有一个事务执行
首先是不同级别的区别
一个表里只有一个数据
V1 V2 V3会分别返回什么呢?
如果为读未提交 V1能看到 2 V2为2 V3也为2
如果为读已提交,V1为1,V2为2,V3为2
如果为可重复度,V1 V2为1 V3为2
如果是串行化 V1 V2位1 V3为2
在MySql中,执行事务过程中会创建一个视图,整个访问过程以事务的逻辑结果为准,在可重复读的隔离级别下,视图在事务启动创建的,整个事务都在用这个视图
在读已提交过程,视图为Sql语句执行的时候才会创建
读未提交则不创建视图
串行化则是直接加锁
对于视图的隔离级别可以通过
set session TRANSACTION ISOLATION LEVEL Read committed
也可以通过show variables来查看当前的值
show variables like ‘transaction_isolation’
在Oracle中同样存在事务的概念,Oracle的默认级别是读已提交,所以别忘了设置
对于重点的级别,必然是可重复读级别
在MySql中,每次的更新装填都能记录一条回滚操作,方便通过回滚操作来得到前一个状态的值
当前值为4,但是事务根据启动时间的不同,有不同的read-view 如图,A B C中记录的为 1 2 4
同一条记录在系统中具有多个版本,就是所谓的MVCC多版本控制
也就是现在有一个事务将4改为5,也不会和事务A B C有所冲突
但是,回滚日志虽然并不会一直保留,但是只有判断没有用的时候才会将其删除,这也是为啥不建议使用长事务的原因,因为长事务可能一直保存着很老的事务视图,保存着可能用的所有回滚记录,占用了大量空间
在5.5之前,回滚日志和数据字典一起存放在ibdata中,所以即使事务被提交了,也不会减小文件
如何启动事务呢?
显式的启动: begin或者start transaction 配套提交的commit 回滚是rollback
或者,set autocommit = 0 将自动提交关闭,任何sql都会启动事务,而且不会自动提交,只有手动触发commit或者rollback语句才行
展开来讲MySQL的可重复读事务隔离性
事务如果启动的级别为可重复读的隔离,会在事务的启动时候创建一个视图read-view,这样在执行期间,如果其他的事务修改了数据,事务看到的仍然和启动的时候一样,这就带来了一个问题,不可能没开启一个事务都将数据库的事务进行一次保存吧,太占空间了
按照上面的思路,一个事务的执行,必然要等待其他事务的执行结束,不然如何知道执行后的值是什么呢
上述的执行流程中,
在使用 begin/start transaction 命令的时候,这个语句只有在真正的执行到一个Sql语句的时候,才会真正的启动事务
如果需要立刻启动一个事务,那么需要使用 start transaction with consistent snapshot 命令
在MySQL中,有视图的概念,
1.是view,用于查询语句的虚拟表,调用的时候执行查询语句并且生成结果
2.实现MVCC时候用的一致性视图,consistent read view,用于支持RC 读提交 和RR 可重复度隔离级别的实现
对于实际快照在MVCC中的实现
是因为InnoDB中,每个事务都有唯一的事务ID,每个事务在更新数据的时候,都会生成一个新的数据版本,并且把transaction id赋值给这个数据版本的事务ID,即为row_trx_id,但并不会因此舍弃旧的事务,能够有信息能够直接获取到旧版本的事务
比如下面的图
对于数据表中的一行数据,可能有多个版本的row,每个版本都有自己的row_trx_id
上面一行的数据呢,有4个版本,当前版本的数据,值为22,事务id为25
实际数据库呢,并不会实际的有上面的链表,而是利用undo log来进行计算的
那么一个事务的查找过程就很简单了,事务会在启动的时候声明,以我启动的时间为准,如果一个数据版本在我启动事务之后进行更新,我是不会认的,只有往前不断寻找,直到我认识的事务id
实际实现上,,InnoDB为每一个事务构造了一个数组,保存事务启动的时候,所有活跃的事务ID,事务的ID的最小值认为是数组的低水位,已经创建的事务的最大值的ID+1记为高水位,视图的数组和高水位构成了事务的一致性视图
事务的可见性原则,就是基于了数据的row trx_id和这个一致性的视图进行比较的结果
这就有如下的几种情况产生了
1.如果在绿色部分,说明是已经提交的事务和事务自己产生的,这个数据是可见的
2.如果是红色的,说明是未开始的事务,不可见的
3.如果是黄色的部分,说明包括两种情况
(1) row trx_id在数组中,说明是没有提交的事务提交的,不可见
(2) row trx_id不在数组中,说明是已经提交的事务生成的,可见
这说明在生成这个事务的时候,没有在数组的事务已经提交了,也就是可见的
利用这种特性,InnoDB做到了所有的数据都有多个版本,实现了秒级创建快照能力
解释完了查找的流程,接下来进行更新了
因为如果在更新数据的时候,还要回去查找的话,会造成数据的覆盖丢失,于是更新数据都是先读后写,这个读是当前的值,称为当前读
同样,如果使用了for update和share mode,都可以读取到最新版本的数据,因为采用了读写锁
来说下事务的可重复读的能力如何实现的,就是一致性读
事务更新数据的时候,只能用当前读,如果当前的读被占用了,进入锁的等待
读已提交和可重复读的逻辑类似,
在可重复读的隔离级别下,只会在事务开始的时候创建一个视图,然后一直使用这个视图
在读已提交的隔离级别下,会在每个语句执行前重新算出一个视图
对于真正的可重复读的隔离级别,更新逻辑如下
假设k一开始是1,实际在事务B提交时候,k是3
为何呢?事务 B 的视图数组是先生成的，之后事务 C 才提交，不是应该看不见 (1,2) 吗，怎么能算出 (1,3) 来？
那么,如果事务 B 在更新之前查询一次数据，这个查询返回的 k 的值确实是 1。
当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务 C 的更新就丢失了。因此，事务 B 此时的 set k=k+1 是在（1,2）的基础上进行的操作。
这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read)
这样,除了update语句之外,select 语句如果加锁，也是当前读。
所以，如果把事务 A 的查询语句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以读到版本号是 101 的数据，返回的 k 的值是 3。下面这两个 select 语句，就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）。
mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;
如果数据是这样的呢?
事务 C’的不同是，更新后并没有马上提交，在它提交前，事务 B 的更新语句先发起了。前面说过了，虽然事务 C’还没提交，但是 (1,2) 这个版本也已经生成了，并且是当前的最新版本。那么，事务 B 的更新语句会怎么处理呢？
这时候，我们在上一篇文章中提到的“两阶段锁协议”就要上场了。事务 C’没提交，也就是说 (1,2) 这个版本上的写锁还没释放。而事务 B 是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C’释放这个锁，才能继续它的当前读。
这样就是可重复读+行锁+当前读
可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
最后一个问题的解答:
为何获取不到最新的数据,并进行更新呢?
可能有一种情况:
就是在事务A执行期间,有一个事务B进行了更新,导致事务A在进行更新的时候获取最新的值获取不到
无法更新,而且在读取的时候,会利用up_limit_id进行回滚查找,找到update之前的值
从侧面证明了一个道理:事务启动的时候会保存现在正在执行的所有事务ID,如果一个row_trx_id在这个列表中,也会不可见
最后关于事务,需要了解:
1.可以通过information_shcema库汇总的innodb_trx这个表来查询事务
2.如何避免长事务
从开发来看
首先是否使用了set autocommit = 0,这个需要注意避免使用
然后确定是否有不必要的只读事务,将select语句尽可能的从事务中去掉
最后可以设置SET MAX_EXECUTION_TIME来避免一个语句执行时间过长
从数据库来看
查看information_shcema.Innodb_trx,设置避免长事务的阀值,有就直接杀掉
查看general_log,避免问题
设置 innodb_undo_tablespaces 为2,方便大事务回滚清理