MySQL鎖(表鎖,行鎖,共享鎖,排它鎖,間隙鎖)使用詳解

2024-07-25 19:09:07

字體：大中小

供稿：網(wǎng)友

鎖，在現(xiàn)實生活中是為我們想要隱藏于外界所使用的一種工具。在計算機中，是協(xié)調(diào)多個進程或縣城并發(fā)訪問某一資源的一種機制。在數(shù)據(jù)庫當(dāng)中，除了傳統(tǒng)的計算資源（CPU、RAM、I/O等等）的爭用之外，數(shù)據(jù)也是一種供許多用戶共享訪問的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性，是所有數(shù)據(jù)庫必須解決的一個問題，鎖的沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。從這一角度來說，鎖對于數(shù)據(jù)庫而言就顯得尤為重要。

MySQL鎖

相對于其他的數(shù)據(jù)庫而言，MySQL的鎖機制比較簡單，最顯著的特點就是不同的存儲引擎支持不同的鎖機制。根據(jù)不同的存儲引擎，MySQL中鎖的特性可以大致歸納如下：

	行鎖	表鎖	頁鎖
MyISAM		√
BDB		√	√
InnoDB	√	√

開銷、加鎖速度、死鎖、粒度、并發(fā)性能

表鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定力度大，發(fā)生鎖沖突概率高，并發(fā)度最低
行鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度小，發(fā)生鎖沖突的概率低，并發(fā)度高
頁鎖：開銷和加鎖速度介于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間，并發(fā)度一般

從上述的特點課件，很難籠統(tǒng)的說哪種鎖最好，只能根據(jù)具體應(yīng)用的特點來說哪種鎖更加合適。僅僅從鎖的角度來說的話：

表鎖更適用于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用；行鎖更適用于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時又有并發(fā)查詢的應(yīng)用。（PS：由于BDB已經(jīng)被InnoDB所取代，我們只討論MyISAM表鎖和InnoDB行鎖的問題）

MyISAM表鎖

MyISAM存儲引擎只支持表鎖，這也是MySQL開始幾個版本中唯一支持的鎖類型。隨著應(yīng)用對事務(wù)完整性和并發(fā)性要求的不斷提高，MySQL才開始開發(fā)基于事務(wù)的存儲引擎，后來慢慢出現(xiàn)了支持頁鎖的BDB存儲引擎和支持行鎖的InnoDB存儲引擎（實際 InnoDB是單獨的一個公司，現(xiàn)在已經(jīng)被Oracle公司收購）。但是MyISAM的表鎖依然是使用最為廣泛的鎖類型。本節(jié)將詳細介紹MyISAM表鎖的使用。

查詢表級鎖爭用情況

可以通過檢查table_locks_waited和table_locks_immediate狀態(tài)變量來分析系統(tǒng)上的表鎖定爭奪：

mysql> show status like 'table%';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Table_locks_immediate | 2979 || Table_locks_waited | 0 |+-----------------------+-------+2 rows in set (0.00 sec))

如果Table_locks_waited的值比較高，則說明存在著較嚴重的表級鎖爭用情況。

MySQL表級鎖的鎖模式

MySQL的表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。鎖模式的兼容性如下表所示。

MySQL中的表鎖兼容性

請求鎖模式

是否兼容

當(dāng)前鎖模式

None

讀鎖

寫鎖

讀鎖

是

否

寫鎖

是

否

可見，對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對 MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的！根據(jù)如下表所示的例子可以知道，當(dāng)一個線程獲得對一個表的寫鎖后，只有持有鎖的線程可以對表進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。

MyISAM存儲引擎的寫阻塞讀例子

session_1 session_2

獲得表film_text的WRITE鎖定

mysql> lock table film_text write;
Query OK, 0 rows affected (0.00 sec)

當(dāng)前session對鎖定表的查詢、更新、插入操作都可以執(zhí)行：

mysql> select film_id,title from film_text where film_id = 1001;
+---------+-------------+
| film_id | title |
+---------+-------------+
| 1001 | Update Test |
+---------+-------------+
1 row in set (0.00 sec)
mysql> insert into film_text (film_id,title) values(1003,'Test');
Query OK, 1 row affected (0.00 sec)
mysql> update film_text set style="margin: 0px; padding: 0px; outline: none; line-height: 25.2px; font-size: 14px; width: 660px; overflow: hidden; clear: both; font-family: tahoma, arial, "Microsoft YaHei";">

Select sum(total) from orders;Select sum(subtotal) from order_detail;

這時，如果不先給兩個表加鎖，就可能產(chǎn)生錯誤的結(jié)果，因為第一條語句執(zhí)行過程中，order_detail表可能已經(jīng)發(fā)生了改變。因此，正確的方法應(yīng)該是：

Lock tables orders read local, order_detail read local;Select sum(total) from orders;Select sum(subtotal) from order_detail;Unlock tables;

要特別說明以下兩點內(nèi)容。

上面的例子在LOCK TABLES時加了“local”選項，其作用就是在滿足MyISAM表并發(fā)插入條件的情況下，允許其他用戶在表尾并發(fā)插入記錄，有關(guān)MyISAM表的并發(fā)插入問題，在后面的章節(jié)中還會進一步介紹。
在用LOCK TABLES給表顯式加表鎖時，必須同時取得所有涉及到表的鎖，并且MySQL不支持鎖升級。也就是說，在執(zhí)行LOCK TABLES后，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那么只能執(zhí)行查詢操作，而不能執(zhí)行更新操作。其實，在自動加鎖的情況下也基本如此，MyISAM總是一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現(xiàn)死鎖（Deadlock Free）的原因。

在如下表所示的例子中，一個session使用LOCK TABLE命令給表film_text加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或訪問其他表都會提示錯誤；同時，另外一個session可以查詢表中的記錄，但更新就會出現(xiàn)鎖等待。

MyISAM存儲引擎的讀阻塞寫例子

session_1 session_2

獲得表film_text的READ鎖定

mysql> lock table film_text write;
Query OK, 0 rows affected (0.00 sec)

當(dāng)前session可以查詢該表記錄

其他session也可以查詢該表的記錄

當(dāng)前session不能查詢沒有鎖定的表

mysql> select film_id,title from film where film_id = 1001;
ERROR 1100 (HY000): Table 'film' was not locked with LOCK TABLES

其他session可以查詢或者更新未鎖定的表

mysql> select film_id,title from film where film_id = 1001;
+---------+---------------+
| film_id | title |
+---------+---------------+
| 1001 | update record |
+---------+---------------+
1 row in set (0.00 sec)
mysql> update film set style="margin: 0px; padding: 0px; outline: none; line-height: 25.2px; font-size: 14px; width: 660px; overflow: hidden; clear: both; font-family: tahoma, arial, "Microsoft YaHei";">

mysql> lock table actor read;Query OK, 0 rows affected (0.00 sec)

（2）但是通過別名訪問會提示錯誤：

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES

（3）需要對別名分別鎖定：

mysql> lock table actor as a read,actor as b read;Query OK, 0 rows affected (0.00 sec)

