本文希望幫助讀者更加深刻地理解 MySQL 中的鎖和事務，從而在業務系統開發過程中更好地優化與數據庫的交互。

[[275104]]

圖片來自 Pexels

鎖的分類及特性

數據庫鎖定機制簡單來說，就是數據庫為了保證數據的一致性，而使各種共享資源在被并發訪問時變得有序所設計的一種規則。

對于任何一種數據庫來說都需要有相應的鎖定機制，所以 MySQL 自然也不能例外。

MySQL 數據庫由于其自身架構的特點，存在多種數據存儲引擎，每種存儲引擎所針對的應用場景特點都不太一樣。

為了滿足各自特定應用場景的需求，每種存儲引擎的鎖定機制都是為各自所面對的特定場景而優化設計，所以各存儲引擎的鎖定機制也有較大區別。

MySQL 各存儲引擎使用了三種類型(級別)的鎖定機制：

• 表級鎖定
• 行級鎖定
• 頁級鎖定

表級鎖定(table-level)

表級別的鎖定是 MySQL 各存儲引擎中最大顆粒度的鎖定機制。該鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統負面影響最小。

所以獲取鎖和釋放鎖的速度很快。由于表級鎖定一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。

當然，鎖定顆粒度大所帶來最大的負面影響就是出現鎖定資源爭用的概率也會最高，致使并大度大打折扣。

使用表級鎖定的主要是 MyISAM，MEMORY，CSV 等一些非事務性存儲引擎。

行級鎖定(row-level)

行級鎖定最大的特點就是鎖定對象的顆粒度很小，也是目前各大數據庫管理軟件所實現的鎖定顆粒度最小的。

由于鎖定顆粒度很小，所以發生鎖定資源爭用的概率也最小，能夠給予應用程序盡可能大的并發處理能力而提高一些需要高并發應用系統的整體性能。

雖然能夠在并發處理能力上面有較大的優勢，但是行級鎖定也因此帶來了不少弊端。

由于鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大了。

此外，行級鎖定也最容易發生死鎖。使用行級鎖定的主要是 InnoDB 存儲引擎。

頁級鎖定(page-level)

頁級鎖定是 MySQL 中比較獨特的一種鎖定級別，在其他數據庫管理軟件中也并不是太常見。

頁級鎖定的特點是鎖定顆粒度介于行級鎖定與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的并發處理能力也同樣是介于上面二者之間。另外，頁級鎖定和行級鎖定一樣，會發生死鎖。

在數據庫實現資源鎖定的過程中，隨著鎖定資源顆粒度的減小，鎖定相同數據量的數據所需要消耗的內存數量是越來越多的，實現算法也會越來越復雜。

不過，隨著鎖定資源顆粒度的減小，應用程序的訪問請求遇到鎖等待的可能性也會隨之降低，系統整體并發度也隨之提升。使用頁級鎖定的主要是 BerkeleyDB 存儲引擎。

總的來說，MySQL 這三種鎖的特性可大致歸納如下：

• 表級鎖：開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖沖突的概率最高，并發度最低。
• 行級鎖：開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。
• 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間;會出現死鎖;鎖定粒度界于表鎖和行鎖之間，并發度一般。

適用：從鎖的角度來說，表級鎖更適合于以查詢為主，只有少量按索引條件更新數據的應用，如 Web 應用。

而行級鎖則更適合于有大量按索引條件并發更新少量不同數據，同時又有并發查詢的應用，如一些在線事務處理(OLTP)系統。

表級鎖定(MyISAM 舉例)

由于 MyISAM 存儲引擎使用的鎖定機制完全是由 MySQL 提供的表級鎖定實現，所以下面我們將以 MyISAM 存儲引擎作為示例存儲引擎。

MySQL 表級鎖的鎖模式

MySQL 的表級鎖有兩種模式：

• 表共享讀鎖(Table Read Lock)
• 表獨占寫鎖(Table Write Lock)

鎖模式的兼容性：

• 對 MyISAM 表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求。
• 對 MyISAM 表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作。
• MyISAM 表的讀操作與寫操作之間，以及寫操作之間是串行的。當一個線程獲得對一個表的寫鎖后，只有持有鎖的線程可以對表進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。

