一文詳解MySql行級鎖與表級鎖

MySql 行级锁 表级锁

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL锁概述

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。  
    比如

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）。

InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。下面几节我们重点介绍MySQL表锁和 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）。

MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；  
    MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；  
    MyISAM表的读操作与写操作之间，以及写操作之间是串行的；  
    当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

获得表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）的原因。

一個session使用LOCK TABLE指令為表film_text加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或存取其他表都會提示錯誤；同時，另外一個session可以查詢表中的記錄，但更新就會出現鎖定等待。

當使用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 #####################

1 row in set (0.00 sec)

並發插入（Concurrent Inserts）

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

MyISAM儲存引擎有一個系統變數concurrent_insert，專門用於控制其並發插入的行為，其值分別可以為0、1或2。

當concurrent_insert設定為0時，不允許並發插入。

當concurrent_insert設定為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個程序從表尾插入記錄。這也是MySQL的預設值。

當concurrent_insert設定為2時，無論MyISAM表中有沒有空洞，都允許在表尾並發插入記錄。

可以利用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屬性。

（Atomicity）原子性：事務是一個原子操作單元，其對資料的修改，要麼全都執行，要麼全都不執行。

（Consistent）一致性：在交易開始和完成時，資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改，以保持資料的完整性；當事務結束時，所有的內部資料結構（如B樹索引或雙向鍊錶）也都必須是正確的。

（Isolation）隔離性：資料庫系統提供一定的隔離機制，確保交易在不受外部並發操作影響的「獨立」環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。

（Durable）持久性：事務完成之後，它對於資料的修改是永久性的，即使出現系統故障也能夠保持。

銀行轉帳就是事務的典型例子。

2.並發事務處理所帶來的問題

相對於串列處理來說，並發事務處理能大幅增加資料庫資源的使用率，提高資料庫系統的事務吞吐量，從而可以支援更多的使用者。但並發事務處理也會帶來一些問題，主要包括以下幾種情況。

更新遺失（Lost Update）：當兩個或多個交易選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生遺失更新問題－最後的更新覆蓋了其他事務所所做的更新。例如，兩位編輯人員製作了同一文件的電子副本。每個編輯人員獨立地更改其副本，然後保存更改後的副本，這樣就覆蓋了原始文件。最後儲存其更改副本的編輯人員會覆蓋另一個編輯人員所做的更改。如果在一個編輯人員完成並提交事務之前，另一個編輯人員無法存取相同文件，則可避免此問題。

髒讀（Dirty Reads）：一個事務正在對一筆記錄做修改，在這個事務完成並提交前，這條記錄的資料就處於不一致狀態；這時，另一個事務也來讀取同一筆記錄，如果不加控制，第二個事務讀取了這些「髒」數據，並據此做進一步的處理，就會產生未提交的數據依賴關係。這種現像被形像地叫做"髒讀"。

不可重複讀（Non-Repeatable Reads）：一個事務在讀取某些數據後的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做「不可重複讀」。

幻讀（Phantom Reads）：一個事務以相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為“幻讀」。

3.事務隔離等級

在上面講到的並發事務處理所帶來的問題中，「更新遺失」通常是應該完全避免的。但防止更新遺失，並不能單靠資料庫事務控制器來解決，需要應用程式對要更新的資料加必要的鎖定來解​​決，因此，防止更新遺失應該是應用程式的責任。

“髒讀”、“不可重複讀”和“幻讀”，其實都是資料庫讀取一致性問題，必須由資料庫提供一定的事務隔離機制來解決。資料庫實現事務隔離的方式，基本上可分為以下兩種。

一種是在讀取資料前，對其加鎖，阻止其他交易對資料進行修改。

另一種是不用加任何鎖，透過一定機制產生一個資料請求時間點的一致性資料快照（Snapshot)，並用這個快照來提供某一層級（語句級或交易級）的一致性讀取。從使用者的角度來看，好像是資料庫可以提供相同資料的多個版本，因此，這種技術叫做資料多版本並發控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也常稱為多版本資料庫。

一致性讀，又稱為快照讀。使用的是MVCC機制讀取undo中的已經提交的資料。所以它的讀取是非阻塞的。

一致性讀取肯定是讀取在某個時間點已經提交了的數據，有個特例：本事務中修改的數據，即使未提交的數據也可以在本事務的後面部分讀取到。一致性讀是指普通的select語句，不含 for update, in share mode 等等子句。使用的是undo中的提交的數據，不需要使用鎖(MDL除外)。而當前讀，是指update, delete, select for update, select in share mode等等語句進行的讀，它們讀取的是資料庫中的最新的數據，並且會鎖住讀取的行和gap(RR隔離時)。如果不能獲得鎖，則會一直等待，直到獲得或逾時。