（4）按照別名的查詢可以正確執(zhí)行：

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;+------------+-----------+------------+-----------+| first_name | last_name | first_name | last_name |+------------+-----------+------------+-----------+| Lisa | Tom | LISA | MONROE |+------------+-----------+------------+-----------+1 row in set (0.00 sec)

并發(fā)插入（Concurrent Inserts）

上文提到過MyISAM表的讀和寫是串行的，但這是就總體而言的。在一定條件下，MyISAM表也支持查詢和插入操作的并發(fā)進行。

MyISAM存儲引擎有一個系統(tǒng)變量concurrent_insert，專門用以控制其并發(fā)插入的行為，其值分別可以為0、1或2。

當(dāng)concurrent_insert設(shè)置為0時，不允許并發(fā)插入。
當(dāng)concurrent_insert設(shè)置為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的默認設(shè)置。
當(dāng)concurrent_insert設(shè)置為2時，無論MyISAM表中有沒有空洞，都允許在表尾并發(fā)插入記錄。

在如下表所示的例子中，session_1獲得了一個表的READ LOCAL鎖，該線程可以對表進行查詢操作，但不能對表進行更新操作；其他的線程（session_2），雖然不能對表進行刪除和更新操作，但卻可以對該表進行并發(fā)插入操作，這里假設(shè)該表中間不存在空洞。

MyISAM存儲引擎的讀寫（INSERT）并發(fā)例子

session_1 session_2

獲得表film_text的READ LOCAL鎖定

mysql> lock table film_text read local;
Query OK, 0 rows affected (0.00 sec)

當(dāng)前session不能對鎖定表進行更新或者插入操作：

mysql> insert into film_text (film_id,title) values(1002,'Test');
ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated
mysql> update film_text set style="margin: 0px 0px 0px 25px; padding: 0.3em 0px; outline: none; list-style: decimal; line-height: 25px; word-break: break-word; font-family: tahoma, arial, "Microsoft YaHei"; font-size: 14px;">

通過指定啟動參數(shù)low-priority-updates，使MyISAM引擎默認給予讀請求以優(yōu)先的權(quán)利。

通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1，使該連接發(fā)出的更新請求優(yōu)先級降低。

通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優(yōu)先級。

雖然上面3種方法都是要么更新優(yōu)先，要么查詢優(yōu)先的方法，但還是可以用其來解決查詢相對重要的應(yīng)用（如用戶登錄系統(tǒng)）中，讀鎖等待嚴重的問題。

另外，MySQL也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突，即給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個合適的值，當(dāng)一個表的讀鎖達到這個值后，MySQL就暫時將寫請求的優(yōu)先級降低，給讀進程一定獲得鎖的機會。

上面已經(jīng)討論了寫優(yōu)先調(diào)度機制帶來的問題和解決辦法。這里還要強調(diào)一點：一些需要長時間運行的查詢操作，也會使寫進程“餓死”！因此，應(yīng)用中應(yīng)盡量避免出現(xiàn)長時間運行的查詢操作，不要總想用一條SELECT語句來解決問題，因為這種看似巧妙的SQL語句，往往比較復(fù)雜，執(zhí)行時間較長，在可能的情況下可以通過使用中間表等措施對SQL語句做一定的“分解”，使每一步查詢都能在較短時間完成，從而減少鎖沖突。如果復(fù)雜查詢不可避免，應(yīng)盡量安排在數(shù)據(jù)庫空閑時段執(zhí)行，比如一些定期統(tǒng)計可以安排在夜間執(zhí)行。

InnoDB鎖問題

InnoDB與MyISAM的最大不同有兩點：一是支持事務(wù)（TRANSACTION）；二是采用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，另外，事務(wù)的引入也帶來了一些新問題。下面我們先介紹一點背景知識，然后詳細討論InnoDB的鎖問題。

背景知識

1．事務(wù)（Transaction）及其ACID屬性

事務(wù)是由一組SQL語句組成的邏輯處理單元，事務(wù)具有以下4個屬性，通常簡稱為事務(wù)的ACID屬性。

原子性（Atomicity）：事務(wù)是一個原子操作單元，其對數(shù)據(jù)的修改，要么全都執(zhí)行，要么全都不執(zhí)行。
一致性（Consistent）：在事務(wù)開始和完成時，數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持數(shù)據(jù)的完整性；事務(wù)結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如B樹索引或雙向鏈表）也都必須是正確的。
隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機制，保證事務(wù)在不受外部并發(fā)操作影響的“獨立”環(huán)境執(zhí)行。這意味著事務(wù)處理過程中的中間狀態(tài)對外部是不可見的，反之亦然。
持久性（Durable）：事務(wù)完成之后，它對于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。

銀行轉(zhuǎn)帳就是事務(wù)的一個典型例子。

2．并發(fā)事務(wù)處理帶來的問題

相對于串行處理來說，并發(fā)事務(wù)處理能大大增加數(shù)據(jù)庫資源的利用率，提高數(shù)據(jù)庫系統(tǒng)的事務(wù)吞吐量，從而可以支持更多的用戶。但并發(fā)事務(wù)處理也會帶來一些問題，主要包括以下幾種情況。

更新丟失（Lost Update）：當(dāng)兩個或多個事務(wù)選擇同一行，然后基于最初選定的值更新該行時，由于每個事務(wù)都不知道其他事務(wù)的存在，就會發(fā)生丟失更新問題－－最后的更新覆蓋了由其他事務(wù)所做的更新。例如，兩個編輯人員制作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。最后保存其更改副本的編輯人員覆蓋另一個編輯人員所做的更改。如果在一個編輯人員完成并提交事務(wù)之前，另一個編輯人員不能訪問同一文件，則可避免此問題。
臟讀（Dirty Reads）：一個事務(wù)正在對一條記錄做修改，在這個事務(wù)完成并提交前，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)；這時，另一個事務(wù)也來讀取同一條記錄，如果不加控制，第二個事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此做進一步的處理，就會產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象地叫做"臟讀"。
不可重復(fù)讀（Non-Repeatable Reads）：一個事務(wù)在讀取某些數(shù)據(jù)后的某個時間，再次讀取以前讀過的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了！這種現(xiàn)象就叫做“不可重復(fù)讀”。
幻讀（Phantom Reads）：一個事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀”。

3．事務(wù)隔離級別

在上面講到的并發(fā)事務(wù)處理帶來的問題中，“更新丟失”通常是應(yīng)該完全避免的。但防止更新丟失，并不能單靠數(shù)據(jù)庫事務(wù)控制器來解決，需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來解決，因此，防止更新丟失應(yīng)該是應(yīng)用的責(zé)任。

“臟讀”、“不可重復(fù)讀”和“幻讀”，其實都是數(shù)據(jù)庫讀一致性問題，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機制來解決。數(shù)據(jù)庫實現(xiàn)事務(wù)隔離的方式，基本上可分為以下兩種。

一種是在讀取數(shù)據(jù)前，對其加鎖，阻止其他事務(wù)對數(shù)據(jù)進行修改。
另一種是不用加任何鎖，通過一定機制生成一個數(shù)據(jù)請求時間點的一致性數(shù)據(jù)快照（Snapshot)，并用這個快照來提供一定級別（語句級或事務(wù)級）的一致性讀取。從用戶的角度來看，好像是數(shù)據(jù)庫可以提供同一數(shù)據(jù)的多個版本，因此，這種技術(shù)叫做數(shù)據(jù)多版本并發(fā)控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也經(jīng)常稱為多版本數(shù)據(jù)庫。