總結：表鎖，讀鎖會阻塞寫，不會阻塞讀。而寫鎖則會把讀寫都阻塞。

如何加表鎖

MyISAM 在執行查詢語句(SELECT)前，會自動給涉及的所有表加讀鎖，在執行更新操作(UPDATE、DELETE、INSERT等)前，會自動給涉及的表加寫鎖。

這個過程并不需要用戶干預，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。

顯示加鎖：

• 共享讀鎖：lock table tableName read
• 獨占寫鎖：lock table tableName write
• 同時加多鎖：lock table t1 write，t2 read
• 批量解鎖：unlock tables

MyISAM 表鎖優化建議

對于 MyISAM 存儲引擎，雖然使用表級鎖定在鎖定實現的過程中比實現行級鎖定或者頁級鎖定所帶來的附加成本都要小，鎖定本身所消耗的資源也是最少。

但是由于鎖定的顆粒度比較大，所以造成鎖定資源的爭用情況也會比其他的鎖定級別都要多，從而在較大程度上會降低并發處理能力。

所以，在優化 MyISAM 存儲引擎鎖定問題的時候，最關鍵的就是如何讓其提高并發度。

由于鎖定級別是不可能改變的了，所以我們首先需要盡可能讓鎖定的時間變短，然后就是讓可能并發進行的操作盡可能的并發。

①查詢表級鎖爭用情況

MySQL 內部有兩組專門的狀態變量記錄系統內部鎖資源爭用情況：

mysql> show status like 'table%'; 
+----------------------------+---------+ 
| Variable_name              | Value   | 
+----------------------------+---------+ 
| Table_locks_immediate      | 100     | 
| Table_locks_waited         | 11      | 
+----------------------------+---------+ 

這里有兩個狀態變量記錄 MySQL 內部表級鎖定的情況，兩個變量說明如下：

• Table_locks_immediate：產生表級鎖定的次數。
• Table_locks_waited：出現表級鎖定爭用而發生等待的次數;此值越高則說明存在著越嚴重的表級鎖爭用情況。

此外，MyISAM 的讀寫鎖調度是寫優先，這也是 MyISAM 不適合做寫為主表的存儲引擎的原因。

因為寫鎖后，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永久阻塞。

兩個狀態值都是從系統啟動后開始記錄，出現一次對應的事件則數量加 1。如果這里的 Table_locks_waited 狀態值比較高，那么說明系統中表級鎖定爭用現象比較嚴重，就需要進一步分析為什么會有較多的鎖定資源爭用了。

②縮短鎖定時間

如何讓鎖定時間盡可能的短呢?唯一的辦法就是讓我們的 Query 執行時間盡可能的短：

• 盡量減少大的復雜 Query，將復雜 Query 分拆成幾個小的 Query 分布進行。
• 盡可能的建立足夠高效的索引，讓數據檢索更迅速。
• 盡量讓 MyISAM 存儲引擎的表只存放必要的信息，控制字段類型。
• 利用合適的機會優化 MyISAM 表數據文件。

③分離能并行的操作

說到 MyISAM 的表鎖，而且是讀寫互相阻塞的表鎖，可能有些人會認為在 MyISAM 存儲引擎的表上就只能是完全的串行化，沒辦法再并行了。

大家不要忘記了，MyISAM 的存儲引擎還有一個非常有用的特性，那就是 Concurrent Insert(并發插入)的特性。

MyISAM 存儲引擎有一個控制是否打開 Concurrent Insert 功能的參數選項：concurrent_insert，可以設置為 0，1 或者 2。

三個值的具體說明如下：

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

可以利用 MyISAM 存儲引擎的并發插入特性，來解決應用中對同一表查詢和插入的鎖爭用。

例如，將 concurrent_insert 系統變量設為 2，總是允許并發插入;同時，通過定期在系統空閑時段執行 OPTIMIZE TABLE 語句來整理空間碎片，收回因刪除記錄而產生的中間空洞。

④合理利用讀寫優先級

