按粒度

MySQL支持不同級(jí)別的鎖，其鎖定的數(shù)據(jù)的范圍也不同，也即我們常說(shuō)的鎖的粒度。MySQL有三種鎖級(jí)別：行級(jí)鎖、頁(yè)級(jí)鎖、表級(jí)鎖。不同的存儲(chǔ)引擎支持不同的鎖粒度，例如MyISAM和MEMORY存儲(chǔ)引擎采用的是表級(jí)鎖，頁(yè)級(jí)鎖僅被BDB存儲(chǔ)引擎支持，InnoDB存儲(chǔ)引擎支持行級(jí)鎖和表級(jí)鎖，默認(rèn)情況下是采用行級(jí)鎖。

特點(diǎn)

表級(jí)鎖：開(kāi)銷(xiāo)小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。數(shù)據(jù)庫(kù)引擎總是一次性同時(shí)獲取所有需要的鎖以及總是按相同的順序獲取表鎖從而避免死鎖。
行級(jí)鎖：開(kāi)銷(xiāo)大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。行鎖總是逐步獲得的，因此會(huì)出現(xiàn)死鎖。
頁(yè)面鎖：開(kāi)銷(xiāo)和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

下面詳細(xì)介紹行鎖和表鎖，頁(yè)鎖由于使用得較少就不介紹了。

行鎖

按行對(duì)數(shù)據(jù)進(jìn)行加鎖。InnoDB行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，Innodb一定存在聚簇索引，行鎖最終都會(huì)落到聚簇索引上，通過(guò)非聚簇索引查詢的時(shí)候，先鎖非聚簇索引，然后再鎖聚簇索引。如果一個(gè)where語(yǔ)句里面既有聚簇索引，又有二級(jí)索引，則會(huì)先鎖聚簇索引，再鎖二級(jí)索引。由于是分步加鎖的，因此可能會(huì)有死鎖發(fā)生。

MySQL的行鎖對(duì)S、X鎖上做了一些更精確的細(xì)分，使得行鎖的粒度更細(xì)小，可以減少?zèng)_突，這就是被稱為“precise mode”的兼容矩陣。（該矩陣沒(méi)有出現(xiàn)在官方文檔上，是有人通過(guò)Mysql lock0lock.c:lock_rec_has_to_wait源代碼推測(cè)出來(lái)的。）

行鎖兼容矩陣

• 間隙鎖(Gap Lock)：只鎖間隙，前開(kāi)后開(kāi)區(qū)間(a,b)，對(duì)索引的間隙加鎖，防止其他事務(wù)插入數(shù)據(jù)。
• 記錄鎖(Record Lock)：只鎖記錄，特定幾行記錄。
• 臨鍵鎖(Next-Key Lock)：同時(shí)鎖住記錄和間隙，前開(kāi)后閉區(qū)間(a,b]。
• 插入意圖鎖(Insert Intention Lock)：插入時(shí)使用的鎖。在代碼中，插入意圖鎖，實(shí)際上是GAP鎖上加了一個(gè)LOCK_INSERT_INTENTION的標(biāo)記。

S鎖和S鎖是完全兼容的，因此在判別兼容性時(shí)不需要對(duì)比精確模式。精確模式的檢測(cè)，用在S、X和X、X之間。從這個(gè)矩陣可以看到幾個(gè)特點(diǎn)：

• INSERT操作之間不會(huì)有沖突：你插入你的，我插入我的。
• GAP，Next-Key會(huì)阻止Insert：插入的數(shù)據(jù)正好在區(qū)間內(nèi)，不允許插入。
• GAP和Record，Next-Key不會(huì)沖突
• Record和Record、Next-Key之間相互沖突。
• 已有的Insert鎖不阻止任何準(zhǔn)備加的鎖。
• 間隙鎖（無(wú)論是S還是X）只會(huì)阻塞insert操作。
  

注意點(diǎn)

• 對(duì)于記錄鎖，列必須是唯一索引列或者主鍵列，查詢語(yǔ)句必須為精確匹配，如“=”，否則記錄鎖會(huì)退化為臨鍵鎖。
• 間隙鎖和臨鍵鎖基于非唯一索引，在唯一索引列上不存在間隙鎖和臨鍵鎖。
  

表鎖與鎖表的誤區(qū)

只有正確通過(guò)索引條件檢索數(shù)據(jù)（沒(méi)有索引失效的情況），InnoDB才會(huì)使用行級(jí)鎖，否則InnoDB對(duì)表中的所有記錄加鎖，也就是將鎖住整個(gè)表。注意，這里說(shuō)的是鎖住整個(gè)表，但是Innodb并不是使用表鎖來(lái)鎖住表的，而是使用了下面介紹的Next-Key Lock來(lái)鎖住整個(gè)表。網(wǎng)上很多的說(shuō)法都是說(shuō)用表鎖，然而實(shí)際上并不是，我們可以通過(guò)下面的例子來(lái)看看。

假設(shè)我們有以下的數(shù)據(jù)(MySQL8)：

mysql> select * from users;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | a  | 1  |
| 2 | a  | 1  |
| 3 | a  | 1  |
| 4 | a  | 1  |
| 5 | a  | 1  |
+----+------+-----+

