974
MySQL鎖係列（二）之 鎖解讀
背景
- 鎖係列第一期的時候介紹的鎖，我們要如何去解讀呢？
- 在哪裏能夠看到這些鎖？
鎖信息解讀
工欲善其事必先利其器
show engine innodb status 關於鎖的信息是最詳細的
案例一（有索引的情況）
- 前期準備
dba:lc_3>
dba:lc_3>
dba:lc_3> show create table a;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------+
| a | CREATE TABLE `a` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `idx_b` (`b`),
KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------+
1 row in set (0.00 sec)
dba:lc_3>
dba:lc_3> select * from a;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 1 | 3 | 5 | 7 |
| 3 | 5 | 7 | 9 |
| 5 | 7 | 9 | 11 |
| 7 | 9 | 11 | 13 |
+---+------+------+------+
4 rows in set (0.00 sec)
- 產生鎖的語句
dba:lc_3> set tx_isolation = 'repeatable-read'; --事務隔離級別為repeatable-read，以後介紹
Query OK, 0 rows affected (0.00 sec)
begin;
select * from a where c=7 for update;
- show engine innodb status
------------
TRANSACTIONS
------------
Trx id counter 133588132
Purge done for trx's n:o < 133588131 undo n:o < 0 state: running but idle
History list length 836
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421565826149088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 133588131, ACTIVE 8 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 116, OS thread handle 140001238423296, query id 891 localhost dba cleaning up
TABLE LOCK table `lc_3`.`a` trx id 133588131 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588131 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 4; hex 80000003; asc ;;
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133588131 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d011d; asc ' ;;
3: len 4; hex 80000005; asc ;;
4: len 4; hex 80000007; asc ;;
5: len 4; hex 80000009; asc ;;
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588131 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc ;;
1: len 4; hex 80000005; asc ;;
- show engine innodb status 解讀
* Trx id counter 133588132
描述的是：下一個事務的id為133588132
* Purge done for trx's n:o < 133588131 undo n:o < 0 state: running but idle
Purge線程已經將trxid小於133588131的事務都purge了，目前purge線程的狀態為idle
Purge線程無法控製
* History list length 836
undo中未被清除的事務數量，如果這個值非常大，說明係統來不及回收undo，需要人工介入了。
疑問：上麵的purge都已經刷新完了，為什麼History list length 不等於0，這是一個有意思的問題
* ---TRANSACTION 133588131, ACTIVE 8 sec
當前事務id為133588131
* 4 lock struct(s), heap size 1136, 3 row lock(s)
產生了4個鎖對象結構，占用內存大小1136字節，3條記錄被鎖住(1個表鎖，3個記錄鎖)
* TABLE LOCK table `lc_3`.`a` trx id 133588131 lock mode IX
在a表上麵有一個表鎖，這個鎖的模式為IX（排他意向鎖）
* RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588131 lock_mode X
在space id=281（a表的表空間），page no=5的頁上，對表a上的idx_c索引加了記錄鎖，鎖模式為：next-key 鎖（這個在上一節中有告知）
該頁上麵的位圖鎖占有72bits
* 具體鎖了哪些記錄
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 -- heap no 3 的記錄被鎖住了
0: len 4; hex 80000007; asc ;; --這是一個二級索引上的鎖，7被鎖住
1: len 4; hex 80000003; asc ;; --二級索引上麵還會自帶一個主鍵，所以主鍵值3也會被鎖住
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133588131 lock_mode X locks rec but not gap（這是一個記錄鎖，在主鍵上鎖住的）
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000003; asc ;; --第一個字段是主鍵3，占用4個字節，被鎖住了
1: len 6; hex 000007f66444; asc dD;; --該字段為6個字節的事務id，這個id表示最近一次被更新的事務id
2: len 7; hex fc0000271d011d; asc ' ;; --該字段為7個字節的回滾指針，用於mvcc
3: len 4; hex 80000005; asc ;; --該字段表示的是此記錄的第二個字段5
4: len 4; hex 80000007; asc ;; --該字段表示的是此記錄的第三個字段7
5: len 4; hex 80000009; asc ;; --該字段表示的是此記錄的第四個字段9
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133588131 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc ;; --這是一個二級索引上的鎖，9被鎖住
1: len 4; hex 80000005; asc ;; --二級索引上麵還會自帶一個主鍵，所以主鍵值5被鎖住
案例二(無索引的情況)
- 前期準備
dba:lc_3> show create table t;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
dba:lc_3> select * from t;
+------+
| i |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
+------+
16 rows in set (0.00 sec)
- 產生鎖語句
dba:lc_3> set tx_isolation = 'repeatable-read';
Query OK, 0 rows affected (0.00 sec)
dba:lc_3> select * from t where i=1 for update;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
- show engine innodb status
------------
TRANSACTIONS
------------
Trx id counter 133588133
Purge done for trx's n:o < 133588131 undo n:o < 0 state: running but idle
History list length 836
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421565826149088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 133588132, ACTIVE 6 sec
2 lock struct(s), heap size 1136, 17 row lock(s)
MySQL thread id 118, OS thread handle 140001238955776, query id 904 localhost dba cleaning up
TABLE LOCK table `lc_3`.`t` trx id 133588132 lock mode IX
RECORD LOCKS space id 278 page no 3 n bits 88 index GEN_CLUST_INDEX of table `lc_3`.`t` trx id 133588132 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 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff05; asc ;;
1: len 6; hex 000007f66397; asc c ;;
2: len 7; hex fb0000271c0110; asc ' ;;
3: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff06; asc ;;
1: len 6; hex 000007f663ea; asc c ;;
2: len 7; hex bb000027340110; asc '4 ;;
3: len 4; hex 80000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff07; asc ;;
1: len 6; hex 000007f66426; asc d&;;
2: len 7; hex e4000040210110; asc @! ;;
3: len 4; hex 80000003; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff08; asc ;;
1: len 6; hex 000007f66427; asc d';;
2: len 7; hex e5000040220110; asc @" ;;
3: len 4; hex 80000004; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff09; asc ;;
1: len 6; hex 000007f6642c; asc d,;;
2: len 7; hex e8000040230110; asc @# ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff0a; asc ;;
1: len 6; hex 000007f6642d; asc d-;;
2: len 7; hex e9000040240110; asc @$ ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff0b; asc ;;
1: len 6; hex 000007f66432; asc d2;;
2: len 7; hex ec0000273f0110; asc '? ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff0c; asc ;;
1: len 6; hex 000007f66433; asc d3;;
2: len 7; hex ed000040020110; asc @ ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff0d; asc ;;
1: len 6; hex 000007f66434; asc d4;;
2: len 7; hex ee000040030110; asc @ ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff0e; asc ;;
1: len 6; hex 000007f66435; asc d5;;
2: len 7; hex ef000040040110; asc @ ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff0f; asc ;;
1: len 6; hex 000007f66436; asc d6;;
2: len 7; hex f0000040050110; asc @ ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff10; asc ;;
1: len 6; hex 000007f66437; asc d7;;
2: len 7; hex f1000040060110; asc @ ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 14 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff11; asc ;;
1: len 6; hex 000007f66438; asc d8;;
2: len 7; hex f2000027130110; asc ' ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 15 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff12; asc ;;
1: len 6; hex 000007f66439; asc d9;;
2: len 7; hex f3000027140110; asc ' ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 16 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff13; asc ;;
1: len 6; hex 000007f6643a; asc d:;;
2: len 7; hex f4000027150110; asc ' ;;
3: len 4; hex 80000005; asc ;;
Record lock, heap no 17 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 0000000dff14; asc ;;
1: len 6; hex 000007f6643b; asc d;;;
2: len 7; hex f5000027160110; asc ' ;;
3: len 4; hex 80000005; asc ;;
- 鎖解讀
1. 這裏隻列出跟第一個案例不同的地方解讀，其他的都一樣
2. RECORD LOCKS space id 278 page no 3 n bits 88 index GEN_CLUST_INDEX of table `lc_3`.`t` trx id 133588132 lock_mode X
由於表定義沒有顯示的索引，而InnoDB又是索引組織表，會自動創建一個索引，這裏麵叫index GEN_CLUST_INDEX
3. 由於沒有索引，那麼會對每條記錄都加上lock_mode X （next-key lock）
4. 這裏有一個明顯不一樣的是：
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
supremum 值得是頁裏麵的最後一條記錄(偽記錄，通過select查不到的,並不是真實的記錄)，heap no=1 , Infimum 表示的是頁裏麵的第一個記錄（偽記錄）
可以簡單的認為：
supremum 為upper bounds，正去窮大
Infimum 為Minimal bounds，負無窮大
那這裏的加鎖的意思就是：通過supremum 鎖住index GEN_CLUST_INDEX的最大值到正無窮大的區間，這樣就可以鎖住全部記錄，以及全部間隙，相當於表鎖
鎖開銷
- 鎖10條記錄和鎖1條記錄的開銷是成正比的嗎？
1. 由於鎖的內存對象針對的是頁而不是記錄，所以開銷並不是非常大
2. 鎖10條記錄和鎖1條記錄的內存開銷都是一樣的，都是heap size=1136個字節
最後
這裏麵select * from a where c=7 for update; 明明隻鎖一條記錄，為什麼卻看到4把鎖呢？
看到這裏是不是有點暈，沒關係，這個問題，後麵會慢慢揭曉答案
最後更新：2017-06-05 21:31:49