《MySQL技术内幕:InnoDB存储引擎》7. 事务

最新推荐文章于 2022-09-05 20:01:46 发布

larva_s

最新推荐文章于 2022-09-05 20:01:46 发布

阅读量307

点赞数

分类专栏： MySQL 文章标签：事务

本文链接：https://blog.csdn.net/larva_s/article/details/87890437

版权

MySQL 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

认识事务

事务概念

确保要么所有修改都已经保存了，要么所有修改都不保存。不同数据库厂商也没遵循严格的事务ACID特性，
不同隔离级别的事务有不同的ACID定义。

原子性（Atomicity）

原子性是指事务是不可分割的工作单位，若是只读事务，则保持原子性很简单，不修改数据。
一致性

有更新或新增的事务，就需要控制某个动作失败了，能回滚到初始状态。
隔离性

不同事务之间相互隔离，粒度锁策略：事务仅锁住一个实体对象的子集
持久性

事务一旦提交，其结果就是永久性的，这也是保证高可靠性。（不能保证高可用：解决硬件或者自然灾害等导致的问题才是高可用）

事务的分类

事务类型	介绍
扁平式事务	所有操作都位于同一层次，其间是原子性操作
带有保存点的扁平式事务	可以回滚到指定保存点的状态，而不是全部回滚，开销代价大
链式事务	保存点模式的一种变种，系统崩溃时，所有保存点消失，而非持久，链式事务就是提交事务操作和开始下一个事务的操作合并成一个原子性操作，只能恢复到最近的一个保存点
嵌套事务	任意一个事务回滚，其所有子事务都会回滚，只有叶子节点才能访问数据库，将顶层事务和父事务作为逻辑控制;提交操作并不会马上生效，除非其父事务已经提交。
分布式事务	分布式环境，需要多个网络节点的数据库参与的事务，比如从银行A转账到银行B，此时两个银行的数据库节点不同，此时就是需要分布式事务了。

事务的实现

redo和undo

redo log并不是在事务提交时，才写入的，而是事务执行过程中便写入到redo log：保证即使宕机了，事务还未提交的状态下，能够从redo log中恢复

类型	介绍
redo	1. 重做日志，保证事务的原子性和一致性 2. 恢复提交事务修改的页操作 3. 物理日志，记录的是页的物理修改操作
undo	1. 保证事务的一致性 2. 回滚行记录到某个特定版本 3. 逻辑日志，根据每行记录进行记录 4. 回滚时，仅将数据从逻辑上恢复到之前的状态，而不是操作物理页面，和redo不一样

redo：重做日志

redo是由两部分组成：内存中的重做日志缓冲（易失），还有重做日志（持久）

InnoDB：必须将该事务的所有日志写入到重做日志文件进行持久化，待事务commit操作完成才算完成

重做日志分类

redo日志分类	区别
redo log	1. 保证事务持久性 2. 基本是顺序写，不需要对redo log文件进行读取操作
undo log(不是undo日志)	1. 帮助事务回滚以及MVCC功能 2. 随机读写的

确保重做日志写入磁盘，需要进行一次fsync操作，可以通过设置参数来设置这一属性的策略，毕竟每次事务都需要从内存缓冲区刷出到磁盘中，调用fsync太耗资源了

1533136652059

重做日志都是以512字节存储的，和磁盘扇区一样大，写入能保证原子性。
重做日志是基于页的

LSN:Log sequence number,表示日志写入重做日志的字节总量，LSN不止存在于重做日志中，还存在于页中，页中的LSN可以用来判断是否需要进行恢复操作

LOG
---
Log sequence number 2668297  当前的LSN
Log flushed up to   2668297  刷新到重做日志文件的LSN
Pages flushed up to 2668297  
Last checkpoint at  2668288  刷新到磁盘的LSN
0 pending log flushes, 0 pending chkp writes
10 log i/o's done, 0.00 log i/o's/second
----------------------

InnoDB不管上一次关闭是否正常，每次启动都会尝试恢复操作。重做日志记录的是物理日志，所以恢复速度比逻辑日志快

1533137122745

undo：撤销/回滚操作

undo存放在数据库内部的一个特殊段中（segment），成为undo段，undo段位于共享表空间中

undo作用一：回滚

回滚	回滚操作
insert回滚	对应delete
update回滚	对应update回之前的数据
物理上的操作：比如申请了空间不能做回滚，只能逻辑上将数据回滚

undo作用二：MVCC

若用户读取一行记录时，该记录被其他事务锁住，则通过undo读取之前的行版本信息，实现非锁定读取。

undo log会产生redo log

undo格式

insert undo log：

