公眾號 MySQL學習

###一、問題丟擲

最近遇到一個問題，得到棧如下（5.6.25）：

![image.png](https://upload-images.jianshu.io/upload_images/7398834-8aab92d1bbcb86df.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

出現這個問題的時候只存在一個讀寫事務，那就是本事務。
對這裡的紅色部分比較感興趣，但是這裡不是所有的內容都和這個問題相關，主要還是圍繞可見性判斷和隱式鎖判定進行，算是我的思考過程。但是對Innodb認知水平有限，如有誤導請諒解。使用的原始碼版本5.7.29。

###二、read view 簡述
關於read view說明的文章已經很多了，我這裡簡單記錄一下我學習的地方。
一致性讀取（consistent read），根據隔離級別的不同，會在不同的時機建立read view，如下：
- RR 事務的第一個select命令發起的時候建立read view，直到事務提交釋放
- RC 事務的每一個select都會單獨建立read view

有了read view 就能夠對每行資料的可見性進行判斷了，下面是read view中的關鍵屬性
- m_up_limit_id：如果行的trx id 小於了m_up_limit_id則可見。
- m_low_limit_id：如果行的trx id 大於了m_low_limit_id則不可見。
- m_ids：是用於記錄建立read view時刻的讀寫事務的vector陣列，用於對於m_up_limit_id和m_low_limit_id之間的trx需要根據它來進行判定，是否處於活躍狀態。
- m_low_limit_no則用於記錄建立read view時刻的最小trx no，主要用於purge執行緒判斷清理undo使用。

如何拿到值得具體可以參見附錄，而對於可見性的判斷我們可以參考如下函式:
```
/** Check whether the changes by id are visible.
	@param[in]	id	transaction id to check against the view
	@param[in]	name	table name
	@return whether the view sees the modifications of id. */
	bool changes_visible(
		trx_id_t		id,
		const table_name_t&	name) const
		MY_ATTRIBUTE((warn_unused_result))
	{
		ut_ad(id > 0);
		if (id < m_up_limit_id || id == m_creator_trx_id) { //小於 可見
			return(true);
		}
		check_trx_id_sanity(id, name);
		if (id >= m_low_limit_id) { //大於不可見
			return(false);
		} else if (m_ids.empty()) { //如果之間的 active 為空 則可見 
			return(true);
		}
		const ids_t::value_type*	p = m_ids.data();
		return(!std::binary_search(p, p + m_ids.size(), id)); //否則比較本trx id 是否在這之中，如果在不可以見，反之可見
	}
```

###三、關於可見性判斷的幾個問題

#####1、有大量的刪除行，且已經提交，但是沒有被purge執行緒清理
這種情況由於大量刪除行（或者update）並且已經提交，但是由於有長時間的select語句導致read view記錄的狀態也比較陳舊，因此根據m_low_limit_no的判斷purge執行緒是不能清理一些比較老舊的undo的，因此這會導致一個問題，如果這些del flag的記錄會存在於邏輯記錄連結串列內部，因此其他select掃描的時候回根據next offset掃描到，但是根據可見性判斷條件這些del flag的記錄trx id小於本select語句的read view 的 m_up_limit_id，因此是可見的debug如下：
```
387             return(view->changes_visible(trx_id, index->table->name));
(gdb) p view->changes_visible(trx_id, index->table->name)
$14 = true
```

但是因為已經標記為del flag因此會做跳過處理如下：
```
row_search_mvcc:
	if (rec_get_deleted_flag(rec, comp)) {
		/* The record is delete-marked: we can skip it */
       ...
       goto next_rec;
```
也就是實際上在長時間read view的“保護”下，我們的undo不能清理，並且del flag不能清理還儲存在block的邏輯連結串列中，掃描的時候會實際掃描到，只是做了跳過處理。因此會出現如下現象
|T1|T2|T3|
|-|-|-|
|select sleep(1000) from test(模擬長時間查詢)|||
||begin;delete from test10;commit;||
|||select * from test10;(時間還是很久)|
這就是上面說的原因，雖然沒有資料了，但是查詢依舊很慢。

#####2、大量刪除，還未提交
那麼select掃描的時候會根據next offset 掃描到，但是由於read view 判斷這些資料的trx id 位於 m_up_limit_id和m_low_limit_id之間，需要根據事務是否活躍（read view的m_ids，顯然這裡是活躍的）透過undo構建其前印象，如下判斷：

```
lock_clust_rec_cons_read_sees
	trx_id_t	trx_id = row_get_rec_trx_id(rec, index, offsets);
	return(view->changes_visible(trx_id, index->table->name));
```
#####3、using index也可能回表
我們知道如果執行計劃使用到using index那麼不會回表去取主鍵的資料，使用整個二級索引即可。但是這裡有一種特殊情況，這裡進行描述。

對於二級索引而言，因為row記錄不包含trx id和undo ptr兩個偽列，那麼其可見性判斷和前的印象構建均需要回表獲取主鍵的記錄，當然可見性判斷可以先根據本二級索引page的max trx id是否小於read view的m_up_limit_id來進行第一次粗略過濾，那麼可見性判斷的可能性就低很多，如果透過了這個比對，那麼剩餘精確判斷還是需要回表透過主鍵來比對才行，如下：
- 對於二級索引回表操作來講，精確的可見性判斷放到了回表後的lock_clust_rec_cons_read_sees函式上，關於二級索引的回表，參考附錄。
- 對於不回表訪問（using index），透過了粗略判斷後（lock_sec_rec_cons_read_sees），如果遇到需要精確的可見性判斷，那麼也是要回表的，原因前面解釋了（row記錄不包含trx id和undo ptr），參考附錄。

對於這個問題我們可以簡單的做如下的測試，當然需要打斷點才行：
```
測試表如下：
mysql> show create table testimp4 \G
*************************** 1. row ***************************
       Table: testimp4
Create Table: CREATE TABLE `testimp4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `d` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`),
  KEY `d` (`d`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testimp4;
+------+------+------+------------------------------------+
| id   | a    | b    | d                                  |
+------+------+------+------------------------------------+
|    5 |    5 |  300 | NULL                               |
|    6 | 7000 | 7700 | 1124                               |
|   11 | 7000 | 7700 | 1124                               |
|   12 | 7000 | 7700 | 1124                               |
|   13 | 2900 | 1800 | NULL                               |
|   14 | 2900 | 1800 | NULL                               |
| 1000 |   88 | 1499 | NULL                               |
| 4000 | 6000 | 5904 | iiiafsafasfihhhccccchhhigggofgo111 |
| 4001 | 7000 | 7700 | 1124454555                         |
| 9999 | 9999 | 9999 | a                                  |
+------+------+------+------------------------------------+
10 rows in set (0.00 sec)
```
對於下列語句的執行話是：
```

mysql> desc select b from testimp4  where b=300;
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | testimp4 | NULL       | ref  | b             | b    | 5       | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

```
我們做如下語句：
|T1|T2|
|-|-|
|begin;delete from testimp4 where id=5;（不提交）||
||select b from testimp4  where b=300;（這裡是需要回表的）|
這裡顯然T2（5 ,5 ,300 ,NULL ）的這條記錄已經被T1刪除了，但是沒有提交，T2首先判斷二級索引b上這行資料所在的page其max trx id是否小於本select語句的read view的m_up_limit_id，顯然這不成立，因為T1還會處於活躍狀態，然後就進入了回表判斷流程。棧如下：
```
#0  lock_clust_rec_cons_read_sees (rec=0x7fff060980a8 "\200", index=0x7ffec0499330, offsets=0x7fffe8399a70, view=0x33b1368)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:369
#1  0x0000000001afbca4 in Row_sel_get_clust_rec_for_mysql::operator() (this=0x7fffe839a2d0, prebuilt=0x7ffec80c97a0, sec_index=0x7ffec049a2c0, rec=0x7fff060a008c "\200", 
    thr=0x7ffec80c9f88, out_rec=0x7fffe839a310, offsets=0x7fffe839a2e8, offset_heap=0x7fffe839a2f0, vrow=0x0, mtr=0x7fffe8399d90)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:3763
#2  0x0000000001b00a94 in row_search_mvcc (buf=0x7ffec80c8a00 <incomplete sequence="">, mode=PAGE_CUR_GE, prebuilt=0x7ffec80c97a0, match_mode=1, direction=0)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:6051
```

#####4、關於page的max trx id
我們上面多次提到二級索引page的max trx id，這個max trx id實際就是PAGE_MAX_TRX_ID，它位於page的offset 56後的8個位元組，實際上這個值只會存在於二級索引上，主鍵沒有這個值，我們可以看到如下：
```
#define PAGE_MAX_TRX_ID	 18	/* highest id of a trx which may have modified
				a record on the page; trx_id_t; defined only
				in secondary indexes and in the insert buffer
				tree */

[root@mgr2 test]# ./bcview testimp2.ibd 16 56 8
******************************************************************
This Tool Is Uesed For Find The Data In Binary format(Hexadecimal)
Usage:./bcview file blocksize offset cnt-bytes!                   
file: Is Your File Will To Find Data!                             
blocksize: Is N kb Block.Eg: 8 Is 8 Kb Blocksize(Oracle)!         
                         Eg: 16 Is 16 Kb Blocksize(Innodb)!       
offset:Is Every Block Offset Your Want Start!                                     
cnt-bytes:Is After Offset,How Bytes Your Want Gets!                               
Edtor QQ:22389860!                                                
Used gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)                
******************************************************************
----Current file size is :0.125000 Mb
----Current use set blockszie is 16 Kb
----Current file name is testimp2.ibd
current block:00000000--Offset:00056--cnt bytes:08--data is:0021000000060000
current block:00000001--Offset:00056--cnt bytes:08--data is:0000000000000000
current block:00000002--Offset:00056--cnt bytes:08--data is:0001000000000000
current block:00000003--Offset:00056--cnt bytes:08--data is:0000000000000000(主鍵沒有這個值)
current block:00000004--Offset:00056--cnt bytes:08--data is:0000000000016903(二級索引)
current block:00000005--Offset:00056--cnt bytes:08--data is:0000000000016924(二級索引)
```
每次每行更新後會更新這個值，如果大於則修改，小於則不變。函式page_update_max_trx_id中有如下片段
```
if (page_get_max_trx_id(buf_block_get_frame(block)) < trx_id) { //是否本次事務的trx id大於page的max trx id
		page_set_max_trx_id(block, page_zip, trx_id, mtr);
	}
```

####四、關於加鎖的階段
我們一般鎖需要加鎖的都是DML語句和select for update這樣的語句，這裡將加鎖分為資料查詢和資料修改兩個階段。
- 對於select for update：

主鍵訪問資料：訪問主鍵判斷是否存在隱式鎖，然後加顯示鎖。
二級索引訪問資料（需要回表的情況）：訪問二級索引判斷是否存在隱式鎖，然後加顯示鎖，接著回表主鍵判斷是否存在隱式鎖，然後加顯示鎖。

- 對於update/delete：

主鍵訪問修改資料：資料查詢階段主鍵判斷是否存在隱式鎖，然後加顯示鎖。資料修改階段涉及到了其他二級索引，那麼維護相應的二級索引加隱含鎖。

二級索引訪問修改資料：資料查詢階段二級索引判斷是否存在隱式鎖（可能需要回表判斷），二級索引加顯示鎖，資料修改階段回表修改主鍵資料加顯示鎖，然後維護各個二級索引（修改欄位涉及的二級索引或者修改主鍵則包含全部二級索引）加隱式鎖。

- 對於insert而言如果沒有堵塞（插入印象鎖和gap lock堵塞），那麼始終為隱式鎖。

注意這裡我們看到了隱式鎖，隱式鎖不會佔用row lock的結構體，因此在show engine innodb status裡面是看不到的，除非有其他事務顯示將其轉換為顯示鎖。我們來做幾個例子如下（REPEATABLE READ隔離級別）：
```
表結構和資料
mysql> show create table testimp4 \G
*************************** 1. row ***************************
       Table: testimp4
Create Table: CREATE TABLE `testimp4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `d` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`),
  KEY `d` (`d`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select *from testimp4;
+------+------+------+------------------------------------+
| id   | a    | b    | d                                  |
+------+------+------+------------------------------------+
|    5 |    5 |  300 | NULL                               |
|    6 | 7000 | 7700 | 1124                               |
|   11 | 7000 | 7700 | 1124                               |
|   12 | 7000 | 7700 | 1124                               |
|   13 | 2900 | 1800 | NULL                               |
|   14 | 2900 | 1800 | NULL                               |
| 1000 |   88 | 1499 | NULL                               |
| 4000 | 6000 | 5904 | iiiafsafasfihhhccccchhhigggofgo111 |
| 4001 | 7000 | 7700 | 1124454555                         |
| 9999 | 9999 | 9999 | a                                  |
+------+------+------+------------------------------------+
10 rows in set (0.00 sec)

```

###### 4.1 插入資料
```
begin；insert into testimp4 values(10000,10000,10000,'gp');（不提交）

```
|TIME|S1|S2|S3|S4|
|-|-|-|-|-|
|T1|begin；insert into testimp4 values(10000,10000,10000,'gp');（不提交）||||
|T2||select * from  testimp4 where id=10000 for update|||
|T3|||select * from  testimp4 where b=10000 for update||
|T4||||select * from  testimp4 where d='a' for update|
```
T1時刻S1鎖狀態：
---TRANSACTION 94487, ACTIVE 5 sec
1 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 482 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX

T2時刻S1鎖狀態：
---TRANSACTION 94487, ACTIVE 271 sec
2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 484 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 6; hex 000000017117; asc     q ;;
 2: len 7; hex d0000002c40110; asc        ;;
 3: len 4; hex 80002710; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 2; hex 6770; asc gp;;

T3時刻S1鎖狀態：
---TRANSACTION 94487, ACTIVE 337 sec
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 521 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 6; hex 000000017117; asc     q ;;
 2: len 7; hex d0000002c40110; asc        ;;
 3: len 4; hex 80002710; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 2; hex 6770; asc gp;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 4; hex 80002710; asc   ' ;;

T4時刻S1鎖狀態：
---TRANSACTION 94487, ACTIVE 408 sec
4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 559 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 6; hex 000000017117; asc     q ;;
 2: len 7; hex d0000002c40110; asc        ;;
 3: len 4; hex 80002710; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 2; hex 6770; asc gp;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 4; hex 80002710; asc   ' ;;

RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 6770; asc gp;;
 1: len 4; hex 80002710; asc   ' ;;
```
實際上我們看到這裡insert語句後主鍵和各個索引都上了隱含鎖只是看不到，透過其他S2,S3,S4我們逐步把這些隱式鎖轉換為了顯示鎖。

######4.2 delete語句透過主鍵刪除資料
|TIME|S1|S2|S3|
|-|-|-|-|
|T1|begin；delete from testimp4 where id=9999;（不提交）|||
|T2||select * from  testimp4 where b=9999 for update||
|T3||| select * from  testimp4 where d='a' for update;|

```
T1時刻S1鎖狀態：
---TRANSACTION 94493, ACTIVE 3 sec
2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 567 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94493 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 00000001711d; asc     q ;;
 2: len 7; hex 550000003b071b; asc U   ;  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

T2時刻S1鎖狀態：

---TRANSACTION 94493, ACTIVE 112 sec
4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 567 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94493 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 00000001711d; asc     q ;;
 2: len 7; hex 550000003b071b; asc U   ;  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

T3時刻S1鎖狀態：

---TRANSACTION 94493, ACTIVE 133 sec
4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 567 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94493 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 00000001711d; asc     q ;;
 2: len 7; hex 550000003b071b; asc U   ;  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 1; hex 61; asc a;;
 1: len 4; hex 8000270f; asc   ' ;;
```
實際上我們看到這裡delete語句後，主鍵加了顯示鎖，這是因為資料查詢階段需要加顯示鎖，但是各個二級索引是由於維護而加的是隱式鎖，我們透過S2,S3將其轉換為了顯示鎖。

######4.3 delete語句透過二級索引刪除資料

|TIME|S1|S2|
|-|-|-|
|T1|begin；delete from testimp4 where b=9999;（不提交）|
|T2||select * from  testimp4 where d='a' for update|

```
T1時刻S1鎖狀態：

---TRANSACTION 94501, ACTIVE 109 sec
3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 576 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94501 lock mode IX
RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94501 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94501 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017125; asc     q%;;
 2: len 7; hex 5a0000002518ea; asc Z   %  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

T2時刻S1鎖狀態：

---TRANSACTION 94501, ACTIVE 119 sec
4 lock struct(s), heap size 1160, 4 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 576 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94501 lock mode IX
RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94501 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94501 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017125; asc     q%;;
 2: len 7; hex 5a0000002518ea; asc Z   %  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94501 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 1; hex 61; asc a;;
 1: len 4; hex 8000270f; asc   ' ;;

```
實際上我們看到這裡delete語句後，顯示二級索引加了顯示鎖，然後主鍵加了顯示鎖，這是因為資料查詢階段先查詢的二級索引然後回表查的主鍵，但是對於二級索引d來講是由於維護而加的是隱式鎖，我們透過S2將其轉換為了顯示鎖。

######4.4 update語句透過主鍵修改資料
這裡要特別注意一下，對於二級索引的更新通常是進行了刪除和插入，因此這裡有2行資料都有隱式鎖
|TIME|S1|S2|S3|
|-|-|-|-|
|T1|begin;update testimp4 set b=10000  where id=9999;（不提交）||
|T2||select * from  testimp4 where b=9999 for update||
|T3|||select * from  testimp4 where b=10000 for update|

```
T1時刻S1鎖狀態
---TRANSACTION 94553, ACTIVE 7 sec
2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 730 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017159; asc     qY;;
 2: len 7; hex 770000002a187f; asc w   *  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

T2時刻S1鎖狀態
---TRANSACTION 94553, ACTIVE 62 sec
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 730 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017159; asc     qY;;
 2: len 7; hex 770000002a187f; asc w   *  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

T3時刻S1鎖狀態
---TRANSACTION 94553, ACTIVE 128 sec
3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 730 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017159; asc     qY;;
 2: len 7; hex 770000002a187f; asc w   *  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

```
這裡由於對錶的二級索引b透過主鍵進行了修改，那麼二級索引包含了2條資料，一條標記為del flag，另外一條為插入如下：
```
(11) normal record offset:266 heapno:12 n_owned 0,delflag:Y minflag:0 rectype:0
(12) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(13) SUPREMUM record offset:112 heapno:1 n_owned 8,delflag:N minflag:0 rectype:3
```
因此這兩行都上了隱式鎖，這是由於二級索引維護而上的，值得注意的是二級索引d不會上隱式鎖，因為update語句的修改不會涉及到d列索引，因此不會維護。如果查詢d列上的值（for update），會獲取d列上的鎖成功，然後會堵塞在主鍵id上如下：
```
---TRANSACTION 94565, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 16, OS thread handle 140737086228224, query id 748 localhost root Sending data
select * from testimp4 where d='a' for update
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94565 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017161; asc     qa;;
 2: len 7; hex 7c0000002d25eb; asc |   -% ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

------------------
TABLE LOCK table `test`.`testimp4` trx id 94565 lock mode IX
RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94565 lock_mode X
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 61; asc a;;
 1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94565 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017161; asc     qa;;
 2: len 7; hex 7c0000002d25eb; asc |   -% ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

````

情況還有很多不在一一列舉，Innodb行鎖一直都是一個令人頭疼的問題。

####五、關於鎖的判定

######5.1 lock_sec_rec_read_check_and_lock函式
主要用於二級索引資料查詢段階段加顯示鎖，，對於update/delete而言，首先是需要找到需要修改的資料，加鎖前需要判斷本記錄是否存在隱式鎖，由於二級索引行資料不包含trx id，因此先用page的max trx id和當前活躍的最小讀寫事務進行比對判斷，如果大於等於則可能存在顯示鎖，然後需要回表透過主鍵進行精細化判斷。而精細化回表判斷行是否存在隱式鎖，那麼代價就比較大了，因此這需要一個判斷流程如下
```  
lock_sec_rec_read_check_and_lock：
                                
	if ((page_get_max_trx_id(block->frame) >= trx_rw_min_trx_id()
	     || recv_recovery_is_on())
	    && !page_rec_is_supremum(rec)) {

lock_rec_convert_impl_to_expl(block, rec, index, offsets);//如果符合前面的條件才調入 lock_rec_convert_impl_to_expl
	} 
如下調入：                               
  ->lock_rec_convert_impl_to_expl
    ->lock_sec_rec_some_has_impl
      ->row_vers_impl_x_locked  此處會進行聚集索引的回表，同樣是透過二級索引進行定位返回btr_cur_search_to_nth_level
        ->row_vers_impl_x_locked_low 最後會調入 row_vers_impl_x_locked_low函式進行核心判斷
  
```       
棧如下：
```
#0  row_vers_impl_x_locked_low (clust_rec=0x7fff39a21226 "\200", clust_index=0x7ffeb5092680, rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730, mtr=0x7fffe8460e90)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0vers.cc:101
#1  0x0000000001b2c84e in row_vers_impl_x_locked (rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0vers.cc:390
#2  0x00000000019e8448 in lock_sec_rec_some_has_impl (rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:1276
#3  0x00000000019f339a in lock_rec_convert_impl_to_expl (block=0x7fff38d94ca0, rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:6124
#4  0x00000000019f3dd2 in lock_sec_rec_read_check_and_lock (flags=0, block=0x7fff38d94ca0, rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730, mode=LOCK_X, 
    gap_mode=1024, thr=0x7ffeb4c89358) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:6357
