InnoDB鎖

1. Shared and Exclusive Locks

  1. Shared Lock(簡(jiǎn)稱(chēng)S Lock，共享鎖): 允許持有鎖的事務(wù)讀取行的操作
  2. Exclusive Lock(簡(jiǎn)稱(chēng) X Lock，排他鎖): 允許持有鎖的事務(wù)進(jìn)行更新和刪除行的操作

事務(wù)T1如果持有記錄a的S Lock,此時(shí)事務(wù)t2也對(duì)記錄a進(jìn)行操作時(shí)，有兩種情況：

  • t2請(qǐng)求的是S Lock： t1,t2同時(shí)持有記錄a的S Lock
  • t2請(qǐng)求的是X Lock: t2會(huì)等待t1釋放鎖后，才能獲取X Lock

事務(wù)t1如果持有是記錄a的X Lock，那么t2不管請(qǐng)求S 還是X Lock,都要等t1釋放鎖后才能去請(qǐng)求。

2. Intention Locks

Intention Locks是表級(jí)鎖，它表示之后的事務(wù)需要獲取哪種類(lèi)型的行鎖(S、X)。

  • Intention Shared Lock(IS Lock): 表示事務(wù)意圖在表中各個(gè)行上加一個(gè)共享鎖
  • Intention Exclusive Lock (IX Lock): 表示事務(wù)意圖在表中各個(gè)行上加一個(gè)排他鎖

我們可以通過(guò)SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE 來(lái)獲取IS、IX Lock。

意圖鎖有以下限制：

  • 在事務(wù)可以獲取表中某行的共享鎖之前，它必須首先獲取表上的IS鎖或更強(qiáng)的鎖(IX、S、X)。
  • 在事務(wù)可以獲取表中某行的獨(dú)占鎖之前，它必須首先獲取表上的IX鎖。

表級(jí)鎖的兼容性如下表：

\ 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ù)請(qǐng)求的鎖和先有鎖兼容，則獲取到鎖。否則，事務(wù)會(huì)等待，直到現(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

表里就兩條記錄，但有三把行鎖 ？
此時(shí)事務(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鎖的是索引。如果表沒(méi)有，InnoDB會(huì)建一個(gè)隱藏的聚簇索引，并用該隱藏索引來(lái)鎖定記錄。

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開(kāi)啟一個(gè)事務(wù) 并執(zhí)行下面語(yǔ)句

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不是索引或者非唯一索引，會(huì)鎖住前一個(gè)間隙

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開(kāi)啟一個(gè)事務(wù)，并執(zhí)行下面insert語(yǔ)句，

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]

檢索條件必須有索引,沒(méi)有索引的話，會(huì)鎖定整張表所有的記錄
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],沒(méi)有值為5和10的記錄。insert 數(shù)據(jù)的時(shí)候，gap locks 會(huì)擴(kuò)大到就近的存在記錄的范圍。如擴(kuò)大到[3,15]

5. Insert Intention Locks

不同數(shù)據(jù)插入到相同索引間隙不需要等待，互不影響。

6. Next-Key Locks

InnoDB在REPEATABLE READ事務(wù)隔離級(jí)別下，默認(rèn)開(kāi)啟。其實(shí)就是record lock 和gap lock 組合使用。
官方文檔寫(xiě)的范圍

假設(shè)索引包含值10,11,13和20,

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 原文地址：https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/in...
    翼徳閱讀 2,563評(píng)論 0贊 4
  • 1. 鎖類(lèi)型 鎖是數(shù)據(jù)庫(kù)區(qū)別與文件系統(tǒng)的一個(gè)關(guān)鍵特性，鎖機(jī)制用于管理對(duì)共享資源的并發(fā)訪問(wèn)。InnoDB使用的鎖類(lèi)型...
    butterfly100閱讀 1,232評(píng)論 0贊 2
  • 1）自增鎖（AUTO-INC Locks），表級(jí)鎖用于AUTO_INCREMENT的自增主鍵，MySQL 8.0....
    哈密朵閱讀 3,017評(píng)論 5贊 4
  • InnoDB Locking This section describes lock types used by ...
    誓言的夢(mèng)閱讀 515評(píng)論 0贊 0
  • 所謂“修身”，便是求學(xué)問(wèn)。只有學(xué)到了知識(shí)才能明白自己志向，知識(shí)就是力量；知識(shí)可以改變自己的命運(yùn)，自己變得強(qiáng)大了才有...
    裴佳霞閱讀 661評(píng)論 2贊 0

友情鏈接更多精彩內(nèi)容