MyISAM 存儲引擎的讀寫是互相阻塞的，那么，一個進程請求某個 MyISAM 表的讀鎖，同時另一個進程也請求同一表的寫鎖，MySQL 如何處理呢?

答案是寫進程先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀鎖請求之前。

這是因為 MySQL 的表級鎖定對于讀和寫是有不同優先級設定的，默認情況下是寫優先級要大于讀優先級。

所以，如果我們可以根據各自系統環境的差異決定讀與寫的優先級：

通過執行命令 SET LOW_PRIORITY_UPDATES=1，使該連接讀比寫的優先級高。

如果我們的系統是一個以讀為主，可以設置此參數，如果以寫為主，則不用設置。

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

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

另外，MySQL 也提供了一種折中的辦法來調節讀寫沖突，即給系統參數 max_write_lock_count 設置一個合適的值，當一個表的讀鎖達到這個值后，MySQL 就暫時將寫請求的優先級降低，給讀進程一定獲得鎖的機會。

這里還要強調一點：一些需要長時間運行的查詢操作，也會使寫進程“餓死”。

因此，應用中應盡量避免出現長時間運行的查詢操作，不要總想用一條 SELECT 語句來解決問題，因為這種看似巧妙的 SQL 語句，往往比較復雜，執行時間較長。

在可能的情況下可以通過使用中間表等措施對 SQL 語句做一定的“分解”，使每一步查詢都能在較短時間完成，從而減少鎖沖突。

如果復雜查詢不可避免，應盡量安排在數據庫空閑時段執行，比如一些定期統計可以安排在夜間執行。

InnoDB 默認采用行鎖，在未使用索引字段查詢時升級為表鎖。MySQL 這樣設計并不是給你挖坑。它有自己的設計目的。

即便你在條件中使用了索引字段，MySQL 會根據自身的執行計劃，考慮是否使用索引(所以 explain 命令中會有 possible_key 和 key)。

如果 MySQL 認為全表掃描效率更高，它就不會使用索引，這種情況下 InnoDB 將使用表鎖，而不是行鎖。

因此，在分析鎖沖突時，別忘了檢查 SQL 的執行計劃，以確認是否真正使用了索引。

關于執行計劃，第一種情況：全表更新。事務需要更新大部分或全部數據，且表又比較大。

若使用行鎖，會導致事務執行效率低，從而可能造成其他事務長時間鎖等待和更多的鎖沖突。

第二種情況：多表級聯。事務涉及多個表，比較復雜的關聯查詢，很可能引起死鎖，造成大量事務回滾。

這種情況若能一次性鎖定事務涉及的表，從而可以避免死鎖、減少數據庫因事務回滾帶來的開銷。

行級鎖定

行級鎖定不是 MySQL 自己實現的鎖定方式，而是由其他存儲引擎自己所實現的，如廣為大家所知的 InnoDB 存儲引擎，以及 MySQL 的分布式存儲引擎 NDB Cluster 等都是實現了行級鎖定。

考慮到行級鎖定均由各個存儲引擎自行實現，而且具體實現也各有差別，而 InnoDB 是目前事務型存儲引擎中使用最為廣泛的存儲引擎，所以這里我們就主要分析一下 InnoDB 的鎖定特性。

InnoDB 鎖定模式及實現機制

總的來說，InnoDB 的鎖定機制和 Oracle 數據庫有不少相似之處。InnoDB 的行級鎖定同樣分為兩種類型，共享鎖和排他鎖，而在鎖定機制的實現過程中為了讓行級鎖定和表級鎖定共存，InnoDB 也同樣使用了意向鎖(表級鎖定)的概念，也就有了意向共享鎖和意向排他鎖這兩種。

當一個事務需要給自己需要的某個資源加鎖的時候，如果遇到一個共享鎖正鎖定著自己需要的資源的時候，自己可以再加一個共享鎖，不過不能加排他鎖。

但是，如果遇到自己需要鎖定的資源已經被一個排他鎖占有之后，則只能等待該鎖定釋放資源之后自己才能獲取鎖定資源并添加自己的鎖定。

