1 認識鎖機制

在認識鎖機制前，首先思考一個問題:在同一時刻，使用者A和使用者B同時要擷取并修改sh_goods表中id等于2的stock庫存量值，此時會發生什麼呢?

假設在初始情況下，sh_ goods表中id等于2的stock庫存量值為500。

在不添加鎖的前提下，使用者A關閉自動送出，将stock的值修改為300，然後查詢目前stock值為300(修改但未送出);與此同時使用者B也擷取stock，它的值卻為500。當使用者A送出了修改後，使用者B擷取到的值又變為300。整個操作過程出現了兩個大的問題，一是使用者B第1次查詢stock字段的值與使用者A不同，二是使用者B前後兩次讀取的stock值不同，進而産生了使用者并發操作時資料不一緻的情況。

解決辦法就是，在使用者A和使用者B同時向sh_goods表送出請求操作時，根據系統内部設定的操作優先級(擷取資料優先或修改資料優先的原則)，鎖住指定使用者(如A)要操作的資源(sh_goods 表)，同時讓另外一個使用者(如B)排隊等候，直到鎖定資源的使用者(如A)操作完成，并釋放鎖後，再讓另一個使用者(如B)對資源進行操作。其中，對資源加鎖的方式，可以采用修改事務隔離級别(前面章節已講)的方式實作。

簡單地說，鎖機制就是為了保證多使用者并發操作時，能使被操作的資料資源保持一緻性的設計規則。又因MySQL資料庫自身設計的特點，利用多種存儲引擎處理不同特定的應用場景，是以鎖機制在不同存儲引擎中的表現也有一-定的差別。

根據存儲引擎的不同，MySQL中常見的鎖有兩種，分别為表級鎖(如MyISAM、MEMORY存儲引擎)和行級鎖(如InnoDB存儲引擎)。另外InnoDB存儲引擎中還含有表級鎖，具體内容會在後面的小節詳細講解，此處了解即可。

表級鎖是MySQL中鎖的作用範圍(鎖的粒度)最大的一種鎖，它鎖定的是使用者操作資源所在的整個資料表，有效地避免了死鎖的發生，且具有加鎖速度快、消耗資源小的特點。

正所謂事物都有兩面性，表級鎖的優勢同樣給它帶來了一定的缺陷，因其鎖定的粒度大，在并發操作時發生鎖沖突的機率也大。

行級鎖是MySQL中鎖的作用範圍最小的一種鎖，它僅鎖定使用者操作所涉及的記錄資源，有效地減少了鎖定資源競争的發生，具有較高處理并發操作的能力，提升系統的整體性MySQL資料庫原理、設計與應用能。但同時也因其鎖定的粒度過小，每次加鎖和解鎖所消耗的資源也會更多，發生死鎖的可能性更高。

另外，根據鎖在MySQL中的狀态也可将其分為“隐式”與“顯式”。所謂“隐式”鎖指的是MySQL伺服器本身對資料資源的争用進行管理，它完全由伺服器自動執行。而“顯式”鎖指的是使用者根據實際需求，對操作的資料顯式地添加鎖，同樣在使用完資料資源後也需要使用者對其進行解鎖。

小提示:在了解死鎖前，首先要了解什麼是鎖等待。所謂鎖等待指的是一個使用者(線程)等待其他使用者(線程)釋放鎖的過程。而死鎖可以簡單地了解為兩個或多個使用者(線程)在互相等待對方釋放鎖而出現的一種“僵持”狀态，若無外力作用，它們将永遠處于鎖等待的狀态，此時就可以說系統産生了死鎖或處于死鎖狀态。

2 表級鎖

在實際應用中，表級鎖根據操作的不同可以分為讀鎖和寫鎖。讀鎖表示使用者讀取(如SELECT查詢)資料資源時添加的鎖，此時其他使用者雖然不可以修改或增加資料資源，但是可以讀取該資料資源，是以讀鎖也可以稱為共享鎖;而寫鎖表示使用者對資料資源執行寫(如INSERT，UPDATE.DELETE等)操作時添加的鎖，此時除了目前添加寫鎖的使用者外，其他使用者都不能對其進行讀/寫操作，是以寫鎖也可以稱為排他鎖或獨占鎖。

MyISAM存儲引擎表是MySQL資料庫中最典型的表級鎖，下面就以此存儲引擎的表級鎖為例詳細講解“隐式”讀/寫的表級鎖和“顯式”讀/寫表級鎖的添加。

