[TOC] # mysql 鎖概述 MySQL 兩種鎖特性歸納 ： * 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低。 * 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度小，發生鎖沖突的概率最低，并發度也最高。 * MySQL 不同的存儲引擎支持不同的鎖機制。 myisam 和 memory 存儲引擎采用的是 表級鎖； innodb 存儲引擎既支持行級鎖，也支持表級鎖，但默認情況下采用行級鎖。 表級鎖更適合于以查詢為主，只有少量按索引條件更新數據的應用，如 web 應用； 而行級鎖則更適合于有大量按索引條件并發更新少量不同數據，同時又有并發查詢的應用。 # myisam 表鎖 ## 查詢表級鎖爭用情況 可以通過檢查 table_locks_waited 和 table_locks_immediate 狀態變量來分析系統上的表鎖定爭奪： ~~~ MySQL [sakila]> show status like 'table_locks%'; ~~~ +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 100 | | Table_locks_waited | 0 | +-----------------------+-------+ 如果 table_locks_waited 的值比較高，則說明存在著較嚴重的表級鎖爭用情況。 ## MySQL 表級鎖的鎖模式 MySQL 的表級鎖有兩種模式，表共享讀鎖(table read lock)和表獨占寫鎖（table write lock）。 **對 myisam 表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對 myisam 表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；myisam 表的讀操作和寫操作之間，以及寫操作之間時串行的。** 當一個線程獲得對一個表的寫鎖戶，只有持有鎖的線程可以對表進行更新操作，其他線程的讀、寫操作都會等待，直到鎖被釋放。 ## 加鎖 **myisam 在執行查詢語句（select）前，會自動給涉及的所有表加讀鎖**，在執行更新操作（update、delete、insert等）前，會自動給涉及的表加寫鎖，這個過程并不需要直接用 lock table 命令給 myisam 表顯示加鎖。**給 myisam 表顯式加鎖，一般是為了在一定程度模擬事務操作 ** **myisam 在自動加鎖的情況下，總是一次獲得 sql 語句所需要的全部鎖，所以顯示鎖表的時候，必須同時取得所有涉及表的鎖，這也正是 myisam 表不會出現死鎖（deadlock）的原因**。 注意：**在使用 lock tables 時，不僅需要一次鎖定用到的所有表，而且，同一個表在 sql 語句中出現多少次，就要通過與 sql 語句中相同的別名鎖定多少次，否則會報錯。** ## 并發插入 myisam 表的讀和寫是串行的，但這是就總體而言的。在一定條件下，myisam 表也支持查詢和插入操作的并發進行。 myisam 存儲引擎有一個系統變量 concurrent_insert , 專門用以控制其并發插入的行為，其值分別可以為0,1,2。 * 當 concurrent_insert 設置為 0 時，不允許并發插入。 * 當 concurrent_insert 設置為 1 時，如果 myisam 表中沒有空洞（即表的中間沒有被刪除的行），myisam 允許在一個進程讀表的同時，另一個進程從表尾插入記錄。這也是 MySQL 的默認設置。 * 當 concurrent_insert 設置為 2 時，無論 myisam 表中有沒有空洞，都允許在表尾并發插入記錄。 ## mysiam鎖調度 myisam 存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作時串行的。當一個進程請求某個 myisam 表的讀鎖，同時另一個進程也請求同一表的寫鎖時，寫進程會先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀鎖請求之前，這是因為 mysql 認為寫請求一般比讀請求重要。這也正是** myisam 表不太適合有大量更新操作和查詢操作應用的原因**，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。 通過一些參數設置可以調節 MySQL 的默認調度行為： * 通過指定啟動參數 low-priority-updates, 使 myisam 引擎默認給予讀請求以優先的權利。 * 通過執行命令 set low_priority_updates = 1, 使該連接發出的更新請求優先級降低。 * 通過指定 insert、update、delete 語句的 low_priority 屬性，降低該語句的優先級。 上述方式都是要么更新優先，要么查詢優先，MySQL 也提供了一種折中的辦法調節讀寫沖突： 給系統參數 max_write_lock_count 設置一個合適的值，當一個表的讀鎖達到這個值后，MySQL 就暫時將寫請求的優先級降低，給讀進程一定獲得鎖的機會。 # innodb鎖 innodb 與 myisam 的最大不同有兩點，一是支持事務（transaction），二是采用了行級鎖。 ## 并發事務處理存在的問題 相對于串行處理來說，并發事務處理能力大大增加數據庫資源的利用率，提高數據庫系統事務吞吐量，從而可以支持更多的用戶，但并發事務處理也會帶來一些問題： ### 更新丟失（lost update） 當兩個或多個事務選擇同一行，然后基于最初選定的值更新該行時，由于每個事務都不知道其他事務的存在，就會發生丟失更新問題，最后的更新覆蓋了由其他事務所做的更新。 ### 臟讀（dirty reads） 一個事務正在對一條記錄做修改，在這個事務完成并提交前，這條記錄的數據就處于不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“臟”數據，并據此作進一步的處理，就會產生未提交的數據依賴關系。 ### 不可重復讀（non-repeatable reads） 一個事務在讀取某些數據后的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變或某些記錄已經被刪除了！這種現象就是“不可重復讀”。 ### 幻讀（phantom reads） 一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象稱為“幻讀”。 ## 事務隔離級別 并發事務處理帶來的問題中，“更新丟失”，通常是可以避免的，需要應用程序對要更新的數據加必要的鎖來解決。 “臟讀”，“不可重復讀”和“幻讀”， 其實都是數據庫讀一致性問題，必須由數據庫提供一定的事務隔離機制來解決。 數據庫實現事務隔離的方式，基本可以分為兩種： * 在讀取數據前，對其加鎖，阻止其他事務對數據進行修改 * 不加任何鎖，通過一定機制生成一個數據請求時間點的一致性數據快照，這種方式叫做數據多版本并發控制。 數據庫的事務隔離越嚴格，并發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上“串行化”進行，這顯然與“并發”是矛盾的。為了解決“隔離”與“并發”的矛盾，ISO/ANSI SQL92 定義了 4 個事務隔離級別，MySQL 實現了這四種級別，應用可以根據自己的業務邏輯要求，選擇合適的隔離級別來平衡“隔離”與“并發”的矛盾。  ## 查看 Innodb 行鎖爭用情況 可以通過檢查 innodb_row_lock 狀態變量來分析系統上的行鎖的爭奪情況： MySQL [sakila]> 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.00 sec) 如果發現鎖爭用比較嚴重，如 Innodb_row_lock_waits 和 Innodb_row_lock_time_avg 的值比較高，可以通過查詢 information_schema 數據庫中相關的表來查看鎖情況，或者通過設置 innodb monitors 來進一步觀察。 (1)查詢 information_schema 數據庫中的表了解鎖等待 ~~~ MySQL [sakila]> use information_schema Database changed MySQL [information_schema]> select * from innodb_locks \G; ~~~ (2) 通過設置 innodb monitors 觀察鎖沖突情況 ~~~ MySQL [sakila]> create table innodb_monitor (a int) engine=innodb; Query OK, 0 rows affected (0.05 sec) show engine innodb status \G; ~~~ ## innodb 的行鎖模式及加鎖方法 Innodb 實現了兩種類型的行鎖： * 共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。 * 排他鎖（X）：允許獲得排他鎖的事務更新數據，阻止其他事務獲取相同數據集的共享讀鎖和排他寫鎖。 另外，為了允許行鎖和表鎖共存，事項多粒度鎖機制，innodb 還有兩種內部使用的意向鎖，這兩種意向鎖都是表鎖： * 意向共享鎖（IS）：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的 IS 鎖。 * 意向排它鎖（IX）: 事務打算給數據行加行排它鎖，事務在給一個數據行加排它鎖前必須先取得該表的 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 方式獲得排他鎖。 所謂死鎖<DeadLock>: 是指兩個或兩個以上的進程在執行過程中, 因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去. 此時稱系統處于死鎖狀態或系統產生了死鎖,這些永遠在互相等的進程稱為死鎖進程. 表級鎖不會產生死鎖.所以解決死鎖主要還是針對于最常用的InnoDB. ## innodb 存儲引擎共享鎖例子(更新時死鎖)  ## innodb 存儲引擎排它鎖例子  ## innodb行鎖實現方式 innodb 行鎖是通過給索引項加鎖來實現的，如果么有索引，innodb 將通過隱藏的聚簇索引來對記錄加鎖。innodb 行鎖分為 3 種情形： * record lock： 對索引項加鎖 * gap lock： 對索引項之間的“間隙”、第一條記錄前的“間隙”或最后一條記錄的“間隙”加鎖。 * next-key lock： 前兩種的結合，對記錄及其前面的間隙加鎖。 **innodb 這種行鎖實現特點意味著：如果不通過索引條件檢索數據，那么 innodb 將對表中的所有記錄加鎖，實際效果和表鎖一樣！** 在實際應用中，要特別注意 innodb 行鎖的這一特性，否則可能導致大量的鎖沖突，從而影響并發性能 (1) 在不通過索引條件查詢時，innodb 會鎖定表中的所有記錄。 創建測試表： ~~~ MySQL [sakila]> create table tab_no_index (id int, name varchar(10)) engine=innodb; Query OK, 0 rows affected (0.04 sec) MySQL [sakila]> insert into tab_no_index values (1,'1'),(2,'2'),(3,'3'),(4,'4'); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 ~~~  看起來 session_1 只給一行加了排他鎖，但 session_2 在請求其他行的排他鎖時，卻出現了鎖等待！原因就是在沒有索引的情況下，Innodb 會對所有記錄都加鎖。當給其增加一個索引后，innodb 就只鎖定了符合條件的行 創建測試表: ~~~ MySQL [sakila]> create table tab_with_index (id int , name varchar(10)) engine = innodb; Query OK, 0 rows affected (0.01 sec) MySQL [sakila]> alter table tab_with_index add index id(id); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [sakila]> insert into tab_with_index values (1,'1'),(2,'2'),(3,'3'),(4,'4'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 ~~~  (2) 由于 MySQL 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖沖突的。 創建測試表,id字段有索引，name字段沒有索引： ~~~ MySQL [sakila]> create table tab_with_index (id int , name varchar(10)) engine = innodb; Query OK, 0 rows affected (0.01 sec) MySQL [sakila]> alter table tab_with_index add index id(id); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL [sakila]> insert into tab_with_index values (1,'1'),(1,'4'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 ~~~  (3) 當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，不論是使用主鍵索引、唯一索引或普通索引，innodb 都會使用行鎖對數據加鎖。 創建測試表，id 字段和 name 字段都有索引： ~~~ MySQL [sakila]> create table tab_with_index (id int , name varchar(10),index id (id),index name (name)) engine = innodb; Query OK, 0 rows affected (0.00 sec) MySQL [sakila]> insert into tab_with_index values (1,'1'),(1,'4'),(2,'2'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 ~~~  (4) 即便在條件中使用了索引字段，但是否使用索引來檢索數據是由 MySQL 通過判斷不同執行計劃的代價來決定的，**如果 MySQL 認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下 innodb 也會對所有記錄加鎖**。因此，在分析鎖沖突時，別忘了檢查 sql 的執行計劃，以確認是否真正使用了索引。 ## next-key 鎖 當我們用范圍條件而不是相等條件檢索數據，并請求共享或排他鎖時,innodb 會給符合條件的已有數據記錄的索引項加鎖；對于鍵值在條件范圍內但并不存在的記錄，叫做“間隙（gap）”，innodb 也會對這個“間隙”加鎖，這種鎖機制就是所謂的 next-key 鎖。 舉例來說，假如 emp 表中只有 101 條記錄，其 id 的值分別是1、2、...、100、101，下面的 sql: `# 這是一個范圍條件的檢索，innodb 不僅會對符合條件的 id 值為 101 的記錄加鎖，也會對 id 大于 101（這些記錄并不存在）的“間隙”加鎖。` select * from emp where id > 100 for update; 為什么使用 next-key 鎖 innodb 使用 next-key 鎖的目的，一方面是為了防止幻讀，以滿足相關隔離級別的要求，對于上面的例子，要是不使用間隙鎖，如果其他事務插入了 id 大于 100 的任何記錄，那么本事務如果再次執行上述語句，就會發生幻讀；另一方面，是為了滿足其恢復和復制的需要。 在使用范圍條件檢索并鎖定記錄時，innodb 這種加鎖機制會阻塞符合條件范圍內鍵值的并發插入，這往往會造成嚴重的鎖等待。因此，**在實際開發中，尤其是并發插入比較多的應用，應該盡量優化業務邏輯，盡量使用相等條件來訪問更新數據，避免使用范圍條件**。 innodb 除了通過范圍條件加鎖時使用 next-key 鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，innodb 也會使用 next-key 鎖！ ## 恢復和復制的需要，對 innodb 鎖機制的影響 MySQL 通過 binlog 記錄執行成功的 insert、update 、delete 等更新數據的 sql 語句，并由此實現 MySQL 數據庫的恢復和主從復制。 MySQL 5.6 支持 3 種 日志格式，即基于語句的日志格式 sbl，基于行的日志格式 rbl 和混合格式。它還支持 4 種復制模式： * 基于 sql 語句的復制 sbr：這也是 MySQL 最早支持的復制模式。 * 基于 行數據的復制 rbr： 這是 MySQL5.1 以后喀什支持的復制模式，主要優點是支持對非安全 sql 的復制模式。 * 混合復制模式：對安全的 sql 語句采用基于 sql 語句的復制模式，對于非安全的 sql 語句采用局于行的復制模式。 * 使用全局事務id（gtids）的復制：主要是解決主從自動同步一致的問題。 對基于語句日志格式（sbl）的恢復和復制而言，由于 MySQL 的 binlog 是按照事務提交的先后順序記錄的，因此要正確恢復或復制數據，就必須滿足： 在一個事務未提交前，其他并發事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀。這已經超過了“可重復讀”隔離級別的要求，實際上是要求事務要串行化。這也是許多情況下，innodb 要用 next-key 鎖的原因。 ## 什么時候使用表鎖 對于 innodb 表，在絕大部分情況下都應該使用行級鎖，因為事務和行鎖往往是我們選擇 innodb 表的理由，但在個別特殊任務中，也可以考慮使用表級鎖： 1. 事務需要更新大部分或全部數據，表又比較大，如果使用默認的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖 2. 事務涉及多個表，比較復雜，很可能引起死鎖，造成大量事務回滾。這種情況也可以考慮一次性鎖定多個表，從而避免死鎖，減少數據庫因事務回滾帶來的開銷。 當然，應用中這兩種事務不能太多，否則，就應該考慮使用 myisam 表了。 在 innodb 下，使用表鎖要注意以下兩點： 1. 使用 lock tables 雖然可以給 innodb 加表級鎖，但必須說明的是，表鎖不是由 innodb 存儲引擎管理的，而是由其上一層———— MySQL server 負責的，**僅當 autocommit=0、innodb_table_locks=1(默認設置)時，innodb 層才知道 MySQL 加的表鎖，MySQL server 也才能夠感知 innodb 加的行鎖，這種情況下，innodb 才能自動識別涉及到的鎖。** 2. **在用 lock_tables 對 innodb 表加鎖時要注意，要將 autocommit 設為 0**，否則 MySQL 不會給表加鎖；事務結束前，不要用 unlock tables 釋放表鎖，因為 unlock tables 會隱含的提交事務；commit 或 rollback 并不能釋放用 lock tables 加的表鎖，必須用 unlock tables 釋放表鎖 ~~~ set autocommit = 0; lock tables ti write, t2 read, ...; [do something with tables t1 and t2 here]; commit; unlock tables; ~~~ ## 關于死鎖 myisam 表鎖是 deadlock free 的，這是因為 myisam 總是一次獲取所需的全部鎖，要么全部滿足，要么等待，因此不會出現死鎖。但在 innodb 中，除單個 sql 組成的事務外，鎖是逐步獲得的，這就決定了在 innodb 中發生死鎖是可能的。  上面的例子中，**兩個事務都需要獲得對方持有的排他鎖才能繼續完成事務，這種循環鎖等待就是典型的死鎖**。 發生死鎖后，innodb 一般都能自動檢測到，并使一個事務釋放鎖回退，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖或表鎖的情況下，innodb 并不能完全自動檢測到死鎖，只需要通過設置鎖等待超時參數 innodb_lock_wait_timeout 來解決。需要說明的是，這個參數并不是只用來解決死鎖問題，在并發訪問比較高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會占用大量計算機資源，造成嚴重的性能問題，甚至拖垮數據庫。 **通常來說，死鎖都是應用設計的問題，通過調整業務流程，數據庫對象設計、事務大小、以及訪問數據庫的 sql 語句，絕大部分死鎖都可以避免。** 幾種避免死鎖的方法: (1) 在應用中，如果不同的程序會并發存取多個表，應盡量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。 下面的例子中，由于兩個 session 訪問兩個表的順序不同，發生死鎖的機會就非常高！但如果以相同的順序來訪問，死鎖就可以避免。  如果 session_2 以相同的順序執行 sql 語句，會造成鎖等待，但不會死鎖。 (2) 在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能。  (3) 在事務中，如果要更新記錄，應該申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖，更新時再申請排他鎖，因為當用戶申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。 (4) 在 repeatable-read 隔離級別下，如果兩個線程同時對相同條件記錄用 select ... for update 加排他鎖，在沒有符合該條件記錄情況下，兩個線程過會加鎖成功。程序發現記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這么做，就會出現死鎖。這種情況下，將隔離級別改成 read committed ，就可避免問題。 (5) 當隔離級別為 read committed 時，如果兩個線程都先執行 select ... for update, 判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現鎖等待，當第 1 個線程提交后，第 2 個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖，如果有第 3 個線程又來申請排他鎖，也會出現死鎖。對于這種情況，可以直接做插入操作，然后再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執行 rollback 釋放獲得的排他鎖 盡管通過上面介紹的設計和 sql 優化等措施，可以大大減少死鎖，但死鎖很難完全避免。因此，在程序設計中總是捕獲并處理死鎖異常是一個很好的編程習慣 如果出現死鎖，可以用 show innodb status 命令來確定最后一個死鎖產生的原因。返回結果中包括死鎖相關事務的詳細信息，如引發死鎖的 sql 語句，事務已經獲得的鎖，正在等待什么鎖，以及被回滾的事務等。可以據此分析產生死鎖的原因。