Mysql鎖機制中行鎖、表鎖、死鎖如何實現

這篇文章主要介紹了Mysql鎖機制中行鎖、表鎖、死鎖如何實現，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

一、Mysql鎖是什么？鎖有哪些類別？

鎖定義:
    同一時間同一資源只能被一個線程訪問
    在數據庫中，除傳統的計算資源（如CPU、I/O等）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。

樂觀鎖用的最多的就是數據的版本記錄來體現 version ，其實就是一個標識。

例如:update test set a=a-1 where id=100 and a> 0; 對應的version就是a字段，并不一定非得要求有一個字段叫做version，要求的是有這個字段，同時當滿足這個條件的時候才會觸發

 鎖的分類:
從對數據操作的類型分法（讀或寫）
讀鎖(共享鎖)：針對同一份數據，多個讀操作可以同時進行而不會互相影響。
寫鎖（排它鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

從對數據操作的粒度分法
表級鎖：表級鎖是MySQL中鎖定粒度最大的一種鎖，表示對當前操作的整張表加鎖(MyISAM引擎默認表級鎖，也只支持表級鎖)。比如說更新一張10萬表數據中的一條數據，在這條update沒提交事務之前，其它事務是會被排斥掉的，粒度很大。
行級鎖：行級鎖是Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖(基于索引實現的，所以一旦某個加鎖操作沒有使用索引，那么該鎖就會退化為表鎖)
頁級鎖：頁級鎖是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖，一次鎖定相鄰的一組記錄

從并發角度的分發--實際上樂觀鎖和悲觀鎖只是一種思想
悲觀鎖：對數據被外界（包括本系統當前的其他事務，以及來自外部系統的事務處理）修改持保守態度(悲觀) ，因此，在整個數據處理過程中，將數據處于鎖定狀態。
樂觀鎖：樂觀鎖假設認為數據一般情況下不會造成沖突，所以在數據進行提交更新的時候，才會正式對數據的沖突與否進行檢測，如果發現沖突了，則讓返回錯誤信息再進行業務重試

其他鎖：
間隙鎖：在條件查詢中，如：where id>100，InnoDB會給符合條件的已有數據記錄的索引項加鎖；對于鍵值在條件范圍內但并不存在的記錄，叫做“間隙（GAP)”，間隙的目的是為了防止幻讀
意向鎖：意向鎖分為 intention shared lock (IS) 和 intention exclusive lock (IX)，意向鎖的目的就是表明有事務正在或者將要鎖住某個表中的行

二、行鎖和表鎖的區別

表級鎖是MySQL中鎖定粒度最大的一種鎖，表示對當前操作的整張表加鎖，它實現簡單。最常使用的MYISAM與INNODB都支持表級鎖定。
特點：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖沖突的概率最高，并發度最低。

行級鎖是Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少數據庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大。
特點：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高
使用：InnoDB行鎖是通過給索引上的索引項加鎖來實現的，只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖

下面這個update語句，b是一般字段不是索引列的話，那么此時行級鎖將改為表級鎖。

update from test set a=100 where b='100';

現在舉個實際例子操作一下，看看innnodb是怎么來用行鎖的。

當前表中數據:

首先開啟兩個session會話窗口，然后將mysql事務級別設置成不提交級別:

會話一窗口:

會話二窗口:

 其中會話2的update一直都在Running中，一直到超時結束，或者會話1提交事務后才會Running結束。

可以通過show VARIABLES like "%innodb_lock_wait_timeout%" 查詢當前mysql設置的鎖超時時間,默認是50秒。 

可以通過set innodb_lock_wait_timeout = 60; 設置鎖的超時時間。

當第一個會話commit之后，第二個會話的update語句才會執行成功。這代表了innodb用了鎖。

那怎么確定是用了行鎖呢？

 總結:會話一更新id=125的時候，給這條數據add lock了，那么在會話2中再次更新id=125的時候，這條數據是locked中的。這個lock加的是id=125這條記錄。此時除了id=125這條之外的，都是可以成功的，證明這條默認加的是行鎖。

三、InnoDB死鎖概念和死鎖案例

