763
MySQL鎖係列（七）之 鎖算法詳解
能學到什麼
- 隔離級別和鎖的關係
- 重點講解在RR隔離級別下的加鎖算法邏輯
- 重點羅列了比較典型的幾種加鎖邏輯案例
- 對insert的加鎖邏輯進行了深度剖析
- 實戰中剖析加鎖的全過程
- InnoDB為什麼要這樣加鎖
隔離級別和算法
- repeatable-read
1. 使用的是next-key locking
2. next-key lock = record lock + Gap lock
- read-committed
1. 使用的是 record lock
2. 當然特殊情況下( purge + unique key )，也會有Gap lock
我們接下來就以RR隔離級別來闡述，因為RC更加簡單
- 鎖的通用算法
RR隔離級別
1. 鎖是在索引上實現的
2. 假設有一個key，有5條記錄， 1，3，5，7，9. 如果where id<5 ， 那麼鎖住的區間不是（-∞，5），而是(-∞,1],(1,3],(3,5] 多個區間組合而成
3. RR隔離級別使用的是：next-key lock算法，即：鎖住 記錄本身+區間
4. next-key lock 降級為 record lock的情況
如果是唯一索引，且查詢條件得到的結果集是1條記錄（等值，而不是範圍），那麼會降級為記錄鎖
典型的案例：where primary_key = 1 (會降級), 而不是 where primary_key < 10 （由於返回的結果集不僅僅一條，那麼不會降級）
5. 上鎖，不僅僅對主鍵索引加鎖，還需要對輔助索引加鎖，這一點非常重要
鎖算法的案例剖析
RR隔離級別
- 表結構
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> 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)
* 設置RR隔離級別
set tx_isolation = 'repeatable-read';
- 等值查詢，非唯一索引的加鎖邏輯
dba:lc_3> begin;
Query OK, 0 rows affected (0.00 sec)
dba:lc_3> select * from a where c=9 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 5 | 7 | 9 | 11 |
+---+------+------+------+
1 row in set (0.00 sec)
TABLE LOCK table `lc_3`.`a` trx id 133601815 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601815 lock_mode X
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 ;;
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601815 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d012a; asc ' *;;
3: len 4; hex 80000007; asc ;;
4: len 4; hex 80000009; asc ;;
5: len 4; hex 8000000b; asc ;;
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601815 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 4; hex 80000007; asc ;;
鎖的結構如下：
對二級索引idx_c：
1. 加next-key lock，((7,3),(9,5)] , ((9,5),(11,7)]，解讀一下：((7,3),(9,5)] 表示：7是二級索引key，3是對應的主鍵
2.這樣寫不太好懂，所以以後就暫時忽略掉主鍵這樣寫： next-key lock = (7,9],(9,11]
對主鍵索引primary： 加record lock，[5]
- 等值查詢，唯一鍵的加鎖邏輯
dba:lc_3> select * from a where b=9 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 7 | 9 | 11 | 13 |
+---+------+------+------+
1 row in set (0.00 sec)
TABLE LOCK table `lc_3`.`a` trx id 133601816 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601816 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc ;;
1: len 4; hex 80000007; asc ;;
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601816 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d0137; asc ' 7;;
3: len 4; hex 80000009; asc ;;
4: len 4; hex 8000000b; asc ;;
5: len 4; hex 8000000d; asc ;;
鎖的結構如下：
對二級索引idx_b：
1. 加record lock，[9]
對主鍵索引primary：
1. 加record lock，[7]
- >= ，非唯一索引的加鎖邏輯
dba:lc_3> select * from a where c>=9 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 5 | 7 | 9 | 11 |
| 7 | 9 | 11 | 13 |
+---+------+------+------+
2 rows in set (0.00 sec)
TABLE LOCK table `lc_3`.`a` trx id 133601817 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601817 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 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc ;;
1: len 4; hex 80000005; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 4; hex 80000007; asc ;;
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601817 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d012a; asc ' *;;
3: len 4; hex 80000007; asc ;;
4: len 4; hex 80000009; asc ;;
5: len 4; hex 8000000b; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d0137; asc ' 7;;
3: len 4; hex 80000009; asc ;;
4: len 4; hex 8000000b; asc ;;
5: len 4; hex 8000000d; asc ;;
鎖的結構如下：
對二級索引idx_c：
1. 加next-key lock， (7,9],(9,11],(11,∞]
對主鍵索引primary：
1. 加record lock，[5],[7]
- >= ，唯一索引的加鎖邏輯
dba:lc_3> select * from a where b>=7 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 5 | 7 | 9 | 11 |
| 7 | 9 | 11 | 13 |
+---+------+------+------+
2 rows in set (0.00 sec)
TABLE LOCK table `lc_3`.`a` trx id 133601820 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601820 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 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 4; hex 80000005; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc ;;
1: len 4; hex 80000007; asc ;;
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601820 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d012a; asc ' *;;
3: len 4; hex 80000007; asc ;;
4: len 4; hex 80000009; asc ;;
5: len 4; hex 8000000b; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d0137; asc ' 7;;
3: len 4; hex 80000009; asc ;;
4: len 4; hex 8000000b; asc ;;
5: len 4; hex 8000000d; asc ;;
鎖的結構如下：
對二級索引idx_b：
1. 加next-key lock， (5,7],(7,9],(9,∞]
對主鍵索引primary：
1. 加record lock，[5],[7]
- <= , 非唯一索引的加鎖邏輯
dba:lc_3> select * from a where c<=7 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 1 | 3 | 5 | 7 |
| 3 | 5 | 7 | 9 |
+---+------+------+------+
2 rows in set (0.00 sec)
TABLE LOCK table `lc_3`.`a` trx id 133601822 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601822 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000001; asc ;;
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 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 ;;
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601822 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 80000001; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d0110; asc ' ;;
3: len 4; hex 80000003; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000007; asc ;;
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 ;;
鎖的結構如下：
對二級索引idx_c：
1. 加next-key lock， (-∞,5],(5,7],(7,9]
對主鍵索引primary：
1. 加record lock，[1],[3]
- <= , 唯一索引的加鎖邏輯
dba:lc_3> select * from a where b<=5 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 1 | 3 | 5 | 7 |
| 3 | 5 | 7 | 9 |
+---+------+------+------+
2 rows in set (0.00 sec)
TABLE LOCK table `lc_3`.`a` trx id 133601823 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601823 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000003; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 4; hex 80000005; asc ;;
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601823 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 80000001; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d0110; asc ' ;;
3: len 4; hex 80000003; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000007; asc ;;
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 ;;
鎖的結構如下：
對二級索引idx_b：
1. 加next-key lock， (-∞,3],(3,5],(5,7]
對主鍵索引primary：
1. 加record lock，[1],[3]
- > , 非唯一索引的加鎖邏輯
dba:lc_3> select * from a where c>9 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 7 | 9 | 11 | 13 |
+---+------+------+------+
1 row in set (0.00 sec)
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601825 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 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 4; hex 80000007; asc ;;
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601825 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d0137; asc ' 7;;
3: len 4; hex 80000009; asc ;;
4: len 4; hex 8000000b; asc ;;
5: len 4; hex 8000000d; asc ;;
鎖的結構如下：
對二級索引idx_c：
1. 加next-key lock， (9,11],(11,∞]
對主鍵索引primary：
1. 加record lock，[7]
- > , 唯一索引的加鎖邏輯
dba:lc_3> select * from a where b>7 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 7 | 9 | 11 | 13 |
+---+------+------+------+
1 row in set (0.00 sec)
TABLE LOCK table `lc_3`.`a` trx id 133601826 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601826 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 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc ;;
1: len 4; hex 80000007; asc ;;
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601826 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d0137; asc ' 7;;
3: len 4; hex 80000009; asc ;;
4: len 4; hex 8000000b; asc ;;
5: len 4; hex 8000000d; asc ;;
鎖的結構如下：
對二級索引idx_b：
1. 加next-key lock， (7,9],(9,∞]
對主鍵索引primary：
1. 加record lock，[7]
- < , 非唯一索引的加鎖邏輯
dba:lc_3> select * from a where c<7 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 1 | 3 | 5 | 7 |
+---+------+------+------+
1 row in set (0.00 sec)
TABLE LOCK table `lc_3`.`a` trx id 133601827 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601827 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000001; asc ;;
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 133601827 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 80000001; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d0110; asc ' ;;
3: len 4; hex 80000003; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000007; asc ;;
鎖的結構如下：
對二級索引idx_c：
1. 加next-key lock， (-∞,5],(5,7]
對主鍵索引primary：
1. 加record lock，[1]
- < , 唯一索引的加鎖邏輯
dba:lc_3> select * from a where b<5 for update;
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 1 | 3 | 5 | 7 |
+---+------+------+------+
1 row in set (0.00 sec)
TABLE LOCK table `lc_3`.`a` trx id 133601828 lock mode IX
RECORD LOCKS space id 281 page no 4 n bits 72 index idx_b of table `lc_3`.`a` trx id 133601828 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; 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 133601828 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 80000001; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d0110; asc ' ;;
3: len 4; hex 80000003; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000007; asc ;;
鎖的結構如下：
對二級索引idx_c：
1. 加next-key lock， (-∞,3],(3,5]
對主鍵索引primary：
1. 加record lock，[1]
- 總結之前的加鎖邏輯
* 如果
1. select * from xx where col <比較運算符> M for update
2. M->next-rec: 表示M的下一條記錄
3. M->pre-rec: 表示M的前一條記錄
########第一輪總結########
* 等值查詢M，非唯一索引的加鎖邏輯
(M->pre-rec,M],(M,M->next-rec]
* 等值查詢M，唯一鍵的加鎖邏輯
[M], next-lock 降級為 record locks
* >= ，非唯一索引的加鎖邏輯
(M->pre_rec,M],(M,M->next-rec]....(∞]
* >= ，唯一索引的加鎖邏輯
(M->pre_rec,M],(M,M->next-rec]....(∞]
* <= , 非唯一索引的加鎖邏輯
(-∞] ... (M,M->next-rec]
* <= , 唯一索引的加鎖邏輯
(-∞] ... (M,M->next-rec]
* > , 非唯一索引的加鎖邏輯
(M,M->next-rec] ... (∞]
* > , 唯一索引的加鎖邏輯
(M,M->next-rec] ... (∞]
* < , 非唯一索引的加鎖邏輯
(-∞] ... (M->rec,M]
* < , 唯一索引的加鎖邏輯
(-∞] ... (M->rec,M]
########第二輪總結合並########
* 等值查詢M，非唯一索引的加鎖邏輯
(M->pre-rec,M],(M,M->next-rec]
* 等值查詢M，唯一鍵的加鎖邏輯
[M], next-lock 降級為 record locks
這裏大家還記得之前講過的通用算法嗎：
next-key lock 降級為 record lock的情況：
如果是唯一索引，且查詢條件得到的結果集是1條記錄（等值，而不是範圍），那麼會降級為記錄鎖
* >= ，加鎖邏輯
(M->pre_rec,M],(M,M->next-rec]....(∞]
* > , 加鎖邏輯
(M,M->next-rec] ... (∞]
* <= , 加鎖邏輯
(-∞] ... (M,M->next-rec]
* < , 加鎖邏輯
(-∞] ... (M->rec,M]
########最後的疑問和總結########
1. 疑問： 為什麼要對M->next-rec 或者 M->pre-rec ？
1. 回答： 因為為了防止幻讀。
insert 操作的加鎖邏輯
RR 隔離級別
- 表結構
dba:lc_3> show create table tb_non_uk;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_non_uk | CREATE TABLE `tb_non_uk` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_id2` (`id_2`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
dba:lc_3> show create table tb_uk;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_uk | CREATE TABLE `tb_uk` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_idx` (`id_2`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
dba:lc_3> select * from tb_non_uk;
+----+------+
| id | id_2 |
+----+------+
| 1 | 100 |
| 2 | 200 |
+----+------+
2 rows in set (0.00 sec)
dba:lc_3> select * from tb_uk;
+----+------+
| id | id_2 |
+----+------+
| 1 | 10 |
| 2 | 20 |
| 33 | 30 |
+----+------+
3 rows in set (0.00 sec)
- 普通的insert,insert之前,其他事務沒有對next-record加任何鎖
dba:lc_3> insert into tb_uk select 100,200;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
鎖的結構：
MySQL thread id 11888, OS thread handle 140000862643968, query id 24975 localhost dba cleaning up
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601936 lock mode IX
沒有加任何的鎖，除了在表上麵加了意向鎖之外，這個鎖基本上隻要訪問到表都會加的
難道insert不會加鎖嗎？顯然不是，那是因為加的是隱式類型的鎖
- 有唯一鍵約束，insert之前，其他事務且對其next-record加了Gap-lock
* session 1:
select * from tb_uk where id_2 >= 30 for update;
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601951 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601951 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 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 4; hex 80000021; asc !;;
RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601951 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000021; asc !;;
1: len 6; hex 000007f69a77; asc w;;
2: len 7; hex ad00000d010110; asc ;;
3: len 4; hex 8000001e; asc ;;
鎖住： (20,30](30,∞) ， 對30有Gap鎖
* session 2:
dba:lc_3> insert into tb_uk select 3,25;
Query OK, 1 row affected (6.30 sec)
Records: 1 Duplicates: 0 Warnings: 0
* session 1:
rollback;
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601952 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601952 lock_mode X locks gap before rec insert intention
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 4; hex 80000021; asc !;;
當session2 插入25的時候，這時候session2 會被卡住。 然後session 2 釋放gap lock後，session 1 就持有插入意向鎖 lock_mode X locks gap before rec insert intention
- 有唯一鍵約束，insert之前，其他事務且對其next-record加了record lock
* session 1:
dba:lc_3> select * from tb_uk where id_2 = 30 for update;
+----+------+
| id | id_2 |
+----+------+
| 33 | 30 |
+----+------+
1 row in set (0.00 sec)
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601943 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 4; hex 80000021; asc !;;
RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000021; asc !;;
1: len 6; hex 000007f69a77; asc w;;
2: len 7; hex ad00000d010110; asc ;;
3: len 4; hex 8000001e; asc ;;
* session 2:
dba:lc_3> insert into tb_uk select 3,25;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
鎖結構：
說明有唯一鍵約束，insert之前，其他事務且對其next-record加了record lock，不會阻塞insert。
此時的insert，也不會產生insert intension lock
- 有唯一鍵約束，insert 記錄之後，發現原來的表有重複值的情況,
* session 1:
dba:lc_3> select * from tb_uk where id_2 = 30 for update;
+----+------+
| id | id_2 |
+----+------+
| 33 | 30 |
+----+------+
1 row in set (0.00 sec)
dba:lc_3> delete from tb_uk where id_2 = 20;
Query OK, 1 row affected (0.00 sec)
這時候的鎖結構如下：
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601943 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000014; asc ;;
1: len 4; hex 80000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 4; hex 80000021; asc !;;
RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000007f69a97; asc ;;
2: len 7; hex 460000403f090b; asc F @? ;;
3: len 4; hex 80000014; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000021; asc !;;
1: len 6; hex 000007f69a77; asc w;;
2: len 7; hex ad00000d010110; asc ;;
3: len 4; hex 8000001e; asc ;;
對二級索引uniq_idx ：
1. 加record lock ， [20]，[30]
對主鍵索引：
1. 加record lock，[2],[33]
* session 2:
dba:lc_3> insert into tb_uk select 3,20;
...............waiting.................
這時候，我們再來看看鎖結構：
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601949 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601949 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000014; asc ;;
1: len 4; hex 80000002; asc ;;
---TRANSACTION 133601943, ACTIVE 490 sec
3 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 11889, OS thread handle 140000878618368, query id 25018 localhost dba cleaning up
TABLE LOCK table `lc_3`.`tb_uk` trx id 133601943 lock mode IX
RECORD LOCKS space id 301 page no 4 n bits 72 index uniq_idx of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000014; asc ;;
1: len 4; hex 80000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000001e; asc ;;
1: len 4; hex 80000021; asc !;;
RECORD LOCKS space id 301 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_uk` trx id 133601943 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000007f69a97; asc ;;
2: len 7; hex 460000403f090b; asc F @? ;;
3: len 4; hex 80000014; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000021; asc !;;
1: len 6; hex 000007f69a77; asc w;;
2: len 7; hex ad00000d010110; asc ;;
3: len 4; hex 8000001e; asc ;;
info bits 32 表示這條記錄已經標記為刪除狀態
這裏麵的session 2 ： insert into tb_uk select 3,20; 被阻塞了
因為，這條insert 語句需要對 uniq_idx中的20加lock mode S ， 但是發現session 1 已經對其加了lock_mode X locks rec but not gap，而這條記錄被標記為刪除狀態
所以發生鎖等待，因為S lock 和 X lock 衝突
- 沒有唯一鍵約束,insert之前，其他事務對其next-record加了Gap-lock
* session 1:
dba:lc_3> select * from tb_non_uk where id_2>=100 for update;
+----+------+
| id | id_2 |
+----+------+
| 1 | 100 |
| 2 | 200 |
+----+------+
2 rows in set (0.00 sec)
鎖結構：
TABLE LOCK table `lc_3`.`tb_non_uk` trx id 133601939 lock mode IX
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601939 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 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800000c8; asc ;;
1: len 4; hex 80000002; asc ;;
RECORD LOCKS space id 302 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_non_uk` trx id 133601939 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000007f69a6b; asc k;;
2: len 7; hex a500000d360110; asc 6 ;;
3: len 4; hex 800000c8; asc ;;
對idx_id2二級索引： (100,200],(200,∞]
對主鍵索引： [2]
* session 2:
dba:lc_3> insert into tb_non_uk select 3,150;
......waiting.....
---TRANSACTION 133601940, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 11888, OS thread handle 140000862643968, query id 24996 localhost dba executing
insert into tb_non_uk select 3,150
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601940 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800000c8; asc ;;
1: len 4; hex 80000002; asc ;;
------------------
TABLE LOCK table `lc_3`.`tb_non_uk` trx id 133601940 lock mode IX
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601940 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800000c8; asc ;;
1: len 4; hex 80000002; asc ;;
---TRANSACTION 133601939, ACTIVE 311 sec
3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 11889, OS thread handle 140000878618368, query id 24994 localhost dba cleaning up
TABLE LOCK table `lc_3`.`tb_non_uk` trx id 133601939 lock mode IX
RECORD LOCKS space id 302 page no 4 n bits 72 index idx_id2 of table `lc_3`.`tb_non_uk` trx id 133601939 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 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800000c8; asc ;;
1: len 4; hex 80000002; asc ;;
RECORD LOCKS space id 302 page no 3 n bits 72 index PRIMARY of table `lc_3`.`tb_non_uk` trx id 133601939 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000007f69a6b; asc k;;
2: len 7; hex a500000d360110; asc 6 ;;
3: len 4; hex 800000c8; asc ;;
鎖結構：
多了一個插入意向鎖 lock_mode X locks gap before rec insert intention
- 總結Insert 操作的加鎖流程
* insert 的流程(沒有唯一索引的情況)： insert N
1. 找到大於N的第一條記錄M
2. 如果M上麵沒有gap ， next-key locking的話，可以插入 ， 否則等待 (對其next-rec加insert intension lock，由於有gap鎖，所以等待)
* insert 的流程(有唯一索引的情況)： insert N
1. 找到大於N的第一條記錄M，以及前一條記錄P
2. 如果M上麵沒有gap ， next-key locking的話，進入第三步驟 ， 否則等待(對其next-rec加insert intension lock，由於有gap鎖，所以等待)
3. 檢查p：
判斷p是否等於n：
如果不等: 則完成插入（結束）
如果相等：
再判斷P 是否有鎖，
如果沒有鎖:
報1062錯誤（duplicate key） --說明該記錄已經存在，報重複值錯誤
加S-lock --說明該記錄被標記為刪除, 事務已經提交，還沒來得及purge
如果有鎖: 則加S-lock --說明該記錄被標記為刪除，事務還未提交.
* insert intension lock 有什麼用呢？鎖的兼容矩陣是啥？
1. insert intension lock 是一種特殊的Gap lock，記住非常特殊哦
2. insert intension lock 和 insert intension lock 是兼容的，其次都是不兼容的
3. Gap lock 是為了防止insert， insert intension lock 是為了insert並發更快，兩者是有區別的
4. 什麼情況下會出發insert intension lock ？
當insert的記錄M的 next-record 加了Gap lock才會發生，record lock並不會觸發
實戰案例
RR 隔離級別
最後來一個比較複雜的案例作為結束
通過這幾個案例，可以複習下之前講過的理論，鎖不僅對主鍵加，還要考慮二級索引哦
- 環境
set tx_isolation = 'repeatable-read';
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
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)
- 加鎖語句
select * from a where c<9 for update;
鎖結構：
TABLE LOCK table `lc_3`.`a` trx id 133601957 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601957 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000001; asc ;;
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 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 ;;
RECORD LOCKS space id 281 page no 3 n bits 72 index PRIMARY of table `lc_3`.`a` trx id 133601957 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 80000001; asc ;;
1: len 6; hex 000007f66444; asc dD;;
2: len 7; hex fc0000271d0110; asc ' ;;
3: len 4; hex 80000003; asc ;;
4: len 4; hex 80000005; asc ;;
5: len 4; hex 80000007; asc ;;
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 ;;
二級索引idx_c 加鎖 next-key lock： (-∞,5],(5,7],(7,9]
primary key 加鎖 record lock： [1]和[3]
- 案例一 insert into a select 4,40,9,90
大家覺得能夠插入成功嗎？
dba:lc_3> insert into a select 4,40,9,90;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
...................waiting.................
顯然是被鎖住了
TABLE LOCK table `lc_3`.`a` trx id 133601961 lock mode IX
RECORD LOCKS space id 281 page no 5 n bits 72 index idx_c of table `lc_3`.`a` trx id 133601961 lock_mode X locks gap before rec insert intention waiting
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 ;;
- 案例二 insert into a select 6,40,9,90;
大家覺得能夠插入成功嗎？
dba:lc_3> insert into a select 6,40,9,90;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
顯然是插入成功了
最後更新：2017-06-21 16:32:58