資料庫的事務隔離越嚴格，並發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上「串行化」進行，這顯然與「並發」是矛盾的。同時，不同的應用程式對讀取一致性和事務隔離程度的要求也是不同的，例如許多應用程式對「不可重複讀取」和「幻讀」並不敏感，可能更關心資料並發存取的能力。

為了解決「隔離」與「並發」的矛盾，ISO/ANSI SQL92定義了4個事務隔離級別，每個級別的隔離程度不同，允許出現的副作用也不同，應用可以根據自己的業務邏輯要求，透過選擇不同的隔離等級來平衡「隔離」與「並發」的矛盾。表20-5很好地概括了這4個隔離等級的特性。

各具體資料庫不一定完全實現了上述4個隔離級別Oracle只提供Read committed和Serializable兩個標準隔離級別，另外還提供自己定義的Read only隔離級別；SQL Server除支援上述ISO/ANSI SQL92定義的4個隔離級別外，還支援一個稱為「快照」的隔離級別，但嚴格來說它是一個用MVCC實現的Serializable隔離級別。

MySQL 支援全部4個隔離級別，但在具體實現時，有一些特點，例如在一些隔離級別下是採用MVCC一致性讀，但某些情況下又不是，這些內容在後面的章節中將會進一步介紹。

4.取得InnoDB行鎖定爭用情況

可以透過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖定的爭奪情況。

mysql> show status like 'innodb_row_lock%';

| Variable_name        #         

#| InnoDB_row_lock_current_waits | 0     |

#| InnoDB_row_lock_lock_current_waits | 0     |

| InnoDB_row_lock_time          | 000_row_lock_lock_no_    | |

| 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;

監視器可以透過發出下列語句來停止檢視：

mysql> ; DROP TABLE innodb_monitor;

Query OK, 0 rows affected (0.05 sec)

設定監視器後，在SHOW INNODB STATUS的顯示內容中，會有詳細的目前鎖定等待的資訊，包括表名、鎖類型、鎖定記錄的情況等，以便進行進一步的分析和問題的確定。開啟監視器以後，預設每15秒會向日誌中記錄監控的內容，如果長時間開啟會導致.err檔案變得非常的巨大，所以使用者在確認問題原因之後，要記得刪除監控表以關閉監視器，或透過使用“--console”選項來啟動伺服器以關閉寫入日誌檔案。

5. InnoDB的行鎖定模式及加鎖方法

InnoDB實作了下列兩種類型的行鎖定。

共享鎖定（S）：允許一個交易去讀一行，阻止其他交易獲得相同資料集的排他鎖。

排他鎖（X）：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖（Intention Locks），

這兩種意向鎖都是表鎖。

意向共享鎖定（IS）：交易打算為資料行加行共享鎖，交易在給一個資料行加上共享鎖前必須先取得該表的IS鎖。

意向排他鎖（IX）：交易打算為資料行加行排他鎖，交易在給一個資料行加排他鎖前必須先取得該表的IX鎖。

如果一個交易請求的鎖定模式與目前的鎖定相容，InnoDB就將請求的鎖定授予該交易；反之，如果兩者不相容，該事務就要等待鎖釋放。

意向鎖定是InnoDB自動加總的，不需要使用者介入。

總結如下：

1.   對於UPDATE、DELETE和INSERT語句，InnoDB會自動將涉及資料集加上排他鎖定（X）；

2.  對於普通SELECT語句，InnoDB不會加任何鎖定；

3.  事務可以透過以下語句顯示給記錄集加上共享鎖定或排他鎖定。

共享鎖定（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方式獲得排他鎖。

6. InnoDB行鎖定實作方式

InnoDB行鎖定是透過在索引上的索引項加鎖來實現的，這一點MySQL與Oracle不同，後者是透過在資料區塊中對對應資料行加鎖來實現的。

InnoDB這種行鎖實現特點意味著：只有透過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

在實際應用中，要特別注意InnoDB行鎖的這項特性，不然的話，可能導致大量的鎖定衝突，進而影響並發效能。

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

（2）由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是存取不同行的記錄，

但如果是使用相同的索引鍵，是會出現鎖衝突的。應用設計的時候要注意這一點。

（3）當表有多個索引的時候，不同的交易可以使用不同的索引鎖定不同的行，

另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對資料加鎖。

（4）即便在條件中使用了索引字段，但是否使用索引來檢索資料是由MySQL透過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，例如對一些很小的表，它就不會使用索引，這種情況下InnoDB會使用表鎖，而不是行鎖。因此，在分析鎖定衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。關於MySQL在什麼情況下不使用索引的詳細討論，請參閱本章「索引問題」一節的介紹。

