开始介绍之前,先让我们了解一些基本概念。ANSI SQL STANDARD定义了4类隔离级别(READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE),包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。低级别的隔离级别一般支持更高的并发处理,并拥有更低的系统开销。
脏读(Drity Read):事务T1修改了一行数据,事务T2在事务T1提交之前读到了该行数据。
不可重复读(Non-repeatable read): 事务T1读取了一行数据。 事务T2接着修改或者删除了改行数据,当T1再次读取同一行数据的时候,读到的数据时修改之后的或者发现已经被删除。
幻读(Phantom Read): 事务T1读取了满足某条件的一个数据集,事务T2插入了一行或者多行数据满足了T1的选择条件,导致事务T1再次使用同样的选择条件读取的时候,得到了比第一次读取更多的数据集。
MySQL/INNODB支持ANSI SQL STANDARD规定的四种隔离级别(READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE).本篇文章重点关注一下MySQL REPEATABLE READ隔离级别与其他数据实现方式上的不同之处。
下面看一下MySQL在REPEATABLE READ 隔离级别下的工作方式:
开启两个session。
接下来看一下另外一个开源数据库PostgreSQL在REPEATABLE READ 隔离级别下的工作方式:
同样测试了SQL SERVER,得到的结果与PostgreSQL是一致的。
从上面的执行情况我们可以看到MySQL与PostgreSQL两者工作方式上有所不同。MySQL在执行UPDATE语句的时候对于session2的INSERT语句是可以看到的,也就是说发生了幻读。那么MySQL在隔离级别为REPEATABLE READ的情况下,表现出来的幻读现象是否属于一个BUG呢?曾经有人在2013年给官方提过一个关于该现象的BUG,请参考https://bugs.mysql.com/bug.php?id=63870。 从BUG页面的注释可以了解到,该现象是与MySQL对REPATABLE READ隔离级别的实现方式有关。而这种幻读现象对于REPATABLE READ隔离级别也是正确的方式。请看wikipedia上对于REPEATABLE READ的描述:
- Repeatable reads
- In this isolation level, a lock-based concurrency control DBMS implementation keeps read and
- write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so phantom reads can occur.
另外我们接着看一下ANSI SQL STANDARD对于各种隔离级别发生幻读的规定:
我们从wikipedia以及ANSI SQL STANDARD可以看到对于REPEATABLE READ隔离级别下是允许出现幻读现象的。
接下来我们从源码的角度分析一下Innodb对于REPEATABLE READ隔离级别的执行过程(代码只覆盖重要执行部分)。
以上面的例子为依据进行剖析:
对于第一条SELECT语句,InnoDB将调用row_search_for_mysql函数来返回扫描行。函数row_search_for_mysql调用相关代码如下:
- UNIV_INTERN
- dberr_t
- row_search_for_mysql(
- /*=================*/
- byte* buf, /* 用来存放记录的空间地址 */
- ulint mode, /* InnoDB页扫描顺序 */
- row_prebuilt_t* prebuilt, /* InnoDB扫描需要的所有信息都包含在这个结构体,比如表以及Index等信息 */
- ulint match_mode, /* 对于Index的匹配模式,是精确匹配还是前缀索引匹配 */
- ulint direction) /* 指定扫描顺序,正序还是倒叙扫描 */
- {
- ...
- /* 从这里我们看出开始一个新事务,并非是从执行BEGIN语句位置开始,而是从其后开始执行的第一条语句开始分配事务ID */
- trx_start_if_not_started(trx, ((trx->mysql_thd
- && thd_is_select(trx->mysql_thd)
- ) || srv_read_only_mode) ? FALSE : TRUE);
- ...
- // 如果是SQL语句第一次开始执行,需要考虑对TABLE增加意向所
- if (!prebuilt->sql_stat_start) {
- // 这里标记SQL语句已经开始执行,处理一条SQL语句循环扫描记录的过程
- /* No need to set an intention lock or assign a read view */
- if (UNIV_UNLIKELY
- (trx->read_view == NULL
- && prebuilt->select_lock_type == LOCK_NONE)) {
- fputs("InnoDB: Error: MySQL is trying to" " perform a consistent read\n" "InnoDB: but the read view is not assigned!\n", stderr);
- trx_print(stderr, trx, 600);
- fputc('\n', stderr);
- ut_error;
- }
- } else if (prebuilt->select_lock_type == LOCK_NONE) {
- /* This is a consistent read */
- /* Assign a read view for the query */
- // 如果是第一次执行SELECT语句,构建READ_VIEW. 该READ_VIEW 用来判断记录的可见性
- trx_assign_read_view(trx);
- prebuilt->sql_stat_start = FALSE;
- } else {
- ...
- }
- ...
- /* We are ready to look at a possible new index entry in the result
- set: the cursor is now placed on a user record */
- /* 从这里我们看一下InnoDB如何获取一条新纪录。由于上面例子中SESSION1的第一条语句是SELECT语句,InnoDB在REPEATABLE READ 隔离级别下,不对SELECT 语句加锁,所以这里执行SELECT语句的时候prebuilt->select_lock_type为LOCK_NONE。下面我们直接看一下prebuilt->select_lock_type为LOCK_NONE的情况下,InnoDB如何扫描行? */
- if (prebuilt->select_lock_type != LOCK_NONE) {
- ... //稍后会对prebuilt->select_lock_type != LOCK_NONE的情况进行分析
- }
- else
- {
- /* This is a non-locking consistent read: if necessary, fetch
- a previous version of the record */
- if (trx->isolation_level == TRX_ISO_READ_UNCOMMITTED) {
- /* 对于READ UNCOMMITTED隔离级别,我们什么都不需要,只要让他读取最新的记录版本即可 */
- } else if (index == clust_index) {
- /* Fetch a previous version of the row if the current
- one is not visible in the snapshot; if we have a very
- high force recovery level set, we try to avoid crashes
- by skipping this lookup */
- // 如果是全表扫描或主键扫描,这里需要看看当前记录是否对当前事务可见
- if (UNIV_LIKELY(srv_force_recovery < 5)
- && !lock_clust_rec_cons_read_sees(
- rec, index, offsets, trx->read_view)) {
- // 如果不可见,这里需要查找历史版本
- rec_t* old_vers;
- /* The following call returns 'offsets'
- associated with 'old_vers' */
- err = row_sel_build_prev_vers_for_mysql(
- trx->read_view, clust_index,
- prebuilt, rec, &offsets, &heap,
- &old_vers, &mtr);
- if (err != DB_SUCCESS) {
- goto lock_wait_or_error;
- }
- if (old_vers == NULL) {
- /* The row did not exist yet in
- the read view */
- // 如果当前记录对当前事务不可见,也没有历史版本,直接查找下一条记录
- goto next_rec;
- }
- rec = old_vers;
- } else {
- /* We are looking into a non-clustered index,
- and to get the right version of the record we
- have to look also into the clustered index: this
- is necessary, because we can only get the undo
- information via the clustered index record. */
- ut_ad(!dict_index_is_clust(index));
- // 这里处理是Secondary index扫描的情况
- if (!lock_sec_rec_cons_read_sees(
- rec, trx->read_view)) {
- /* We should look at the clustered index.
- However, as this is a non-locking read,
- we can skip the clustered index lookup if
- the condition does not match the secondary
- index entry. */
- // 这里InnoDB做了一下优化,如果当前记录不满足ICP,直接查找下一条记录;如果满足ICP则需要继续根据聚集索引寻找历史版本
- switch (row_search_idx_cond_check(
- buf, prebuilt, rec, offsets)) {
- case ICP_NO_MATCH:
- goto next_rec;
- case ICP_OUT_OF_RANGE:
- err = DB_RECORD_NOT_FOUND;
- goto idx_cond_failed;
- case ICP_MATCH:
- goto requires_clust_rec;
- }
- ut_error;
- }
- }
- }
- ...
- }
- }
接下来我们看一下UPDATE的执行过程。对于UPDATE操作执行流程的简单描述如下:
根据WHERE条件扫描一条记录(row_search_for_mysql)
更新当前获取的记录(ha_innobase::update_row)
重新将更新后的记录写入InnoDB存储引擎(row_upd_step)
那么我们按照上面的这个流程看一下源码方面的执行过程:
- UNIV_INTERN
- dberr_t
- row_search_for_mysql(
- /*=================*/
- byte* buf, /* 用来存放记录的空间地址 */
- ulint mode, /* InnoDB页扫描顺序 */
- row_prebuilt_t* prebuilt, /* InnoDB扫描需要的所有信息都包含在这个结构体,比如表以及Index等信息 */
- ulint match_mode, /* 对于Index的匹配模式,是精确匹配还是前缀索引匹配 */
- ulint direction) /* 指定扫描顺序,正序还是倒叙扫描 */
- {
- ...
- /* 从这里我们看出开始一个新事务,并非是从执行BEGIN语句位置开始,而是从其后开始执行的第一条语句开始分配事务ID */
- trx_start_if_not_started(trx, ((trx->mysql_thd
- && thd_is_select(trx->mysql_thd)
- ) || srv_read_only_mode) ? FALSE : TRUE);
- ...
- // 如果是SQL语句第一次开始执行,需要考虑对TABLE增加意向所
- if (!prebuilt->sql_stat_start) {
- // 这里标记SQL语句已经开始执行,处理一条SQL语句循环扫描记录的过程
- /* No need to set an intention lock or assign a read view */
- if (UNIV_UNLIKELY
- (trx->read_view == NULL
- && prebuilt->select_lock_type == LOCK_NONE)) {
- ...
- }
- } else if (prebuilt->select_lock_type == LOCK_NONE) {
- ...
- } else {
- // 这里开始非INSERT的DML操作,因为DML会对记录增加记录排他锁。具体需要增加什么类型的锁,可以参考https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
- wait_table_again:
- // 这里要对TABLE加意向锁
- err = lock_table(0, index->table,
- prebuilt->select_lock_type == LOCK_S
- ? LOCK_IS : LOCK_IX, thr);
- if (err != DB_SUCCESS) {
- table_lock_waited = TRUE;
- goto lock_table_wait;
- }
- prebuilt->sql_stat_start = FALSE;
- }
- ...
- if (prebuilt->select_lock_type != LOCK_NONE) {
- ulint lock_type;
- if (!set_also_gap_locks
- || srv_locks_unsafe_for_binlog
- || trx->isolation_level <= TRX_ISO_READ_COMMITTED
- || (unique_search && !rec_get_deleted_flag(rec, comp))) {
- // 这里对于READ_UNCOMMITTED以及READ_COMMITTED,或者唯一键扫描不需要使用gap锁
- goto no_gap_lock;
- } else {
- lock_type = LOCK_ORDINARY;
- }
- /* If we are doing a 'greater or equal than a primary key
- value' search from a clustered index, and we find a record
- that has that exact primary key value, then there is no need
- to lock the gap before the record, because no insert in the
- gap can be in our search range. That is, no phantom row can
- appear that way.
- An example: if col1 is the primary key, the search is WHERE
- col1 >= 100, and we find a record where col1 = 100, then no
- need to lock the gap before that record. */
- if (index == clust_index
- && mode == PAGE_CUR_GE
- && direction == 0
- && dtuple_get_n_fields_cmp(search_tuple)
- == dict_index_get_n_unique(index)
- && 0 == cmp_dtuple_rec(search_tuple, rec, offsets)) {
- no_gap_lock:
- lock_type = LOCK_REC_NOT_GAP;
- }
- err = sel_set_rec_lock(btr_pcur_get_block(pcur),
- rec, index, offsets,
- prebuilt->select_lock_type,
- lock_type, thr);
- switch (err) {
- const rec_t* old_vers;
- case DB_SUCCESS_LOCKED_REC:
- if (srv_locks_unsafe_for_binlog
- || trx->isolation_level
- <= TRX_ISO_READ_COMMITTED) {
- /* Note that a record of
- prebuilt->index was locked. */
- prebuilt->new_rec_locks = 1;
- }
- err = DB_SUCCESS;
- case DB_SUCCESS:
- // 加锁成功后就认为记录可见了,并未像SELECT语句一样根据事务开始的READ_VIEW进行可见性判断。所以对于DML来说,所有提交的事务都是可见的。
- break;
- case DB_LOCK_WAIT:
- /* Never unlock rows that were part of a conflict. */
- // 如果存在锁冲突,也就是其他事务正在更新同一行
- prebuilt->new_rec_locks = 0;
- if (UNIV_LIKELY(prebuilt->row_read_type
- != ROW_READ_TRY_SEMI_CONSISTENT)
- || unique_search
- || index != clust_index) {
- goto lock_wait_or_error;
- }
- /* The following call returns 'offsets'
- associated with 'old_vers' */
- // 这里需要查看是否有别的事务提交了,以便获取最新版本的记录
- row_sel_build_committed_vers_for_mysql(
- clust_index, prebuilt, rec,
- &offsets, &heap, &old_vers, &mtr);
- /* Check whether it was a deadlock or not, if not
- a deadlock and the transaction had to wait then
- release the lock it is waiting on. */
- err = lock_trx_handle_wait(trx);
- switch (err) {
- case DB_SUCCESS:
- /* The lock was granted while we were
- searching for the last committed version.
- Do a normal locking read. */
- offsets = rec_get_offsets(
- rec, index, offsets, ULINT_UNDEFINED,
- &heap);
- goto locks_ok;
- case DB_DEADLOCK:
- goto lock_wait_or_error;
- case DB_LOCK_WAIT:
- err = DB_SUCCESS;
- break;
- default:
- ut_error;
- }
- if (old_vers == NULL) {
- /* The row was not yet committed */
- goto next_rec;
- }
- did_semi_consistent_read = TRUE;
- rec = old_vers;
- break;
- default:
- goto lock_wait_or_error;
- }
- }
从上面的代码我们可以看到,对于UPDATE操作更新的记录包含幻读读取到的已提交事务的最新记录。那么接下来看为什么UPDATE之后的SELECT语句对于UPDATE之后的所有语句都可见了? 原因是前面的UPDATE语句执行之后,会将当前记录上存储的事务信息更新为当前的事务,而当前事务所做的任何更新,对本事务所有SELECT查询都变的可见,因此最后输出的结果是UPDATE执行后更新的所有记录。
当前各种数据库对于隔离级别的支持不尽相同,比如ORACLE,它只实现了READ COMMITTED和SERIALIZABLE两种ANSI SQL STANDARD规定的隔离级别(这里ORACLE还实现了一种自定义的READ ONLY隔离级别,具体请参考https://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm#CNCPT621) , 而没有实现REPEATABLE READ。对于相同的隔离级别,不同的数据库有着自己不同的实现方式。所以我们在理解隔离级别的时候需要针对具体的数据库。综上所述,我们看到了MySQL InnoDB引擎对于REPEATABLE READ隔离级别有着不同于其他数据库的实现方式。而该实现方式符合ANSI SQL STANDARD,并非属于实现上的BUG。
来源: http://mysql.taobao.org/monthly/2017/06/07/