#5  0x0000000001af7271 in sel_set_rec_lock (pcur=0x7ffeb4c887d8, rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730, mode=3, type=1024, thr=0x7ffeb4c89358, 
    mtr=0x7fffe8461a50) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:1278
#6  0x0000000001b00049 in row_search_mvcc (buf=0x7ffeb4977070 "\370\211\037", mode=PAGE_CUR_GE, prebuilt=0x7ffeb4c885b0, match_mode=1, direction=1)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:5710
```

但是需要注意的是，max trx id只會在二級索引上更新，並且每次更新一行都會更新掉，那麼引起的一個問題就是如果連續多次刪除同一個二級索引上的記錄（delete from testimp4 where b=7700;），除第一次以外都會調入row_vers_impl_x_locked_low這個函式，因為這是查詢一行加鎖一行修改一行（每行都會修改page的max trx id）的。但是update卻不同，update如果修改本二級索引的值一般會進入（如：update testimp4 set b=1500 where b=1800;）Searching rows for update狀態，先建立一個臨時檔案來先儲存需要更改的行記錄，然後進行批次更改進入updating狀態，那麼則不會出現這種問題，因為這是在資料查詢階段進行的判斷，而不是資料修改階段。又比如（如：update testimp2 set c='a' where b=1800)這樣的語句也不會觸發，這是因為b索引的行記錄一直沒有改變，因此不會修改b索引page的max trx id。因此update很好的規避了這個問題不會頻繁的進入函式row_vers_impl_x_locked_low進行判定，但是delete卻不行。
關於row_vers_impl_x_locked_low函式對於二級索引是否存在隱式鎖的判定，比較複雜分為好多種情況，不再描述。
因此最開始我們看到的問題，這個過程已經進入了row_vers_impl_x_locked_low函式，那麼可以判斷這個delete語句可能更新了多行（但是從程式碼行數上判斷不是這種情況），或者有可能本語句事務做過修改本語句修改記錄的其他語句，需要進行精細化判斷。

##### 5.2 lock_sec_rec_modify_check_and_lock
主要用於資料修改階段加隱式鎖，二級索引由於行資料的修改（update修改了本二級索引字包含段值或者尾部的主鍵）而被動維護的加鎖。注意如果是select for update where條件是主鍵則不會加判斷二級索引是否包含隱含鎖，如果出現衝突會堵塞在主鍵上。

##### 5.3 lock_clust_rec_read_check_and_lock 
資料查詢階段加顯示鎖，主要用於主鍵查詢資料加顯示鎖或者二級索引訪問後的回表主鍵加顯示鎖，加鎖前需要判斷是否存在隱含鎖。由於主鍵行中包含了trx id偽列，因此可以簡單的用本行trx id的事務是否還活躍進行判定了，這個過程代價很小，因此每行加鎖總是會有這個過程，也就是每次都會呼叫lock_rec_convert_impl_to_expl函式進行判斷，如下：
```
lock_clust_rec_read_check_and_lock
 ->lock_rec_convert_impl_to_expl
   ->lock_clust_rec_some_has_impl (主鍵判斷非常簡單)
