MySQL中的InnoDB引擎实现了事务。

MySQL事务

事务的特性

原子性：由undo log保证

隔离性：

一致性：由redo log保证

持久性：有redo log保证

InnoDB架构

内存结构

BufferPool 缓冲池

数据页

脏页

insert buffer 插入缓冲（插入比较复杂）

lock info 锁信息

data dictionary 表定义信息

undo log（数据的历史记录）

Redo log Buffer

redo log 的落盘机制

buffer pool

redo log buffer

OS Buffer（文件系统缓存）：存储应用程序命令的缓存，这是属于操作系统的。调用fsync可以将文件系统缓存写入到磁盘。

ib_logfiles（磁盘）：操作系统才能操作磁盘。

默认落盘机制：

innodb_flflush_log_at_trx_commit =2

commit;// 记录到redo log buffer 并经过OSBuffer调用一次fsync写入到redo log file(磁盘)
innodb_flflush_log_at_trx_commit =0时：

commit;// 记录到 redo log buffer，隔固定时间，刷新到redo log file（磁盘）
innodb_flflush_log_at_trx_commit =1时：

commitj;//记录到 redo log buffer,并写入到 OS Buffer，操作系统隔固定时间刷新到磁盘。

这一级别下，操作系统不崩溃，数据也不会丢失。

buffer pool 落盘机制

Double write双写

带给InnoDB存储引擎的是数据页的可靠性

Double write由两部分组成，一部分是内存中的double write buffer,大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也是2MB。对缓冲池的脏页刷新时，通过memcpy函数将脏页先复制到内存中的double write buffer区域，之后通过double write buffer再分两次，每次1MB写入（系统）共享表空间的物理磁盘上，然后调用fsync函数，同步磁盘。完成double write页的写入后，再将double write buffer中的页写入各个表空间文件中。如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB可以从共享表空间中的double write 中找到该页的副本，将其复制到表空间文件中。

系统表空间和用户表空间各存一份。

CheckPoint检查点

将脏页写入到磁盘的时机。

目的：

缩短数据库的恢复时间
buffer pool 空间不够用时，将脏页刷新到磁盘
redo log不够用时，刷新脏页

检查点分类

sharp checkpoint：完全检查点，数据库正常关闭时，会触发把所有的脏页都写入到磁盘上
fuzzy checkpoint:正常使用时，模糊检查点，部分页写入磁盘。
- maste thread checkpoint
  
  每秒或每10秒的速度从缓冲池的脏页列表中刷新一定比例的页到磁盘，异步。
- flush_lru_list checkpoint
  
  读取lru (Least Recently Used) list，找到脏页，写入磁盘。最近最少使用
- async/sync flush checkpoint
  
  redo log fifile快满了，会批量的触发数据页回写，这个事件触发的时候又分为异步和同步，不可被覆盖的redolog占log file的比值：75%—>异步、90%—>同步。
- dirty page too mush checkpoint
  
  默认是脏页占比75%的时候，就会触发刷盘，将脏页写入磁盘

磁盘结构

系统表空间和用户表空间

系统表空间（共享表空间）

数据字典（data dictionary):记录数据库相关信息
double writer
insert buffer:内存insert buffer 数据，周期写入共享表空间
回滚段（rollback segments)
undo 空间：undo 页

用户表空间（独立表空间）

每个表的数据和索引都存在自己的表空间中
每个表的结构
undo 空间：undo 页
double write

redo log file

两个重做日志文件，大小一致，循环写入。顺序IO循环存储，保证了数据存储的速度和持久性。（每一条SQL致使数据库的改变都真正存储到了磁盘，顺序IO又保证存数据的速度。）

两个redo log file文件。默认10M大小。（不需要很大，只要保证能存储数据库崩溃，buffer pool中没有写入得到磁盘的脏页、undo log写入到磁盘的redo log file即可。）

存储内容：

执行SQL后表的记录（脏页的信息）
执行SQL前表的的记录（undo log页的信息)
索引页的信息

数据库事务，sql执行经过 undo和redo 的流程

事务并发问题：

脏读：

事务A读取到了事务B为提交的数据，事务B进行了回滚。

1
2
3

#事务B:
begin;
update user set username = "li" where id =1;

1
2
3

#事务A:
begin;
select * from user where id =1;

id	username	birthday	sex	address
1	li	2019-11-28	男	北京
3	han	0000-00-00	nan	bei
4	han	0000-00-00	nan	bei

1 2	#事务B: rollback;