而意向鎖的作用就是當一個事務在需要獲取資源鎖定的時候，如果遇到自己需要的資源已經被排他鎖占用的時候，該事務需要在鎖定行的表上面添加一個合適的意向鎖。

如果自己需要一個共享鎖，那么就在表上面添加一個意向共享鎖。而如果自己需要的是某行(或者某些行)上面添加一個排他鎖的話，則先在表上面添加一個意向排他鎖。

意向共享鎖可以同時并存多個，但是意向排他鎖同時只能有一個存在。

所以，可以說 InnoDB 的鎖定模式實際上可以分為四種：

• 共享鎖(S)
• 排他鎖(X)
• 意向共享鎖(IS)
• 意向排他鎖(IX)

我們可以通過以下表格來總結上面這四種鎖的共存邏輯關系：

如果一個事務請求的鎖模式與當前的鎖兼容，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 方式獲得排他鎖。

InnoDB 行鎖實現方式

InnoDB 行鎖是通過給索引上的索引項加鎖來實現的，只有通過索引條件檢索數據，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖。

在實際應用中，要特別注意 InnoDB 行鎖的這一特性，不然的話，可能導致大量的鎖沖突，從而影響并發性能。

下面通過一些實際例子來加以說明：

• 在不通過索引條件查詢的時候，InnoDB 確實使用的是表鎖，而不是行鎖。
• 由于 MySQL 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖沖突的。
• 當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會使用行鎖來對數據加鎖。
• 即便在條件中使用了索引字段，但是否使用索引來檢索數據是由 MySQL 通過判斷不同執行計劃的代價來決定的，如果 MySQL 認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引。

這種情況下 InnoDB 將使用表鎖，而不是行鎖。因此，在分析鎖沖突時，別忘了檢查 SQL 的執行計劃，以確認是否真正使用了索引。

間隙鎖(Next-Key鎖)

當我們用范圍條件而不是相等條件檢索數據，并請求共享或排他鎖時，InnoDB 會給符合條件的已有數據記錄的索引項加鎖。

對于鍵值在條件范圍內但并不存在的記錄，叫做“間隙(GAP)”，InnoDB 也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖(Next-Key鎖)。

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

mysql> select * from emp where empid > 100 for update; 

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

InnoDB 使用間隙鎖的目的：

• 防止幻讀，以滿足相關隔離級別的要求(關于事務的隔離級別)。對于上面的例子，要是不使用間隙鎖，如果其他事務插入了 empid 大于 100 的任何記錄，那么本事務如果再次執行上述語句，就會發生幻讀。
• 為了滿足其恢復和復制的需要。很顯然，在使用范圍條件檢索并鎖定記錄時，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定鍵值范圍內的任何數據。在某些場景下這可能會對性能造成很大的危害。

除了間隙鎖給 InnoDB 帶來性能的負面影響之外，通過索引實現鎖定的方式還存在其他幾個較大的性能隱患：

• 當 Query 無法利用索引的時候，InnoDB 會放棄使用行級別鎖定而改用表級別的鎖定，造成并發性能的降低。
• 當 Query 使用的索引并不包含所有過濾條件的時候，數據檢索使用到的索引鍵所指向的數據可能有部分并不屬于該 Query 的結果集的行列，但是也會被鎖定，因為間隙鎖鎖定的是一個范圍，而不是具體的索引鍵。
• 當 Query 在使用索引定位數據的時候，如果使用的索引鍵一樣但訪問的數據行不同的時候(索引只是過濾條件的一部分)，一樣會被鎖定。

因此，在實際應用開發中，尤其是并發插入比較多的應用，我們要盡量優化業務邏輯，盡量使用相等條件來訪問更新數據，避免使用范圍條件。

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

死鎖

上文講過，MyISAM 表鎖是 deadlock free 的，這是因為 MyISAM 總是一次獲得所需的全部鎖，要么全部滿足，要么等待，因此不會出現死鎖。

但在 InnoDB 中，除單個 SQL 組成的事務外，鎖是逐步獲得的，當兩個事務都需要獲得對方持有的排他鎖才能繼續完成事務，這種循環鎖等待就是典型的死鎖。

