MySQL事务原理详解

MySQL事务详解

insert语句执行流程

事务介绍

 

过多的就不废话了,数据库事务具有ACID四大特性。ACID是以下4个词的缩写

ACID四大特性

  • 原子性

    • 事务最小工作单元,要么全成功,要么全失败

  • 一致性

    • 事务开始和结束后,数据库的完整性不会被破坏

  • 隔离性

    • 不同事务之间互不影响,四种隔离级别为RU(读未提交)、RC(读已提交)、RR(可重复读)、SERIALIZABLE (串行化)

  • 持久性

    • 事务提交后,对数据的修改是永久性的,即使系统故障也不会丢失

隔离级别

在理解下面之前先要明白几个术语的意义

  • 脏读:一个事务读取到另一个事务未提交的数据

读未提交(READ UNCOMMITTED/RU )

  • 如果一个事务读到了另一个未提交事务修改过的数据,那么这种隔离级别就称之为读未提交

  • 暴露问题:(脏读)

读已提交(READ COMMITTED/RC )

  • 如果一个事务只能读到另一个已经提交的事务修改过的数据,

  • 并且其他事务每对该数据进行一次修改并提交后,该事务都能查询得到最新值,

  • 那么这种隔离级别就称之为读已提交

  • 暴露问题:(不可重复读)

    • 一个事务因读取到另一个事务已提交的update。导致对同一条记录读取两次以上的结果不一致

可重复读(REPEATABLE READ/RR )

  • 一个事务只能读到另一个已经提交的事务修改过的数据

  • 但是第一次读过某条记录后,即使其他事务修改了该记录的值并且提交,

  • 该事务之后再读该条记录时,读到的仍是第一次读到的值,

  • 而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读

  • 暴露问题:(幻读)

    • 一个事务因读取到另一个事务已提交的insert数据或者delete数据。导致对同一张表读取两次以上的结果不一致

串行化(SERIALIZABLE )

  • 以上3种隔离级别都允许对同一条记录进行 读-读 、 读-写 、 写-读 的并发操作

  • 如果我们不允许 读-写 、写-读 的并发操作,可以使用串行化隔离级别

相关数据库命令

  • 查看当前事务级别

    • select @@tx_isolation;

  • 设置事务级别

    • set session transaction isolation level [read uncommitted / read committed / repeatable read / serializable ];

事务和MVCC底层原理详解

丢失更新思考

两个事务针对同一数据都发生修改操作时,会存在丢失更新问题

张三和李四在银行各有存款1000

时间线银行家张三 、 李四
20:01 查看银行总存款数(开启事务)  
20:02 读取到张三有1000余额  
20:03   张三向李四转钱 500
20:04   转款完成:张三:500 --- 李四:1500
20:05 读取到李四有1500余额  
20:06 统计得到银行总存款数为 :2500  

解决方案1:LBCC

  • LBCC:基于锁的并发控制

    • 银行家读取存款总额时,会对读取的行--》上锁

    • 张三向李四转账,发现自己账户被锁,操作被阻塞

    • 银行家读取存款总额操作完成--》释放锁

    • 张三向李四转账的操作开始执行

  • 这种方案比较简单粗暴,就是一个事务去读取一条数据的时候,就上锁,不允许其他事务来操作

    • MySQL加锁之后就是当前读

    • 假如当前事务只是加共享锁,那么其他事务就不能有排他锁,也就是不能修改数据

    • 假如当前事务需要加排他锁,那么其他事务就不能持有任何锁

    • 总而言之,能加锁成功,就确保了除了当前事务之外,其他事务不会对当前数据产生影响

解决方案2:MVCC

  • MVCC:多版本的并发控制

    • 银行家准备读取存款总额(事务开启)

    • 读取张三的余额为1000

    • 张三向李四转账500,转账成功

    • 读取李四的余额为1000(读取的是事务开启时,所有数据的副本,所以读取的不是最新的1500)

  • 该方案对于select 读不会对数据加锁,提高了数据并发处理性能

  • 借助该方案,数据库可以实现 [读已提交,可重复读]等隔离级别