update undo log：因为可能会提供MVCC机制，就不能在事务提交时删除，提交时放入undo链表，等待purge线程进行最后的删除。

purge

delete from t where a = 1；

对于delete操作，undo log仅仅是将主键等于1的记录delete flag 设置为1，记录并没有被删除，还存在于B+

树中，最终的删除操作是在purge中完成的，这样设计的原因，InnoDB支持MVVC，所以记录不能在事务提交时立即进行处理，以免有其他事务正在使用这些数据，若该行记录已不被其他事务所使用则才能真正的delete操作。

group commit

InnoDB对于事务的提交，包含两部分：

①修改内存中的信息，并将日志写入重做日志缓冲中。

②调用fsync确保将日志从重做日志缓冲刷新到磁盘中。

每次提交事务的话，都要调用fsync则消耗大，磁盘的fsync的效率的有限的，则通过group commit一次fsync刷新就能确保多个事务日志写入文件。

事务控制语句

commit

commit work:控制事务结束后的行为是chain还是release，chain则为链事务。

commit and chain：提交并开启一个相同隔离级别的事务

commit and release：提交后自动断开与服务器的连接

completion_type值	含义
0	此时commit work和commit的意义一样。
1	此时commit work等同于commit and chain 表示自动开启一个相同隔离级别的事务
2	commit work等同于commit and release

delete from t_record_lock;

select @@autocommit;

set @@completion_type = 1;

-- 若是此时completion_type为0，则下面rollback后不会回滚数据，为1时，是链式事务，commit后自动开启另一个事务，会回滚数据。

begin;

insert into t_record_lock select 1;

commit work;

insert into t_record_lock select 2;

rollback;

select * from t_record_lock;

rollback

rollback和commit才是提交事务，rollback to savepoint t1只是回滚到保存点，并不提交事务。

隐式提交的sql语句

truncate

语句	区别
delete from	删除数据，每条删除的数据都会写入日志中，可恢复只删除内容，不删除定义、不释放表空间
truncate table	删除数据以及是释放表空间，不删除定义属于DDL语句，不能被回滚恢复
drop table	删除数据、定义、释放表空间

事务的隔离级别

read uncommitted：读未提交–>脏读，违反隔离性

两个session，都开启了事务，通过update或insert修改了同一个表的数据后，另一个事务能够及时读取到新修改的数据，也就是读取了未提交的事务，未提交的事务中，数据的改变并不一定是最终结果，可能还会再次变动，读取的数据可能无效，为脏数据，这就是脏读。

read committed：读已提交（不可重复读），违法一致性

两个session，都开启了事务，session A通过update或insert修改了同一个表的数据后，另一事务不能读取到修改的书，得session A提交之后，才能取到数据，也就是读取的数据是已提交的，此时session B读取的是快照数据，最新行修改后的快照数据。

repeatable read：可重复读–>幻读丢失更新

两个session，都开启了事务，session A通过update或insert修改了同一个表的数据后，即使session A提交了事务，session B还是读取不到，此时session B读取的是快照数据，是事务开始时的快照数据，这样保证了session B执行过程中，不会受到其他session修改数据导致的影响。

而在session A开启事务时，session B新增了数据，提交了，而session A却因为事务隔离级别，只能看到事务开始前的数据，也就看不到session B新增的数据，此时session A也新增了和session B新增的数据一样，数据库就提示错误，可在session A中又查不到该数据，以为出现了幻觉，这就是幻读。

丢失更新：逻辑上来说，用户A和用户B同时用同一个账号转账，此时先查询到缓存起来的金额数据，然后通过这个金额数据去逻辑判断进行转账等，就有可能导致有一个用户的转账修改信息丢失，此时就需要串行化了。

mysql> select * from t where a > 3;
+----+------+
| a  | b    |
+----+------+
|  5 |    6 |
|  7 |    8 |
|  9 |    9 |
| 22 |   33 |
| 23 |   33 |
| 24 |   24 |
| 29 |   90 |
+----+------+
7 rows in set (0.00 sec)

mysql> insert into t select 32,23;上诉语句找不到a为32的记录，插入却提示冲突，出现了幻读
ERROR 1062 (23000): Duplicate entry '32' for key 'PRIMARY'
mysql> select * from t where a = 32;
Empty set (0.00 sec)

serializable：串行化

两个session，都开启了事务，session A通过update或insert修改了同一个表的数据后，另一个session不能修改数据，被session A锁住了，保证了session A执行过程中，整体数据一致性，这是最高的隔离级别，不过并发性能低。

serializable会为每个select语句加上lock in share mode，每个读取都有共享锁（这样修改该读取记录时，就得等待共享锁释放），主要用于分布式事务。

