mysql隐式事务_MySQL 存储引擎(二)

InnoDB核心特性--事务支持

什么是事务，可以看作为交易。

物换物，等价交换。货币换物，等价交换。虚拟货币换物(虚拟物品)，等价交换。

数据库中为了保证线上交易的“和谐”，加入了“事务”工作机制。

事务ACID特性

原子性

不可再分性：一个事务生命周期中的DML语句，要么全成功要么全失败，不可以出现中间状态。begin;DML1;DML2;DML3;commit;

一致性

事务发生前，中，后，数据都最终保持一致。CR  + double write

隔离性

事务操作数据行的时候，不会受到其他时候的影响。

持久性

一但事务提交，永久生效(落盘)。

事务的生命周期管理

标准(显示)的事务控制语句

# 开启事务begin;# 提交事务commit;# 回滚事务rollback;

注意：事务生命周期中，只能使用DML语句(select、update、delete、insert)

事务的生命周期演示

mysql> use worldmysql> begin;mysql> delete from city where id=1;mysql> update city set countrycode='CHN' where id=2;mysql> commit;mysql> begin;mysql> select * from city limit 10;mysql> update city set countrycode='AFG' where id=2;mysql> delete from city where id=3;mysql> rollback;

MySQL的自动提交机制(auto_commit)

mysql> select @@autocommit;+--------------+| @@autocommit |+--------------+|            1          |+--------------+

作用： 在没有显示的使用begin语句的时候，执行DML，会在DML前自动添加begin，并在DML执行后自动添加commit。建议： 频繁事务业务场景中，关闭autocommit。或者每次事务执行时都是显示的begin和commit;关闭方法： # 临时： mysql> set global autocommit=0;退出会话，重新连接配置生效。# 永久： [root@db01 ~]# vim /etc/my.cnfautocommit=0重启生效。

隐式提交和回滚

# 隐式提交情况 begin abbeginSET AUTOCOMMIT = 1导致提交的非事务语句：DDL语句： (ALTER、CREATE 和 DROP)DCL语句： (GRANT、REVOKE 和 SET PASSWORD)锁定语句：(LOCK TABLES 和 UNLOCK TABLES)导致隐式提交的语句示例：TRUNCATE TABLELOAD DATA INFILESELECT FOR UPDATE# 隐式回滚 会话窗口被关闭。数据库关闭 。出现事务冲突(死锁)。

事务的隔离级别

作用实现事务工作期间的“读”的隔离

级别类型mysql> select @@transaction_isolation;+-------------------------+| @@transaction_isolation    |+-------------------------+| READ-COMMITTED          |+-------------------------+1 row in set (0.00 sec)

RU ： READ-UNCOMMITTED 读未提交

可以读取到事务未提交的数据。隔离性差，会出现脏读(当前内存读)，不可重复读，幻读问题

RC ： READ-COMMITTED 读已提交(可以用)

可以读取到事务已提交的数据。隔离性一般，不会出现脏读问题，但是会出现不可重复读，幻读问题

RR ： REPEATABLE-READ 可重复读(默认)

防止脏读(当前内存读)，不可重复读，幻读问题

SR ： SERIALIZABLE 可串行化

结论： 隔离性越高，事务的并发读就越差。

事务的工作流程原理

名词介绍

#  重做日志 (redo log)ib_logfile0~N   48M   , 轮询使用# 日志缓冲区redo log buffer ： redo内存区域# 表空间数据文件ibd  ： 存储数据行和索引 # 数据缓冲区InnoDB buffer pool ： 缓冲区池,数据和索引的缓冲# 日志序列号 LSN磁盘数据页(ibd文件的page),redo log文件(ib_logfile),Innodb_buffer_pool中的数据页,redo bufferMySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动#WAL : Write Ahead LogRedo日志优先于数据页写到磁盘。# 脏页: Dirty Page内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.# CheckPointCKPT:检查点,就是将脏页刷写到磁盘的动作# DB_TRX_ID(6字节)  事务ID号InnoDB会为每一个事务生成一个事务号,伴随着整个事务生命周期.# DB_ROLL_PTR(7字节) 回滚指针rollback 时，会使用 undo 日志回滚已修改的数据。DB_ROLL_PTR指向了此次事务的回滚位置点，用来找到undo日志信息。

事务工作流程原理

事务举例： begin; update t1 set A=2 where A=1;commit;

# redo log 重做日志如何应用1. 用户发起update事务语句，将磁盘数据页(page100,A=1,LSN=1000)加载到内存(buffer_pool)缓冲区。2. 在内存中发生数据页修改(A=1改成A=2)，形成脏页，更改中数据页的变化，记录到redo buffer中，加入1000个字节日志。LSN=1000+1000=2000。3. 当commit语句执行时，基于WAL机制，等到redo buffer中的日志完全落盘到ib_logfileN中，commit正式完成。4. ib_logfileN中记录了一条日志。内容：page100数据页变化+LSN=2000。

