共計 39427 個字符，預計需要花費 99 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 MySQL 中鎖有什么用，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
鎖是計算機協調多個進程或線程并發訪問某一資源的機制。在數據庫中，除傳統的計算資源（如 CPU、RAM、I/ O 等）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。從這個角度來說，鎖對數據庫而言顯得尤其重要，也更加復雜。本章我們著重討論 MySQL 鎖機制的特點，常見的鎖問題，以及解決 MySQL 鎖問題的一些方法或建議。
MySQL 鎖概述
相對其他數據庫而言，MySQL 的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。比如，MyISAM 和 MEMORY 存儲引擎采用的是表級鎖（table-level locking）；BDB 存儲引擎采用的是頁面鎖（page-level locking），但也支持表級鎖；InnoDB 存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認情況下是采用行級鎖。
MySQL 這 3 種鎖的特性可大致歸納如下。
開銷、加鎖速度、死鎖、粒度、并發性能
l 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高, 并發度最低。
l 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低, 并發度也最高。
l 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。
從上述特點可見，很難籠統地說哪種鎖更好，只能就具體應用的特點來說哪種鎖更合適！僅從鎖的角度來說：表級鎖更適合于以查詢為主，只有少量按索引條件更新數據的應用，如 Web 應用；而行級鎖則更適合于有大量按索引條件并發更新少量不同數據，同時又有并發查詢的應用，如一些在線事務處理（OLTP）系統。這一點在本書的“開發篇”介紹表類型的選擇時，也曾提到過。下面幾節我們重點介紹 MySQL 表鎖和 InnoDB 行鎖的問題，由于 BDB 已經被 InnoDB 取代，即將成為歷史，在此就不做進一步的討論了。
MyISAM 表鎖
MyISAM 存儲引擎只支持表鎖，這也是 MySQL 開始幾個版本中唯一支持的鎖類型。隨著應用對事務完整性和并發性要求的不斷提高，MySQL 才開始開發基于事務的存儲引擎，后來慢慢出現了支持頁鎖的 BDB 存儲引擎和支持行鎖的 InnoDB 存儲引擎（實際 InnoDB 是單獨的一個公司，現在已經被 Oracle 公司收購）。但是 MyISAM 的表鎖依然是使用最為廣泛的鎖類型。本節將詳細介紹 MyISAM 表鎖的使用。
查詢表級鎖爭用情況
可以通過檢查 table_locks_waited 和 table_locks_immediate 狀態變量來分析系統上的表鎖定爭奪：
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）。鎖模式的兼容性如表 20- 1 所示。
表 20-1 MySQL 中的表鎖兼容性
請求鎖模式
是否兼容
當前鎖模式
None
否
可見，對 MyISAM 表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對 MyISAM 表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；MyISAM 表的讀操作與寫操作之間，以及寫操作之間是串行的！根據如表 20- 2 所示的例子可以知道，當一個線程獲得對一個表的寫鎖后，只有持有鎖的線程可以對表進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。
表 20-2 MyISAM 存儲引擎的寫阻塞讀例子
session_1
session_2
獲得表 film_text 的 WRITE 鎖定
mysql lock table film_text write;
Query OK, 0 rows affected (0.00 sec)
當前 session 對鎖定表的查詢、更新、插入操作都可以執行：
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 title = Test where film_id = 1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
其他 session 對鎖定表的查詢被阻塞，需要等待鎖被釋放：
mysql select film_id,title from film_text where film_id = 1001;
等待
釋放鎖：
mysql unlock tables;
Query OK, 0 rows affected (0.00 sec)
Session2 獲得鎖，查詢返回：
mysql select film_id,title from film_text where film_id = 1001;
+———+——-+
| film_id | title |
+———+——-+
| 1001 | Test |
+———+——-+
1 row in set (57.59 sec)
如何加表鎖
MyISAM 在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT 等）前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。在本書的示例中，顯式加鎖基本上都是為了方便而已，并非必須如此。
給 MyISAM 表顯示加鎖，一般是為了在一定程度模擬事務操作，實現對某一時間點多個表的一致性讀取。例如，有一個訂單表 orders，其中記錄有各訂單的總金額 total，同時還有一個訂單明細表 order_detail，其中記錄有各訂單每一產品的金額小計 subtotal，假設我們需要檢查這兩個表的金額合計是否相符，可能就需要執行如下兩條 SQL：
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
這時，如果不先給兩個表加鎖，就可能產生錯誤的結果，因為第一條語句執行過程中，order_detail 表可能已經發生了改變。因此，正確的方法應該是：
Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;
要特別說明以下兩點內容。
? 上面的例子在 LOCK TABLES 時加了“local”選項，其作用就是在滿足 MyISAM 表并發插入條件的情況下，允許其他用戶在表尾并發插入記錄，有關 MyISAM 表的并發插入問題，在后面的章節中還會進一步介紹。
? 在用 LOCK TABLES 給表顯式加表鎖時，必須同時取得所有涉及到表的鎖，并且 MySQL 不支持鎖升級。也就是說，在執行 LOCK TABLES 后，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那么只能執行查詢操作，而不能執行更新操作。其實，在自動加鎖的情況下也基本如此，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖。這也正是 MyISAM 表不會出現死鎖（Deadlock Free）的原因。
在如表 20- 3 所示的例子中，一個 session 使用 LOCK TABLE 命令給表 film_text 加了讀鎖，這個 session 可以查詢鎖定表中的記錄，但更新或訪問其他表都會提示錯誤；同時，另外一個 session 可以查詢表中的記錄，但更新就會出現鎖等待。
表 20-3 MyISAM 存儲引擎的讀阻塞寫例子
session_1
session_2
獲得表 film_text 的 READ 鎖定
mysql lock table film_text read;
Query OK, 0 rows affected (0.00 sec)
當前 session 可以查詢該表記錄
mysql select film_id,title from film_text where film_id = 1001;
+———+——————+
| film_id | title |
+———+——————+
| 1001 | ACADEMY DINOSAUR |
+———+——————+
1 row in set (0.00 sec)
其他 session 也可以查詢該表的記錄
mysql select film_id,title from film_text where film_id = 1001;
+———+——————+
| film_id | title |
+———+——————+
| 1001 | ACADEMY DINOSAUR |
+———+——————+
1 row in set (0.00 sec)
當前 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 title = Test where film_id = 1001;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
當前 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 title = Test where film_id = 1001;
ERROR 1099 (HY000): Table film_text was locked with a READ lock and can t be updated
其他 session 更新鎖定表會等待獲得鎖：
mysql update film_text set title = Test where film_id = 1001;
等待
釋放鎖
mysql unlock tables;
Query OK, 0 rows affected (0.00 sec)
Session 獲得鎖，更新操作完成：
mysql update film_text set title = Test where film_id = 1001;
Query OK, 1 row affected (1 min 0.71 sec)
Rows matched: 1 Changed: 1 Warnings: 0
當使用 LOCK TABLES 時，不僅需要一次鎖定用到的所有表，而且，同一個表在 SQL 語句中出現多少次，就要通過與 SQL 語句中相同的別名鎖定多少次，否則也會出錯！舉例說明如下。
（1）對 actor 表獲得讀鎖：
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）按照別名的查詢可以正確執行：
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)
并發插入（Concurrent Inserts）
上文提到過 MyISAM 表的讀和寫是串行的，但這是就總體而言的。在一定條件下，MyISAM 表也支持查詢和插入操作的并發進行。
MyISAM 存儲引擎有一個系統變量 concurrent_insert，專門用以控制其并發插入的行為，其值分別可以為 0、1 或 2。
l 當 concurrent_insert 設置為 0 時，不允許并發插入。
l 當 concurrent_insert 設置為 1 時，如果 MyISAM 表中沒有空洞（即表的中間沒有被刪除的行），MyISAM 允許在一個進程讀表的同時，另一個進程從表尾插入記錄。這也是 MySQL 的默認設置。
l 當 concurrent_insert 設置為 2 時，無論 MyISAM 表中有沒有空洞，都允許在表尾并發插入記錄。
在如表 20- 4 所示的例子中，session_1 獲得了一個表的 READ LOCAL 鎖，該線程可以對表進行查詢操作，但不能對表進行更新操作；其他的線程（session_2），雖然不能對表進行刪除和更新操作，但卻可以對該表進行并發插入操作，這里假設該表中間不存在空洞。
表 20-4 MyISAM 存儲引擎的讀寫（INSERT）并發例子
session_1
session_2
獲得表 film_text 的 READ LOCAL 鎖定
mysql lock table film_text read local;
Query OK, 0 rows affected (0.00 sec)
當前 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 title = Test where film_id = 1001;
ERROR 1099 (HY000): Table film_text was locked with a READ lock and can t be updated
其他 session 可以進行插入操作，但是更新會等待：
mysql insert into film_text (film_id,title) values(1002, Test
Query OK, 1 row affected (0.00 sec)
mysql update film_text set title = Update Test where film_id = 1001;
等待
當前 session 不能訪問其他 session 插入的記錄：
mysql select film_id,title from film_text where film_id = 1002;
Empty set (0.00 sec)
釋放鎖：
mysql unlock tables;
Query OK, 0 rows affected (0.00 sec)
等待
當前 session 解鎖后可以獲得其他 session 插入的記錄：
mysql select film_id,title from film_text where film_id = 1002;
+———+——-+
| film_id | title |
+———+——-+
| 1002 | Test |
+———+——-+
1 row in set (0.00 sec)
Session2 獲得鎖，更新操作完成：
mysql update film_text set title = Update Test where film_id = 1001;
Query OK, 1 row affected (1 min 17.75 sec)
Rows matched: 1 Changed: 1 Warnings: 0
可以利用 MyISAM 存儲引擎的并發插入特性，來解決應用中對同一表查詢和插入的鎖爭用。例如，將 concurrent_insert 系統變量設為 2，總是允許并發插入；同時，通過定期在系統空閑時段執行 OPTIMIZE TABLE 語句來整理空間碎片，收回因刪除記錄而產生的中間空洞。有關 OPTIMIZE TABLE 語句的詳細介紹，可以參見第 18 章中“兩個簡單實用的優化方法”一節的內容。
MyISAM 的鎖調度
前面講過，MyISAM 存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的。那么，一個進程請求某個 MyISAM 表的讀鎖，同時另一個進程也請求同一表的寫鎖，MySQL 如何處理呢？答案是寫進程先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀鎖請求之前！這是因為 MySQL 認為寫請求一般比讀請求要重要。這也正是 MyISAM 表不太適合于有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。這種情況有時可能會變得非常糟糕！幸好我們可以通過一些設置來調節 MyISAM 的調度行為。
? 通過指定啟動參數 low-priority-updates，使 MyISAM 引擎默認給予讀請求以優先的權利。
? 通過執行命令 SET LOW_PRIORITY_UPDATES=1，使該連接發出的更新請求優先級降低。
? 通過指定 INSERT、UPDATE、DELETE 語句的 LOW_PRIORITY 屬性，降低該語句的優先級。
雖然上面 3 種方法都是要么更新優先，要么查詢優先的方法，但還是可以用其來解決查詢相對重要的應用（如用戶登錄系統）中，讀鎖等待嚴重的問題。
另外，MySQL 也提供了一種折中的辦法來調節讀寫沖突，即給系統參數 max_write_lock_count 設置一個合適的值，當一個表的讀鎖達到這個值后，MySQL 就暫時將寫請求的優先級降低，給讀進程一定獲得鎖的機會。
上面已經討論了寫優先調度機制帶來的問題和解決辦法。這里還要強調一點：一些需要長時間運行的查詢操作，也會使寫進程“餓死”！因此，應用中應盡量避免出現長時間運行的查詢操作，不要總想用一條 SELECT 語句來解決問題，因為這種看似巧妙的 SQL 語句，往往比較復雜，執行時間較長，在可能的情況下可以通過使用中間表等措施對 SQL 語句做一定的“分解”，使每一步查詢都能在較短時間完成，從而減少鎖沖突。如果復雜查詢不可避免，應盡量安排在數據庫空閑時段執行，比如一些定期統計可以安排在夜間執行。
InnoDB 鎖問題
InnoDB 與 MyISAM 的最大不同有兩點：一是支持事務（TRANSACTION）；二是采用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，另外，事務的引入也帶來了一些新問題。下面我們先介紹一點背景知識，然后詳細討論 InnoDB 的鎖問題。
背景知識
1．事務（Transaction）及其 ACID 屬性
事務是由一組 SQL 語句組成的邏輯處理單元，事務具有以下 4 個屬性，通常簡稱為事務的 ACID 屬性。
l 原子性（Atomicity）：事務是一個原子操作單元，其對數據的修改，要么全都執行，要么全都不執行。
l 一致性（Consistent）：在事務開始和完成時，數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用于事務的修改，以保持數據的完整性；事務結束時，所有的內部數據結構（如 B 樹索引或雙向鏈表）也都必須是正確的。
l 隔離性（Isolation）：數據庫系統提供一定的隔離機制，保證事務在不受外部并發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。
l 持久性（Durable）：事務完成之后，它對于數據的修改是永久性的，即使出現系統故障也能夠保持。
銀行轉帳就是事務的一個典型例子。
2．并發事務處理帶來的問題
相對于串行處理來說，并發事務處理能大大增加數據庫資源的利用率，提高數據庫系統的事務吞吐量，從而可以支持更多的用戶。但并發事務處理也會帶來一些問題，主要包括以下幾種情況。
l 更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然后基于最初選定的值更新該行時，由于每個事務都不知道其他事務的存在，就會發生丟失更新問題--最后的更新覆蓋了由其他事務所做的更新。例如，兩個編輯人員制作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。最后保存其更改副本的編輯人員覆蓋另一個編輯人員所做的更改。如果在一個編輯人員完成并提交事務之前，另一個編輯人員不能訪問同一文件，則可避免此問題。
l 臟讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務完成并提交前，這條記錄的數據就處于不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“臟”數據，并據此做進一步的處理，就會產生未提交的數據依賴關系。這種現象被形象地叫做 臟讀。
l 不可重復讀（Non-Repeatable Reads）：一個事務在讀取某些數據后的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做“不可重復讀”。
l 幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為“幻讀”。
3．事務隔離級別
在上面講到的并發事務處理帶來的問題中，“更新丟失”通常是應該完全避免的。但防止更新丟失，并不能單靠數據庫事務控制器來解決，需要應用程序對要更新的數據加必要的鎖來解決，因此，防止更新丟失應該是應用的責任。
“臟讀”、“不可重復讀”和“幻讀”，其實都是數據庫讀一致性問題，必須由數據庫提供一定的事務隔離機制來解決。數據庫實現事務隔離的方式，基本上可分為以下兩種。
l 一種是在讀取數據前，對其加鎖，阻止其他事務對數據進行修改。
l 另一種是不用加任何鎖，通過一定機制生成一個數據請求時間點的一致性數據快照（Snapshot)，并用這個快照來提供一定級別（語句級或事務級）的一致性讀取。從用戶的角度來看，好像是數據庫可以提供同一數據的多個版本，因此，這種技術叫做數據多版本并發控制（MultiVersion Concurrency Control，簡稱 MVCC 或 MCC），也經常稱為多版本數據庫。
數據庫的事務隔離越嚴格，并發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上“串行化”進行，這顯然與“并發”是矛盾的。同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對“不可重復讀”和“幻讀”并不敏感，可能更關心數據并發訪問的能力。
為了解決“隔離”與“并發”的矛盾，ISO/ANSI SQL92 定義了 4 個事務隔離級別，每個級別的隔離程度不同，允許出現的副作用也不同，應用可以根據自己的業務邏輯要求，通過選擇不同的隔離級別來平衡“隔離”與“并發”的矛盾。表 20- 5 很好地概括了這 4 個隔離級別的特性。
表 20-5 4 種隔離級別比較
讀數據一致性及允許的并發副作用
隔離級別
讀數據一致性
不可重復讀
幻讀
未提交讀（Read uncommitted）
最低級別，只能保證不讀取物理上損壞的數據是是是
已提交度（Read committed）
語句級否是是
可重復讀（Repeatable read）
事務級否否是
可序列化（Serializable）
最高級別，事務級否否否
最后要說明的是：各具體數據庫并不一定完全實現了上述 4 個隔離級別，例如，Oracle 只提供 Read committed 和 Serializable 兩個標準隔離級別，另外還提供自己定義的 Read only 隔離級別；SQL Server 除支持上述 ISO/ANSI SQL92 定義的 4 個隔離級別外，還支持一個叫做“快照”的隔離級別，但嚴格來說它是一個用 MVCC 實現的 Serializable 隔離級別。MySQL 支持全部 4 個隔離級別，但在具體實現時，有一些特點，比如在一些隔離級別下是采用 MVCC 一致性讀，但某些情況下又不是，這些內容在后面的章節中將會做進一步介紹。
獲取 InnoDB 行鎖爭用情況
可以通過檢查 InnoDB_row_lock 狀態變量來分析系統上的行鎖的爭奪情況：
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)
如果發現鎖爭用比較嚴重，如 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比較高，還可以通過設置 InnoDB Monitors 來進一步觀察發生鎖沖突的表、數據行等，并分析鎖爭用的原因。
具體方法如下：
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: InnoDB
Name:
Status:
…
…
————
TRANSACTIONS
————
Trx id counter 0 117472192
Purge done for trx s n:o 0 117472190 undo n:o 0 0
History list length 17
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0 117472185, not started, process no 11052, OS thread id 1158191456
MySQL thread id 200610, query id 291197 localhost root
—TRANSACTION 0 117472183, not started, process no 11052, OS thread id 1158723936
MySQL thread id 199285, query id 291199 localhost root
Show innodb status
…
監視器可以通過發出下列語句來停止查看：
mysql DROP TABLE innodb_monitor;
Query OK, 0 rows affected (0.05 sec)
設置監視器后，在 SHOW INNODB STATUS 的顯示內容中，會有詳細的當前鎖等待的信息，包括表名、鎖類型、鎖定記錄的情況等，便于進行進一步的分析和問題的確定。打開監視器以后，默認情況下每 15 秒會向日志中記錄監控的內容，如果長時間打開會導致.err 文件變得非常的巨大，所以用戶在確認問題原因之后，要記得刪除監控表以關閉監視器，或者通過使用“–console”選項來啟動服務器以關閉寫日志文件。
InnoDB 的行鎖模式及加鎖方法
InnoDB 實現了以下兩種類型的行鎖。
l 共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。
l 排他鎖（X)：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。
另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB 還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。
l 意向共享鎖（IS）：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的 IS 鎖。
l 意向排他鎖（IX）：事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的 IX 鎖。
上述鎖模式的兼容情況具體如表 20- 6 所示。
表 20-6 InnoDB 行鎖模式兼容性列表
請求鎖模式
是否兼容
當前鎖模式
兼容
如果一個事務請求的鎖模式與當前的鎖兼容，InnoDB 就將請求的鎖授予該事務；反之，如果兩者不兼容，該事務就要等待鎖釋放。
意向鎖是 InnoDB 自動加的，不需用戶干預。對于 UPDATE、DELETE 和 INSERT 語句，InnoDB 會自動給涉及數據集加排他鎖（X)；對于普通 SELECT 語句，InnoDB 不會加任何鎖；事務可以通過以下語句顯示給記錄集加共享鎖或排他鎖。
? 共享鎖（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
? 排他鎖（X)：SELECT * FROM table_name WHERE … FOR UPDATE。
用 SELECT … IN SHARE MODE 獲得共享鎖，主要用在需要數據依存關系時來確認某行記錄是否存在，并確保沒有人對這個記錄進行 UPDATE 或者 DELETE 操作。但是如果當前事務也需要對該記錄進行更新操作，則很有可能造成死鎖，對于鎖定行記錄后需要進行更新操作的應用，應該使用 SELECT… FOR UPDATE 方式獲得排他鎖。
在如表 20- 7 所示的例子中，使用了 SELECT … IN SHARE MODE 加鎖后再更新記錄，看看會出現什么情況，其中 actor 表的 actor_id 字段為主鍵。
表 20-7 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)
當前 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)
當前 session 對鎖定的記錄進行更新操作，等待鎖：
mysql update actor set last_name = MONROE T where actor_id = 178;
等待
其他 session 也對該記錄進行更新操作，則會導致死鎖退出：
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
當使用 SELECT…FOR UPDATE 加鎖后再更新記錄，出現如表 20- 8 所示的情況。
表 20-8 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)
當前 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;
等待
當前 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 行鎖實現方式
InnoDB 行鎖是通過給索引上的索引項加鎖來實現的，這一點 MySQL 與 Oracle 不同，后者是通過在數據塊中對相應數據行加鎖來實現的。InnoDB 這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖！
在實際應用中，要特別注意 InnoDB 行鎖的這一特性，不然的話，可能導致大量的鎖沖突，從而影響并發性能。下面通過一些實際例子來加以說明。
（1）在不通過索引條件查詢的時候，InnoDB 確實使用的是表鎖，而不是行鎖。
在如表 20- 9 所示的例子中，開始 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
表 20-9 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;
等待
在如表 20 - 9 所示的例子中，看起來 session_1 只給一行加了排他鎖，但 session_2 在請求其他行的排他鎖時，卻出現了鎖等待！原因就是在沒有索引的情況下，InnoDB 只能使用表鎖。當我們給其增加一個索引后，InnoDB 就只鎖定了符合條件的行，如表 20-10 所示。
創建 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
表 20-10 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 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖沖突的。應用設計的時候要注意這一點。
在如表 20-11 所示的例子中，表 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: 0
mysql 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)
表 20-11 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）當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會使用行鎖來對數據加鎖。
在如表 20-12 所示的例子中，表 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
表 20-12 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)
由于訪問的記錄已經被 session_1 鎖定，所以等待獲得鎖。：
mysql select * from tab_with_index where name = 4 for update;
（4）即便在條件中使用了索引字段，但是否使用索引來檢索數據是由 MySQL 通過判斷不同執行計劃的代價來決定的，如果 MySQL 認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下 InnoDB 將使用表鎖，而不是行鎖。因此，在分析鎖沖突時，別忘了檢查 SQL 的執行計劃，以確認是否真正使用了索引。關于 MySQL 在什么情況下不使用索引的詳細討論，參見本章“索引問題”一節的介紹。
在下面的例子中，檢索值的數據類型與索引字段不同，雖然 MySQL 能夠進行數據類型轉換，但卻不會使用索引，從而導致 InnoDB 使用表鎖。通過用 explain 檢查兩條 SQL 的執行計劃，我們可以清楚地看到了這一點。
例子中 tab_with_index 表的 name 字段有索引，但是 name 字段是 varchar 類型的，如果 where 條件中不是和 varchar 類型進行比較，則會對 name 進行類型轉換，而執行的全表掃描。
mysql alter table tab_no_index add index name(name);
Query OK, 4 rows affected (8.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql explain select * from tab_with_index where name = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab_with_index
type: ALL
possible_keys: name
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)
mysql explain select * from tab_with_index where name = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab_with_index
type: ref
possible_keys: name
key: name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
間隙鎖（Next-Key 鎖）
當我們用范圍條件而不是相等條件檢索數據，并請求共享或排他鎖時，InnoDB 會給符合條件的已有數據記錄的索引項加鎖；對于鍵值在條件范圍內但并不存在的記錄，叫做“間隙（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 使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關隔離級別的要求，對于上面的例子，要是不使用間隙鎖，如果其他事務插入了 empid 大于 100 的任何記錄，那么本事務如果再次執行上述語句，就會發生幻讀；另外一方面，是為了滿足其恢復和復制的需要。有關其恢復和復制對鎖機制的影響，以及不同隔離級別下 InnoDB 使用間隙鎖的情況，在后續的章節中會做進一步介紹。
很顯然，在使用范圍條件檢索并鎖定記錄時，InnoDB 這種加鎖機制會阻塞符合條件范圍內鍵值的并發插入，這往往會造成嚴重的鎖等待。因此，在實際應用開發中，尤其是并發插入比較多的應用，我們要盡量優化業務邏輯，盡量使用相等條件來訪問更新數據，避免使用范圍條件。
還要特別說明的是，InnoDB 除了通過范圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB 也會使用間隙鎖！
在如表 20-13 所示的例子中，假如 emp 表中只有 101 條記錄，其 empid 的值分別是 1,2,……,100,101。
表 20-13 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)
當前 session 對不存在的記錄加 for update 的鎖：
mysql select * from emp where empid = 102 for update;
Empty set (0.00 sec)
這時，如果其他 session 插入 empid 為 102 的記錄（注意：這條記錄并不存在），也會出現鎖等待：
mysql insert into emp(empid,…) values(102,…);
阻塞等待
Session_1 執行 rollback：
mysql rollback;
Query OK, 0 rows affected (13.04 sec)
由于其他 session_1 回退后釋放了 Next-Key 鎖，當前 session 可以獲得鎖并成功插入記錄：
mysql insert into emp(empid,…) values(102,…);
Query OK, 1 row affected (13.35 sec)
恢復和復制的需要，對 InnoDB 鎖機制的影響
MySQL 通過 BINLOG 錄執行成功的 INSERT、UPDATE、DELETE 等更新數據的 SQL 語句，并由此實現 MySQL 數據庫的恢復和主從復制（可以參見本書“管理篇”的介紹）。MySQL 的恢復機制（復制其實就是在 Slave Mysql 不斷做基于 BINLOG 的恢復）有以下特點。
l 一是 MySQL 的恢復是 SQL 語句級的，也就是重新執行 BINLOG 中的 SQL 語句。這與 Oracle 數據庫不同，Oracle 是基于數據庫文件塊的。
l 二是 MySQL 的 Binlog 是按照事務提交的先后順序記錄的，恢復也是按這個順序進行的。這點也與 Oralce 不同，Oracle 是按照系統更新號（System Change Number，SCN）來恢復數據的，每個事務開始時，Oracle 都會分配一個全局唯一的 SCN，SCN 的順序與事務開始的時間順序是一致的。
從上面兩點可知，MySQL 的恢復機制要求：在一個事務未提交前，其他并發事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀，這已經超過了 ISO/ANSI SQL92“可重復讀”隔離級別的要求，實際上是要求事務要串行化。這也是許多情況下，InnoDB 要用到間隙鎖的原因，比如在用范圍條件更新記錄時，無論在 Read Commited 或是 Repeatable Read 隔離級別下，InnoDB 都要使用間隙鎖，但這并不是隔離級別要求的，有關 InnoDB 在不同隔離級別下加鎖的差異在下一小節還會介紹。
另外，對于“insert into target_tab select * from source_tab where …”和“create table new_tab …select … From source_tab where …(CTAS)”這種 SQL 語句，用戶并沒有對 source_tab 做任何更新操作，但 MySQL 對這種 SQL 語句做了特別處理。先來看如表 20-14 的例子。
表 20-14 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;
返回結果
commit;
在上面的例子中，只是簡單地讀 source_tab 表的數據，相當于執行一個普通的 SELECT 語句，用一致性讀就可以了。ORACLE 正是這么做的，它通過 MVCC 技術實現的多版本數據來實現一致性讀，不需要給 source_tab 加任何鎖。我們知道 InnoDB 也實現了多版本數據，對普通的 SELECT 一致性讀，也不需要加任何鎖；但這里 InnoDB 卻給 source_tab 加了共享鎖，并沒有使用多版本數據一致性讀技術！
MySQL 為什么要這么做呢？其原因還是為了保證恢復和復制的正確性。因為不加鎖的話，如果在上述語句執行過程中，其他事務對 source_tab 做了更新操作，就可能導致數據恢復的結果錯誤。為了演示這一點，我們再重復一下前面的例子，不同的是在 session_1 執行事務前，先將系統變量 innodb_locks_unsafe_for_binlog 的值設置為“on”（其默認值為 off），具體結果如表 20-15 所示。
表 20-15 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)
此時查看數據，target_tab 中可以插入 source_tab 更新前的結果，這符合應用邏輯：
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)
從上可見，設置系統變量 innodb_locks_unsafe_for_binlog 的值為“on”后，InnoDB 不再對 source_tab 加鎖，結果也符合應用邏輯，但是如果分析 BINLOG 的內容：
……
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=0
SET 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 = 5
COMMIT;
# at 406
#070119 10:52:14 server id 1 end_log_pos 474 Query thread_id=2 exec_time=0 error_code=0
SET 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=0
SET 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 = 7
COMMIT;
……
可以發現，在 BINLOG 中，更新操作的位置在 INSERT…SELECT 之前，如果使用這個 BINLOG 進行數據庫恢復，恢復的結果與實際的應用邏輯不符；如果進行復制，就會導致主從數據庫不一致！
通過上面的例子，我們就不難理解為什么 MySQL 在處理“Insert into target_tab select * from source_tab where …”和“create table new_tab …select … From source_tab where …”時要給 source_tab 加鎖，而不是使用對并發影響最小的多版本數據來實現一致性讀。還要特別說明的是，如果上述語句的 SELECT 是范圍條件，InnoDB 還會給源表加間隙鎖（Next-Lock）。
因此，INSERT…SELECT… 和 CREATE TABLE…SELECT… 語句，可能會阻止對源表的并發更新，造成對源表鎖的等待。如果查詢比較復雜的話，會造成嚴重的性能問題，我們在應用中應盡量避免使用。實際上，MySQL 將這種 SQL 叫作不確定（non-deterministic）的 SQL，不推薦使用。
如果應用中一定要用這種 SQL 來實現業務邏輯，又不希望對源表的并發更新產生影響，可以采取以下兩種措施：
? 一是采取上面示例中的做法，將 innodb_locks_unsafe_for_binlog 的值設置為“on”，強制 MySQL 使用多版本數據一致性讀。但付出的代價是可能無法用 binlog 正確地恢復或復制數據，因此，不推薦使用這種方式。
? 二是通過使用“select * from source_tab … Into outfile”和“load data infile …”語句組合來間接實現，采用這種方式 MySQL 不會給 source_tab 加鎖。
InnoDB 在不同隔離級別下的一致性讀及鎖的差異
前面講過，鎖和多版本數據是 InnoDB 實現一致性讀和 ISO/ANSI SQL92 隔離級別的手段，因此，在不同的隔離級別下，InnoDB 處理 SQL 時采用的一致性讀策略和需要的鎖是不同的。同時，數據恢復和復制機制的特點，也對一些 SQL 的一致性讀策略和鎖策略有很大影響。將這些特性歸納成如表 20-16 所示的內容，以便讀者查閱。
表 20-16 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
從表 20-16 可以看出：對于許多 SQL，隔離級別越高，InnoDB 給記錄集加的鎖就越嚴格（尤其是使用范圍條件的時候），產生鎖沖突的可能性也就越高，從而對并發性事務處理性能的影響也就越大。因此，我們在應用中，應該盡量使用較低的隔離級別，以減少鎖爭用的機率。實際上，通過優化事務邏輯，大部分應用使用 Read Commited 隔離級別就足夠了。對于一些確實需要更高隔離級別的事務，可以通過在程序中執行 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 動態改變隔離級別的方式滿足需求。
什么時候使用表鎖
對于 InnoDB 表，在絕大部分情況下都應該使用行級鎖，因為事務和行鎖往往是我們之所以選擇 InnoDB 表的理由。但在個別特殊事務中，也可以考慮使用表級鎖。
? 第一種情況是：事務需要更新大部分或全部數據，表又比較大，如果使用默認的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務的執行速度。
? 第二種情況是：事務涉及多個表，比較復雜，很可能引起死鎖，造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表，從而避免死鎖、減少數據庫因事務回滾帶來的開銷。
當然，應用中這兩種事務不能太多，否則，就應該考慮使用 MyISAM 表了。
在 InnoDB 下，使用表鎖要注意以下兩點。
（1）使用 LOCK TABLES 雖然可以給 InnoDB 加表級鎖，但必須說明的是，表鎖不是由 InnoDB 存儲引擎層管理的，而是由其上一層──MySQL Server 負責的，僅當 autocommit=0、innodb_table_locks=1（默認設置）時，InnoDB 層才能知道 MySQL 加的表鎖，MySQL Server 也才能感知 InnoDB 加的行鎖，這種情況下，InnoDB 才能自動識別涉及表級鎖的死鎖；否則，InnoDB 將無法自動檢測并處理這種死鎖。有關死鎖，下一小節還會繼續討論。
（2）在用 LOCK TABLES 對 InnoDB 表加鎖時要注意，要將 AUTOCOMMIT 設為 0，否則 MySQL 不會給表加鎖；事務結束前，不要用 UNLOCK TABLES 釋放表鎖，因為 UNLOCK TABLES 會隱含地提交事務；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;
關于死鎖
上文講過，MyISAM 表鎖是 deadlock free 的，這是因為 MyISAM 總是一次獲得所需的全部鎖，要么全部滿足，要么等待，因此不會出現死鎖。但在 InnoDB 中，除單個 SQL 組成的事務外，鎖是逐步獲得的，這就決定了在 InnoDB 中發生死鎖是可能的。如表 20-17 所示的就是一個發生死鎖的例子。
表 20-17 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;
死鎖
在上面的例子中，兩個事務都需要獲得對方持有的排他鎖才能繼續完成事務，這種循環鎖等待就是典型的死鎖。
發生死鎖后，InnoDB 一般都能自動檢測到，并使一個事務釋放鎖并回退，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB 并不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數 innodb_lock_wait_timeout 來解決。需要說明的是，這個參數并不是只用來解決死鎖問題，在并發訪問比較高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會占用大量計算機資源，造成嚴重性能問題，甚至拖跨數據庫。我們通過設置合適的鎖等待超時閾值，可以避免這種情況發生。
通常來說，死鎖都是應用設計的問題，通過調整業務流程、數據庫對象設計、事務大小，以及訪問數據庫的 SQL 語句，絕大部分死鎖都可以避免。下面就通過實例來介紹幾種避免死鎖的常用方法。
（1）在應用中，如果不同的程序會并發存取多個表，應盡量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。在下面的例子中，由于兩個 session 訪問兩個表的順序不同，發生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可以避免。
表 20-18 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）在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能。
表 20-19 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 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）在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖，更新時再申請排他鎖，因為當用戶申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。具體演示可參見 20.3.3 小節中的例子。
（4）前面講過，在 REPEATABLE-READ 隔離級別下，如果兩個線程同時對相同條件記錄用 SELECT…FOR UPDATE 加排他鎖，在沒有符合該條件記錄情況下，兩個線程都會加鎖成功。程序發現記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這么做，就會出現死鎖。這種情況下，將隔離級別改成 READ COMMITTED，就可避免問題，如表 20-20 所示。
表 20-20 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)
當前 session 對不存在的記錄加 for update 的鎖：
mysql select actor_id,first_name,last_name from actor where actor_id = 201 for update;
Empty set (0.00 sec)
其他 session 也可以對不存在的記錄加 for update 的鎖：
mysql select actor_id,first_name,last_name from actor where actor_id = 201 for update;
Empty set (0.00 sec)
因為其他 session 也對該記錄加了鎖，所以當前的插入會等待：
mysql insert into actor (actor_id , first_name , last_name) values(201, Lisa , Tom
等待
因為其他 session 已經對記錄進行了更新，這時候再插入記錄就會提示死鎖并退出：
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 已經退出，當前 session 可以獲得鎖并成功插入記錄：
mysql insert into actor (actor_id , first_name , last_name) values(201, Lisa , Tom
Query OK, 1 row affected (13.35 sec)
（5）當隔離級別為 READ COMMITTED 時，如果兩個線程都先執行 SELECT…FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現鎖等待，當第 1 個線程提交后，第 2 個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第 3 個線程又來申請排他鎖，也會出現死鎖。
對于這種情況，可以直接做插入操作，然后再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執行 ROLLBACK 釋放獲得的排他鎖，如表 20-21 所示。
表 20-21 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 獲得鎖，發現插入記錄主鍵重，這個時候拋出了異常，但是并沒有釋放共享鎖：
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 已經鎖定該記錄，所以 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)
盡管通過上面介紹的設計和 SQL 優化等措施，可以大大減少死鎖，但死鎖很難完全避免。因此，在程序設計中總是捕獲并處理死鎖異常是一個很好的編程習慣。
如果出現死鎖，可以用 SHOW INNODB STATUS 命令來確定最后一個死鎖產生的原因。返回結果中包括死鎖相關事務的詳細信息，如引發死鎖的 SQL 語句，事務已經獲得的鎖，正在等待什么鎖，以及被回滾的事務等。據此可以分析死鎖產生的原因和改進措施。下面是一段 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 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1216
MySQL thread id 7521657, query id 673468054 localhost root update
insert 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 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 7521664, query id 673468058 localhost root statistics
select 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 中 MyISAM 表級鎖和 InnoDB 行級鎖的實現特點，并討論了兩種存儲引擎經常遇到的鎖問題和解決辦法。
對于 MyISAM 的表鎖，主要討論了以下幾點：
（1）共享讀鎖（S）之間是兼容的，但共享讀鎖（S）與排他寫鎖（X）之間，以及排他寫鎖（X）之間是互斥的，也就是說讀和寫是串行的。
（2）在一定條件下，MyISAM 允許查詢和插入并發執行，我們可以利用這一點來解決應用中對同一表查詢和插入的鎖爭用問題。
（3）MyISAM 默認的鎖調度機制是寫優先，這并不一定適合所有應用，用戶可以通過設置 LOW_PRIORITY_UPDATES 參數，或在 INSERT、UPDATE、DELETE 語句中指定 LOW_PRIORITY 選項來調節讀寫鎖的爭用。
（4）由于表鎖的鎖定粒度大，讀寫之間又是串行的，因此，如果更新操作較多，MyISAM 表可能會出現嚴重的鎖等待，可以考慮采用 InnoDB 表來減少鎖沖突。
對于 InnoDB 表，本章主要討論了以下幾項內容。
l InnoDB 的行鎖是基于鎖引實現的，如果不通過索引訪問數據，InnoDB 會使用表鎖。
l 介紹了 InnoDB 間隙鎖（Next-key) 機制，以及 InnoDB 使用間隙鎖的原因。
l 在不同的隔離級別下，InnoDB 的鎖機制和一致性讀策略不同。
l MySQL 的恢復和復制對 InnoDB 鎖機制和一致性讀策略也有較大影響。
l 鎖沖突甚至死鎖很難完全避免。
在了解 InnoDB 鎖特性后，用戶可以通過設計和 SQL 調整等措施減少鎖沖突和死鎖，包括：
l 盡量使用較低的隔離級別；
l 精心設計索引，并盡量使用索引訪問數據，使加鎖更精確，從而減少鎖沖突的機會；
l 選擇合理的事務大小，小事務發生鎖沖突的幾率也更小；
l 給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數據的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產生死鎖；
l 不同的程序訪問一組表時，應盡量約定以相同的順序訪問各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會；
l 盡量用相等條件訪問數據，這樣可以避免間隙鎖對并發插入的影響；
l 不要申請超過實際需要的鎖級別；除非必須，查詢時不要顯示加鎖；
l 對于一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能。
以上是“MySQL 中鎖有什么用”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！