在 InnoDB 的事務管理和鎖定機制中，有專門檢測死鎖的機制，會在系統中產生死鎖之后的很短時間內就檢測到該死鎖的存在。

當 InnoDB 檢測到系統中產生了死鎖之后，InnoDB 會通過相應的判斷來選這產生死鎖的兩個事務中較小的事務來回滾，而讓另外一個較大的事務成功完成。

那 InnoDB 是以什么來為標準判定事務的大小的呢?MySQL 官方手冊中也提到了這個問題，實際上在 InnoDB 發現死鎖之后，會計算出兩個事務各自插入、更新或者刪除的數據量來判定兩個事務的大小。也就是說哪個事務所改變的記錄條數越多，在死鎖中就越不會被回滾掉。

但是有一點需要注意的就是，當產生死鎖的場景中涉及到不止 InnoDB 存儲引擎的時候，InnoDB 是沒辦法檢測到該死鎖的，這時候就只能通過鎖定超時限制參數 InnoDB_lock_wait_timeout 來解決。

需要說明的是，這個參數并不是只用來解決死鎖問題，在并發訪問比較高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會占用大量計算機資源，造成嚴重性能問題，甚至拖跨數據庫。我們通過設置合適的鎖等待超時閾值，可以避免這種情況發生。

通常來說，死鎖都是應用設計的問題，通過調整業務流程、數據庫對象設計、事務大小，以及訪問數據庫的 SQL 語句，絕大部分死鎖都可以避免。

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

• 在應用中，如果不同的程序會并發存取多個表，應盡量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。
• 在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能。
• 在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖，更新時再申請排他鎖，因為當用戶申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。
• 在 REPEATABLE-READ 隔離級別下，如果兩個線程同時對相同條件記錄用 SELECT...FOR UPDATE 加排他鎖，在沒有符合該條件記錄情況下，兩個線程都會加鎖成功。

程序發現記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這么做，就會出現死鎖。這種情況下，將隔離級別改成 READ COMMITTED，就可避免問題。

• 當隔離級別為 READ COMMITTED 時，如果兩個線程都先執行 SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。

此時，只有一個線程能插入成功，另一個線程會出現鎖等待，當第一個線程提交后，第二個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖。這時如果有第三個線程又來申請排他鎖，也會出現死鎖。

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

什么時候使用表鎖

對于 InnoDB 表，在絕大部分情況下都應該使用行級鎖，因為事務和行鎖往往是我們之所以選擇 InnoDB 表的理由。

但在個別特殊事務中，也可以考慮使用表級鎖：

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

當然，應用中這兩種事務不能太多，否則，就應該考慮使用 MyISAM 表了。

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

• 使用 LOCK TABLES 雖然可以給 InnoDB 加表級鎖，但必須說明的是，表鎖不是由 InnoDB 存儲引擎層管理的，而是由其上一層──MySQL Server 負責的。

僅當 autocommit=0(不自動提交，默認是自動提交的)、InnoDB_table_locks=1(默認設置)時，InnoDB 層才能知道 MySQL 加的表鎖，MySQL Server 也才能感知 InnoDB 加的行鎖。

這種情況下，InnoDB 才能自動識別涉及表級鎖的死鎖，否則，InnoDB 將無法自動檢測并處理這種死鎖。

• 在用 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; 

InnoDB 行鎖優化建議

InnoDB 存儲引擎由于實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體并發處理能力方面要遠遠優于 MyISAM 的表級鎖定的。

當系統并發量較高的時候，InnoDB 的整體性能和 MyISAM 相比就會有比較明顯的優勢了。

但是，InnoDB 的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓 InnoDB 的整體性能表現不僅不能比 MyISAM 高，甚至可能會更差。

①要想合理利用 InnoDB 的行級鎖定，做到揚長避短，我們必須做好以下工作：

• 盡可能讓所有的數據檢索都通過索引來完成，從而避免 InnoDB 因為無法通過索引鍵加鎖而升級為表級鎖定。
• 合理設計索引，讓 InnoDB 在索引鍵上面加鎖的時候盡可能準確，盡可能的縮小鎖定范圍，避免造成不必要的鎖定而影響其他 Query 的執行。
• 盡可能減少基于范圍的數據檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄。
• 盡量控制事務的大小，減少鎖定的資源量和鎖定時間長度。
• 在業務環境允許的情況下，盡量使用較低級別的事務隔離，以減少 MySQL 因為實現事務隔離級別所帶來的附加成本。