```
棧如下：
```
#0  lock_clust_rec_some_has_impl (rec=0x7fff05ad40db "\200", index=0x7ffe8802ce70, offsets=0x7fffe8461660)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/include/lock0priv.ic:69
#1  0x00000000019f3333 in lock_rec_convert_impl_to_expl (block=0x7fff050a0950, rec=0x7fff05ad40db "\200", index=0x7ffe8802ce70, offsets=0x7fffe8461660)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:6118
#2  0x00000000019f418d in lock_clust_rec_read_check_and_lock (flags=0, block=0x7fff050a0950, rec=0x7fff05ad40db "\200", index=0x7ffe8802ce70, offsets=0x7fffe8461660, mode=LOCK_X, 
    gap_mode=1024, thr=0x7ffeb49903c8) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:6430
#3  0x0000000001af7193 in sel_set_rec_lock (pcur=0x7ffeb498fe38, rec=0x7fff05ad40db "\200", index=0x7ffe8802ce70, offsets=0x7fffe8461660, mode=3, type=1024, thr=0x7ffeb49903c8, 
    mtr=0x7fffe8461980) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:1263
#4  0x0000000001b00049 in row_search_mvcc (buf=0x7ffeb498f380 "\371\005", mode=PAGE_CUR_GE, prebuilt=0x7ffeb498fc10, match_mode=1, direction=0)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:5710
```                                   
##### 5.4 lock_clust_rec_modify_check_and_lock

主鍵資料修改階段加隱式鎖，當前發現為在直接update主鍵值或者delete操作的時候，但是這種情況下實際上主鍵已經在資料查詢階段加了顯示鎖。

###六、update不完全等同於delete&&insert

直接區分如下：

- 主鍵更新，介面row_upd_clust_step

```
row_upd_changes_ord_field_binary 判斷是否更新了聚集索引的值
如果更新了
  -> row_upd_clust_rec_by_insert 進行主鍵刪除插入(設定del flag)
