MySQL鎖總結

01-26

鎖是計算機協調多個進程或線程並發訪問某一資源的機制。鎖保證數據並發訪問的一致性、有效性；鎖衝突也是影響資料庫並發訪問性能的一個重要因素。鎖是Mysql在伺服器層和存儲引擎層的的並發控制。

加鎖是消耗資源的，鎖的各種操作，包括獲得鎖、檢測鎖是否是否已解除、釋放鎖等。

鎖機制

共享鎖與排他鎖

共享鎖（讀鎖）：其他事務可以讀，但不能寫。
排他鎖（寫鎖）：其他事務不能讀取，也不能寫。

粒度鎖

MySQL 不同的存儲引擎支持不同的鎖機制，所有的存儲引擎都以自己的方式顯現了鎖機制，伺服器層完全不了解存儲引擎中的鎖實現：

MyISAM 和 MEMORY 存儲引擎採用的是表級鎖（table-level locking）
BDB 存儲引擎採用的是頁面鎖（page-level locking），但也支持表級鎖
InnoDB 存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認情況下是採用行級鎖。

默認情況下，表鎖和行鎖都是自動獲得的，不需要額外的命令。

但是在有的情況下，用戶需要明確地進行鎖表或者進行事務的控制，以便確保整個事務的完整性，這樣就需要使用事務控制和鎖定語句來完成。

不同粒度鎖的比較：

表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，並發度最低。

這些存儲引擎通過總是一次性同時獲取所有需要的鎖以及總是按相同的順序獲取表鎖來避免死鎖。
表級鎖更適合於以查詢為主，並發用戶少，只有少量按索引條件更新數據的應用，如Web 應用

行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，並發度也最高。

最大程度的支持並發，同時也帶來了最大的鎖開銷。
在 InnoDB 中，除單個 SQL 組成的事務外，
鎖是逐步獲得的，這就決定了在 InnoDB 中發生死鎖是可能的。
行級鎖只在存儲引擎層實現，而Mysql伺服器層沒有實現。行級鎖更適合於有大量按索引條件並發更新少量不同數據，同時又有並發查詢的應用，如一些在線事務處理（OLTP）系統

頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，並發度一般。

MyISAM 表鎖

MyISAM表級鎖模式：

表共享讀鎖（Table Read Lock）：不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；
表獨佔寫鎖（Table Write Lock）：會阻塞其他用戶對同一表的讀和寫操作；

MyISAM 表的讀操作與寫操作之間，以及寫操作之間是串列的。當一個線程獲得對一個表的寫鎖後，只有持有鎖的線程可以對錶進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。

默認情況下，寫鎖比讀鎖具有更高的優先順序：當一個鎖釋放時，這個鎖會優先給寫鎖隊列中等候的獲取鎖請求，然後再給讀鎖隊列中等候的獲取鎖請求。（This ensures that updates to a table are not 「starved」 even when there is heavy SELECT activity for the table. However, if there are many updates for a table, SELECT statements wait until there are no more updates.）。

這也正是 MyISAM 表不太適合於有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。同時，一些需要長時間運行的查詢操作，也會使寫線程「餓死」，應用中應盡量避免出現長時間運行的查詢操作（在可能的情況下可以通過使用中間表等措施對SQL語句做一定的「分解」，使每一步查詢都能在較短時間完成，從而減少鎖衝突。如果複雜查詢不可避免，應盡量安排在資料庫空閑時段執行，比如一些定期統計可以安排在夜間執行）。

可以設置改變讀鎖和寫鎖的優先順序：

通過指定啟動參數low-priority-updates，使MyISAM引擎默認給予讀請求以優先的權利。
通過執行命令SET LOW_PRIORITY_UPDATES=1，使該連接發出的更新請求優先順序降低。
通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序。
給系統參數max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值後，MySQL就暫時將寫請求的優先順序降低，給讀進程一定獲得鎖的機會。

MyISAM加表鎖方法：

MyISAM 在執行查詢語句（SELECT）前，會自動給涉及的表加讀鎖，在執行更新操作

（UPDATE、DELETE、INSERT 等）前，會自動給涉及的表加寫鎖，這個過程並不需要用戶干預，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。

在自動加鎖的情況下，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，這也正是 MyISAM 表不會出現死鎖（Deadlock Free）的原因。