7. 間隙鎖（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也會使用間隙鎖！

恢復與複製的需要，對InnoDB鎖定機制的影響

MySQL透過BINLOG記錄執行成功的INSERT、UPDATE、DELETE等更新資料的SQL語句，並由此實現MySQL資料庫的恢復和主從複製。 MySQL的復原機制（複製其實就是在Slave Mysql不斷做基於BINLOG的復原）有以下特點。

一是MySQL的復原是SQL語句級的，也就是重新執行BINLOG中的SQL語句。這與Oracle資料庫不同，Oracle是基於資料庫檔案區塊的。

二是MySQL的BINLOG是依照事務提交的先後順序記錄的，復原也是照這個順序進行的。這點也與Oralce不同，Oracle是按照系統更新號（System Change Number，SCN）來恢復資料的，每個事務開始時，Oracle都會分配一個全域唯一的SCN，SCN的順序與交易開始的時間順序是一致的。

從上面兩點可知，MySQL的復原機制需求：在一個事務未提交前，其他並發事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀，這已經超過了ISO/ANSI SQL92「可重複讀取」隔離等級的要求，實際上是要求交易要串行化。

另外，對於「insert  into target_tab select * from source_tab where ...」和「create  table new_tab ...select ... From  source_tab where ...(CTAS)」這種SQL語句，用戶並沒有對source_tab做任何更新操作，但MySQL對這種SQL語句做了特別處理。

（這裡InnoDB卻為source_tab加了共享鎖定，並沒有使用多重版本資料一致性讀取技術！） 

在上面的範例中，只是簡單地讀source_tab表的數據，相當於執行一個普通的SELECT語句，用一致性讀就可以了。 ORACLE正是這麼做的，它透過MVCC技術實現的多版本資料來實現一致性讀，不需要為source_tab加上任何鎖。我們知道InnoDB也實作了多版本數據，對普通的SELECT一致性讀，也不需要加任何鎖；但這裡InnoDB卻給source_tab加了共享鎖，並沒有使用多版本數據一致性讀技術！

MySQL為什麼要這麼做呢？原因還是為了確保恢復和複製的正確性。因為不加鎖的話，如果在上述語句執行過程中，其他交易對source_tab做了更新操作，就可能導致資料復原的結果錯誤。為了示範這一點，我們再重複一下前面的例子，不同的是在session_1執行事務前，先將系統變數innodb_locks_unsafe_for_binlog的值設為「on」（其預設值為off）

#

從上可見，設定係統變數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   ##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  end_log_pos 47410:52:14 server _/time end_log_pos 4741 error_code=0

SET TIMESTAMP=1169175134;

BEGIN;

# at 474

#070119 10:51:29 server id 1 

070119 10:51:29 server id 1 

070119 10:51:29 server id 1 

070119 10:51:29 server id 1 

0_pos 1199ery thread_id=2     exec_time=0     error_code=0

SET TIMESTAMP=1169175089;

insert into target_tab select d1,name from source_ 593

#070119 10:52:14 server id 1  end_log_pos 620   Xid = 7

#COMMIT;

#可以發現，在BINLOG中，更新作業的位置在INS#ERT. ..SELECT之前，如果使用這個BINLOG進行資料庫恢復，恢復的結果與實際的應用邏輯不符；如果進行複製，就會導致主從資料庫不一致！

因此，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加鎖。

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

前面講過，鎖和多版本資料是InnoDB實作一致性讀和ISO/ANSI SQL92隔離級別的手段，因此，在不同的隔離等級下，InnoDB處理SQL時所採用的一致性讀取策略和所需的鎖定是不同的。同時，資料復原和複製機制的特點，也對一些SQL的一致性讀取策略和鎖定策略有很大影響。將這些特性歸納成如表20-16所示的內容，以便讀者查閱。

1: 在隔離等級是RC情況下，間隙鎖是用不到的，官方文件說明如下：

Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. For information about consistent reads, see Section 14.8.2.3, “Consistent Nonlocking Reads”.

#For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE state. locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key#ecking.#說明網址為：https://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html

2: 在可重複讀取隔離等級下，若索引是唯一的，且查找也是唯一的，則也不用間隙鎖，否則就用間隙鎖，官方說明如下：

REPEATABLE READ

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, theseSELECT statements are consistent to Sspecistt. Nonlocking Reads」.

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, orther the statement uses a unique index with a unique search condition, or or a search -type search condition.

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 14.8.1, “InnoDBd

官方文件說明網址為：https://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html

9.何時使用表鎖?

對於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 和 t2 here];

COMMIT;

##UNLOCK TABLES;

# 10.關於死鎖

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