## 情景： 当此时，redo落地了，数据页没有落地，宕机了。1. MySQL CR(自动故障恢复)工作模式，启动数据库时，自动检查redo的LSN和数据页LSN。2. 如果发现redoLSN>数据页的LSN ，加载原始数据页+变化redo指定内存。使用redo重构脏页(前滚)。3. 如果确认此次事务已经提交(commit标签)，立即触发CKPT动作，将脏页刷写到磁盘上。## 补充一点： MySQL有一种机制，批量刷写redo的机制。会在A事务commit时，顺便将redo buffer中的未提交的redo日志也一并刷到磁盘。为了区分不同状态的redo，日志记录时，会标记是否COMMIT。

redo保证了ACID哪些特性?主要是D的特性，另外A、C也有间接关联

# undo log 回滚日志如何应用？
1. 事务发生数据页修改之前，会申请一个undo事务操作，保存事务回滚日志(逆向操作的逻辑日志)。
2. undo写完之后，事务修改数据页头部(会记录DB_TRX_ID+DB_ROLL_PTR)，这个信息也会被记录的redo。
情景1： 
当执行rollback命令时。根据数据页的DB_TRX_ID+DB_ROLL_PTR信息，找到undo日志，进行回滚。
情景2：
	begin; 
	update t1 set A=2 where A=1;
	宕机。

假设： undo 有  ， redo没有
启动数据库时，检查redo和数据页的LSN号码。发现是一致的。
所以不需要进行redo的前滚，此时也不需要回滚。undo信息直接被标记为可覆盖状态。
假设：undo 有，redo也有(没有commit标签。)
1. MySQL CR(自动故障恢复)工作模式，启动数据库时，自动检查redo的LSN和数据页LSN。
2. 如果发现redoLSN>数据页的LSN ，加载原始数据页+变化redo指定内存。使用redo重构脏页(前滚)。
3. 如果确认此次事务没有commit标记，立即触发回滚操作，根据DB_TRX_ID+DB_ROLL_PTR信息，找到und回滚日志，实现回滚。
以上流程被称之为InnoDB的核心特性：自动故障恢复(Crash Recovery)。先前滚再回滚，先应用redo再应用undo。
## undo在ACID中保证了啥？
主要保证事务的A的特性，同时C和I的特性也有关系。
6.5.3 事务中的C特性怎么保证？
InnoDB crash recovery：数据库意外宕机时刻，通过redo前滚+undo回滚保证数据的最终一致。
InnoDB doublewrite buffer： 默认存储在ibdataN中。解决数据页写入不完整
mysqld process crash in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.
DWB一共2M。分两次，每次1M写入

事务中的I的特性怎么保证？

隔离级别：读隔离性
	RU : 脏读 、 不可重复读 、幻读
	RC : 不可重复读、幻读
	RR ：有可能会出现幻读。
	SR ：事务串行工作。

锁机制：写的隔离

作用：保护并发访问资源。
保护的资源分类： 
latch(闩锁)：rwlock、mutex，主要保护内存资源
MDL： Metadata_lock,元数据(DDL操作)
table_lock: 表级别 
lock table t1 read ;
mysqldump、XBK(PBK)：备份非InnoDB数据时，触发FTWRL全局锁表(Global)。
行锁升级为表锁。
row lock：InnoDB 默认锁粒度，加锁方式都是在索引加锁的。
record lock ： 记录锁，在聚簇索引锁定。RC级别只有record lock。
gap lock    ： 间隙锁，在辅助索引间隙加锁。RR级别存在。防止幻读。
next lock   ： 下一键锁， GAP+Record。     RR级别存在。防止幻读。

什么是幻读，RR又是如何防止幻读？

RC级别下不可重读现象演示：  
vim /etc/my.cnf 
#添加隔离级别参数：
transaction_isolation=READ-COMMITTED
#重启数据库 
[root@db01 ~]# /etc/init.d/mysqld restart

打开两个会话窗口：sessionA: 左边的窗口sessionB: 右边的窗口