定義：當兩個或以上的事務相互持有和請求鎖，并形成一個循環的依賴關系，就會產生死鎖。多個事務同時鎖定同一個資源時，也會產生死鎖。在一個事務系統中，死鎖是確切存在并且是不能完全避免的。

解決：InnoDB會自動檢測事務死鎖，立即回滾其中某個事務，并且返回一個錯誤。它根據某種機制來選擇那個最簡單（代價最小）的事務來進行回滾

死鎖場景一之select for update:

產生場景：兩個transaction都有兩個select for update，transaction a先鎖記錄1，再鎖記錄2；而transaction b先鎖記錄2，再鎖記錄1

寫鎖：for update，讀鎖：for my share mode show engine innodb status

驗證下死鎖的場景:

 第一步更新會話一:

start TRANSACTION;
select * from wnn_test where a=199 for update;

第二步更新會話二:

start TRANSACTION;
select * from wnn_test where a=101 for update;

第三步更新會話一:

select * from wnn_test where a=101 for update;

第四步更新會話二;

select * from wnn_test where a=199 for update;

在更新到第三步和第四步的時候，已經發生了死鎖。

來看下執行的日志:

show engine innodb status;最后一個鎖的時間，鎖的表，引起鎖的語句。其中session1被鎖 14秒(ACTIVE 14)，session 2被鎖了10秒(Active 10)

死鎖場景二之兩個update

產生場景：兩個transaction都有兩個update，transaction a先更新記錄1，再更新記錄2；而transaction b先更新記錄2，再更新記錄1

 產生日志：

 注意：仔細查看上面2個例子可以發現一個現象，當2條資源鎖住后，再執行第三個會執行成功，但是第四個會提示死鎖。在mysql5.7中，執行第三個的時候就會一直在Running狀態了，本博文使用的是mysql8.0 ，其中 有這個參數 innodb_deadlock_detect 可以用于控制 InnoDB 是否執行死鎖檢測，當啟用了死鎖檢測時（默認設置），InnoDB 自動執行事務的死鎖檢測，并且回滾一個或多個事務以解決死鎖。InnoDB 嘗試回滾更小的事務，事務的大小由它所插入、更新或者刪除的數據行數決定。

 那么這個innodb_deadlock_detect參數，到底要不要啟用呢？

對于高并發的系統，當大量線程等待同一個鎖時，死鎖檢測可能會導致性能的下降。此時，如果禁用死鎖檢測，而改為依靠參數 innodb_lock_wait_timeout 執行發生死鎖時的事務回滾可能會更加高效。
通常來說，應該啟用死鎖檢測，并且在應用程序中盡量避免產生死鎖，同時對死鎖進行相應的處理，例如重新開始事務。

只有在確認死鎖檢測影響了系統的性能，并且禁用死鎖檢測不會帶來負面影響時，可以嘗試關閉 innodb_deadlock_detect 選項。另外，如果禁用了 InnoDB 死鎖檢測，需要調整參數 innodb_lock_wait_timeout 的值，以滿足實際的需求。

 四、程序開發過程中應該如何注意避免死鎖

 鎖的本質是資源相互競爭，相互等待，往往是兩個(或以上)的Session加鎖的順序不一致

如何有效避免：

在程序中，操作多張表時，盡量以相同的順序來訪問（避免形成等待環路）

批量操作單張表數據的時候，先對數據進行排序（避免形成等待環路） A線程 id：1 ,10 ,20按順序加鎖     B線程id:20,10,1   這種的話就容易鎖。

如果可以，大事務化成小事務，甚至不開啟事務 select for update==>insert==>update = insert into update on duplicate key

盡量使用索引訪問數據，避免沒有 where 條件的操作，避免鎖表 有走索引是記錄行鎖，沒走索引是表鎖

使用等值查詢而不是范圍查詢查詢數據，命中記錄，避免間隙鎖對并發的影響 1，10，20 等值where id in (1,10,20) 范圍查詢 id>1 and id<20

避免在同一時間點運行多個對同一表進行讀寫的腳本，特別注意加鎖且操作數據量比較大的語句；我們經常會有一些定時腳本，避免它們在同一時間點運行

感謝你能夠認真閱讀完這篇文章，希望小編分享的“Mysql鎖機制中行鎖、表鎖、死鎖如何實現”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!