Mysql的行級鎖是什么意思？

4611

我們首先需要知道的一個大前提是：mysql的鎖是由具體的存儲引擎實現(xiàn)的。所以像mysql的默認引擎myisam和第三方插件引擎innodb的鎖實現(xiàn)機制是有區(qū)別的。

Mysql的行級鎖是什么意思？

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

一、定義

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

二、優(yōu)缺點

1. 優(yōu)點

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

2. 缺點

  • 實現(xiàn)復雜，開銷大。

  • 加鎖慢、容易出現(xiàn)死鎖

三、支持存儲引擎

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

四、行級鎖類型

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

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

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

鎖模式的兼容性：

Mysql的行級鎖是什么意思？

五、行級鎖定實現(xiàn)方式

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

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

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

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

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

隱式加鎖：

  • InnoDB自動加意向鎖。

  • 對于UPDATE、delete和INSERT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（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獲得共享鎖，主要用在需要數(shù)據(jù)依存關(guān)系時來確認某行記錄是否存在，并確保沒有人對這個記錄進行UPDATE或者DELETE操作。

但是如果當前事務也需要對該記錄進行更新操作，則很有可能造成死鎖，對于鎖定行記錄后需要進行更新操作的應用，應該使用SELECT… FOR UPDATE方式獲得排他鎖。

InnoDB如何加表鎖：

在用 LOCK TABLES對InnoDB表加鎖時要注意，要將AUTOCOMMIT設(shè)為0，否則MySQL不會給表加鎖；事務結(jié)束前，不要用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的鎖定規(guī)則是通過在指向數(shù)據(jù)記錄的第一個索引鍵之前和最后一個索引鍵之后的空域空間上標記鎖定信息而實現(xiàn)的。 Innodb的這種鎖定實現(xiàn)方式被稱為“ NEXT-KEY locking” （間隙鎖），因為Query執(zhí)行過程中通過范圍查找的話，它會鎖定整個范圍內(nèi)所有的索引鍵值，即使這個鍵值并不存在。

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

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

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

2. 間隙鎖的缺點：

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

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

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

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

3 . 間隙鎖的作用：

  • 防止幻讀，以滿足相關(guān)隔離級別的要求。

  • 為了數(shù)據(jù)恢復和復制的需要。

4. 注意

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

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

七、查看行級鎖爭用情況

執(zhí)行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?????|+-------------------------------+-------+

如果發(fā)現(xiàn)鎖爭用比較嚴重，還可以通過設(shè)置InnoDB Monitors 來進一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等，并分析鎖爭用的原因。如：

設(shè)置監(jiān)視器：mysql> create table InnoDB_monitor(a int) engine=InnoDB;

查看：mysql> show engine InnoDB status;

停止查看：mysql> drop table InnoDB_monitor;

具體參考：InnoDB Monitor

八、死鎖

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

如何發(fā)現(xiàn)死鎖： 在InnoDB的事務管理和鎖定機制中，有專門檢測死鎖的機制，會在系統(tǒng)中產(chǎn)生死鎖之后的很短時間內(nèi)就檢測到該死鎖的存在

解決辦法：

  • 回滾較小的那個事務

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

判斷事務大?。菏聞崭髯圆迦搿⒏禄蛘邉h除的數(shù)據(jù)量

注意：

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

九、優(yōu)化行級鎖定

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

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

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

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

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

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

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

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

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

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

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

相關(guān)推薦：《mysql教程》

? 版權(quán)聲明
THE END
數(shù)據(jù)庫
# mysql# sql# 并發(fā)# 線程# select# 分布式# for# delete# table# int
喜歡就支持一下吧
點贊11 分享