共計 6092 個字符，預計需要花費 16 分鐘才能閱讀完成。

這篇文章主要介紹了 MySQL 鎖機制有什么用，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓丸趣 TV 小編帶著大家一起了解一下。

MySQL 的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。比如，MyISAM 和 MEMORY 存儲引擎采用的是表級鎖；BDB 存儲引擎采用的是頁面鎖，但也支持表級鎖；InnoDB 存儲引擎既支持行級鎖，也支持表級鎖，但默認情況下采用行級鎖。

MySQL 這 3 種鎖的特性可大致歸納如下：

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

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

（3）頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。

僅從鎖的角度來說，表級鎖更適合于以查詢為主，只有少量按索引條件更新數據的應用，如 Web 應用；而行級鎖則更適合于有大量按索引條件并發更新少量不同數據，同時又有并發查詢的應用，如一些在線事務處理系統。

一、MyISAM 表鎖

1. 查詢表級鎖爭用情況

show status like  table%

如果 table_locks_waited 的值比較高，則說明存在著比較嚴重的表級鎖爭用情況。

2. MySQL 表級鎖的鎖模式

MySQL 的表級鎖有兩種模式：表共享讀鎖和表獨占寫鎖。

當一個 session 對某個表加了讀鎖之后，該 session 只能訪問加鎖的這個表，而且只能進行讀操作；其他 session 可以對這個表進行讀操作，但是進行寫操作會被阻塞，需要等待鎖的釋放。當一個 session 對某個表加了寫鎖之后，該 session 只能訪問加鎖的這個表，可以進行讀操作和寫操作，其他 session 對這個表的讀和寫操作都會被阻塞，需要等待鎖的釋放。

MyISAM 表的讀操作與寫操作之間，以及寫操作之間是串行的。

3. 如何加表鎖

加讀鎖：

lock table tbl_name read;

加寫鎖：

lock table tbl_name write;

釋放鎖：

unlock tables;

MyISAM 在執行查詢語句前，會自動給涉及的所有表加讀鎖，在執行更新操作前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。給 MyISAM 表顯式加鎖，一般是為了在一定程度模擬事務操作，實現對某一時間點多個表的一致性讀取。

注意，當使用 LOCK TABLES 時，不僅需要一次鎖定用到的所有表，而且，同一個表在 SQL 語句中出現多少次，就要通過與 SQL 語句中相同的別名鎖定多少次，否則也會出錯！

4. 并發插入

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

（1）當 concurrent_insert 設置為 0 時，不允許并發插入。

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

（3）當 concurrent_insert 設置為 2 時，無論 MyISAM 表中有沒有空洞，都允許在表尾并發插入記錄。

只需在加表鎖命令中加入“local”選項，即：lock table tbl_name local read，在滿足 MyISAM 表并發插入條件的情況下，其他用戶就可以在表尾并發插入記錄，但更新操作會被阻塞，而且加鎖的用戶無法訪問到其他用戶并發插入的記錄。

5. MyISAM 鎖調度

當寫進程和讀進程同時請求同一個 MyISAM 表的寫鎖和讀鎖時，寫進程會優先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀鎖請求之前！這是因為 MySQL 認為寫請求一般比讀請求更重要。這也正是 MyISAM 表不太適合于有大量更新操作和查詢操作應用的原因，因為大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。

通過一下一些設置調節 MyISAM 的調度行為：

（1）通過指定啟動參數 low-priority-updates，使 MyISAM 引擎默認給予讀請求以優先的權利。

（2）通過執行命令 SET LOW_PRIORITY_UPDATES=1，使該連接發出的更新請求優先級降低。

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

（4）給系統參數 max_write_lock_count 設置一個合適的值，當一個表的讀鎖達到這個值后，MySQL 就暫時將寫請求的優先級降低，給讀進程一定獲得鎖的機會。

二、InnoDB 鎖問題

1. 查詢 InnoDB 行鎖爭用情況

show status like  innodb_row_lock%

如果 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比較高，說明鎖爭用比較嚴重，這時可以通過設置 InnoDB Monitors 來進一步觀察發生鎖沖突的表、數據行等，并分析鎖爭用的原因。

打開監視器：

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
Show innodb status\G;

停止監視器：

DROP TABLE innodb_monitor;

打開監視器以后，默認情況下每 15 秒會向日志中記錄監控的內容，如果長時間打開會導致.err 文件變得非常的巨大，所以用戶在確認問題原因之后，要記得刪除監控表以關閉監視器，或者通過使用“–console”選項來啟動服務器以關閉寫日志文件。

2. InnoDB 的行鎖及加鎖方法

InnoDB 的行鎖有兩種：共享鎖（S）和排他鎖（X）。為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB 還有兩種內部使用的意向鎖：意向共享鎖和意向排他鎖，這兩種意向鎖都是表鎖。一個事務在給數據行加鎖之前必須先取得對應表對應的意向鎖。

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