InnoDB使用的是repeatable read，可重复读，虽然普通的repeatable read会导致幻读的现象，而InnoDB则通过next-key lock解决了这种问题（貌似不是完全解决），所以InnoDB使用repeatable read就足够了。

分布式事务

外部XA事务

分布式事务必须用serializable隔离级别，XA事务由多个资源管理器(通常一个数据库就是一个资源管理器)、一个事务管理器（管理资源之间的通信）、以及一个应用程序组成。

分布式事务使用两段式提交（two-phase commit）:

① 所有参与全局事务的节点都开始准备（prepare）

② 事务管理器告诉资源管理器（数据库）执行rollback还是commit

若是任意一个节点显示不能提交，则全部都需要回滚。

mysql> xa start 'a';
mysql> insert into t_record_lock select 11;
mysql> xa end 'a';
mysql> xa prepare 'a';-- 比起普通事务，分布式事务多了一个prepara：告知事务管理器准备好了
mysql> xa recover;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
|        1 |            1 |            0 | a    |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)
mysql> xa commit 'a';

内部XA事务

资源管理器不是数据库本身，而是在存储引擎和插件、或者存储引擎和存储引擎之间的事务，称为内部XA事务。

例如binlog和InnoDB之间，在事务提交时，需要先写入到二进制文件，再写入到重做日志，这两个操作是原子性操作，不然会导致主从不一致（主机写入到了二进制以及重做日志，而从机只写入一部分，则数据不一致）

不好的事务处理习惯

循环中提交事务–尽量减少提交次数

create table t1(id int ,b char(80))engine = innodb;
-- ①循环中提交事务：InnoDB是默认自动提交事务，所以不用手动commit
delimiter //
create procedure load1(count int unsigned)
begin
    declare s int unsigned default 1;
    declare c char(80) default repeat('a',80);
    while s <= count do
        insert into t1 select null,c;
        commit;
        set s = s + 1;
    end while;
end;
//

create procedure load2(count int unsigned)
begin
    declare s int unsigned default 1;
    declare c char(80) default repeat('a',80);
        while s <= count do
        insert into t1 select null,c;
        set s = s + 1;
    end while;
end;
//

-- ②上面两个存储过程中，在循环中提交事务，每次提交都要写一次重做日志，效率低，下面的存储过程则一次提交即可
create procedure load3(count int unsigned)
begin
    declare s int unsigned default 1;
    declare c char(80) default repeat('a',80);
    start transaction;
        while s <= count do
            insert into t1 select null,c;
            set s = s + 1;
        end while;
    commit;
end;
//
delimiter ;

mysql> call load1(10000);
Query OK, 0 rows affected (14.43 sec)

mysql> truncate t1;
Query OK, 0 rows affected (0.03 sec)

mysql> call load2(10000);
Query OK, 1 row affected (24.97 sec)

mysql> delete from t1;
Query OK, 10000 rows affected (0.03 sec)

mysql> call load3(10000);
Query OK, 0 rows affected (0.19 sec)
-- 由上执行即可知道，循环提交的事务效率有多低了，而上面还有一点要注意的，就是truncate table会将数据以删除及表空间释放，而delete操作只删除数据，表空间不释放，且delete有写入到重做日志而truncate没有，一般使用delete而不用truncate就是因为无法恢复。

自动提交事务

MySQL默认是自动提交事务，可以通过start transaction或begin来显示控制事务的开启，这两个命令会使MYSQL自动执行set autocommit = 0的命令，并在commit或rollback结束一个事务时，执行set autocommit = 1修改会原来的默认自动提交。

使用自动回滚

create procedure sp_auto_rollback_demo()
begin
declare exit handler for sqlexception rollback;select -1;end;-- 异常时会返回-1
start transaction;
insert into t_record_lock select 1;
insert into t_record_lock select 2;
insert into t_record_lock select 1;
insert into t_record_lock select 3;
commit;
select 1;
end;

一般控制异常是在程序代码层，通过捕获异常得到对应的异常情况，而sql层次的异常就如上上诉语句一样，只能返回是否有异常而不能知道对应的具体错误。

larva_s

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
《MySQL技术内幕:InnoDB存储引擎》7. 事务

文章目录认识事务事务概念事务的分类事务的实现redo和undoredo：重做日志undo：撤销/回滚操作undo作用一：回滚undo作用二：MVCCundo格式purgegroup commit事务控制语句commitrollback隐式提交的sql语句truncate事务的隔离级别read uncommitted：读未提交--&gt;脏读，违反隔离性read committed：读已提交（不可重...
复制链接

扫一扫