1.“隐式”讀/寫的表級鎖

當使用者對MyISAM存儲引擎表執行SELECT查詢操作前，伺服器會“自動”地為其添加一個表級的讀鎖，執行INSERT.UPDATE.DELETE等寫操作前，伺服器會“自動”地為其添加一個表級的寫鎖;直到查詢完畢，伺服器再“自動”地為其解鎖。執行時間可以看作是“隐式”表級鎖讀/寫的生命周期，且該生命周期的持續時間一般都比較短暫。

預設情況下，伺服器在“自動”添加“隐式”鎖時，表的更新操作優先級高于表的查詢操作。在添加寫鎖時，若表中沒有任何鎖則添加，否則将其插人到寫鎖等待的隊列中;在添加讀鎖時，若表中沒有寫鎖則添加，否則将其插人到讀鎖等待的隊列中。

2.“顯式”讀/寫的表級鎖

在實際應用中，可以根據開發需求，對要操作的資料表進行“顯式”地添加表級鎖。其基本文法格式如下。

LOCK TABLES 資料表名 READ [LOCAL]| WRITE, …

在上述文法中，LOCKTABLES可以同時鎖定多張資料表。READ表示表級的讀鎖，添加此鎖的使用者可以讀取該表但不能對此表進行寫操作，否則系統會報錯;此時其他使用者可以讀取此表，若執行對此表的寫操作則會進入等待隊列。WRITE 表示表級的寫鎖，添加此鎖的使用者可以對該表進行讀/寫操作，在釋放鎖之前，不允許其他使用者通路與操作。

需要注意的是，在為MyISAM存儲引擎表設定“顯式”讀鎖時，若添加LOCAL關鍵字，則在不發生鎖沖突的情況下，未添加此鎖的其他使用者可以在表的末尾實作并發插人資料的功能。

此外，對于表級鎖來說，雖然鎖本身消耗的資源很少，但是鎖定的粒度卻很大，當多個使用者通路時，會造成鎖資源的競争，降低了并發處理的能力。是以，從資料庫優化的角度來考慮，應該盡量減少表級鎖定的時間，進而提高多使用者的并發能力。此時，對于使用者添加的“顯式”表級鎖，需要使用MySQL提供的UNLOCKTABLES語句釋放鎖。

值得一提的是，使用者設定的“顯式”表級鎖僅在目前會話内有效，若會話期間内未釋放鎖，在會話結束後也會自動釋放。

為了讀者更好地了解，下面通過一個具體的案例進行示範。具體步驟如下。

(1)建立MyISAM表并插人2條測試資料。