1 2	#事务A: select * from user where id =1;

id	username	birthday	sex	address
1	zs	2019-11-28	男	北京
3	han	0000-00-00	nan	bei
4	han	0000-00-00	nan	bei

不可重复读：

事务A因事务B修改了数据导致事务A两次读取的数据不一致。

#事务A:
begin;
select * from user where id =1;
#暂停

1 2	id username birthday sex address 1 zs 2019-11-28 男北京

1 2	#事务B: update user set username = "li" where id =1;

1 2	#事务A：继续 select * from user where id =1;

1 2	id username birthday sex address 1 li 2019-11-28 男北京

幻读：

事务A因事务B插入了新的数据导致事务A两次读取的数据不一致（插入或删除）

#事务A:
begin;
select * from user;
#暂停

1
2
3

id	username	birthday	sex	address
1	zs	2019-11-28	男	北京
3	han	0000-00-00	nan	bei

1 2	#事务B: insert INTO `user` VALUES(4,'han',2019-11-28,'nan','bei');

1 2	#事务A：继续 select * from user;

id	username	birthday	sex	address
1	li	2019-11-28	男	北京
3	han	0000-00-00	nan	bei
4	han	0000-00-00	nan	bei

MySQL事务隔离级别

RU读未提交（read-uncommitted）：事务A可读到事务B未提交的数据。

RC读已提交（read-committed）：事务A可读到事务B已提交的数据。

RR可重复读（repeatable-read）:事务B不会对A造成影响。

串行化（serializable）

MySQL事务隔离级别与事务并发问题

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	否
串行化（serializable）	否	否	否

mysql的RR隔离级使用MVCC机制解决了不可重复读，使用间隙锁解决了幻读的发生。

RR不严格，纯select走readview，update不走readview。会引发一些问题。

mysql的MVCC机制

作用：未加读锁实现了事务并发时可重复读。

MVCC依赖undo log，ReadView和版本号。

每个事务都有自己的事务id，id使用当前的系统版本号，每有新的事务，系统版本号+1

undo log

聚簇索引记录中有三个隐藏列：row_id、trx_id、roll_pointer

trx_id：存储每次对某条聚簇索引记录进行修改的时候的事务id。
roll_pointer：每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向undo log的老版本地址。

undo log 的删除是由 purge Thead操作的

readview

数据结构：

struct read_view_t{
	ulint	type;
	undo_no_t	undo_no;
	trx_id_t	low_limit_no;
	trx_id_t	low_limit_id;	// 高水位，大于此事务id的记录都不可见。
	trx_id_t	up_limit_id;	// 低水位，小于此事务id的记录可见。
	ulint	n_trx_ids;			// 活跃事务数量
	trx_id_t*	trx_ids;		// 以逆序排列的当前获取活跃事务id的数组
	trx_id_t	creator_trx_id;	// 创建当前视图的事务id
	UT_LIST_NODE_T(read_view_t)	view_list;	// 事务系统中的一致性视图链表
}

trx_id_t* trx_ids;
- 记录系统活跃事务的id数组。
- 除去本身事务的id，除去内存中commit的id
- 直到事务中的 select 不加锁语句执行时创建。update、delete不会创建。
trx_id_t low_limit_id;
- 因id倒数排列，代表活跃事务中最高版本的事务id
- 其他事务找到的行记录的trx_id > low_limit_id时，找到的行记录不可见，去undo log中找老版本。
trx_id_t up_limit_id;
- 因id倒数排列，代表活跃事务中最低版本的事务id
- 其他事务找到的行记录的trx_id < up_limit_id时，找到的行记录可见，返回此行记录。

判断行记录是否可见

/*********************************************************************//**
Checks if a read view sees the specified transaction.
@return	true if sees */
UNIV_INLINE
bool
read_view_sees_trx_id(
	const read_view_t*	view,	/*!< in: read view */
	trx_id_t		trx_id)	/*!< in: trx id */
{
	if (trx_id < view->up_limit_id) {/*行记录的事务trx_id < 活跃事务的*/
		return(true);
	} else if (trx_id >= view->low_limit_id) {
		return(false);
	} else {
		ulint	lower = 0;
		ulint	upper = view->n_trx_ids - 1;

		ut_a(view->n_trx_ids > 0);

		do {
			ulint		mid	= (lower + upper) >> 1;
			trx_id_t	mid_id	= view->trx_ids[mid];

			if (mid_id == trx_id) {
				return(FALSE);
			} else if (mid_id < trx_id) {
				if (mid > 0) {
					upper = mid - 1;
				} else {
					break;
				}
			} else {
				lower = mid + 1;
			}
		} while (lower <= upper);
	}

	return(true);
}