②由于 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     | 
+-------------------------------+-------+ 

InnoDB 的行級鎖定狀態變量不僅記錄了鎖定等待次數，還記錄了鎖定總時長，每次平均時長，以及最大時長，此外還有一個非累積狀態量顯示了當前正在等待鎖定的等待數量。

對各個狀態量的說明如下：

• InnoDB_row_lock_current_waits：當前正在等待鎖定的數量。
• InnoDB_row_lock_time：從系統啟動到現在鎖定總時間長度。
• InnoDB_row_lock_time_avg：每次等待所花平均時間。
• InnoDB_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間。
• InnoDB_row_lock_waits：系統啟動后到現在總共等待的次數。

對于這五個狀態變量，比較重要的三項是：

• InnoDB_row_lock_time_avg(等待平均時長)
• InnoDB_row_lock_waits(等待總次數)
• InnoDB_row_lock_time(等待總時長)

尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什么會有如此多的等待，然后根據分析結果著手指定優化計劃。

如果發現鎖爭用比較嚴重，如 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比較高。

還可以通過設置 InnoDB Monitors 來進一步觀察發生鎖沖突的表、數據行等，并分析鎖爭用的原因。

鎖沖突的表、數據行等，并分析鎖爭用的原因。具體方法如下：

mysql> create table InnoDB_monitor(a INT) engine=InnoDB; 

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

mysql> show engine InnoDB status; 

監視器可以通過發出下列語句來停止查看：

mysql> drop table InnoDB_monitor; 

設置監視器后，會有詳細的當前鎖等待的信息，包括表名、鎖類型、鎖定記錄的情況等，便于進行進一步的分析和問題的確定。可能會有讀者朋友問為什么要先創建一個叫 InnoDB_monitor 的表呢?

因為創建該表實際上就是告訴 InnoDB 我們開始要監控他的細節狀態了，然后 InnoDB 就會將比較詳細的事務以及鎖定信息記錄進入 MySQL 的 errorlog 中，以便我們后面做進一步分析使用。

打開監視器以后，默認情況下每 15 秒會向日志中記錄監控的內容，如果長時間打開會導致 .err 文件變得非常的巨大。

所以用戶在確認問題原因之后，要記得刪除監控表以關閉監視器，或者通過使用“--console”選項來啟動服務器以關閉寫日志文件。

查看死鎖、解除鎖

結合上面對表鎖和行鎖的分析情況，解除正在死鎖的狀態有兩種方法：

第一種

①查詢是否鎖表

show OPEN TABLES where In_use > 0; 

②查詢進程(如果您有 SUPER 權限，您可以看到所有線程。否則，您只能看到您自己的線程)

show processlist 

③殺死進程 id(就是上面命令的 id 列)

kill id 

第二種

①查看下在鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 

②殺死進程 id(就是上面命令的 trx_mysql_thread_id 列)

kill 線程ID 

例子：

• 查出死鎖進程：SHOW PROCESSLIST
• 殺掉進程：KILL 420821

其他關于查看死鎖的命令：

①查看當前的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 

②查看當前鎖定的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

③查看當前等鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

事務

MySQL 事務屬性

事務是由一組 SQL 語句組成的邏輯處理單元，事務具有 ACID 屬性：

• 原子性(Atomicity)：事務是一個原子操作單元。在當時原子是不可分割的最小元素，其對數據的修改，要么全部成功，要么全部都不成功。
• 一致性(Consistent)：事務開始到結束的時間段內，數據都必須保持一致狀態。
• 隔離性(Isolation)：數據庫系統提供一定的隔離機制，保證事務在不受外部并發操作影響的"獨立"環境執行。
• 持久性(Durable)：事務完成后，它對于數據的修改是永久性的，即使出現系統故障也能夠保持。

事務常見問題

①更新丟失(Lost Update)