數(shù)據(jù)庫的事務(wù)隔離越嚴格，并發(fā)副作用越小，但付出的代價也就越大，因為事務(wù)隔離實質(zhì)上就是使事務(wù)在一定程度上 “串行化”進行，這顯然與“并發(fā)”是矛盾的。同時，不同的應(yīng)用對讀一致性和事務(wù)隔離程度的要求也是不同的，比如許多應(yīng)用對“不可重復(fù)讀”和“幻讀”并不敏感，可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力。

為了解決“隔離”與“并發(fā)”的矛盾，ISO/ANSI SQL92定義了4個事務(wù)隔離級別，每個級別的隔離程度不同，允許出現(xiàn)的副作用也不同，應(yīng)用可以根據(jù)自己的業(yè)務(wù)邏輯要求，通過選擇不同的隔離級別來平衡 “隔離”與“并發(fā)”的矛盾。下表很好地概括了這4個隔離級別的特性。

4種隔離級別比較

讀數(shù)據(jù)一致性及允許的并發(fā)副作用隔離級別	讀數(shù)據(jù)一致性	臟讀	不可重復(fù)讀	幻讀
未提交讀（Read uncommitted）	最低級別，只能保證不讀取物理上損壞的數(shù)據(jù)	是	是	是
已提交度（Read committed）	語句級	否	是	是
可重復(fù)讀（Repeatable read）	事務(wù)級	否	否	是
可序列化（Serializable）	最高級別，事務(wù)級	否	否	否

最后要說明的是：各具體數(shù)據(jù)庫并不一定完全實現(xiàn)了上述4個隔離級別，例如，Oracle只提供Read committed和Serializable兩個標(biāo)準(zhǔn)隔離級別，另外還提供自己定義的Read only隔離級別；SQL Server除支持上述ISO/ANSI SQL92定義的4個隔離級別外，還支持一個叫做“快照”的隔離級別，但嚴格來說它是一個用MVCC實現(xiàn)的Serializable隔離級別。MySQL 支持全部4個隔離級別，但在具體實現(xiàn)時，有一些特點，比如在一些隔離級別下是采用MVCC一致性讀，但某些情況下又不是，這些內(nèi)容在后面的章節(jié)中將會做進一步介紹。

獲取InnoDB行鎖爭用情況

可以通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況：

mysql> show status like 'innodb_row_lock%';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| InnoDB_row_lock_current_waits | 0 || InnoDB_row_lock_time | 0 || InnoDB_row_lock_time_avg | 0 || InnoDB_row_lock_time_max | 0 || InnoDB_row_lock_waits | 0 |+-------------------------------+-------+5 rows in set (0.01 sec)

如果發(fā)現(xiàn)鎖爭用比較嚴重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高，還可以通過設(shè)置InnoDB Monitors來進一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等，并分析鎖爭用的原因。

具體方法如下：

mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;Query OK, 0 rows affected (0.14 sec)

然后就可以用下面的語句來進行查看：

mysql> Show innodb status/G;*************************** 1. row ***************************Type: InnoDBName:Status:……------------TRANSACTIONS------------Trx id counter 0 117472192Purge done for trx's n:o < 0 117472190 undo n:o < 0 0History list length 17Total number of lock structs in row lock hash table 0LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0 117472185, not started, process no 11052, OS thread id 1158191456MySQL thread id 200610, query id 291197 localhost root---TRANSACTION 0 117472183, not started, process no 11052, OS thread id 1158723936MySQL thread id 199285, query id 291199 localhost rootShow innodb status…

監(jiān)視器可以通過發(fā)出下列語句來停止查看：

mysql> DROP TABLE innodb_monitor;Query OK, 0 rows affected (0.05 sec)

設(shè)置監(jiān)視器后，在SHOW INNODB STATUS的顯示內(nèi)容中，會有詳細的當(dāng)前鎖等待的信息，包括表名、鎖類型、鎖定記錄的情況等，便于進行進一步的分析和問題的確定。打開監(jiān)視器以后，默認情況下每15秒會向日志中記錄監(jiān)控的內(nèi)容，如果長時間打開會導(dǎo)致.err文件變得非常的巨大，所以用戶在確認問題原因之后，要記得刪除監(jiān)控表以關(guān)閉監(jiān)視器，或者通過使用“--console”選項來啟動服務(wù)器以關(guān)閉寫日志文件。

InnoDB的行鎖模式及加鎖方法

InnoDB實現(xiàn)了以下兩種類型的行鎖。

共享鎖（S）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
排他鎖（X)：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。另外，為了允許行鎖和表鎖共存，實現(xiàn)多粒度鎖機制，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖，事務(wù)在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

上述鎖模式的兼容情況具體如下表所示。

InnoDB行鎖模式兼容性列表

請求鎖模式是否兼容當(dāng)前鎖模式	X	IX	S	IS
X	沖突	沖突	沖突	沖突
IX	沖突	兼容	沖突	兼容
S	沖突	沖突	兼容	兼容
IS	沖突	兼容	兼容	兼容

如果一個事務(wù)請求的鎖模式與當(dāng)前的鎖兼容，InnoDB就將請求的鎖授予該事務(wù)；反之，如果兩者不兼容，該事務(wù)就要等待鎖釋放。

意向鎖是InnoDB自動加的，不需用戶干預(yù)。對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X)；對于普通SELECT語句，InnoDB不會加任何鎖；事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排他鎖。

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE獲得共享鎖，主要用在需要數(shù)據(jù)依存關(guān)系時來確認某行記錄是否存在，并確保沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果當(dāng)前事務(wù)也需要對該記錄進行更新操作，則很有可能造成死鎖，對于鎖定行記錄后需要進行更新操作的應(yīng)用，應(yīng)該使用SELECT... FOR UPDATE方式獲得排他鎖。

在如下表所示的例子中，使用了SELECT ... IN SHARE MODE加鎖后再更新記錄，看看會出現(xiàn)什么情況，其中actor表的actor_id字段為主鍵。

InnoDB存儲引擎的共享鎖例子

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ \| actor_id \| first_name \| last_name \| +----------+------------+-----------+ \| 178 \| LISA \| MONROE \| +----------+------------+-----------+ 1 row in set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ \| actor_id \| first_name \| last_name \| +----------+------------+-----------+ \| 178 \| LISA \| MONROE \| +----------+------------+-----------+ 1 row in set (0.00 sec)
當(dāng)前session對actor_id=178的記錄加share mode 的共享鎖： mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode; +----------+------------+-----------+ \| actor_id \| first_name \| last_name \| +----------+------------+-----------+ \| 178 \| LISA \| MONROE \| +----------+------------+-----------+ 1 row in set (0.01 sec)
	其他session仍然可以查詢記錄，并也可以對該記錄加share mode的共享鎖： mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode; +----------+------------+-----------+ \| actor_id \| first_name \| last_name \| +----------+------------+-----------+ \| 178 \| LISA \| MONROE \| +----------+------------+-----------+ 1 row in set (0.01 sec)
