第19章　事务概念基础 - xiaoboluo768/qianjinliangfang GitHub Wiki

19.1.1　标准的事务隔离级别

1．脏读
- 表19-1

Transaction 1	Transaction 2
begin;
select age from t1 where id =1;　return 26;	begin;
	update t1 set age = 27 where id = 1;
select age from t1 where id =1;　return 27;
	Rollback

2．不可重复读
- 表19-2

Transaction 1	Transaction 2
begin;
select age from t1 where age BETWEEN 10 AND 30;　return 26;	begin;
	update t1 set age = 27 where id = 1;　commit;
select age from t1 where id =1;return 27;
Commit

3．幻读
- 表19-3

Transaction 1	Transaction 2
begin;
select age from t1 where age between 26 and 30;　return 27;	begin;
	insert into t1 (id , age) values (2,28);　　commit;
select age from t1 where age between 26 and 30;　return [27,28];
Commit;

19.1.2　调整事务隔离级别

SET GLOBAL transaction_isolation = 'READ-COMMITTED'

19.2.1　事务管理

mysql> begin;
Query OK, 0 rows affected
mysql> insert into dhytest values(10, 'donghongyu');
Query OK, 1 row affected
mysql> insert into dhytest values(10, 'luoxiaobo');
(1062, "Duplicate entry '10' for key 'PRIMARY'")
mysql> insert into dhytest values(20, 'lichun');
Query OK, 1 row affected
mysql> commit;
Query OK, 0 rows affected
mysql> select * from dhytest;
+----+------------+
| id | name        |
+----+------------+
| 10 | donghongyu |
| 20 | lichun      |
+----+------------+

19.3.2　Redo日志格式

2．逻辑日志

mysql> create table t1 (id int , name char (20), age int);
Query OK, 0 rows affected (0.04 sec)

