Mysql鎖機制之行鎖、表鎖、死鎖的實現

2022-03-16 10:00:20

一、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鎖機制之行鎖、表鎖、死鎖的實現的文章就介紹到這了,更多相關Mysql 行鎖、表鎖、死鎖內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！