InnoDB的MVCC实现

  • 来自官方翻译:

    • MVCC是用于数据库提供并发访问控制的并发控制技术。与MVCC相对的,是基于锁的并发控制

    • MVCC最大的好处,相信也是耳熟能详:读不加锁,读写不冲突

    • 在读多写少的应用中,读写不冲突是非常重要的,极大的增加了系统的并发性能

    • 多版本并发控制仅仅是一种技术概念,并没有统一的实现标准

    • 其核心理念就是数据快照,不同的事务访问不同版本的数据快照,从而实现不同的事务隔离级别

    • 虽然字面上是说具有多个版本的数据快照,但这并不意味保存多份数据文件,这样会浪费大量的存储空间

    • InnoDB通过事务的undo日志巧妙地实现了多版本的数据快照

InnoDB的MVCC

  • InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的

  • 这两个列,一个保存了行的事务ID,一个保存了行的回滚指针

    • 每开始一个新的事务,都会自动递增产生一个新的事务id

    • 事务开始时会把事务id放到当前事务影响的行事务id中,当查询时需要用当前事务id和每行记录的事务id进行比较

下面我们我们一起来看看在可重复读的隔离级别下,MVCC的操作

  • MVCC只在可重复读读已提交两个隔离级别下工作

    • 因为读未提交总是读取最新的数据行,而不是符合当前事务版本的数据行

    • 串行化则会对所有读取的行都加锁

  • select

    • InnoDB 会根据以下两个条件检查每行记录

    • InnoDB只查找版本早于当前事务版本的数据行

      • 确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的

    • 行的删除版本要么未定义,要么大于当前事务版本号

      • 确保事务读取到的行,在事务开始之前未被删除

    • 只有符合上述两个条件的记录,才能返回作为查询结果

  • insert

    • InnoDB为新插入的每一行保存当前事务编号作为行版本号

  • delete

    • InnoDB为删除的每一行保存当前事务编号作为行删除标识

  • update

    • InnoDB为插入一行新记录,保存当前事务编号作为行版本号,同时保存当前事务编号到原来的行作为行删除标识

    • 保存这两个额外事务编号,使大多数读操作都可以不用加锁

    • 不足之处是每行记录都需要额外的存储空间,需要做更多的行检查工作,以及一些额外的维护工作

undo log

  • 根据行为的不同,undo log 分为两种

    • insert undo log

      • 是在 insert 操作中产生的 undo log

      • 因为 insert 操作的记录只对事务本身可见,对于其它事务此记录是不可见的,

      • 所以 insert undo log可以在事务提交后直接删除而不需要进行 purge 操作

    • update undo log

      • update undo log是指在delete和update操作中产生的undo log

      • 因为会对已经存在的记录产生影响 ,该undo log会被后续用于MVCC当中,因此不能提交的时候删除

      • 提交后会放入undo log的history list链表,等待purge线程进行最后的删除

      • 如果我们再次开启修改该数据,如下图所示

      • 为了保证事务并发操作时,在写各自的undo log时不产生冲突,InnoDB采用回滚段的方式来维护undolog的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式

Read View

  • 设计InnoDB的人提出了一个ReadView的概念。

  • 这个概念主要包含当前系统中还有那些活跃的读写事务

  • 把他们的食事务ID放到一个列表中,我们把这个列表命名为:m_ids

  • 这样在访问某条记录时,只需要按照下边的步骤判断记录的某个版本(版本链中的版本)是否可见

    • 如果被访问版本的trx_id属性值小于m_ids列表中最小的事务ID:

      • 表明生成该版本的事务在生成ReadView 前已经提交,所以该版本可以被当前事务访问

    • 如果被访问版本的 trx_id 属性值大于 m_ids 列表中最大的事务ID:

      • 表明生成该版本的事务在生成ReadView 后才生成,所以该版本不可以被当前事务访问

    • 如果被访问版本的 trx_id 属性值在 m_ids 列表中最大的事务id和最小事务id之间:

      • 那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中,

      • 如果在,说明创建 ReadView 时生成该版本的事务还是活跃的,该版本不可以被访问;

      • 如果不在,说明创建 ReadView 时生成该版本的事务已经被提交,该版本可以被访问

  • 如果某个版本的数据对当前事务不可见的话,那就顺着版本链找到下一个版本的数据,继续按照上边的步骤判断可见性,依此类推,直到版本链中的最后一个版本,如果最后一个版本也不可见的话,那么就意味着该条记录对该事务不可见,查询结果就不包含该记录