MyISAM存儲引擎支持並發插入，以減少給定表的讀和寫操作之間的爭用：

如果MyISAM表在數據文件中間沒有空閑塊，則行始終插入數據文件的末尾。在這種情況下，你可以自由混合併發使用MyISAM表的INSERT和SELECT語句而不需要加鎖——你可以在其他線程進行讀操作的時候，同時將行插入到MyISAM表中。文件中間的空閑塊可能是從表格中間刪除或更新的行而產生的。如果文件中間有空閑快，則並發插入會被禁用，但是當所有空閑塊都填充有新數據時，它又會自動重新啟用。要控制此行為，可以使用MySQL的concurrent_insert系統變數。

如果你使用LOCK TABLES顯式獲取表鎖，則可以請求READ LOCAL鎖而不是READ鎖，以便在鎖定表時，其他會話可以使用並發插入。

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

查詢表級鎖爭用情況：

可以通過檢查 table_locks_waited 和 table_locks_immediate 狀態變數來分析系統上的表鎖的爭奪，如果 Table_locks_waited 的值比較高，則說明存在著較嚴重的表級鎖爭用情況：

mysql> SHOW STATUS LIKE "Table%";+-----------------------+---------+| Variable_name | Value |+-----------------------+---------+| Table_locks_immediate | 1151552 || Table_locks_waited | 15324 |+-----------------------+---------+

InnoDB行級鎖和表級鎖

InnoDB鎖模式：

InnoDB 實現了以下兩種類型的行鎖：

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

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

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

鎖模式的兼容情況：

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

InnoDB加鎖方法：

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

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。其他 session 仍然可以查詢記錄，並也可以對該記錄加 share mode 的共享鎖。但是如果當前事務需要對該記錄進行更新操作，則很有可能造成死鎖。
排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查詢該記錄，但是不能對該記錄加共享鎖或排他鎖，而是等待獲得鎖

隱式鎖定：

InnoDB在事務執行過程中，使用兩階段鎖協議：

隨時都可以執行鎖定，InnoDB會根據隔離級別在需要的時候自動加鎖；

鎖只有在執行commit或者rollback的時候才會釋放，並且所有的鎖都是在同一時刻被釋放。

顯式鎖定：

select ... lock in share mode //共享鎖 select ... for update //排他鎖

select for update：

在執行這個 select 查詢語句的時候，會將對應的索引訪問條目進行上排他鎖（X 鎖），也就是說這個語句對應的鎖就相當於update帶來的效果。

select *** for update 的使用場景：為了讓自己查到的數據確保是最新數據，並且查到後的數據只允許自己來修改的時候，需要用到 for update 子句。

select lock in share mode ：in share mode 子句的作用就是將查找到的數據加上一個 share 鎖，這個就是表示其他的事務只能對這些數據進行簡單的select 操作，並不能夠進行 DML 操作。select *** lock in share mode 使用場景：為了確保自己查到的數據沒有被其他的事務正在修改，也就是說確保查到的數據是最新的數據，並且不允許其他人來修改數據。但是自己不一定能夠修改數據，因為有可能其他的事務也對這些數據使用了 in share mode 的方式上了 S 鎖。

性能影響：

select for update 語句，相當於一個 update 語句。在業務繁忙的情況下，如果事務沒有及時的commit或者rollback 可能會造成其他事務長時間的等待，從而影響資料庫的並發使用效率。

select lock in share mode 語句是一個給查找的數據上一個共享鎖（S 鎖）的功能，它允許其他的事務也對該數據上S鎖，但是不能夠允許對該數據進行修改。如果不及時的commit 或者rollback 也可能會造成大量的事務等待。

for update 和 lock in share mode 的區別：

前一個上的是排他鎖（X 鎖），一旦一個事務獲取了這個鎖，其他的事務是沒法在這些數據上執行 for update ；後一個是共享鎖，多個事務可以同時的對相同數據執行 lock in share mode。

InnoDB 行鎖實現方式：