/*行记录的事务trx_id < 活跃事务链表中的最小事务id，此条记录可被当前事务读取。*/
if (trx_id < view->up_limit_id) {
		return(true);
	}

/*行记录的事务trx_id >= 活跃事务链表中的最大事务id，此条记录不可被当前事务读取。*/
else if (trx_id >= view->low_limit_id) {
		return(false);
	}

#空表user,有id、name两列
#例如：事务A，trx_id = 10
begin;
select * from user;//无数据

#事务B，trx_id = 11
insert into user values(1,1);

#事务A
select * from user;//无数据

#解释：
#找到了数据 id = 1,name = 1, trx_id = 11。
#但是事务A (trx_id =10 < trx_id = 11),所以此数据没有返回

/*二分查找算法，如果行记录的事务trx_id == 活跃事务链表的id，此条记录不可被当前事务读取。否则，可被读取。*/
else {
		ulint	lower = 0;
		ulint	upper = view->n_trx_ids - 1;

		ut_a(view->n_trx_ids > 0);

		do {
			ulint		mid	= (lower + upper) >> 1;
			trx_id_t	mid_id	= view->trx_ids[mid];

			if (mid_id == trx_id) {
				return(FALSE);
			} else if (mid_id < trx_id) {
				if (mid > 0) {
					upper = mid - 1;
				} else {
					break;
				}
			} else {
				lower = mid + 1;
			}
		} while (lower <= upper);
	}

例子：

原数据

trx_id	roll_pointer	id	name
5	undo中上一个版本的地址 0x001	1	zs
3	undo中上一个版本的地址 0x002	5	zs
13	undo中上一个版本的地址 0x003	10	13

undo log的数据

地址	trx_id	roll_pointer	id	name
0x001	2	undo中再上一个版本的地址 0x***	1	zs
0x002	1	undo中再上一个版本的地址 0x***	5	zs
0x003	3	undo中再上一个版本的地址 0x***	10	zs

有trx_id 为6、7、8、9、10、11、12七个事务，7和11在内存中准备提交，当前事务为9

#事务的trx_id为9 执行
begin;
update role set name = 'www' where id = 5;// 无关的sql
select * from user; // 普通select执行时创建readview

readview的部分数据

1
2
3

trx_ids[12,10,8,6]
low_limit_id = 12
up_limit_id =6

查出来的数据：

trx_id	id	name
5	1	zs
3	5	zs
3	10	zs

第三条数据：因为行记录(trx_id = 13) > review->low_limit_id,此条行记录对事务9不可见，去undo log中找到行记录trx_id = 3 的记录返回。

不同隔离级如何创建readview

RC隔离级

RC隔离级别下，在每个语句开始的时候，会将当前系统中的所有的活跃事务拷贝到一个列表中(read
view)

每个语句开始，拷贝新的readview，已提交的事务就不在列表中了。

这么一个问题：RC隔离级下使用了MVCC机制，trx_id=5的事务A不应该读到trx_id=10的事务B提交的数据。因为事务B提交后的行记录(trx_id =10) > view->low_limit_id(高水位)

解释：每个语句开始的时候拷贝新的readview，readview包括本事务的id，拷贝新的readview就会更新本事务的id，这样本事务的id = 5变为了id = 11，这样就可以看到事务B提交的 id = 10 的记录了。

RR隔离级

RR隔离级别下，在每个事务开始的时候，会将当前系统中的所有的活跃事务拷贝到一个列表中(readview) ，直到事务结束，不会更新readview。不会看到readview事务链表中提交的事务数据。

设置隔离级别：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

当前会话隔离级读已提交：

set session transaction isolation level read COMMITTED;

RR不严格的问题：

#session1
begin;
select * from user;

id	username	birthday	sex	address
1	123	2019-11-28	男	北京
3	han	0000-00-00	nan	bei
4	hxr234	0000-00-00	nan	bei

#session2
insert into user values(5,'zala',0000-00-00,'nan','beijing');
#session1
update user set username = '123' where id > 1;
select * from user;

id	username	birthday	sex	address
1	zs	2019-11-28	男	北京
3	123	0000-00-00	nan	bei
4	123	0000-00-00	nan	bei
5	123	0000-00-00	nan	beijing

update 不走readview视图使第二次select查到了别的事务insert的数据。

Go after

Mysql-事务