mysql> create index idx_name on t1 (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(20) COLLATE utf8_bin DEFAULT NULL,
`age` int(11) DEFAULT NULL,
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

1 row in set (0.00 sec)

Mysql> insert into t1 (id , name , age) values (1, '小明', 26);

mysql> show create table t1;
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(20) COLLATE utf8_bin DEFAULT NULL,
`age` int(11) DEFAULT NULL,
KEY `idx_name` (`name`)
KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
1 row in set (0.00 sec)

<insert op, base filename = T1 ,page number = 502 , record value = {1,'小明'，26}>
<insert op, index1 filename = idx_name ,page number = 72 , index1 record value = idx_name of r ='小明'>
<insert op, index1 filename = idx_age ,page number = 50 , idx_age record value = idx_age of r =26>

19.3.5　Redo日志的设置

1．执行SHOW engine innodb

LOG
Log sequence number 602763740 	// Redo日志缓冲区中的LSN
Log flushed up to 602763740 		// Redo日志文件中的LSN
Pages flushed up to 584668961 	// 写入磁盘中的数据页对应的LSN
Last checkpoint at 555157885 		// 最后一个CheckPoint对应的LSN

2．INNODB_METRICS

mysql> SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME IN ('log_lsn_current', 'log_lsn_last_checkpoint'); 
+-------------------------+-----------+
| NAME | COUNT | 
+-------------------------+-----------+
| log_lsn_last_checkpoint | 555157885 |
| log_lsn_current | 602763740 | 
+-------------------------+-----------+

3．sys schema（系统库）

mysql> SELECT *FROM sys.metrics WHERE Variable_name IN ('log_lsn_current','log_lsn_ last_checkpoint');
+-----------------------+----------------+-------------------------+---------+ 
| Variable_name | Variable_value | Type | Enabled | 
+-----------------------+----------------+-------------------------+---------+ 
| log_lsn_current | 602763740 | InnoDB Metrics - recovery | YES |
| log_lsn_last_checkpoint | 555157885 | InnoDB Metrics - recovery | YES |
+-----------------------+----------------+-------------------------+---------+

Used log = log_lsn_current – log_lsn_last_checkpoint = 602763740 - 555157885
= 47605855 (bytes)
Used % = (Used log / Total log) * 100
= (47605855 / (innodb_log_file_size * innodb_log_files_in_group)) * 100 = (47605855 / 100663296) * 100
= 47.29 %

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb status\G SELECT sleep(60); show engine innodb status\G
Log sequence number 32026697
Log sequence number 32030006

mysql> SELECT (32030006-32026697) / 1024 / 1024 AS per_redo;
+------------+
| per_redo   |
+------------+
| 0.00315571 |
+------------+

19.4.1 MVCC原理

表19-4

session 1	session 2
select c1 from t1; return c1 = 10
start transaction;
update t1 set c1 = 20;
	start transaction;
	select c1 from t1; return ?
commit;
	select c1 from t1; return ?

19.4.2　具体代码

2．MVCC相关实现

mysql> select * from dhytest;
+------+
| id    |
+------+
| 10    |
+------+
1 row in set (7.99 sec)

mysql> update dhytest set id = 20;
Query OK, 3 rows affected (40.71 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from dhytest;

/** The read should not see any transaction with trx id >= this
value. In other words, this is the "high water mark". */
trx_id_t m_low_limit_id;

/** The read should see all trx ids which are strictly
smaller (<) than this value. In other words, this is the
low water mark". */
trx_id_t m_up_limit_id;
/** trx id of creating transaction, set to TRX_ID_MAX for free
views. */
trx_id_t m_creator_trx_id;

函数row_search_mvcc->lock_clust_rec_cons_read_sees
bool
lock_clust_rec_cons_read_sees(
/*==========================*/
const rec_t* rec, /*!< in: user record which should be read or
passed over by a read cursor */
dict_index_t* index, /*!< in: clustered index */
const ulint* offsets,/*!< in: rec_get_offsets(rec, index) */
ReadView* view) /*!< in: consistent read view */
{
ut_ad(index->is_clustered());
ut_ad(page_rec_is_user_rec(rec));
ut_ad(rec_offs_validate(rec, index, offsets));

/* Temp-tables are not shared across connections and multiple
transactions from different connections cannot simultaneously
operate on same temp-table and so read of temp-table is
always consistent read. */
//只读事务或者临时表是不需要一致性读判断的
if (srv_read_only_mode || index->table->is_temporary()) {
ut_ad(view == 0 || index->table->is_temporary());
return(true);
}

/* NOTE that we call this function while holding the search
system latch. */

trx_id_t trx_id = row_get_rec_trx_id(rec, index, offsets); //获取记录上的TRX_ID。这里需要解释一下，对于一个查询可能有多条记录满足条件，那么每读取一条记录，就要根据这条记录上的TRX_ID判断它是否可见
return(view->changes_visible(trx_id, index->table->name)); //判断记录可见性
}
下面是真正判断记录可见性的代码。
bool changes_visible(
trx_id_t id,
const table_name_t& name) const
MY_ATTRIBUTE((warn_unused_result))
{
ut_ad(id > 0);

//如果ID小于Read View中的最小事务ID，则这条记录是可见的，说明此条记录在select这个事务开始之前就结束了
if (id < m_up_limit_id || id == m_creator_trx_id) {

return(true);
}

check_trx_id_sanity(id, name);

//如果ID大于Read View中的最大事务ID，则说明这条记录在事务开始之后进行了修改，所以此条记录不可见
if (id >= m_low_limit_id) {

return(false);

} else if (m_ids.empty()) {

return(true);
}

const ids_t::value_type* p = m_ids.data();

return(!std::binary_search(p, p + m_ids.size(), id)); //判断是否在Read View中，如果在，则说明在创建Read View时此条记录还处于活跃状态，不应该被查询到；否则，说明在创建Read View时此条记录已经不处于活跃状态，可以被查询到
}

上一篇：第18章　复制技术的演进 | 下一篇：第20章　InnoDB锁

第19章 事务概念基础 - xiaoboluo768/qianjinliangfang GitHub Wiki

19.1.1 标准的事务隔离级别

19.1.2 调整事务隔离级别

19.2.1 事务管理

19.3.2 Redo日志格式

19.3.5 Redo日志的设置

19.4.1 MVCC原理

19.4.2 具体代码

第19章　事务概念基础 - xiaoboluo768/qianjinliangfang GitHub Wiki

19.1.1　标准的事务隔离级别

19.1.2　调整事务隔离级别

19.2.1　事务管理

19.3.2　Redo日志格式

19.3.5　Redo日志的设置

19.4.2　具体代码