sessionA: 第一步：mysql> select @@transaction_isolation;+-------------------------+| @@transaction_isolation  |+-------------------------+| READ-COMMITTED          |+-------------------------+1 row in set (0.00 sec)第三步： mysql> create database test charset utf8mb4;mysql> use test;mysql> create table t1 (id int primary key auto_increment,num int not null , name varchar(20) not null);mysql> insert into t1(num,name) values(1,'a'),(3,'c'),(6,'d'),(7,'x');mysql> insert into t1(num,name) values(11,'a'),(23,'c'),(36,'d'),(37,'x'');mysql> insert into t1(num,name) values(51,'as'),(63,'hc'),(76,'ds'),(87,'x','xyz');mysql> commit;mysql> select * from t1;+----+-----+------+| id | num | name |+----+-----+------+|  1 |   1 | a    ||  2 |   3 | c    ||  3 |   6 | d     ||  4 |   7 | x     ||  5 |  11 | a     ||  6 |  23 | c     ||  7 |  36 | d     ||  8 |  37 | x     ||  9 |  51 | as    || 10 |  63 | hc    || 11 |  76 | ds    || 12 |  87 | xyz   |+----+-----+------+第五步： mysql> begin;第七步： mysql> update t1 set name='aa' where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> commit;Query OK, 0 rows affected (0.00 sec)

sessinB: 第二步:mysql> select @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| READ-COMMITTED          |+-------------------------+1 row in set (0.00 sec)第四步： mysql> use test;mysql> select * from test.t1;+----+-----+------+| id | num | name |+----+-----+------+|  1 |   1 | a    ||  2 |   3 | c    ||  3 |   6 | d    ||  4 |   7 | x    ||  5 |  11 | a    ||  6 |  23 | c    ||  7 |  36 | d    ||  8 |  37 | x    ||  9 |  51 | as   || 10 |  63 | hc   || 11 |  76 | ds   || 12 |  87 | xyz  |+----+-----+------+12 rows in set (0.00 sec)第六步： mysql> begin;mysql> select * from t1 where id=1;+----+-----+------+| id | num | name |+----+-----+------+|  1 |   1 | a    |+----+-----+------+1 row in set (0.00 sec)第八步： mysql> select * from t1 where id=1;+----+-----+------+| id | num | name |+----+-----+------+|  1 |   1 | aa   |+----+-----+------+1 row in set (0.00 sec)

RC级别下幻读现象演示：

# 备份# 准备工作： mysql> alter table t1 add index(num);[root@db01 ~]# mysqldump  test t1 >/tmp/t1.sql

session A : 第一步： mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t1;+----+-----+------+| id | num | name |+----+-----+------+|  1 |   1 | aa   ||  2 |   3 | c    ||  3 |   6 | d    ||  4 |   7 | x    ||  5 |  11 | a    ||  6 |  23 | c    ||  7 |  36 | d    ||  8 |  37 | x    ||  9 |  51 | as   || 10 |  63 | hc   || 11 |  76 | ds   || 12 |  87 | xyz  |+----+-----+------+12 rows in set (0.00 sec)第三步： mysql> update t1 set num=10 where num<10;Query OK, 4 rows affected (0.00 sec)Rows matched: 4  Changed: 4  Warnings: 0第五步： mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from t1;+----+-----+------+| id | num | name |+----+-----+------+|  1 |  10 | aa   ||  2 |  10 | c    ||  3 |  10 | d    ||  4 |  10 | x    ||  5 |  11 | a    ||  6 |  23 | c    ||  7 |  36 | d    ||  8 |  37 | x    ||  9 |  51 | as   || 10 |  63 | hc   || 11 |  76 | ds   || 12 |  87 | xyz  || 13 |   5 | aaa  |+----+-----+------+

Session B: 第二步： mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from t1;+----+-----+------+| id | num | name |+----+-----+------+|  1 |   1 | aa   ||  2 |   3 | c    ||  3 |   6 | d    ||  4 |   7 | x    ||  5 |  11 | a    ||  6 |  23 | c    ||  7 |  36 | d    ||  8 |  37 | x    ||  9 |  51 | as   || 10 |  63 | hc   || 11 |  76 | ds   || 12 |  87 | xyz  |+----+-----+------+12 rows in set (0.00 sec)第四步： mysql> insert into t1(num,name) values(5,'aaa');Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)

# 功能性上： IS   ：                          select * from t1 lock in shared mode;S    ： 读锁。             IX   :  意向排他锁。表上添加的。 select * from t1 for update;X    :  排他锁，写锁。

MVCC ： 多版本并发控制

乐观锁： 乐观。悲观锁： 悲观。每个事务操作都要经历两个阶段： 读:  乐观锁。MVCC利用乐观锁机制，实现非锁定读取。read view：RV,版本号集合。trx1 : begin; dml1  ---> 在做第一个查询的时候，当前事务，获取系统最新的：RV1 版本快照。dml2  ---> 生成 RV2 版本快照。select  查询  RV2 快照数据commit; ---->  RV2 快照数据  ----》系统最新快照。RCtrx1: Rv1  Rv2  commit;trx2 RVV1 RVV1 RV2RR trx1 : 第一个查询时， 生成global consitence snapshot  RV-CS1(10:00) ,一直伴随着事务生命周期结束。trx2 :  第一个查询时，生成global consitence snapshot  RV-CS2(10:01) ,一直伴随着事务生命周期结束。快照技术由undo log来提供。写: 悲观锁 X 总结： 1. MVCC采用乐观锁机制，实现非锁定读取。2. 在RC级别下，事务中可以立即读取到其他事务commit过的readview3. 在RR级别下，事务中从第一次查询开始，生成一个一致性readview，直到事务结束