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; 设置锁的超时时间。

只有在第一个会话提交后，第二个会话的更新语句才能成功执行。这代表了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

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

以上是Mysql鎖機制中行鎖、表鎖、死鎖如何實現的詳細內容。更多資訊請關注PHP中文網其他相關文章！