Set autocommit=0;

共享鎖（S）：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他鎖（X）：

SELECT * FROM table_name WHERE ... FOR UPDATE

釋放鎖：

unlock tables;

（會隱含提交事務）

當一個事務獲得一個表的共享鎖時，其他事務可以查詢該表的記錄，也可以對該記錄加共享鎖。當一個事務對表進行更新操作時，若存在另一個事務也在該表加了共享鎖，則需要等待鎖的釋放，若另一個事務同時也對該表執行了更新操作，則會導致死鎖，另一個事務退出，當前事務完成更新操作。當一個事務獲得一個表的排他鎖時，其他事務只能對該表的記錄進行查詢，不能加共享鎖，也不能更新記錄，會出現等待。

3. InnoDB 行鎖實現方式

InnoDB 行鎖是通過給索引上的索引項加鎖來實現的，InnoDB 這種行鎖實現特點意味著：

（1）只有通過索引條件檢索數據，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖。

（2）由于 MySQL 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖沖突的。

（3）當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會使用行鎖來對數據加鎖。（雖然使用的是不同的索引，但是如果記錄已經被其他 session 鎖定的話也是需要等待的。）

（4）即便在條件中使用了索引字段，但是否使用索引來檢索數據是由 MySQL 通過判斷不同執行計劃的代價來決定的，如果 MySQL 認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下 InnoDB 將使用表鎖，而不是行鎖。

4. 間隙鎖

當使用范圍條件檢索數據的時候，對于鍵值在條件范圍內但并不存在的記錄，InnoDB 也會進行加鎖，這個鎖就叫“間隙鎖”。InnoDB 使用間隙鎖的目的，一方面是為了防止幻讀，另一方面是為了滿足恢復和復制的需要。但是這種加鎖機制會阻塞符合條件范圍內鍵值的并發插入，造成嚴重的鎖等待，所以應該盡量避免使用范圍條件來檢索數據。

除了通過范圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB 也會使用間隙鎖！

5. 恢復和復制的需要對 InnoDB 鎖機制的影響

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

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

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

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

另外，對于一般的 select 語句，MySQL 使用多版本數據來實現一致性，不需要加任何鎖，然而，對于“insert into target_tab select * from source_tab where …”和“create table new_tab …select … From source_tab where …”這種 SQL 語句，用戶并沒有對 source_tab 做任何更新操作，但 MySQL 對這種 SQL 語句做了特別處理，給 source_tab 加了共享鎖。這是因為，不加鎖的話，如果這個 SQL 語句執行期間，有另一個事務對 source_tab 做了更新并且先進行了提交，那么在 BINLOG 中，更新操作的位置會在該 SQL 語句之前，使用這個 BINLOG 進行數據庫恢復的話，恢復的結果就會與實際的應用邏輯不符，進行復制則會導致主從數據庫不一致。因為實際上應用插入 target_tab 或 new_tab 中的數據是另一個事務對 source_tab 更新前的數據，而 BINLOG 記錄的卻是先進行更新再執行 select…insert… 語句。如果上述語句的 SELECT 是范圍條件，InnoDB 還會給源表加間隙鎖。所以這種 SQL 語句會阻塞對原表的并發更新，應盡量避免使用。

6. InnoDB 使用表鎖的情況及注意事項

對于 InnoDB 表，在絕大部分情況下都應該使用行級鎖，但在個別特殊事務中，也可以考慮使用表級鎖，主要有以下兩種情況：

（1）事務需要更新大部分或全部數據，表又比較大，如果使用默認的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務的執行速度。

（2）事務涉及多個表，比較復雜，很可能引起死鎖，造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表，從而避免死鎖、減少數據庫因事務回滾帶來的開銷。

另外，在 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 釋放表鎖。

7. 關于死鎖

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

發生死鎖后，InnoDB 一般都能自動檢測到，并使一個事務釋放鎖并回退，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB 并不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數 innodb_lock_wait_timeout 來解決。

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

（1）在應用中，如果不同的程序會并發存取多個表，應盡量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。

（2）在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能。

（3）在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖，更新時再申請排他鎖，因為當用戶申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。

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

（5）當隔離級別為 READ COMMITTED 時，如果兩個線程都先執行 SELECT…FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現鎖等待，當第 1 個線程提交后，第 2 個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第 3 個線程又來申請排他鎖，也會出現死鎖。對于這種情況，可以直接做插入操作，然后再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執行 ROLLBACK 釋放獲得的排他鎖。

感謝你能夠認真閱讀完這篇文章，希望丸趣 TV 小編分享的“MySQL 鎖機制有什么用”這篇文章對大家有幫助，同時也希望大家多多支持丸趣 TV，關注丸趣 TV 行業資訊頻道，更多相關知識等著你來學習!