InnoDB 行鎖是通過給索引上的索引項加鎖來實現的，這一點 MySQL 與 Oracle 不同，後者是通過在數據塊中對相應數據行加鎖來實現的。InnoDB 這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖！
不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會使用行鎖來對數據加鎖。
只有執行計劃真正使用了索引，才能使用行鎖：即便在條件中使用了索引欄位，但是否使用索引來檢索數據是由 MySQL 通過判斷不同執行計劃的代價來決定的，如果 MySQL 認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下 InnoDB 將使用表鎖，而不是行鎖。因此，在分析鎖衝突時，
別忘了檢查 SQL 的執行計劃（可以通過 explain 檢查 SQL 的執行計劃），以確認是否真正使用了索引。（更多閱讀：MySQL索引總結）
由於 MySQL 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然多個session是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖衝突的（後使用這些索引的session需要等待先使用索引的session釋放鎖後，才能獲取鎖）。應用設計的時候要注意這一點。

InnoDB的間隙鎖：

當我們用範圍條件而不是相等條件檢索數據，並請求共享或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做「間隙（GAP)」，InnoDB也會對這個「間隙」加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。

很顯然，在使用範圍條件檢索並鎖定記錄時，InnoDB這種加鎖機制會阻塞符合條件範圍內鍵值的並發插入，這往往會造成嚴重的鎖等待。因此，在實際應用開發中，尤其是並發插入比較多的應用，我們要盡量優化業務邏輯，盡量使用相等條件來訪問更新數據，避免使用範圍條件。

InnoDB使用間隙鎖的目的：

防止幻讀，以滿足相關隔離級別的要求；
滿足恢復和複製的需要：

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

一是 MySQL 的恢復是 SQL 語句級的，也就是重新執行 BINLOG 中的 SQL 語句。

二是 MySQL 的 Binlog 是按照事務提交的先後順序記錄的，恢復也是按這個順序進行的。

由此可見，MySQL 的恢復機制要求：在一個事務未提交前，其他並發事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀。

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

鎖和多版本數據（MVCC）是 InnoDB 實現一致性讀和 ISO/ANSI SQL92 隔離級別的手段。

因此，在不同的隔離級別下，InnoDB 處理 SQL 時採用的一致性讀策略和需要的鎖是不同的：

對於許多 SQL，隔離級別越高，InnoDB 給記錄集加的鎖就越嚴格（尤其是使用範圍條件的時候），產生鎖衝突的可能性也就越高，從而對並發性事務處理性能的影響也就越大。

因此，我們在應用中，應該盡量使用較低的隔離級別，以減少鎖爭用的機率。實際上，通過優化事務邏輯，大部分應用使用 Read Commited 隔離級別就足夠了。對於一些確實需要更高隔離級別的事務，可以通過在程序中執行 SET SESSION TRANSACTION ISOLATION

LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 動態改變隔離級別的方式滿足需求。

獲取 InnoDB 行鎖爭用情況：

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

LOCK TABLES 和 UNLOCK TABLES

Mysql也支持lock tables和unlock tables，這都是在伺服器層（MySQL Server層）實現的，和存儲引擎無關，它們有自己的用途，並不能替代事務處理。（除了禁用了autocommint後可以使用，其他情況不建議使用）：

LOCK TABLES 可以鎖定用於當前線程的表。如果表被其他線程鎖定，則當前線程會等待，直到可以獲取所有鎖定為止。
UNLOCK TABLES 可以釋放當前線程獲得的任何鎖定。當前線程執行另一個 LOCK TABLES 時，
或當與伺服器的連接被關閉時，所有由當前線程鎖定的表被隱含地解鎖

LOCK TABLES語法：

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

使用LOCK TABLES的場景：

給表顯示加表級鎖（InnoDB表和MyISAM都可以），一般是為了在一定程度模擬事務操作，實現對某一時間點多個表的一致性讀取。（與MyISAM默認的表鎖行為類似）

在用 LOCK TABLES 給表顯式加表鎖時，必須同時取得所有涉及到表的鎖，並且 MySQL 不支持鎖升級。也就是說，在執行 LOCK TABLES 後，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那麼只能執行查詢操作，而不能執行更新操作。

其實，在MyISAM自動加鎖（表鎖）的情況下也大致如此，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，這也正是 MyISAM 表不會出現死鎖（Deadlock Free）的原因。

例如，有一個訂單表 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 存儲引擎支持「並發插入」））

死鎖（Deadlock Free）

死鎖產生：

死鎖是指兩個或多個事務在同一資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性循環。
當事務試圖以不同的順序鎖定資源時，就可能產生死鎖。多個事務同時鎖定同一個資源時也可能會產生死鎖。
鎖的行為和順序和存儲引擎相關。以同樣的順序執行語句，有些存儲引擎會產生死鎖有些不會——死鎖有雙重原因：真正的數據衝突；存儲引擎的實現方式。