當(dāng)前session對鎖定的記錄進行更新操作，等待鎖： mysql> update actor set last_name = 'MONROE T' where actor_id = 178; 等待
	其他session也對該記錄進行更新操作，則會導(dǎo)致死鎖退出： mysql> update actor set last_name = 'MONROE T' where actor_id = 178; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
獲得鎖后，可以成功更新： mysql> update actor set last_name = 'MONROE T' where actor_id = 178; Query OK, 1 row affected (17.67 sec) Rows matched: 1 Changed: 1 Warnings: 0

當(dāng)使用SELECT...FOR UPDATE加鎖后再更新記錄，出現(xiàn)如下表所示的情況。

InnoDB存儲引擎的排他鎖例子

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ \| actor_id \| first_name \| last_name \| +----------+------------+-----------+ \| 178 \| LISA \| MONROE \| +----------+------------+-----------+ 1 row in set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ \| actor_id \| first_name \| last_name \| +----------+------------+-----------+ \| 178 \| LISA \| MONROE \| +----------+------------+-----------+ 1 row in set (0.00 sec)
當(dāng)前session對actor_id=178的記錄加for update的排它鎖： mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update; +----------+------------+-----------+ \| actor_id \| first_name \| last_name \| +----------+------------+-----------+ \| 178 \| LISA \| MONROE \| +----------+------------+-----------+ 1 row in set (0.00 sec)
	其他session可以查詢該記錄，但是不能對該記錄加共享鎖，會等待獲得鎖： mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ \| actor_id \| first_name \| last_name \| +----------+------------+-----------+ \| 178 \| LISA \| MONROE \| +----------+------------+-----------+ 1 row in set (0.00 sec) mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update; 等待
當(dāng)前session可以對鎖定的記錄進行更新操作，更新后釋放鎖： mysql> update actor set last_name = 'MONROE T' where actor_id = 178; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.01 sec)
	其他session獲得鎖，得到其他session提交的記錄： mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update; +----------+------------+-----------+ \| actor_id \| first_name \| last_name \| +----------+------------+-----------+ \| 178 \| LISA \| MONROE T \| +----------+------------+-----------+ 1 row in set (9.59 sec)

InnoDB行鎖實現(xiàn)方式

InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，這一點MySQL與Oracle不同，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的。InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

在實際應(yīng)用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。下面通過一些實際例子來加以說明。

（1）在不通過索引條件查詢的時候，InnoDB確實使用的是表鎖，而不是行鎖。

在如下所示的例子中，開始tab_no_index表沒有索引：

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;Query OK, 0 rows affected (0.15 sec)mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0

InnoDB存儲引擎的表在不使用索引時使用表鎖例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id = 1 ; +------+------+ \| id \| name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id = 2 ; +------+------+ \| id \| name \| +------+------+ \| 2 \| 2 \| +------+------+ 1 row in set (0.00 sec)
mysql> select * from tab_no_index where id = 1 for update; +------+------+ \| id \| name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)
	mysql> select * from tab_no_index where id = 2 for update; 等待

在如上表所示的例子中，看起來session_1只給一行加了排他鎖，但session_2在請求其他行的排他鎖時，卻出現(xiàn)了鎖等待！原因就是在沒有索引的情況下，InnoDB只能使用表鎖。當(dāng)我們給其增加一個索引后，InnoDB就只鎖定了符合條件的行，如下表所示。

創(chuàng)建tab_with_index表，id字段有普通索引：

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;Query OK, 0 rows affected (0.15 sec)mysql> alter table tab_with_index add index id(id);Query OK, 4 rows affected (0.24 sec)Records: 4 Duplicates: 0 Warnings: 0

InnoDB存儲引擎的表在使用索引時使用行鎖例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_with_index where id = 1 ; +------+------+ \| id \| name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_with_index where id = 2 ; +------+------+ \| id \| name \| +------+------+ \| 2 \| 2 \| +------+------+ 1 row in set (0.00 sec)
mysql> select * from tab_with_index where id = 1 for update; +------+------+ \| id \| name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)
	mysql> select * from tab_with_index where id = 2 for update; +------+------+ \| id \| name \| +------+------+ \| 2 \| 2 \| +------+------+ 1 row in set (0.00 sec)

（2）由于MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現(xiàn)鎖沖突的。應(yīng)用設(shè)計的時候要注意這一點。

在如下表所示的例子中，表tab_with_index的id字段有索引，name字段沒有索引：