mysql> CREATE TABLE mydb.table_lock(id int)ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO mydb.table_lock VALUES(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

(2)設定“顯式”讀的表級鎖。

打開兩個用戶端A和B，在用戶端A中為mydb.table_lock設定“顯式”讀的表級鎖後，然後分别在用戶端A和用戶端B中執行SELECT和UPDATE操作。具體SQL語句及執行結果如下。

# ① 在用戶端A中添加表級讀鎖
mysql> LOCK TABLE mydb.table_lock READ;
Query OK, 0 rows affected (0.00 sec)
# ② 在用戶端A中執行SELECT和UPDATE操作
mysql> SELECT * FROM mydb.table_lock \G
*************************** 1. row ***************************
id: 1
*************************** 2. row ***************************
id: 2
2 rows in set (0.00 sec)
mysql> UPDATE mydb.table_lock SET id = 3 WHERE id = 1;
ERROR 1099 (HY000): Table 'table_lock' was locked with a READ lock and can't be updated
mysql> SELECT * FROM mydb.mt \G
ERROR 1100 (HY000): Table 'mt' was not locked with LOCK TABLES
# ③ 在用戶端B中執行SELECT和UPDATE操作
mysql> SELECT * FROM mydb.table_lock \G
*************************** 1. row ***************************
id: 1
*************************** 2. row ***************************
id: 2
2 rows in set (0.00 sec)
mysql> UPDATE mydb.table_lock SET id = 3 WHERE id = 1;
# 此處光标會不停閃爍，進入鎖等待狀态
# ④ 在用戶端A中釋放鎖
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
# ⑤ 用戶端B在用戶端A釋放鎖後，會立即執行③中等待的寫鎖
mysql> UPDATE mydb.table_lock SET id=3 WHERE id=1;
Query OK, 1 row affected (5.64 sec)
Rows matched: 1  Changed: 1  Warnings: 0

從以上的操作可以看出，添加表級讀鎖的用戶端A僅能對mydb.table_lock執行讀取操作，不能執行寫操作，也不能操作其他未鎖定的資料表，如mydb.mt。對于未添加鎖的用戶端B則可以執行SELECT操作，但是執行UPDATE操作則會進入鎖等待狀态，隻有用戶端A結束會話或執行UNLOCK TABLES釋放鎖時，用戶端B的操作才會被執行。具體SQL語句及執行結果如下。

# ① 在用戶端A中添加表級讀鎖
mysql> LOCK TABLE mydb.table_lock READ LOCAL;
Query OK, 0 rows affected (0.00 sec)
# ② 在用戶端B中，插入一條記錄
mysql> INSERT INTO mydb.table_lock VALUES(4);
Query OK, 1 row affected (0.00 sec)

(3)并發插人操作。

在MyISAM存儲引擎的資料表中，還支援并發插入操作，用于減少讀操作與寫操作對表的競争情況。實作文法為LOCK… READ LOCAL，具體SQL語句及執行結果如下。

# ① 在用戶端A中添加表級讀鎖
mysql> LOCK TABLE mydb.table_lock READ LOCAL;
Query OK, 0 rows affected (0.00 sec)
# ② 在用戶端B中，插入一條記錄
mysql> INSERT INTO mydb.table_lock VALUES(4);
Query OK, 1 row affected (0.00 sec)

從上述執行結果可知，即使用戶端A中已添加了表級讀鎖，在未釋放此讀鎖時，在用戶端B中依然可以實作資料插人操作，此操作也稱為并發插人。

需要注意的是，并發插人的資料不能是DELETE操作删除的記錄，并且隻能在表中最後的一行記錄後繼續增加新記錄。

(4)設定“顯式”寫的表級鎖。

# ① 在用戶端A中添加表級寫鎖
mysql> LOCK TABLE mydb.table_lock WRITE;
Query OK, 0 rows affected (0.00 sec)
# ② 在用戶端A中執行更新和查詢操作
mysql> UPDATE mydb.table_lock SET id = 1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM mydb.table_lock \G
*************************** 1. row ***************************
id: 3
*************************** 2. row ***************************
id: 1
*************************** 3. row ***************************
id: 4
3 rows in set (0.00 sec)
# ③ 在用戶端B中執行查詢操作
mysql> SELECT * FROM mydb.table_lock;
# 此處光标會不停閃爍，進入鎖等待狀态

從上述操作可以看出，添加了寫鎖的使用者，可以執行讀/寫操作(如增删改查)，而其他使用者不論執行任何操作(如SELECT)，都隻能處于等待狀态，直到寫鎖被釋放，才能夠執行。

3 行級鎖

InnoDB存儲引擎的鎖機制相對于MyISAM存儲引擎的鎖複雜一些，原因在于它既有表級鎖又有行級鎖。其中，InnoDB表級鎖的應用與MyISAM表級鎖的相同，這裡不再贅述。那麼InnoDB存儲引擎的表什麼時候添加表級鎖，什麼時候添加行級鎖呢?隻有通過索引條件檢索的資料InnoDB存儲引擎才會使用行級鎖，否則将使用表級鎖。

InnoDB的行級鎖根據操作的不同也分為共享鎖和排他鎖。為了讀者更好地了解，下面以“隐式”的行級鎖和“顯式”的行級鎖為例進行詳細講解。

1.“隐式”行級鎖

當使用者對InnoDB存儲引擎表執行INSERT.UPDATE.DELETE等寫操作前，伺服器會“自動”地為通過索引條件檢索的記錄添加行級排他鎖;直到操作語句執行完畢，伺服器再“自動”地為其解鎖。

而語句的執行時間可以看作是“隐式”行級鎖的生命周期，且該生命周期的持續時間一般都比較短暫。通常情況下，若要增加行級鎖的生命周期，最常使用的方式是事務處理，讓其在事務送出或復原後再釋放行級鎖，使行級鎖的生命周期與事務的相同。

為了讀者更好地了解，下面在事務中示範“隐式”行級鎖的使用。具體步驟如下。

(1)建立InnoDB表并插人測試資料。

mysql> CREATE TABLE mydb.row_lock (
    ->   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ->   name VARCHAR(60) NOT NULL,
    ->   cid INT UNSIGNED,
    ->   KEY cid (cid)
    -> )DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO mydb.row_lock (name, cid) VALUES ('鉛筆', 3),
    -> ('風扇', 6), ('綠蘿', 1), ('書包', 9), ('紙巾', 20);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(2)設定“隐式”行級鎖。

打開兩個用戶端A和B，在用戶端A中為mydb.row_lock設定“隐式”行級的排他鎖後，然後在用戶端B中執行SELECT和DELETE操作。具體SQL語句及執行結果如下。

# ① 在用戶端A中，修改cid等于3的name值
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE mydb.row_lock SET name = 'cc' WHERE cid = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# ② 在用戶端B中，删除cid等于2和3的記錄
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM mydb.row_lock WHERE cid = 2;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM mydb.row_lock WHERE cid = 3;
# 此處光标會不停閃爍，進入鎖等待狀态
# ③ 在用戶端A和B中，復原以上的操作
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

從以上執行結果可知，一個用戶端對InnoDB表執行UPDATE操作時，對符合索引條件的記錄會隐式地添加一個行級鎖，與此同時其他使用者不能再執行寫操作，但可以操作不符合索引條件的記錄(如删除cid等于2的記錄)。

2.“顯式"行級鎖

對于InnoDB表來說，若要保證目前事務中查詢出的資料不會被其他事務更新或删除，利用普通的SELECT語句是無法辦到的，此時需要利用MySQL提供的“鎖定讀取”的方式為查詢操作顯式地添加行級鎖。其基本文法格式如下。

在上述文法中，隻需在正常的SELECT語句後添加FOR UPDATE或LOCK IN SHARE MODE即可實作“鎖定讀取”，前者表示在查詢時添加行級排他鎖，後者表示在查詢時添加行級共享鎖。

使用者在向InnoDB表顯式添加行級鎖時，InnoDB存儲引擎首先會“自動”地向此表添加一個意向鎖，然後再添加行級鎖。此意向鎖是一個隐式的表級鎖，多個意向鎖之間不會産生沖突且互相相容。意向鎖是由MySQL伺服器根據行級鎖是共享鎖還是排他鎖，自動添加意向共享鎖或意向排他鎖，不能人為幹預。

意向鎖的作用就是辨別表中的某些記錄正在被鎖定或其他使用者将要鎖定表中的某些記錄。相對行級鎖，意向鎖的鎖定粒度更大，用于在行級鎖中添加表級鎖時判斷它們之間是否能夠互相相容。好處就是大大節約了存儲引擎對鎖處理的性能，更加友善地解決了行級鎖與表級鎖之間的沖突。

為了讀者更好地了解，下面通過一個表格展示表級的共享/排他鎖與意向共享/排他鎖之間的相容性關系，具體如表所示。

表表級共享/排他鎖與意向共享/排他鎖之間的關系

表級共享鎖	表級排他鎖	意向共享鎖	意向排他鎖
表級共享鎖	相容	沖突	相容	沖突
表級排他鎖	沖突	沖突	沖突	沖突
意向共享鎖	相容	沖突	相容	相容
意向排他鎖	沖突	沖突	相容	相容

需要注意的是，InnoDB表中目前使用者的意向鎖若與其他使用者要添加的表級鎖沖突時，有可能會發生死鎖而産生錯誤。

接下來利用上面建立的mydb.row_lock表，示範添加行級排他鎖時用戶端A和用戶端B執行SQL語句的狀态，具體步驟如下。

# ① 在用戶端A中，為cid等于3的記錄添加行級排他鎖
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM mydb.row_lock WHERE cid = 3 FOR UPDATE;
+----+------+------+
| id | name | cid  |
+----+------+------+
|  1 | 鉛筆  |    3 |
+----+------+------+
1 row in set (0.00 sec)
# ② 在用戶端B中，為cid等于2的記錄添加隐式行級排他鎖，設定表級排他鎖
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE mydb.row_lock SET name = 'lili' WHERE cid = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql> LOCK TABLE mydb.row_lock READ;
# 此處光标會不停閃爍，進入鎖等待狀态
# ③ 復原以上的操作并釋放表級鎖

從以上的執行結果可知，在用戶端A中為cid等于3的記錄添加行級排他鎖後，在用戶端B中，可以為除cid等于3外的記錄添加行級排他鎖(如cid等于2的隐式排他鎖)，但是在為表添加表級共享鎖時會發生沖突，進行鎖等待狀态。

此外，預設的情況下，當InnoDB處于REPEATABLE READ(可重複讀)的隔離級别時，行級鎖實際上是一個next-key鎖，它是由間隙鎖(gaplock)和記錄鎖(recordlock)組成的。其中，記錄鎖(recordlock)就是前面講解的行鎖;間隙鎖指的是在記錄索引之間的間隙、負無窮到第1個索引記錄之間或最後1個索引記錄到正無窮之間添加的鎖，它的作用就是在并發時防止其他事務在間隙插入記錄，解決了事務幻讀的問題。

為了讀者更好地了解，下面為mydb.row_lock表添加行鎖，檢視間隙鎖是否存在。具體步驟如下。

# ① 在用戶端A中，為cid等于3的記錄添加行鎖
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM mydb.row_lock WHERE cid=3 FOR UPDATE;
+----+------+------+
| id | name | cid  |
+----+------+------+
|  1 | 鉛筆  |    3 |
+----+------+------+
1 row in set (0.00 sec)
# ② 在用戶端B中，插入cid等于1、2、5、6的記錄
mysql> INSERT INTO mydb.row_lock(name, cid) VALUES('電視', 1);
# 此處光标會不停閃爍，進入鎖等待狀态
mysql> INSERT INTO mydb.row_lock(name, cid) VALUES('電視', 2);
# 此處光标會不停閃爍，進入鎖等待狀态
mysql> INSERT INTO mydb.row_lock(name, cid) VALUES('電視', 5);
# 此處光标會不停閃爍，進入鎖等待狀态
mysql> INSERT INTO mydb.row_lock(name, cid) VALUES('電視', 6);
Query OK, 1 row affected (0.00 sec)

在上述操作中，用戶端A在cid等于3的記錄中添加了行鎖，理論上其他使用者在并發時可以插入除cid等于3的任意記錄，但是因為間隙鎖的存在，伺服器也會鎖定目前表中cid(值分别為1、3、6、9、20)值為3的記錄左右的間隙，間隙的區間範圍為[1 ，3)和[3，6)。

值得一提的是，在執行SELECT-FOR UPDATE時，若檢索時未使用索引，則InnoDB存儲引擎會給全表添加一個表級鎖，并發時不允許其他使用者進行插人。另外，若查詢條件使用的是單字段的唯一性索引，InnoDB存儲引擎的行級鎖不會設定間隙鎖。

間隙鎖的使用雖然解決了事務幻讀的情況，但是也會造成行鎖定的範圍變大，若在開發時想要禁止間隙鎖的使用，可以将事務的隔離級别更改為READ COMMITTED(讀取送出)。

多學一招:檢視InnoDB表的鎖

InnoDB存儲引擎的鎖比較複雜，讀者可以在添加一個行鎖後，使用SHOW ENGINE INNODB

STATUS語句檢視目前表中添加的鎖的類型。另外，在檢視時要保證開啟系統變量innodb_status_output_locks

才能擷取鎖定的資訊。例如，檢視mydb.row_lock 表添加的鎖，部分資訊如下。

TABLE LOCK table `mydb`.`row_lock` trx id 10386 lock mode IX RECORD LOCKS space id 247 page no 4 n bits 80 index cid of table `mydb`.`row_lock` trx id 10386 lock_mode X 
 ︙（此處省略部分内容）
  RECORD LOCKS space id 247 page no 3 n bits 80 index PRIMARY of table `mydb`.`row_lock` trx id 10386 lock_mode X locks rec but not gap
 ︙（此處省略部分内容）  
 RECORD LOCKS space id 247 page no 4 n bits 80 index cid of table ``mydb`.`row_lock` trx id 10386 lock_mode X locks gap before rec
 ︙（此處省略部分内容）

在上述資訊中，“IX”表示mydb. row_ lock 中添加了一個意向排他鎖，“X”表示next-key lock的排他鎖，“X

locks rec but not gap”表示記錄鎖，“X locks gap before rec”表示間隙鎖。

它們之間的關系為“IX”在“X"之前添加，而“X”是由“X locks rec but not gap" 和“X locks gap

before rec”組成的。

超全面的測試IT技術課程，0元立即加入學習！有需要的朋友戳：

騰訊課堂測試技術學習位址

歡迎轉載，但未經作者同意請保留此段聲明，并在文章頁面明顯位置給出原文連結。

MySQL資料庫——鎖機制1 認識鎖機制2 表級鎖3 行級鎖

1 認識鎖機制

2 表級鎖

3 行級鎖

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述