Mysql中行級鎖的示例分析

小編給大家分享一下Mysql中行級鎖的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

我們首先需要知道的一個大前提是：mysql的鎖是由具體的存儲引擎實現的。所以像Mysql的默認引擎MyISAM和第三方插件引擎InnoDB的鎖實現機制是有區別的。

Mysql有三種級別的鎖定：表級鎖定、頁級鎖定、行級鎖定

一、定義

每次鎖定的是一行數據的鎖機制就是行級別鎖定(row-level)。行級鎖定不是MySQL自己實現的鎖定方式，而是由其他存儲引擎自己所實現的

二、優缺點

1. 優點

• 由于鎖粒度小，爭用率低，并發高。

2. 缺點

• 實現復雜，開銷大。

• 加鎖慢、容易出現死鎖

三、支持存儲引擎

• 使用行級鎖定的主要有InnoDB存儲引擎，以及MySQL的分布式存儲引擎NDBCluster

四、行級鎖類型

InnoDB的行級鎖定同樣分為兩種類型：共享鎖和排他鎖，而在鎖定機制的實現過程中為了讓行級鎖定和表級鎖定共存，InnoDB也同樣使用了意向鎖（表級鎖定）的概念，也就有了意向共享鎖和意向排他鎖這兩種。

意向鎖的作用就是當一個事務在需要獲取資源鎖定的時候，如果遇到自己需要的資源已經被排他鎖占用的時候，該事務可以需要鎖定行的表上面添加一個合適的意向鎖。如果自己需要一個共享鎖，那么就在表上面添加一個意向共享鎖。而如果自己需要的是某行（或者某些行）上面添加一個排他鎖的話，則先在表上面添加一個意向排他鎖。

意向共享鎖可以同時并存多個，但是意向排他鎖同時只能有一個存在。所以，可以說InnoDB的鎖定模式實際上可以分為四種：共享鎖（S），排他鎖（X），意向共享鎖（IS）和意向排他鎖（IX）

鎖模式的兼容性：

五、行級鎖定實現方式

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

• 在不通過索引條件查詢的時候，InnoDB使用的是表鎖，而不是行鎖。

• 由于MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以即使是訪問不同行的記錄，如果使用了相同的索引鍵，也是會出現鎖沖突的。

• 當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數據加鎖。

• 即便在條件中使用了索引字段，但具體是否使用索引來檢索數據是由MySQL通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖沖突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。

隱式加鎖：

• 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如何加表鎖：

在用 LOCK TABLES對InnoDB表加鎖時要注意，要將AUTOCOMMIT設為0，否則MySQL不會給表加鎖；事務結束前，不要用UNLOCK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務；COMMIT或ROLLBACK并不能釋放用LOCK TABLES加的表級鎖，必須用UNLOCK TABLES釋放表鎖。

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

既然都用表鎖了，怎么不選擇MyISAM引擎呢！

六、間隙鎖（Next-Key鎖）

1. 間隙鎖定義：

Innodb的鎖定規則是通過在指向數據記錄的第一個索引鍵之前和最后一個索引鍵之后的空域空間上標記鎖定信息而實現的。 Innodb的這種鎖定實現方式被稱為“ NEXT-KEY locking” （間隙鎖），因為Query執行過程中通過范圍查找的話，它會鎖定整個范圍內所有的索引鍵值，即使這個鍵值并不存在。

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

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

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

2. 間隙鎖的缺點：

• 間隙鎖有一個比較致命的弱點，就是當鎖定一個范圍鍵值之后，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定鍵值范圍內的任何數據。在某些場景下這可能會對性能造成很大的危害

• 當Query無法利用索引的時候， Innodb會放棄使用行級別鎖定而改用表級別的鎖定，造成并發性能的降低；

• 當Quuery使用的索引并不包含所有過濾條件的時候，數據檢索使用到的索引鍵所指向的數據可能有部分并不屬于該Query的結果集的行列，但是也會被鎖定，因為間隙鎖鎖定的是一個范圍，而不是具體的索引鍵；

• 當Query在使用索引定位數據的時候，如果使用的索引鍵一樣但訪問的數據行不同的時候（索引只是過濾條件的一部分），一樣會被鎖定

3 . 間隙鎖的作用：

• 防止幻讀，以滿足相關隔離級別的要求。

• 為了數據恢復和復制的需要。

4. 注意

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

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

七、查看行級鎖爭用情況

執行SQL:mysql> show status like '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 Monitors 來進一步觀察發生鎖沖突的表、數據行等，并分析鎖爭用的原因。如：

設置監視器：mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

查看：mysql> show engine InnoDB status;

停止查看：mysql> drop table InnoDB_monitor;

具體參考：InnoDB Monitor

八、死鎖

什么是死鎖：你等我釋放鎖，我等你釋放鎖就會形成死鎖。

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

解決辦法：

• 回滾較小的那個事務

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

判斷事務大小：事務各自插入、更新或者刪除的數據量

注意：

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

九、優化行級鎖定

InnoDB存儲引擎由于實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體并發處理能力方面要遠遠優于MyISAM的表級鎖定的。當系統并發量較高的時候，InnoDB的整體性能和MyISAM相比就會有比較明顯的優勢了。但是，InnoDB的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓InnoDB的整體性能表現不僅不能比MyISAM高，甚至可能會更差。

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

• 盡可能讓所有的數據檢索都通過索引來完成，從而避免InnoDB因為無法通過索引鍵加鎖而升級為表級鎖定；

• 合理設計索引，讓InnoDB在索引鍵上面加鎖的時候盡可能準確，盡可能的縮小鎖定范圍，避免造成不必要的鎖定而影響其他Query的執行；

• 盡可能減少基于范圍的數據檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄；

• 盡量控制事務的大小，減少鎖定的資源量和鎖定時間長度；

• 在業務環境允許的情況下，盡量使用較低級別的事務隔離，以減少MySQL因為實現事務隔離級別所帶來的附加成本。

（2）由于InnoDB的行級鎖定和事務性，所以肯定會產生死鎖，下面是一些比較常用的減少死鎖產生概率的小建議：

• 類似業務模塊中，盡可能按照相同的訪問順序來訪問，防止產生死鎖；

• 在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；

• 對于非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率。

以上是“Mysql中行級鎖的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！