4、關于page的max trx id
我們上面多次提到二級索引page的max trx id，這個max trx id實際就是PAGE_MAX_TRX_ID，它位于page的offset 56後的8個位元組，實際上這個值隻會存在于二級索引上，主鍵沒有這個值，我們可以看到如下：
表結構和資料
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) 每次每行更新後會更新這個值，如果大于則修改，小于則不變。函數page_update_max_trx_id中有如下片段
begin；insert into testimp4 values(10000,10000,10000,'gp');（不送出） 四、關于加鎖的階段
我們一般鎖需要加鎖的都是DML語句和select for update這樣的語句，這裡将加鎖分為資料查找和資料修改兩個階段。
- 對于select for update：
主鍵通路資料：通路主鍵判斷是否存在隐式鎖，然後加顯示鎖。二級索引通路資料（需要回表的情況）：通路二級索引判斷是否存在隐式鎖，然後加顯示鎖，接着回表主鍵判斷是否存在隐式鎖，然後加顯示鎖。
- 對于update/delete：
主鍵通路修改資料：資料查找階段主鍵判斷是否存在隐式鎖，然後加顯示鎖。資料修改階段涉及到了其他二級索引，那麼維護相應的二級索引加隐含鎖。
二級索引通路修改資料：資料查找階段二級索引判斷是否存在隐式鎖（可能需要回表判斷），二級索引加顯示鎖，資料修改階段回表修改主鍵資料加顯示鎖，然後維護各個二級索引（修改字段涉及的二級索引或者修改主鍵則包含全部二級索引）加隐式鎖。
- 對于insert而言如果沒有堵塞（插入印象鎖和gap lock堵塞），那麼始終為隐式鎖。
注意這裡我們看到了隐式鎖，隐式鎖不會占用row的結構體，是以在show engine innodb status裡面是看不到的，除非有其他事務顯示将其轉換為顯示鎖。我們來做幾個例子如下（REPEATABLE READ隔離級别）：
4.1 插入資料
begin；insert into testimp4 values(10000,10000,10000,'gp');（不送出） 
# 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語句通過主鍵删除資料
# 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将其轉換為了顯示鎖。