1. Shared and Exclusive Locks
- Shared Lock(簡稱S Lock，共享鎖): 允許持有鎖的事務(wù)讀取行的操作
- Exclusive Lock(簡稱 X Lock，排他鎖): 允許持有鎖的事務(wù)進(jìn)行更新和刪除行的操作
事務(wù)T1如果持有記錄a的S Lock,此時事務(wù)t2也對記錄a進(jìn)行操作時伺帘，有兩種情況：
- t2請求的是S Lock： t1,t2同時持有記錄a的S Lock
- t2請求的是X Lock: t2會等待t1釋放鎖后代嗤，才能獲取X Lock
事務(wù)t1如果持有是記錄a的X Lock新锈，那么t2不管請求S 還是X Lock,都要等t1釋放鎖后才能去請求字管。
2. Intention Locks
Intention Locks是表級鎖黄橘，它表示之后的事務(wù)需要獲取哪種類型的行鎖(S亥至、X)悼沈。
- Intention Shared Lock(IS Lock): 表示事務(wù)意圖在表中各個行上加一個共享鎖
- Intention Exclusive Lock (IX Lock): 表示事務(wù)意圖在表中各個行上加一個排他鎖
我們可以通過SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE 來獲取IS、IX Lock姐扮。
意圖鎖有以下限制：
- 在事務(wù)可以獲取表中某行的共享鎖之前絮供，它必須首先獲取表上的IS鎖或更強(qiáng)的鎖(IX、S茶敏、X)壤靶。
- 在事務(wù)可以獲取表中某行的獨(dú)占鎖之前，它必須首先獲取表上的IX鎖惊搏。
表級鎖的兼容性如下表：
| \ | X | IX | S | IS |
|---|---|---|---|---|
| X | Conflict | Conflict | Conflict | Conflict |
| IX | Conflict | Compatible | Conflict | Compatible |
| S | Conflict | Conflict | Compatible | Compatible |
| IS | Conflict | Compatible | Compatible | Compatible |
如果事務(wù)請求的鎖和先有鎖兼容贮乳，則獲取到鎖。否則恬惯，事務(wù)會等待向拆，直到現(xiàn)有的鎖被釋放。
2.1 SELECT ... FOR UPDATE
T1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test for update;
+----+------+-------+
| id | name | name2 |
+----+------+-------+
| 1 | aa | aa |
| 2 | aA | aA |
+----+------+-------+
2 rows in set (0.03 sec)
mysql>show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 13049
Purge done for trx's n:o < 12996 undo n:o < 0 state: running but idle
History list length 113
LIST OF TRANSACTIONS FOR EACH SESSION:
...
---TRANSACTION 13048, ACTIVE 122 sec
2 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 19, OS thread handle 12056, query id 367 localhost ::1 root
表里就兩條記錄酪耳，但有三把行鎖 浓恳？
此時事務(wù)T2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test for update;
Lock wait timeout exceeded; try restarting transaction
------------
TRANSACTIONS
------------
Trx id counter 13050
Purge done for trx's n:o < 12996 undo n:o < 0 state: running but idle
History list length 113
LIST OF TRANSACTIONS FOR EACH SESSION:
...
---TRANSACTION 13049, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20, OS thread handle 2708, query id 370 localhost ::1 root Sending data
select * from test for update
------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 153 page no 4 n bits 72 index PRIMARY of table `localtest`.`test` trx id 13049 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 128
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000002e5a; asc .Z;;
2: len 7; hex 010000011a096e; asc n;;
3: len 2; hex 6161; asc aa;;
4: len 2; hex 6161; asc aa;;
------------------
---TRANSACTION 13048, ACTIVE 458 sec
2 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 19, OS thread handle 12056, query id 367 localhost ::1 root
3. Record Locks
Record Lock鎖的是索引。如果表沒有，InnoDB會建一個隱藏的聚簇索引颈将，并用該隱藏索引來鎖定記錄梢夯。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where id=3 for update;
+----+------+-------+
| id | name | name2 |
+----+------+-------+
| 1 | aa | aa |
+----+------+-------+
1 row in set (0.03 sec)
------------
TRANSACTIONS
------------
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 14940, query id 586 localhost ::1 root updating
update test set name='test' where id =3
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 312 page no 4 n bits 96 index PRIMARY of table `localtest`.`test` trx id 18709
lock_mode X locks rec but not gap waiting
Record lock, heap no 19 PHYSICAL RECORD: n_fields 7; compact format; info bits 128
4. Gap Locks
T1開啟一個事務(wù) 并執(zhí)行下面語句
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
| 9 | 9 |
| 10 | 10 |
| 15 | 15 |
| 20 | 20 |
+----+-----+
7 rows in set (0.03 sec)
mysql> select * from test where id between 5 and 10 for update;
+----+-----+
| id | num |
+----+-----+
| 5 | 5 |
| 9 | 9 |
| 10 | 10 |
+----+-----+
3 rows in set (0.03 sec)
T2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test (id,num) values (100,100);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id,num) values (4,4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id,num) values (6,6);
1205 - Lock wait timeout exceeded; try restarting transaction
····
MySQL thread id 20, OS thread handle 13996, query id 856 localhost ::1 root update
insert into test (id,num) values (6,6)
------- TRX HAS BEEN WAITING 42 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 313 page no 4 n bits 96 index PRIMARY
of table `localtest`.`test` trx id 20874 lock_mode X locks gap before rec
insert intention waiting
當(dāng)where xxx=? 條件的xxx不是索引或者非唯一索引，會鎖住前一個間隙
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
| 9 | 9 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| 15 | 15 |
| 20 | 20 |
+----+-----+
9 rows in set (0.03 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where num=15 for update;
+----+-----+
| id | num |
+----+-----+
| 15 | 15 |
+----+-----+
1 row in set (0.03 sec)
T2開啟一個事務(wù)吆鹤，并執(zhí)行下面insert語句厨疙，
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test (id,num) values (17,17);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id,num) values (16,16);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (id,num) values (14,14);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (id,num) values (22,15);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (id,num) values (23,12);
1205 - Lock wait timeout exceeded; try restarting transaction
····
MySQL thread id 20, OS thread handle 13996, query id 765 localhost ::1 root update
insert into test (id,num) values (14,14)
------- TRX HAS BEEN WAITING 41 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 313 page no 6 n bits 88 index num of table `localtest`.`test` trx id 20832
lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2;
compact format; info bits 0
0: len 4; hex 8000000f; asc ;;
1: len 4; hex 8000000f; asc ;;
上面的情況鎖住的區(qū)間是[12,15]
檢索條件必須有索引,沒有索引的話，會鎖定整張表所有的記錄
T1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
| 9 | 9 |
| 10 | 10 |
| 15 | 15 |
| 20 | 20 |
+----+-----+
7 rows in set (0.03 sec)
mysql> select * from test where num=15 for update;
+----+-----+
| id | num |
+----+-----+
| 15 | 15 |
+----+-----+
1 row in set (0.04 sec)
T2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test (id,num) values (4,4);
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (id,num) values (100,100);
1205 - Lock wait timeout exceeded; try restarting transaction
MySQL thread id 20, OS thread handle 13996, query id 834 localhost ::1 root update
insert into test (id,num) values (4,4)
------- TRX HAS BEEN WAITING 24 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 313 page no 4 n bits 96 index PRIMARY of table `localtest`.`test` trx id 20872
lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000005114; asc Q ;;
2: len 7; hex 01000001242299; asc $" ;;
3: len 4; hex 80000005; asc ;;
PS: 如果指定區(qū)間[5,10],沒有值為5和10的記錄疑务。insert 數(shù)據(jù)的時候沾凄，gap locks 會擴(kuò)大到就近的存在記錄的范圍。如擴(kuò)大到[3,15]
5. Insert Intention Locks
不同數(shù)據(jù)插入到相同索引間隙不需要等待知允，互不影響撒蟀。
6. Next-Key Locks
InnoDB在REPEATABLE READ事務(wù)隔離級別下，默認(rèn)開啟温鸽。其實(shí)就是record lock 和gap lock 組合使用保屯。
官方文檔寫的范圍
假設(shè)索引包含值10,11,13和20,
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