读已提交和可重复度生成ReadView的时机

  • 在MySQL中,两者非常大的区别就是他们生成ReadView的时机不同

读已提交生成ReadView

  • 每次读取数据前都生成一个ReadView

  • 比方说现在系统里有两个 id 分别为 100 、 200 的事务在执行

事务ID:100事务ID:200
start start
update t set c = '关羽' where id = 1; 更新了一些别的表的数据
update t set c = '张飞' where id = 1;  
没有提交 没有提交
  • 事务执行过程中,只有在第一次真正修改记录时(比如使用INSERT、DELETE、UPDATE语句),才会被分配一个单独的事务id,这个事务id是递增的

  • 此时表c中id为1的记录得到的版本链为:

  • 此时我们发起一个使用读已提交隔离级别的查询语句:select * from c where id = 1

    • 得到的值会是刘备的那条数据

    • 首先在执行select时,就会生成一个ReadView,ReadView的m_ids列表的数据为:【100、200】

    • 然后从版本链中依次挑选对比得到可见的记录:

      • 首先张飞这条数据的trx_id为100,在m_ids列表内,不符合可见性要求,然后更具roll_pointer跳到下一个版本

      • 来到了关于这条数据,发现trx_id在在m_ids列表内,也不符合可见性要求,继续跳

      • 来到了刘备这一条数据,发现trx_id小于m_ida中最小的实物ID100,于是这个版本可见,便返回

  • 如果我们现在把事务ID为100的提交了,然后再去事务ID为200中更新一下t表

事务ID:100事务ID:200
start  
update t set c = '关羽' where id = 1;  
update t set c = '张飞' where id = 1;  
commit  
  start
  update t set c = '诸葛匹夫' where id = 1;
  update t set c = '黄忠小儿' where id = 1;
  未提交

此时 t 表的记录版本链长这样

  • 此时我们再发起一个使用读已提交隔离级别的查询语句:select * from c where id = 1

    • 得到的值会是张飞的那条数据

    • 首先在执行select时,就会生成一个ReadView,ReadView的m_ids列表的数据为:【200】

      • 100那个事务已经提交了,所以生成快照时没有它了

    • 然后从版本链中依次挑选对比得到可见的记录:

      • 黄忠小儿这条数据,由于trx_id在m_ids中,不符合可见性要求,往下跳

      • 诸葛匹夫这条数据同理,也不符合可见性要求,继续往下跳

      • 张飞这条数据的trx_id为100,比m_ids中最小的实物ID200要小、

      • 所以返回了张飞这条数据

  • 以此类推,如果200事务ID也提交了,在读已提交的隔离级别事务中查询t表id为1的数据就该是:黄忠小儿了

  • 使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView

可重复读生成ReadView

  • 在事务开始后第一次读取数据时生成一个ReadView

  • 比方说现在系统里有两个 id 分别为 100 、 200 的事务在执行

事务ID:100事务ID:200
start start
update t set c = '关羽' where id = 1; 更新了一些别的表的数据
update t set c = '张飞' where id = 1;  
没有提交 没有提交
  • 此时表c中id为1的记录得到的版本链为:

  • 此时我们发起一个使用可重复读隔离级别的查询语句:select * from c where id = 1

    • 得到的值会是刘备

    • 首先在执行select时,就会生成一个ReadView,ReadView的m_ids列表的数据为:【100、200】

    • 然后从版本链中依次挑选对比得到可见的记录:

      • 首先张飞这条数据的trx_id为100,在m_ids列表内,不符合可见性要求,然后更具roll_pointer跳到下一个版本

      • 来到了关于这条数据,发现trx_id在在m_ids列表内,也不符合可见性要求,继续跳

      • 来到了刘备这一条数据,发现trx_id小于m_ida中最小的实物ID100,于是这个版本可见,便返回

  • 如果我们现在把事务ID为100的提交了,然后再去事务ID为200中更新一下t表