在上面的例子中，兩個事務都需要取得對方持有的排他鎖定才能繼續完成事務，而這種循環鎖定等待就是典型的死鎖。

發生死鎖後，InnoDB一般都能自動偵測到，並使一個事務釋放鎖定並回退，另一個事務取得鎖定，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB並不能完全自動偵測到死鎖，這需要透過設定鎖等待逾時參數innodb_lock_wait_timeout來解決。需要說明的是，這個參數並不是只用來解決死鎖問題，在同時存取比較高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會佔用大量電腦資源，造成嚴重效能問題，甚至拖跨資料庫。我們透過設定合適的鎖來等待超時閾值，可以避免這種情況發生。

通常來說，死鎖都是應用設計的問題，透過調整業務流程、資料庫物件設計、交易大小，以及存取資料庫的SQL語句，絕大部分死鎖都可以避免。

下面就透過實例來介紹幾種避免死鎖的常用方法。

（1）在應用程式中，如果不同的程式會並發存取多個表，應盡量約定以相同的順序來存取表，這樣可以大幅降低產生死鎖的機會。在下面的例子中，由於兩個session存取兩個表的順序不同，發生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可以避免。

（2）在程式以批次方式處理資料的時候，如果事先對資料排序，保證每個執行緒以固定的順序來處理記錄，也可以大幅降低出現死鎖的可能。

（3）在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖，更新時再申請排他鎖，因為當使用者申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖衝突，甚至死鎖。具體演示可參考20.3.3小節的範例。

（4）前面講過，在REPEATABLE-READ隔離等級下，如果兩個執行緒同時對相同條件記錄用SELECT...FOR UPDATE加排他鎖，在沒有符合該條件記錄情況下，兩個執行緒都會加鎖成功。程式發現記錄尚不存在，就試圖插入一筆新記錄，如果兩個執行緒都這麼做，就會出現死鎖。這種情況下，將隔離等級改成READ COMMITTED，就可避免問題。

（5）當隔離等級為READ COMMITTED時，如果兩個執行緒都先執行SELECT...FOR UPDATE，判斷是否有符合條件的記錄，如果沒有，就插入記錄。此時，只有一個執行緒能插入成功，另一個執行緒會出現鎖定等待，當第1個執行緒提交後，第2個執行緒會因主鍵重出錯，但雖然這個執行緒出錯了，卻會獲得一個排他鎖！這時如果有第3個線程又來申請排他鎖，也會出現死鎖。

對於這種情況，可以直接做插入操作，然後再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執行ROLLBACK釋放獲得的排他鎖。

儘管透過上面介紹的設計和SQL最佳化等措施，可以大幅減少死鎖，但死鎖很難完全避免。因此，在程式設計中總是捕捉並處理死鎖異常是一個很好的程式設計習慣。

如果出現死鎖，可以用SHOW INNODB STATUS指令來決定最後一個死鎖產生的原因。傳回結果包括死鎖相關事務的詳細信息，如引發死鎖的SQL語句，事務已經獲得的鎖，正在等待什麼鎖，以及被回滾的事務等。據此可以分析死鎖產生的原因和改進措施。

下面是一段SHOW INNODB STATUS輸出的範例：

mysql> show innodb status \G

InnoDB 小結

#本章重點介紹了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表，本章主要討論了以下幾項內容。

InnoDB的行鎖定是基於鎖定引導實現的，如果不透過索引存取數據，InnoDB會使用表鎖定。

介紹了InnoDB間隙鎖定（Next-key)機制，以及InnoDB使用間隙鎖定的原因。

在不同的隔離等級下，InnoDB的鎖定機制和一致性讀取策略不同。

MySQL的復原和複製對InnoDB鎖定機制和一致性讀取策略也有較大影響。

鎖定衝突甚至死鎖很難完全避免。

在了解InnoDB鎖定特性後，使用者可以透過設計和SQL調整等措施減少鎖定衝突和死鎖，包括：

盡量使用較低的隔離等級；

精心設計索引，並儘量使用索引存取數據，使加鎖更精確，從而減少鎖定衝突的機會。

選擇合理的事務大小，小事務發生鎖定衝突的幾率也更小。

給記錄集顯示加鎖時，最好一次要求足夠等級的鎖定。例如要修改資料的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產生死鎖。

不同的程式存取一組表時，應盡量約定以相同的順序存取各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會。

盡量用相等條件存取數據，這樣可以避免間隙鎖對並發插入的影響。

不要申請超過實際需要的鎖定等級；除非必須，查詢時不要顯示加鎖。

對於一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能性。

以上是一文詳解MySql行級鎖與表級鎖的詳細內容。更多資訊請關注PHP中文網其他相關文章！