如果沒有更新
  ->row_upd_clust_rec
    ->btr_cur_optimistic_update 只考慮樂觀update
      ->row_upd_changes_field_size_or_external 判斷新記錄是否超過本行現有大小
      如果否
        ->btr_cur_update_in_place 原地更新 
      如果是
        ->page_cur_delete_rec 則需要進行主鍵刪除(實際刪除非設定del falg)
        ->btr_cur_insert_if_possible 插入
```
- 二級索引更新，介面row_upd_sec_step
始終為刪除插入(設定del flag)

###七、關於History list length 的單位

實際上History list length 就是當一個update undo log （非insert）的計數器，一個事務只有一個undo log 。來源為trx_sys->rseg_history_len，這個值會在事務提交的時候更新，無論事務大小。但是由於很多內部事務的存在，這個值會遠大於可觀測的事務個數。棧如下：
```
#0  trx_purge_add_update_undo_to_history (trx=0x7fffeac7df50, undo_ptr=0x7fffeac7e370, undo_page=0x7fff2837c000 "\373\252\223T", update_rseg_history_len=true, n_added_logs=1, 
    mtr=0x7fffe8399830) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/trx/trx0purge.cc:354
#1  0x0000000001b9c064 in trx_undo_update_cleanup (trx=0x7fffeac7df50, undo_ptr=0x7fffeac7e370, undo_page=0x7fff2837c000 "\373\252\223T", update_rseg_history_len=true, n_added_logs=1, 
    mtr=0x7fffe8399830) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/trx/trx0undo.cc:1970