事务ID:100事务ID:200
start  
update t set c = '关羽' where id = 1;  
update t set c = '张飞' where id = 1;  
commit  
  start
  update t set c = '诸葛匹夫' where id = 1;
  update t set c = '黄忠小儿' where id = 1;
  未提交

此时 t 表的记录版本链长这样

  • 此时我们再发起一个使用可重复读隔离级别的查询语句:select * from c where id = 1

    • 得到的值就会是刘备的那条数据,而不是和上面一样的张飞

    • 首先在执行select时,因为之前已经生成过RaadView了,直接复用:m_ids仍然为:【100、200】

      • 100那个事务虽然已经提交了,但是我们使用的是旧版的ReadView,所以仍然有它

    • 然后从版本链中依次挑选对比得到可见的记录:

      • 黄忠小儿这条数据,由于trx_id在m_ids中,不符合可见性要求,往下跳

      • 诸葛匹夫这条数据同理,也不符合可见性要求,继续往下跳

      • 张飞这条数据同理,也不符合可见性要求,继续往下跳

      • 关羽的这条数据同理,也不符合可见性要求,继续往下跳

      • 刘备这条数据符合可见性要求,rtx_id小于 m_ids中最小的实物ID 100

      • 所以返回了刘备这条数据

  • 就算我们再把事务id为 200 的记录也提交了

  • 之后再使用可重复读隔离级别的事务中继续查找这个id为 1 的记录,得到的结果还是 '刘备'

MVCC总结

  • 从上边的笔记中我们可以看出来,所谓的MVCC(Multi-Version Concurrency Control ,多版本并发控制)指的就是在使用 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行普通的SEELCT 操作时访问记录的版本链的过程,这样子可以使不同事务的 读-写 、 写-读 操作并发执行,从而提升系统性能

  • READ COMMITTD 、 REPEATABLE READ 这两个隔离级别的一个很大不同就是生成 ReadView 的时机不 同, READ COMMITTD 在每一次进行普通 select 操作前都会生成一个 ReadView ,而 REPEATABLEREAD More Actions只在第一次进行普通 select操作前生成一个,之后的查询操作都重复这个ReadView

MVCC下的读操作

  • 在MVCC并发控制中,读操作可以分成两类:快照读 (snapshot read)与当前读 (current read)

    • 快照读:读取的是记录的可见版本 (有可能是历史版本),不用加锁

    • 当前读:读取的是记录的最新版本,并且当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录

  • 在一个支持MVCC并发控制的系统(读已提交、可重复读)中,哪些读操作是快照读?哪些操作又是当前读呢 ?

    • 当然储存引擎命中为:InnoDB

当前读

特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。 加行写锁 读当前版本

简单的select操作,属于快照读,不加锁。(当然,也有例外,下面会分析) 不加读锁 读历史版本

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 ?;
  • 以上的sql都属于当前读,读取记录的最新版本,并且读取之后还保证其他并发事务不能修改当前的记录对读取加锁

    • 第一条sql,对读取记录加了S锁(共享锁)

    • 下面其他的sql,都对读取几率加了X锁(排他锁)

  • 一个update table set ... where ... 的流程是什么样的呢

    • 当update语句被发给MySQL之后

    • MySQL会根据where条件,读取第一条满足条件的数据,然后InnoDB会将其返回并加锁

    • MySQL接收到InnoDB返回的记录后,会再发起一个update请求,更新这条记录

    • 循环往复,直到该表中没有符合where条件的数据记录

    • 所以update内部就包含了一个当前读,delete同理

    • insert操作会稍有不同,可能会触发Uniqu Key冲突检查,也会进行一个当前读

    • 针对一条当前读的sql语句,InnoDB和MySQL的交互都是一条一条进行的,加锁也是一个一个的加锁

快照读

  • 快照读也就是一致性非锁定读,是指InnoDB存储引擎通过多版本控制(MVCC)读取当前数据库中行数据的方式

  • 如果读取的行正在执行delete或update操作,这时读取操作不会因此去等待行上锁的释放。相反地,InnoDB会去读取行的一个最新可见快照

  • 都是上面这一套演示,在不同的事务隔离级别情况下查询到的数据是不一样的