原因：當多個事務選擇同一行操作，并且都是基于最初選定的值，由于每個事務都不知道其他事務的存在，就會發生更新覆蓋的問題。類比 Github 提交沖突。

②臟讀(Dirty Reads)

原因：事務 A 讀取了事務 B 已經修改但尚未提交的數據。若事務 B 回滾數據，事務 A 的數據存在不一致性的問題。

③不可重復讀(Non-Repeatable Reads)

原因：事務 A 第一次讀取最初數據，第二次讀取事務 B 已經提交的修改或刪除數據。導致兩次讀取數據不一致。不符合事務的隔離性。

④幻讀(Phantom Reads)

原因：事務 A 根據相同條件第二次查詢到事務 B 提交的新增數據，兩次數據結果集不一致。不符合事務的隔離性。

幻讀和臟讀有點類似，臟讀是事務 B 里面修改了數據，幻讀是事務 B 里面新增了數據。

事務的隔離級別

數據庫的事務隔離越嚴格，并發副作用越小，但付出的代價也就越大。這是因為事務隔離實質上是將事務在一定程度上"串行"進行，這顯然與"并發"是矛盾的。

根據自己的業務邏輯，權衡能接受的最大副作用。從而平衡了"隔離" 和 "并發"的問題。MySQL 默認隔離級別是可重復讀。

臟讀，不可重復讀，幻讀，其實都是數據庫讀一致性問題，必須由數據庫提供一定的事務隔離機制來解決：

+------------------------------+---------------------+--------------+--------------+--------------+ 
| 隔離級別                      | 讀數據一致性         | 臟讀         | 不可重復 讀   | 幻讀         | 
+------------------------------+---------------------+--------------+--------------+--------------+ 
| 未提交讀(Read uncommitted)    | 最低級別            | 是            | 是           | 是           |  
+------------------------------+---------------------+--------------+--------------+--------------+ 
| 已提交讀(Read committed)      | 語句級              | 否           | 是           | 是           | 
+------------------------------+---------------------+--------------+--------------+--------------+ 
| 可重復讀(Repeatable read)     | 事務級              | 否           | 否           | 是           | 
+------------------------------+---------------------+--------------+--------------+--------------+ 
| 可序列化(Serializable)        | 最高級別，事務級     | 否           | 否           | 否           | 
+------------------------------+---------------------+--------------+--------------+--------------+ 

查看當前數據庫的事務隔離級別：show variables like 'tx_isolation'。

mysql> show variables like 'tx_isolation'; 
+---------------+-----------------+ 
| Variable_name | Value           | 
+---------------+-----------------+ 
| tx_isolation  | REPEATABLE-READ | 
+---------------+-----------------+ 

事務級別的設置

1.未提交讀（READ UNCOMMITED） 解決的障礙：無； 引入的問題：臟讀 
    set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
 
2.已提交讀 （READ COMMITED） 解決的障礙：臟讀； 引入的問題：不可重復讀 
    set SESSION TRANSACTION ISOLATION LEVEL read committed; 
 
3.可重復讀（REPEATABLE READ）解決的障礙：不可重復讀； 引入的問題： 
    set SESSION TRANSACTION ISOLATION LEVEL repeatable read; 
 
4.可串行化（SERIALIZABLE）解決的障礙：可重復讀； 引入的問題：鎖全表，性能低下 
    set SESSION TRANSACTION ISOLATION LEVEL repeatable read; 

總結：事務隔離級別為可重復讀時，如果有索引(包括主鍵索引)的時候，以索引列為條件更新數據，會存在間隙鎖間、行鎖、頁鎖的問題，從而鎖住一些行;如果沒有索引，更新數據時會鎖住整張表。

事務隔離級別為串行化時，讀寫數據都會鎖住整張表，隔離級別越高，越能保證數據的完整性和一致性，但是對并發性能的影響也越大。

對于多數應用程序，可以優先考慮把數據庫系統的隔離級別設為 Read Committed，它能夠避免臟讀取，而且具有較好的并發性能。

事務保存點，實現部分回滾

定義保存點，以及回滾到指定保存點前狀態的語法如下：