檢測死鎖：資料庫系統實現了各種死鎖檢測和死鎖超時的機制。InnoDB存儲引擎能檢測到死鎖的循環依賴並立即返回一個錯誤。
死鎖恢復：死鎖發生以後，只有部分或完全回滾其中一個事務，才能打破死鎖，InnoDB目前處理死鎖的方法是，將持有最少行級排他鎖的事務進行回滾。所以事務型應用程序在設計時必須考慮如何處理死鎖，多數情況下只需要重新執行因死鎖回滾的事務即可。
外部鎖的死鎖檢測：發生死鎖後，InnoDB 一般都能自動檢測到，並使一個事務釋放鎖並回退，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB 並不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數 innodb_lock_wait_timeout 來解決
死鎖影響性能：死鎖會影響性能而不是會產生嚴重錯誤，因為InnoDB會自動檢測死鎖狀況並回滾其中一個受影響的事務。在高並發系統上，當許多線程等待同一個鎖時，死鎖檢測可能導致速度變慢。有時當發生死鎖時，禁用死鎖檢測（使用innodb_deadlock_detect配置選項）可能會更有效，這時可以依賴innodb_lock_wait_timeout設置進行事務回滾。

MyISAM避免死鎖：

在自動加鎖的情況下，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，所以 MyISAM 表不會出現死鎖。

InnoDB避免死鎖：

為了在單個InnoDB表上執行多個並發寫入操作時避免死鎖，可以在事務開始時通過為預期要修改的每個元祖（行）使用SELECT ... FOR UPDATE語句來獲取必要的鎖，即使這些行的更改語句是在之後才執行的。
在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖、更新時再申請排他鎖，因為這時候當用戶再申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖衝突，甚至死鎖
如果事務需要修改或鎖定多個表，則應在每個事務中以相同的順序使用加鎖語句。在應用中，如果不同的程序會並發存取多個表，應盡量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會
通過SELECT ... LOCK IN SHARE MODE獲取行的寫鎖後，如果當前事務再需要對該記錄進行更新操作，則很有可能造成死鎖。
改變事務隔離級別

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

一些優化鎖性能的建議

盡量使用較低的隔離級別；
精心設計索引，並盡量使用索引訪問數據，使加鎖更精確，從而減少鎖衝突的機會
選擇合理的事務大小，小事務發生鎖衝突的幾率也更小
給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數據的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產生死鎖
不同的程序訪問一組表時，應盡量約定以相同的順序訪問各表，對一個表而言，儘可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會
盡量用相等條件訪問數據，這樣可以避免間隙鎖對並發插入的影響
不要申請超過實際需要的鎖級別
除非必須，查詢時不要顯示加鎖。 MySQL的MVCC可以實現事務中的查詢不用加鎖，優化事務性能；MVCC只在COMMITTED READ（讀提交）和REPEATABLE READ（可重複讀）兩種隔離級別下工作
對於一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能

樂觀鎖、悲觀鎖

樂觀鎖(Optimistic Lock)：假設不會發生並發衝突，只在提交操作時檢查是否違反數據完整性。樂觀鎖不能解決臟讀的問題。

樂觀鎖, 顧名思義，就是很樂觀，每次去拿數據的時候都認為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個數據，可以使用版本號等機制。樂觀鎖適用於多讀的應用類型，這樣可以提高吞吐量，像資料庫如果提供類似於write_condition機制的其實都是提供的樂觀鎖。

悲觀鎖(Pessimistic Lock)：假定會發生並發衝突，屏蔽一切可能違反數據完整性的操作。

悲觀鎖，顧名思義，就是很悲觀，每次去拿數據的時候都認為別人會修改，所以每次在拿數據的時候都會上鎖，這樣別人想拿這個數據就會block直到它拿到鎖。傳統的關係型資料庫裡邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。

參考：《高性能MySQL》《深入淺出MySQL》http://www.cnblogs.com/liushuiwuqing/p/3966898.html https://dev.mysql.com/doc/refman/5.7/en/internal-locking.html http://www.cnblogs.com/0201zcr/p/4782283.html

（本文首發於公眾號：EnjoyMoving）