事务回滚和数据恢复

  • 事务的隔离性由多版本控制机制和锁实现,而原子性,持久性和一致性主要是通过redo logundo logForce Log at Commit机制机制来完成的

    • redo log用于在崩溃时恢复数据

    • undo log用于对事务的影响进行撤销,也可以用于多版本控制

    • Force Log at Commit机制保证事务提交后redo log日志都已经持久化

  • 开启一个事务后,用户可以使用commit来提交,也可以用rollback来回滚。其中commit或者rollback执行成功之后,数据一定是会被全部保存或者全部回滚到最初状态的,这也体现了事务的原子性。但是也会有很多的异常情况,比如说事务执行中途连接断开,或者是执行commit或者rollback时发生错误,Server Crash等,此时数据库会自动进行回滚或者重启之后进行恢复

  • 我们先来看一下redo log的原理,redo log顾名思义,就是重做日志,每次数据库的SQL操作导致的数据变化它都会记录一下,具体来说,redo log是物理日志,记录的是数据库页的物理修改操作。如果数据发生了丢失,数据库可以根据redo log进行数据恢复。

  • InnoDB通过Force Log at Commit机制实现事务的持久性,即当事务commit时,必须先将该事务的所有日志都写入到redo log文件进行持久化之后,commit操作才算完成

  • 当事务的各种SQL操作执行时,即会在缓冲区中修改数据,也会将对应的redo log写入它所属的缓存。当事务执行commit时,与该事务相关的redo log缓冲必须都全部刷新到磁盘中之后commit才算执行成功

  • 数据库日志和数据落盘机制之前我们已经详细说明,这里就不再二次累字数了

  • redo log写入磁盘时,必须进行一次操作系统的fsync操作,防止redo log只是写入了操作系统的磁盘缓存中。参数innodb_flush_log_at_trx_commit可以控制redo log日志刷新到磁盘的策略

  • 数据库崩溃重启后需要从redo log中把未落盘的脏页数据恢复出来,重新写入磁盘,保证用户的数据不丢失。当然,在崩溃恢复中还需要回滚没有提交的事务。由于回滚操作需要undo日志的支持,undo日志的完整性和可靠性需要redo日志来保证,所以崩溃恢复先做redo恢复数据,然后做undo回滚

  • 在事务执行的过程中,除了记录redo log,还会记录一定量的undo log。undo log记录了数据在每个操作前的状态,如果事务执行过程中需要回滚,就可以根据undo log进行回滚操作

  • undo log的存储不同于redo log,它存放在数据库内部的一个特殊的段(segment)中,这个段称为回滚段。回滚段位于共享表空间中。undo段中的以undo page为更小的组织单位。undo page和存储数据库数据和索引的页类似。因为redo log是物理日志,记录的是数据库页的物理修改操作。所以undolog(也看成数据库数据)的写入也会产生redo log,也就是undo log的产生会伴随着redo log的产生 ,这是因为undo log也需要持久性的保护。如上图所示,表空间中有回滚段和叶节点段和非叶节点段,而三者都有对应的页结构

  • 我们再来总结一下数据库事务的整个流程,如下图所示

    • 大致事务流程:

      • 事务进行过程中,每次DML sql语句执行,都会记录undo log和redo log,

      • 然后更新数据形成页,然后redo log按照时间或者空间等条件进行落盘,

      • undo log和脏页按照checkpoint进行落盘,落盘后相应的redo log就可以删除了。

      • 此时,事务还未COMMIT,如果发生崩溃,则首先检查checkpoint记录,使用相应的redo log进行数据和undo log的恢复,

      • 然后查看undo log的状态发现事务尚未提交,然后就使用undo log进行事务回滚。

      • 事务执行commit操作时,会将本事务相关的所有redo log都进行落盘,只有所有redo log落盘成功,才算commit成功。

      • 然后内存中的数据脏页继续按照checkpoint进行落盘。如果此时发生了崩溃,则只使用redo log恢复数据

.

 

posted @ 2019-05-29 23:43  鞋破露脚尖儿  阅读(755)  评论(0编辑  收藏  举报