方法一：

我們使用表鎖鎖表，并查看引擎的狀態(tài)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables users write;
Query OK, 0 rows affected (0.00 sec)

mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 4863
Purge done for trx's n:o  4862 undo n:o  0 state: running but idle
History list length 911
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479760456232, not started
mysql tables in use 1, locked 1  ###############注意這里
0 lock struct(s), heap size 1136, 0 row lock(s)
...

然后我們?cè)偻ㄟ^(guò)非索引的字段查詢來(lái)加鎖，并查看引擎的狀態(tài)

## 先解鎖上次的表鎖
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where name = 'a' for update;

mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 4864
Purge done for trx's n:o  4862 undo n:o  0 state: running but idle
History list length 911
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4863, ACTIVE 37 sec
2 lock struct(s), heap size 1136, 6 row lock(s)  ###############注意這里
...

然后我們?cè)賱h除id為2，3，4的數(shù)據(jù)，然后在通過(guò)非索引的字段查詢來(lái)加鎖，并查看引擎的狀態(tài)

mysql> delete from users where id in (2,3,4);
Query OK, 3 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where name = 'a' for update;

mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 4870
Purge done for trx's n:o  4869 undo n:o  0 state: running but idle
History list length 914
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4869, ACTIVE 9 sec
2 lock struct(s), heap size 1136, 3 row lock(s)  ###############注意這里
...

可以看到這里使用了表鎖和因?yàn)闆](méi)法用索引鎖定特定行而轉(zhuǎn)而鎖住整個(gè)表是不一樣的。從第二次和第三次的操作來(lái)看，lock住的row也是不同的，這是因?yàn)閮烧唛g隙的個(gè)數(shù)不同，所以可以看到使用的并不是表鎖，而是Next-Key Lock。第一次鎖住了(-∞,1],(1,2],(2,3],(3,4],(4,5],(5,∞]，第二次鎖住了(-∞,1],(1,5],(5,∞]。

方法二：

也可以通過(guò)以下語(yǔ)句來(lái)查看鎖的信息，也可以知道用的是行鎖，且是鎖住了區(qū)間（插入不了數(shù)據(jù)）和記錄，所以是Next-Key Lock。

mysql> select ENGINE_TRANSACTION_ID,LOCK_TYPE,LOCK_MODE from performance_schema.data_locks where ENGINE_TRANSACTION_ID in (你的事務(wù)id);
+-----------------------+-----------+-----------+
| ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE |
+-----------------------+-----------+-----------+
|         4889 | TABLE   | IX    |
|         4889 | RECORD  | X     |
|         4889 | RECORD  | X     |
|         4889 | RECORD  | X     |
+-----------------------+-----------+-----------+
10 rows in set (0.00 sec)

LOCK_TYPE：對(duì)于InnoDB，可選值為 RECORD(行鎖)， TABLE(表鎖)

LOCK_MODE：對(duì)于InnoDB，可選值為S[,GAP], X[,GAP], IS[,GAP],IX[,GAP], AUTO_INC和UNKNOWN。除了AUTO_INC和UNKNOWN，其他鎖定模式都包含了GAP鎖（如果存在）。

具體可見(jiàn) MySQL文檔：https://dev.mysql.com/doc/ref...

表級(jí)鎖

直接對(duì)整個(gè)表加鎖，影響表中所有記錄，表讀鎖和表寫(xiě)鎖的兼容性見(jiàn)上面的分析。

MySQL中除了表讀鎖和表寫(xiě)鎖之外，還存在一種特殊的表鎖：意向鎖，這是為了解決不同粒度的鎖的兼容性判斷而存在的。

意向鎖

因?yàn)殒i的粒度不同，表鎖的范圍覆蓋了行鎖的范圍，所以表鎖和行鎖會(huì)產(chǎn)生沖突，例如事務(wù)A對(duì)表中某一行數(shù)據(jù)加了行鎖，然后事務(wù)B想加表鎖，正常來(lái)說(shuō)是應(yīng)該要沖突的。如果只有行鎖的話，要判斷是否沖突就得遍歷每一行數(shù)據(jù)了，這樣的效率實(shí)在不高，因此我們就有了意向表鎖。

意向鎖的主要目的是為了使得 行鎖 和 表鎖 共存，事務(wù)在申請(qǐng)行鎖前，必須先申請(qǐng)表的意向鎖，成功后再申請(qǐng)行鎖。注意：申請(qǐng)意向鎖的動(dòng)作是數(shù)據(jù)庫(kù)完成的，不需要開(kāi)發(fā)者來(lái)申請(qǐng)。

意向鎖是表級(jí)鎖，但是卻表示事務(wù)正在讀或?qū)懩骋恍杏涗洠皇钦麄€(gè)表， 所以意向鎖之間不會(huì)產(chǎn)生沖突，真正的沖突在加行鎖時(shí)檢查。

意向鎖分為意向讀鎖(IS)和意向?qū)戞i(IX)。

表鎖的兼容性矩陣

以上就是MySQL 鎖的相關(guān)知識(shí)總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于MySQL 鎖的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！