MySQL事务原理详解

MySQL事务原理详解

四大特性

事务并发问题：

脏读：

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

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

#事务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

#事务B:
rollback;

#事务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;
#暂停

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

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

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

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

幻读：

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

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

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

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

#事务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的部分数据

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，已提交的事务就不在列表中了。

RR隔离级

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

每个语句开始，拷贝新的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的数据。

问题：

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

本人博客地址更详细