#2  0x0000000001b8b639 in trx_write_serialisation_history (trx=0x7fffeac7df50, mtr=0x7fffe8399830) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/trx/trx0trx.cc:1684
#3  0x0000000001b8c9b0 in trx_commit_low (trx=0x7fffeac7df50, mtr=0x7fffe8399830) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/trx/trx0trx.cc:2184
```

######到這裡，雜七雜八記錄了一大堆，記錄於此以備後用。

###附錄1函式介面
#####1、read view
MVCC::view_open：建立read view
ReadView::prepare：準備read view中的值
ReadView::complete：寫入read view中的值
MVCC::view_close：釋放read view
#####2、可見性判斷
lock_clust_rec_cons_read_sees：主鍵可見性判斷
lock_sec_rec_cons_read_sees：二級索引可見性判斷

###附錄2具體函式
#####1、read view
```
	/** 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 (mutex));

m_creator_trx_id = id;

m_low_limit_no = m_low_limit_id = trx_sys->max_trx_id;

if (!trx_sys->rw_trx_ids.empty()) {
		copy_trx_ids(trx_sys->rw_trx_ids);
	} else {
		m_ids.clear();
	}

if (UT_LIST_GET_LEN(trx_sys->serialisation_list) > 0) {
		const trx_t*	trx;

trx = UT_LIST_GET_FIRST(trx_sys->serialisation_list);

if (trx->no < m_low_limit_no) {
			m_low_limit_no = trx->no;
		}
	}
}
void
ReadView::complete()
{
	ut_ad(!m_cloned);
	/* The first active transaction has the smallest id. */
	m_up_limit_id = !m_ids.empty() ? m_ids.front() : m_low_limit_id;