mysql> alter table tab_with_index drop index name;Query OK, 4 rows affected (0.22 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> insert into tab_with_index values(1,'4');Query OK, 1 row affected (0.00 sec)mysql> select * from tab_with_index where id = 1;+------+------+| id | name |+------+------+| 1 | 1 || 1 | 4 |+------+------+2 rows in set (0.00 sec)

InnoDB存儲引擎使用相同索引鍵的阻塞例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 and name = '1' for update; +------+------+ \| id \| name \| +------+------+ \| 1 \| 1 \| +------+------+ 1 row in set (0.00 sec)
	雖然session_2訪問的是和session_1不同的記錄，但是因為使用了相同的索引，所以需要等待鎖： mysql> select * from tab_with_index where id = 1 and name = '4' for update; 等待

（3）當(dāng)表有多個索引的時候，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數(shù)據(jù)加鎖。

在如下表所示的例子中，表tab_with_index的id字段有主鍵索引，name字段有普通索引：

mysql> alter table tab_with_index add index name(name);Query OK, 5 rows affected (0.23 sec)Records: 5 Duplicates: 0 Warnings: 0

InnoDB存儲引擎的表使用不同索引的阻塞例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 for update; +------+------+ \| id \| name \| +------+------+ \| 1 \| 1 \| \| 1 \| 4 \| +------+------+ 2 rows in set (0.00 sec)
	Session_2使用name的索引訪問記錄，因為記錄沒有被索引，所以可以獲得鎖： mysql> select * from tab_with_index where name = '2' for update; +------+------+ \| id \| name \| +------+------+ \| 2 \| 2 \| +------+------+ 1 row in set (0.00 sec)
	由于訪問的記錄已經(jīng)被session_1鎖定，所以等待獲得鎖。： mysql> select * from tab_with_index where name = '4' for update;

（4）即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由MySQL通過判斷不同執(zhí)行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖沖突時，別忘了檢查SQL的執(zhí)行計劃，以確認是否真正使用了索引。

在下面的例子中，檢索值的數(shù)據(jù)類型與索引字段不同，雖然MySQL能夠進行數(shù)據(jù)類型轉(zhuǎn)換，但卻不會使用索引，從而導(dǎo)致InnoDB使用表鎖。通過用explain檢查兩條SQL的執(zhí)行計劃，我們可以清楚地看到了這一點。

例子中tab_with_index表的name字段有索引，但是name字段是varchar類型的，如果where條件中不是和varchar類型進行比較，則會對name進行類型轉(zhuǎn)換，而執(zhí)行的全表掃描。

mysql> alter table tab_no_index add index name(name);Query OK, 4 rows affected (8.06 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> explain select * from tab_with_index where name = 1 /G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tab_with_indextype: ALLpossible_keys: namekey: NULLkey_len: NULLref: NULLrows: 4Extra: Using where1 row in set (0.00 sec)mysql> explain select * from tab_with_index where name = '1' /G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: tab_with_indextype: refpossible_keys: namekey: namekey_len: 23ref: constrows: 1Extra: Using where1 row in set (0.00 sec)

間隙鎖（Next-Key鎖）

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。

舉例來說，假如emp表中只有101條記錄，其empid的值分別是 1,2,...,100,101，下面的SQL：

Select * from emp where empid > 100 for update;

是一個范圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大于101（這些記錄并不存在）的“間隙”加鎖。

InnoDB使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關(guān)隔離級別的要求，對于上面的例子，要是不使用間隙鎖，如果其他事務(wù)插入了empid大于100的任何記錄，那么本事務(wù)如果再次執(zhí)行上述語句，就會發(fā)生幻讀；另外一方面，是為了滿足其恢復(fù)和復(fù)制的需要。有關(guān)其恢復(fù)和復(fù)制對鎖機制的影響，以及不同隔離級別下InnoDB使用間隙鎖的情況，在后續(xù)的章節(jié)中會做進一步介紹。

很顯然，在使用范圍條件檢索并鎖定記錄時，InnoDB這種加鎖機制會阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入，這往往會造成嚴重的鎖等待。因此，在實際應(yīng)用開發(fā)中，尤其是并發(fā)插入比較多的應(yīng)用，我們要盡量優(yōu)化業(yè)務(wù)邏輯，盡量使用相等條件來訪問更新數(shù)據(jù)，避免使用范圍條件。

還要特別說明的是，InnoDB除了通過范圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！

在如下表所示的例子中，假如emp表中只有101條記錄，其empid的值分別是1,2,......,100,101。

InnoDB存儲引擎的間隙鎖阻塞例子

session_1	session_2
mysql> select @@tx_isolation; +-----------------+ \| @@tx_isolation \| +-----------------+ \| REPEATABLE-READ \| +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> select @@tx_isolation; +-----------------+ \| @@tx_isolation \| +-----------------+ \| REPEATABLE-READ \| +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
當(dāng)前session對不存在的記錄加for update的鎖： mysql> select * from emp where empid = 102 for update; Empty set (0.00 sec)
	這時，如果其他session插入empid為102的記錄（注意：這條記錄并不存在），也會出現(xiàn)鎖等待： mysql>insert into emp(empid,...) values(102,...); 阻塞等待
Session_1 執(zhí)行rollback： mysql> rollback; Query OK, 0 rows affected (13.04 sec)
	由于其他session_1回退后釋放了Next-Key鎖，當(dāng)前session可以獲得鎖并成功插入記錄： mysql>insert into emp(empid,...) values(102,...); Query OK, 1 row affected (13.35 sec)

恢復(fù)和復(fù)制的需要，對InnoDB鎖機制的影響

MySQL通過BINLOG錄執(zhí)行成功的INSERT、UPDATE、DELETE等更新數(shù)據(jù)的SQL語句，并由此實現(xiàn)MySQL數(shù)據(jù)庫的恢復(fù)和主從復(fù)制（可以參見本書“管理篇”的介紹）。MySQL的恢復(fù)機制（復(fù)制其實就是在Slave Mysql不斷做基于BINLOG的恢復(fù)）有以下特點。

l 一是MySQL的恢復(fù)是SQL語句級的，也就是重新執(zhí)行BINLOG中的SQL語句。這與Oracle數(shù)據(jù)庫不同，Oracle是基于數(shù)據(jù)庫文件塊的。

l 二是MySQL的Binlog是按照事務(wù)提交的先后順序記錄的，恢復(fù)也是按這個順序進行的。這點也與Oralce不同，Oracle是按照系統(tǒng)更新號（System Change Number，SCN）來恢復(fù)數(shù)據(jù)的，每個事務(wù)開始時，Oracle都會分配一個全局唯一的SCN，SCN的順序與事務(wù)開始的時間順序是一致的。

從上面兩點可知，MySQL的恢復(fù)機制要求：在一個事務(wù)未提交前，其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄，也就是不允許出現(xiàn)幻讀，這已經(jīng)超過了ISO/ANSI SQL92“可重復(fù)讀”隔離級別的要求，實際上是要求事務(wù)要串行化。這也是許多情況下，InnoDB要用到間隙鎖的原因，比如在用范圍條件更新記錄時，無論在Read Commited或是Repeatable Read隔離級別下，InnoDB都要使用間隙鎖，但這并不是隔離級別要求的，有關(guān)InnoDB在不同隔離級別下加鎖的差異在下一小節(jié)還會介紹。

另外，對于“insert into target_tab select * from source_tab where ...”和“create table new_tab ...select ... From source_tab where ...(CTAS)”這種SQL語句，用戶并沒有對source_tab做任何更新操作，但MySQL對這種SQL語句做了特別處理。先來看如下表的例子。

CTAS操作給原表加鎖例子

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ \| d1 \| name \| d2 \| +----+------+----+ \| 4 \| 1 \| 1 \| \| 5 \| 1 \| 1 \| \| 6 \| 1 \| 1 \| \| 7 \| 1 \| 1 \| \| 8 \| 1 \| 1 \| +----+------+----+ 5 rows in set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ \| d1 \| name \| d2 \| +----+------+----+ \| 4 \| 1 \| 1 \| \| 5 \| 1 \| 1 \| \| 6 \| 1 \| 1 \| \| 7 \| 1 \| 1 \| \| 8 \| 1 \| 1 \| +----+------+----+ 5 rows in set (0.00 sec)
mysql> insert into target_tab select d1,name from source_tab where name = '1'; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0
	mysql> update source_tab set name = '1' where name = '8'; 等待
commit;
	返回結(jié)果 commit;

在上面的例子中，只是簡單地讀 source_tab表的數(shù)據(jù)，相當(dāng)于執(zhí)行一個普通的SELECT語句，用一致性讀就可以了。ORACLE正是這么做的，它通過MVCC技術(shù)實現(xiàn)的多版本數(shù)據(jù)來實現(xiàn)一致性讀，不需要給source_tab加任何鎖。我們知道InnoDB也實現(xiàn)了多版本數(shù)據(jù)，對普通的SELECT一致性讀，也不需要加任何鎖；但這里InnoDB卻給source_tab加了共享鎖，并沒有使用多版本數(shù)據(jù)一致性讀技術(shù)！

MySQL為什么要這么做呢？其原因還是為了保證恢復(fù)和復(fù)制的正確性。因為不加鎖的話，如果在上述語句執(zhí)行過程中，其他事務(wù)對source_tab做了更新操作，就可能導(dǎo)致數(shù)據(jù)恢復(fù)的結(jié)果錯誤。為了演示這一點，我們再重復(fù)一下前面的例子，不同的是在session_1執(zhí)行事務(wù)前，先將系統(tǒng)變量 innodb_locks_unsafe_for_binlog的值設(shè)置為“on”（其默認值為off），具體結(jié)果如下表所示。

CTAS操作不給原表加鎖帶來的安全問題例子

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql>set innodb_locks_unsafe_for_binlog='on' Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ \| d1 \| name \| d2 \| +----+------+----+ \| 4 \| 1 \| 1 \| \| 5 \| 1 \| 1 \| \| 6 \| 1 \| 1 \| \| 7 \| 1 \| 1 \| \| 8 \| 1 \| 1 \| +----+------+----+ 5 rows in set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = '1'; +----+------+----+ \| d1 \| name \| d2 \| +----+------+----+ \| 4 \| 1 \| 1 \| \| 5 \| 1 \| 1 \| \| 6 \| 1 \| 1 \| \| 7 \| 1 \| 1 \| \| 8 \| 1 \| 1 \| +----+------+----+ 5 rows in set (0.00 sec)
mysql> insert into target_tab select d1,name from source_tab where name = '1'; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0
	session_1未提交，可以對session_1的select的記錄進行更新操作。 mysql> update source_tab set name = '8' where name = '1'; Query OK, 5 rows affected (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from source_tab where name = '8'; +----+------+----+ \| d1 \| name \| d2 \| +----+------+----+ \| 4 \| 8 \| 1 \| \| 5 \| 8 \| 1 \| \| 6 \| 8 \| 1 \| \| 7 \| 8 \| 1 \| \| 8 \| 8 \| 1 \| +----+------+----+ 5 rows in set (0.00 sec)
	更新操作先提交 mysql> commit; Query OK, 0 rows affected (0.05 sec)
插入操作后提交 mysql> commit; Query OK, 0 rows affected (0.07 sec)
此時查看數(shù)據(jù)，target_tab中可以插入source_tab更新前的結(jié)果，這符合應(yīng)用邏輯： mysql> select * from source_tab where name = '8'; +----+------+----+ \| d1 \| name \| d2 \| +----+------+----+ \| 4 \| 8 \| 1 \| \| 5 \| 8 \| 1 \| \| 6 \| 8 \| 1 \| \| 7 \| 8 \| 1 \| \| 8 \| 8 \| 1 \| +----+------+----+ 5 rows in set (0.00 sec) mysql> select * from target_tab; +------+------+ \| id \| name \| +------+------+ \| 4 \| 1.00 \| \| 5 \| 1.00 \| \| 6 \| 1.00 \| \| 7 \| 1.00 \| \| 8 \| 1.00 \| +------+------+ 5 rows in set (0.00 sec)	mysql> select * from tt1 where name = '1'; Empty set (0.00 sec) mysql> select * from source_tab where name = '8'; +----+------+----+ \| d1 \| name \| d2 \| +----+------+----+ \| 4 \| 8 \| 1 \| \| 5 \| 8 \| 1 \| \| 6 \| 8 \| 1 \| \| 7 \| 8 \| 1 \| \| 8 \| 8 \| 1 \| +----+------+----+ 5 rows in set (0.00 sec) mysql> select * from target_tab; +------+------+ \| id \| name \| +------+------+ \| 4 \| 1.00 \| \| 5 \| 1.00 \| \| 6 \| 1.00 \| \| 7 \| 1.00 \| \| 8 \| 1.00 \| +------+------+ 5 rows in set (0.00 sec)

從上可見，設(shè)置系統(tǒng)變量innodb_locks_unsafe_for_binlog的值為“on”后，InnoDB不再對source_tab加鎖，結(jié)果也符合應(yīng)用邏輯，但是如果分析BINLOG的內(nèi)容：

......SET TIMESTAMP=1169175130;BEGIN;# at 274#070119 10:51:57 server id 1 end_log_pos 105 Query thread_id=1 exec_time=0 error_code=0SET TIMESTAMP=1169175117;update source_tab set name = '8' where name = '1';# at 379#070119 10:52:10 server id 1 end_log_pos 406 Xid = 5COMMIT;# at 406#070119 10:52:14 server id 1 end_log_pos 474 Query thread_id=2 exec_time=0 error_code=0SET TIMESTAMP=1169175134;BEGIN;# at 474#070119 10:51:29 server id 1 end_log_pos 119 Query thread_id=2 exec_time=0 error_code=0SET TIMESTAMP=1169175089;insert into target_tab select d1,name from source_tab where name = '1';# at 593#070119 10:52:14 server id 1 end_log_pos 620 Xid = 7COMMIT;......

可以發(fā)現(xiàn)，在BINLOG中，更新操作的位置在INSERT...SELECT之前，如果使用這個BINLOG進行數(shù)據(jù)庫恢復(fù)，恢復(fù)的結(jié)果與實際的應(yīng)用邏輯不符；如果進行復(fù)制，就會導(dǎo)致主從數(shù)據(jù)庫不一致！

通過上面的例子，我們就不難理解為什么MySQL在處理“Insert into target_tab select * from source_tab where ...”和“create table new_tab ...select ... From source_tab where ...”時要給source_tab加鎖，而不是使用對并發(fā)影響最小的多版本數(shù)據(jù)來實現(xiàn)一致性讀。還要特別說明的是，如果上述語句的SELECT是范圍條件，InnoDB還會給源表加間隙鎖（Next-Lock）。

因此，INSERT...SELECT...和 CREATE TABLE...SELECT...語句，可能會阻止對源表的并發(fā)更新，造成對源表鎖的等待。如果查詢比較復(fù)雜的話，會造成嚴重的性能問題，我們在應(yīng)用中應(yīng)盡量避免使用。實際上，MySQL將這種SQL叫作不確定（non-deterministic）的SQL，不推薦使用。

如果應(yīng)用中一定要用這種SQL來實現(xiàn)業(yè)務(wù)邏輯，又不希望對源表的并發(fā)更新產(chǎn)生影響，可以采取以下兩種措施：

一是采取上面示例中的做法，將innodb_locks_unsafe_for_binlog的值設(shè)置為“on”，強制MySQL使用多版本數(shù)據(jù)一致性讀。但付出的代價是可能無法用binlog正確地恢復(fù)或復(fù)制數(shù)據(jù)，因此，不推薦使用這種方式。
二是通過使用“select * from source_tab ... Into outfile”和“load data infile ...”語句組合來間接實現(xiàn)，采用這種方式MySQL不會給source_tab加鎖。

InnoDB在不同隔離級別下的一致性讀及鎖的差異

前面講過，鎖和多版本數(shù)據(jù)是InnoDB實現(xiàn)一致性讀和ISO/ANSI SQL92隔離級別的手段，因此，在不同的隔離級別下，InnoDB處理SQL時采用的一致性讀策略和需要的鎖是不同的。同時，數(shù)據(jù)恢復(fù)和復(fù)制機制的特點，也對一些SQL的一致性讀策略和鎖策略有很大影響。將這些特性歸納成如下表所示的內(nèi)容，以便讀者查閱。

InnoDB存儲引擎中不同SQL在不同隔離級別下鎖比較

隔離級別一致性讀和鎖 SQL	Read Uncommited	Read Commited	Repeatable Read	Serializable
SQL	條件
select	相等	None locks	Consisten read/None lock	Consisten read/None lock	Share locks
范圍	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key
update	相等	exclusive locks	exclusive locks	exclusive locks	Exclusive locks
范圍	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
Insert	N/A	exclusive locks	exclusive locks	exclusive locks	exclusive locks
replace	無鍵沖突	exclusive locks	exclusive locks	exclusive locks	exclusive locks
鍵沖突	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
delete	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
范圍	exclusive next-key	exclusive next-key	exclusive next-key	exclusive next-key
Select ... from ... Lock in share mode	相等	Share locks	Share locks	Share locks	Share locks
范圍	Share locks	Share locks	Share Next-Key	Share Next-Key
Select * from ... For update	相等	exclusive locks	exclusive locks	exclusive locks	exclusive locks
范圍	exclusive locks	Share locks	exclusive next-key	exclusive next-key
Insert into ... Select ... （指源表鎖）	innodb_locks_unsafe_for_binlog=off	Share Next-Key	Share Next-Key	Share Next-Key	Share Next-Key
innodb_locks_unsafe_for_binlog=on	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key
create table ... Select ... （指源表鎖）	innodb_locks_unsafe_for_binlog=off	Share Next-Key	Share Next-Key	Share Next-Key	Share Next-Key
innodb_locks_unsafe_for_binlog=on	None locks	Consisten read/None lock	Consisten read/None lock	Share Next-Key

從上表可以看出：對于許多SQL，隔離級別越高，InnoDB給記錄集加的鎖就越嚴格（尤其是使用范圍條件的時候），產(chǎn)生鎖沖突的可能性也就越高，從而對并發(fā)性事務(wù)處理性能的影響也就越大。因此，我們在應(yīng)用中，應(yīng)該盡量使用較低的隔離級別，以減少鎖爭用的機率。實際上，通過優(yōu)化事務(wù)邏輯，大部分應(yīng)用使用Read Commited隔離級別就足夠了。對于一些確實需要更高隔離級別的事務(wù)，可以通過在程序中執(zhí)行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ或SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE動態(tài)改變隔離級別的方式滿足需求。

什么時候使用表鎖

對于InnoDB表，在絕大部分情況下都應(yīng)該使用行級鎖，因為事務(wù)和行鎖往往是我們之所以選擇InnoDB表的理由。但在個別特殊事務(wù)中，也可以考慮使用表級鎖。

第一種情況是：事務(wù)需要更新大部分或全部數(shù)據(jù)，表又比較大，如果使用默認的行鎖，不僅這個事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務(wù)的執(zhí)行速度。
第二種情況是：事務(wù)涉及多個表，比較復(fù)雜，很可能引起死鎖，造成大量事務(wù)回滾。這種情況也可以考慮一次性鎖定事務(wù)涉及的表，從而避免死鎖、減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。

當(dāng)然，應(yīng)用中這兩種事務(wù)不能太多，否則，就應(yīng)該考慮使用MyISAM表了。

在InnoDB下，使用表鎖要注意以下兩點。

（1）使用LOCK TABLES雖然可以給InnoDB加表級鎖，但必須說明的是，表鎖不是由InnoDB存儲引擎層管理的，而是由其上一層──MySQL Server負責(zé)的，僅當(dāng)autocommit=0、innodb_table_locks=1（默認設(shè)置）時，InnoDB層才能知道MySQL加的表鎖，MySQL Server也才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動識別涉及表級鎖的死鎖；否則，InnoDB將無法自動檢測并處理這種死鎖。有關(guān)死鎖，下一小節(jié)還會繼續(xù)討論。

（2）在用 LOCK TABLES對InnoDB表加鎖時要注意，要將AUTOCOMMIT設(shè)為0，否則MySQL不會給表加鎖；事務(wù)結(jié)束前，不要用UNLOCK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務(wù)；COMMIT或ROLLBACK并不能釋放用LOCK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖。正確的方式見如下語句：

例如，如果需要寫表t1并從表t讀，可以按如下做：

SET AUTOCOMMIT=0;LOCK TABLES t1 WRITE, t2 READ, ...;[do something with tables t1 and t2 here];COMMIT;UNLOCK TABLES;

關(guān)于死鎖

上文講過，MyISAM表鎖是deadlock free的，這是因為MyISAM總是一次獲得所需的全部鎖，要么全部滿足，要么等待，因此不會出現(xiàn)死鎖。但在InnoDB中，除單個SQL組成的事務(wù)外，鎖是逐步獲得的，這就決定了在InnoDB中發(fā)生死鎖是可能的。如下所示的就是一個發(fā)生死鎖的例子。

InnoDB存儲引擎中的死鎖例子

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from table_1 where where id=1 for update; ... 做一些其他處理...	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from table_2 where id=1 for update; ...
select * from table_2 where id =1 for update; 因session_2已取得排他鎖，等待	做一些其他處理...
	mysql> select * from table_1 where where id=1 for update; 死鎖

在上面的例子中，兩個事務(wù)都需要獲得對方持有的排他鎖才能繼續(xù)完成事務(wù)，這種循環(huán)鎖等待就是典型的死鎖。

發(fā)生死鎖后，InnoDB一般都能自動檢測到，并使一個事務(wù)釋放鎖并回退，另一個事務(wù)獲得鎖，繼續(xù)完成事務(wù)。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB并不能完全自動檢測到死鎖，這需要通過設(shè)置鎖等待超時參數(shù) innodb_lock_wait_timeout來解決。需要說明的是，這個參數(shù)并不是只用來解決死鎖問題，在并發(fā)訪問比較高的情況下，如果大量事務(wù)因無法立即獲得所需的鎖而掛起，會占用大量計算機資源，造成嚴重性能問題，甚至拖跨數(shù)據(jù)庫。我們通過設(shè)置合適的鎖等待超時閾值，可以避免這種情況發(fā)生。

通常來說，死鎖都是應(yīng)用設(shè)計的問題，通過調(diào)整業(yè)務(wù)流程、數(shù)據(jù)庫對象設(shè)計、事務(wù)大小，以及訪問數(shù)據(jù)庫的SQL語句，絕大部分死鎖都可以避免。下面就通過實例來介紹幾種避免死鎖的常用方法。

（1）在應(yīng)用中，如果不同的程序會并發(fā)存取多個表，應(yīng)盡量約定以相同的順序來訪問表，這樣可以大大降低產(chǎn)生死鎖的機會。在下面的例子中，由于兩個session訪問兩個表的順序不同，發(fā)生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可以避免。

InnoDB存儲引擎中表順序造成的死鎖例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 1 for update; +------------+-----------+ \| first_name \| last_name \| +------------+-----------+ \| PENELOPE \| GUINESS \| +------------+-----------+ 1 row in set (0.00 sec)
	mysql> insert into country (country_id,country) values(110,'Test'); Query OK, 1 row affected (0.00 sec)
mysql> insert into country (country_id,country) values(110,'Test'); 等待
	mysql> select first_name,last_name from actor where actor_id = 1 for update; +------------+-----------+ \| first_name \| last_name \| +------------+-----------+ \| PENELOPE \| GUINESS \| +------------+-----------+ 1 row in set (0.00 sec)
mysql> insert into country (country_id,country) values(110,'Test'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

（2）在程序以批量方式處理數(shù)據(jù)的時候，如果事先對數(shù)據(jù)排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現(xiàn)死鎖的可能。

InnoDB存儲引擎中表數(shù)據(jù)操作順序不一致造成的死鎖例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 1 for update; +------------+-----------+ \| first_name \| last_name \| +------------+-----------+ \| PENELOPE \| GUINESS \| +------------+-----------+ 1 row in set (0.00 sec)
	mysql> select first_name,last_name from actor where actor_id = 3 for update; +------------+-----------+ \| first_name \| last_name \| +------------+-----------+ \| ED \| CHASE \| +------------+-----------+ 1 row in set (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 3 for update; 等待
	mysql> select first_name,last_name from actor where actor_id = 1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> select first_name,last_name from actor where actor_id = 3 for update; +------------+-----------+ \| first_name \| last_name \| +------------+-----------+ \| ED \| CHASE \| +------------+-----------+ 1 row in set (4.71 sec)

（3）在事務(wù)中，如果要更新記錄，應(yīng)該直接申請足夠級別的鎖，即排他鎖，而不應(yīng)先申請共享鎖，更新時再申請排他鎖，因為當(dāng)用戶申請排他鎖時，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。

（4）前面講過，在REPEATABLE-READ隔離級別下，如果兩個線程同時對相同條件記錄用SELECT...FOR UPDATE加排他鎖，在沒有符合該條件記錄情況下，兩個線程都會加鎖成功。程序發(fā)現(xiàn)記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這么做，就會出現(xiàn)死鎖。這種情況下，將隔離級別改成READ COMMITTED，就可避免問題，如下所示。

InnoDB存儲引擎中隔離級別引起的死鎖例子1

session_1	session_2
mysql> select @@tx_isolation; +-----------------+ \| @@tx_isolation \| +-----------------+ \| REPEATABLE-READ \| +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> select @@tx_isolation; +-----------------+ \| @@tx_isolation \| +-----------------+ \| REPEATABLE-READ \| +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
當(dāng)前session對不存在的記錄加for update的鎖： mysql> insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom');
	其他session也可以對不存在的記錄加for update的鎖： mysql> insert into actor (actor_id, first_name , last_name) values(201,'Lisa','Tom'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
因為其他session也對該記錄加了鎖，所以當(dāng)前的插入會等待： mysql> insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom'); 等待
	因為其他session已經(jīng)對記錄進行了更新，這時候再插入記錄就會提示死鎖并退出： mysql> insert into actor (actor_id, first_name , last_name) values(201,'Lisa','Tom'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
由于其他session已經(jīng)退出，當(dāng)前session可以獲得鎖并成功插入記錄： mysql> insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom'); Query OK, 1 row affected (13.35 sec)

（5）當(dāng)隔離級別為READ COMMITTED時，如果兩個線程都先執(zhí)行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現(xiàn)鎖等待，當(dāng)?shù)?個線程提交后，第2個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第3個線程又來申請排他鎖，也會出現(xiàn)死鎖。

對于這種情況，可以直接做插入操作，然后再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執(zhí)行ROLLBACK釋放獲得的排他鎖，如下所示。

InnoDB存儲引擎中隔離級別引起的死鎖例子2

session_1	session_2	session_3
mysql> select @@tx_isolation; +----------------+ \| @@tx_isolation \| +----------------+ \| READ-COMMITTED \| +----------------+ 1 row in set (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.01 sec)	mysql> select @@tx_isolation; +----------------+ \| @@tx_isolation \| +----------------+ \| READ-COMMITTED \| +----------------+ 1 row in set (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.01 sec)	mysql> select @@tx_isolation; +----------------+ \| @@tx_isolation \| +----------------+ \| READ-COMMITTED \| +----------------+ 1 row in set (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.01 sec)
Session_1獲得for update的共享鎖： mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)	由于記錄不存在，session_2也可以獲得for update的共享鎖： mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)
Session_1可以成功插入記錄： mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); Query OK, 1 row affected (0.00 sec)
	Session_2插入申請等待獲得鎖： mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); 等待
Session_1成功提交： mysql> commit; Query OK, 0 rows affected (0.04 sec)
	Session_2獲得鎖，發(fā)現(xiàn)插入記錄主鍵重，這個時候拋出了異常，但是并沒有釋放共享鎖： mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); ERROR 1062 (23000): Duplicate entry '201' for key 'PRIMARY'
		Session_3申請獲得共享鎖，因為session_2已經(jīng)鎖定該記錄，所以session_3需要等待： mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update; 等待
	這個時候，如果session_2直接對記錄進行更新操作，則會拋出死鎖的異常： mysql> update actor set last_name='Lan' where actor_id = 201; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
		Session_2釋放鎖后，session_3獲得鎖： mysql> select first_name, last_name from actor where actor_id = 201 for update; +------------+-----------+ \| first_name \| last_name \| +------------+-----------+ \| Lisa \| Tom \| +------------+-----------+ 1 row in set (31.12 sec)

盡管通過上面介紹的設(shè)計和SQL優(yōu)化等措施，可以大大減少死鎖，但死鎖很難完全避免。因此，在程序設(shè)計中總是捕獲并處理死鎖異常是一個很好的編程習(xí)慣。

如果出現(xiàn)死鎖，可以用SHOW INNODB STATUS命令來確定最后一個死鎖產(chǎn)生的原因。返回結(jié)果中包括死鎖相關(guān)事務(wù)的詳細信息，如引發(fā)死鎖的SQL語句，事務(wù)已經(jīng)獲得的鎖，正在等待什么鎖，以及被回滾的事務(wù)等。據(jù)此可以分析死鎖產(chǎn)生的原因和改進措施。下面是一段SHOW INNODB STATUS輸出的樣例：

mysql> show innodb status /G…….------------------------LATEST DETECTED DEADLOCK------------------------070710 14:05:16*** (1) TRANSACTION:TRANSACTION 0 117470078, ACTIVE 117 sec, process no 1468, OS thread id 1197328736 insertingmysql tables in use 1, locked 1LOCK WAIT 5 lock struct(s), heap size 1216MySQL thread id 7521657, query id 673468054 localhost root updateinsert into country (country_id,country) values(110,'Test')………*** (2) TRANSACTION:TRANSACTION 0 117470079, ACTIVE 39 sec, process no 1468, OS thread id 1164048736 starting index read, thread declared inside InnoDB 500mysql tables in use 1, locked 14 lock struct(s), heap size 1216, undo log entries 1MySQL thread id 7521664, query id 673468058 localhost root statisticsselect first_name,last_name from actor where actor_id = 1 for update*** (2) HOLDS THE LOCK(S):………*** (2) WAITING FOR THIS LOCK TO BE GRANTED:………*** WE ROLL BACK TRANSACTION (1)……

本文全面講解了Mysql表鎖,行鎖,共享鎖,排它鎖,間隙鎖的詳細使用方法,希望對大家有所幫助

注：相關(guān)教程知識閱讀請移步到MYSQL教程頻道。

上一篇：定時備份 Mysql并上傳到七牛的方法

下一篇：MySQL中的排序函數(shù)field()實例詳解