• 定義保存點：SAVEPOINT 保存點名。
• 回滾到指定保存點：ROLLBACK TO SAVEPOINT 保存點名。

1、查看user表中的數據 
 
mysql> select * from user; 
+-----+----------+-----+------+ 
| mid | name | scx | word | 
+-----+----------+-----+------+ 
| 1 | zhangsan | 0 | NULL | 
| 2 | wangwu    | 1 | NULL | 
+-----+----------+-----+------+ 
2 rows in set (0.05 sec) 
2、mysql事務開始 
 
mysql> BEGIN; -- 或者start transaction； 
Query OK, 0 rows affected (0.00 sec) 
3、向表user中插入2條數據 
 
mysql> INSERT INTO user VALUES ('3','one','0',''); 
Query OK, 1 row affected (0.08 sec) 
mysql> INSERT INTO user VALUES ('4,'two','0',''); 
Query OK, 1 row affected (0.00 sec) 
mysql> select * from user; 
+-----+----------+-----+------+ 
| mid | name | scx | word | 
+-----+----------+-----+------+ 
| 1 | zhangsan | 0 | NULL | 
| 2 | wangwu    | 1 | NULL | 
| 3 | one            | 0 | | 
| 4 | two             | 0 | | 
+-----+----------+-----+------+ 
4 rows in set (0.00 sec) 
4、指定保存點，保存點名為test 
 
mysql> SAVEPOINT test; 
Query OK, 0 rows affected (0.00 sec) 
5、向表user中插入第3條數據 
 
mysql> INSERT INTO user VALUES ('5','three','0',''); 
Query OK, 1 row affected (0.00 sec) 
mysql> select * from user; 
+-----+----------+-----+------+ 
| mid | name | scx | word | 
+-----+----------+-----+------+ 
| 1 | zhangsan | 0 | NULL | 
| 2 | wangwu | 1 | NULL | 
| 3 | one | 0 | | 
| 4 | two | 0 | | 
| 5 | three | 0 | | 
+-----+----------+-----+------+ 
5 rows in set (0.02 sec) 
6、回滾到保存點test 
 
mysql> ROLLBACK TO SAVEPOINT test; 
Query OK, 0 rows affected (0.31 sec) 
mysql> select * from user; 
+-----+----------+-----+------+ 
| mid | name | scx | word | 
+-----+----------+-----+------+ 
| 1 | zhangsan | 0 | NULL | 
| 2 | wangwu    | 1 | NULL | 
| 3 | one            | 0 | | 
| 4 | two            | 0 | | 
+-----+----------+-----+------+ 
4 rows in set (0.00 sec) 

我們可以看到保存點 test 以后插入的記錄沒有顯示了，即成功團滾到了定義保存點 test 前的狀態。利用保存點可以實現只提交事務中部分處理的功能。

事務控制語句

BEGIN或START TRANSACTION；顯式地開啟一個事務； 
COMMIT；                  也可以使用COMMIT WORK，不過二者是等價的。COMMIT會提交事務，并使已對數據庫進行的所有修改成為永久性的； 
ROLLBACK；                有可以使用ROLLBACK WORK，不過二者是等價的。回滾會結束用戶的事務，并撤銷正在進行的所有未提交的修改； 
SAVEPOINT identifier；    SAVEPOINT允許在事務中創建一個保存點，一個事務中可以有多個SAVEPOINT； 
RELEASE SAVEPOINT identifier；    刪除一個事務的保存點，當沒有指定的保存點時，執行該語句會拋出一個異常； 
ROLLBACK TO identifier；   把事務回滾到標記點； 
SET TRANSACTION；   用來設置事務的隔離級別。InnoDB存儲引擎提供事務的隔離級別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。 
 
 
  用 BEGIN, ROLLBACK, COMMIT來實現 
  BEGIN 開始一個事務 
  ROLLBACK 事務回滾 
  COMMIT 事務確認 
  直接用 SET 來改變 MySQL 的自動提交模式: 
  SET AUTOCOMMIT=0或者off 禁止自動提交 
  SET AUTOCOMMIT=1或者on 開啟自動提交