ut_ad(m_up_limit_id <= m_low_limit_id);

m_closed = false;
}
```

2、可見性判斷

```
二級索引回表判斷可見性

Row_sel_get_clust_rec_for_mysql::operator()
 ->lock_clust_rec_cons_read_sees (回表後根據主鍵判斷其可見性)
 ->row_sel_build_prev_vers_for_mysql(構建前版本)
  ->row_vers_build_for_consistent_read
    本函式迴圈構建，直到條件滿足，或者前版本為NULL
      		if (view->changes_visible(trx_id, index->table->name)) {

/* The view already sees this version: we can copy
		        	it to in_heap and return */
            
		        	buf = static_cast<byte>(
		        		mem_heap_alloc(
		        			in_heap, rec_offs_size(*offsets)));
            
		        	*old_vers = rec_copy(buf, prev_version, *offsets);
		        	rec_offs_make_valid(*old_vers, index, *offsets);
            
		        	if (vrow && *vrow) {
		        		*vrow = dtuple_copy(*vrow, in_heap);
		        		dtuple_dup_v_fld(*vrow, in_heap);
		        	}
		        	break;
  
最終會將前版本的主鍵值根據需求取欄位返回給MySQL層
```

```
關於using index 也需要回表流程

row_search_mvcc:
if (!srv_read_only_mode
			    && !lock_sec_rec_cons_read_sees( // 如果二級索引記錄判斷為不可見
					rec, index, 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. */
				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; //走這裡就進入了回表判斷流程
				}

lock_sec_rec_cons_read_sees：
trx_id_t	max_trx_id = page_get_max_trx_id(page_align(rec));//獲取頁的max trx id
	ut_ad(max_trx_id > 0);
return(view->sees(max_trx_id));

```</byte></incomplete>

MySQL：從一個案例深入剖析InnoDB隱式鎖和可見性判斷

相關文章