MySQL如何鎖定一行及如何分析行鎖定

注意事項：session1中沒有commit之前，該數據行是鎖定的，其他的session修改該行數據時會進入堵塞狀態。

齊齊哈爾ssl適用于網站、小程序/APP、API接口等需要進行數據傳輸應用場景，ssl證書未來市場廣闊！成為成都創新互聯的ssl證書銷售渠道，可以享受市場價格4-6折優惠！如果有意向歡迎電話聯系或者加微信：18980820575（備注：SSL證書合作）期待與您的合作！

MySQL - for update 行鎖 表鎖

for update 的作用是在查詢的時候為行加上排它鎖，當一個事務的操作未完成時候，其他事務可以讀取但是不能寫入或更新。

它的典型使用場景是 高并發并且對于數據的準確性有很高要求 ，比如金錢、庫存等，一般這種操作都是很長一串并且開啟事務的，假如現在要對庫存進行操作，在剛開始讀的時候是1，然后馬上另外一個進程將庫存更新為0了，但事務還沒結束，會一直用1進行后續的邏輯，就會有問題，所以需要用for upate 加鎖防止出錯。

行鎖的具體實現算法有三種：record lock、gap lock以及next-key lock。

只在可重復讀或以上隔離級別下的特定操作才會取得 gap lock 或 next-key lock，在 Select、Update 和 Delete 時，除了基于唯一索引的查詢之外，其它索引查詢時都會獲取 gap lock 或 next-key lock，即鎖住其掃描的范圍。主鍵索引也屬于唯一索引，所以主鍵索引是不會使用 gap lock 或 next-key lock

for update 僅適用于InnoDB，并且必須開啟事務，在begin與commit之間才生效。

select 語句默認不獲取任何鎖，所以是可以讀被其它事務持有排它鎖的數據的！

InnoDB 既實現了行鎖，也實現了表鎖。

當有明確指定的主鍵/索引時候，是行級鎖，否則是表級鎖

假設表 user，存在有id跟name字段，id是主鍵，有5條數據。

明確指定主鍵，并且有此記錄，行級鎖

無主鍵/索引，表級鎖

主鍵/索引不明確，表級鎖

明確指定主鍵/索引，若查無此記錄，無鎖

參考博文：

mysql 的鎖以及間隙鎖

mysql 為并發事務同時對一條記錄進行讀寫時，提出了兩種解決方案：

1）使用 mvcc 的方法，實現多事務的并發讀寫，但是這種讀只是“快照讀”，一般讀的是歷史版本數據，還有一種是“當前讀”，一般加鎖實現“當前讀”，或者 insert、update、delete 也是當前讀。

2）使用加鎖的方法，鎖分為共享鎖（讀鎖），排他鎖（寫鎖）

快照讀：就是select

當前讀：特殊的讀操作，插入/更新/刪除操作，屬于當前讀，處理的都是當前的數據，需要加鎖。

mysql 在 RR 級別怎么處理幻讀的呢？一般來說，RR 級別通過 mvcc 機制，保證讀到低于后面事務的數據。但是 select for update 不會觸發 mvcc，它是當前讀。如果后面事務插入數據并提交，那么在 RR 級別就會讀到插入的數據。所以，mysql 使用 行鎖 + gap 鎖（簡稱 next-key 鎖）來防止當前讀的時候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事務的插入操作，以此防止幻讀的發生。

Innodb自動使用間隙鎖的條件：

MySQL從入門到精通(九) MySQL鎖，各種鎖

鎖是計算機協調多個進程或線程并發訪問某一資源的機制，在數據庫中，除傳統的計算資源（CPU、RAM、I/O）爭用外，數據也是一種供許多用戶共享的資源，如何保證數據并發訪問的一致性，有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素，從這個角度來說，鎖對數據庫而言是尤其重要，也更加復雜。MySQL中的鎖，按照鎖的粒度分為:1、全局鎖，就鎖定數據庫中的所有表。2、表級鎖，每次操作鎖住整張表。3、行級鎖，每次操作鎖住對應的行數據。

全局鎖就是對整個數據庫實例加鎖，加鎖后整個實例就處于只讀狀態，后續的DML的寫語句，DDL語句，已經更新操作的事務提交語句都將阻塞。其典型的使用場景就是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證數據的完整性。但是對數據庫加全局鎖是有弊端的，如在主庫上備份，那么在備份期間都不能執行更新，業務會受影響，第二如果是在從庫上備份，那么在備份期間從庫不能執行主庫同步過來的二進制日志，會導致主從延遲。

解決辦法是在innodb引擎中，備份時加上--single-transaction參數來完成不加鎖的一致性數據備份。

添加全局鎖: flush tables with read lock; 解鎖 unlock tables。

表級鎖,每次操作會鎖住整張表.鎖定粒度大,發送鎖沖突的概率最高,并發讀最低,應用在myisam、innodb、BOB等存儲引擎中。表級鎖分為: 表鎖、元數據鎖(meta data lock, MDL)和意向鎖。

表鎖又分為: 表共享讀鎖 read lock、表獨占寫鎖write lock

語法: 1、加鎖 lock tables 表名 ... read/write

2、釋放鎖 unlock tables 或者關閉客戶端連接

注意: 讀鎖不會阻塞其它客戶端的讀，但是會阻塞其它客戶端的寫，寫鎖既會阻塞其它客戶端的讀，又會阻塞其它客戶端的寫。大家可以拿一張表來測試看看。

元數據鎖,在加鎖過程中是系統自動控制的，無需顯示使用，在訪問一張表的時候會自動加上，MDL鎖主要作用是維護表元數據的數據一致性，在表上有活動事務的時候，不可以對元數據進行寫入操作。為了避免DML和DDL沖突，保證讀寫的正確性。

在MySQL5.5中引入了MDL，當對一張表進行增刪改查的時候，加MDL讀鎖(共享)；當對表結構進行變更操作時，加MDL寫鎖(排他).

查看元數據鎖:

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;

意向鎖，為了避免DML在執行時，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數據是否加鎖，使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖is由語句select ... lock in share mode添加。意向排他鎖ix,由insert，update，delete，select。。。for update 添加。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;

行級鎖，每次操作鎖住對應的行數據，鎖定粒度最小，發生鎖沖突的概率最高，并發讀最高，應用在innodb存儲引擎中。

innodb的數據是基于索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖，對于行級鎖，主要分為以下三類:

1、行鎖或者叫record lock記錄鎖，鎖定單個行記錄的鎖，防止其他事物對次行進行update和delete操作，在RC,RR隔離級別下都支持。

2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄)，確保索引記錄間隙不變，防止其他事物在這個間隙進行insert操作，產生幻讀，在RR隔離級別下都支持。

3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合，同時鎖住數據，并鎖住數據前面的間隙Gap,在RR隔離級別下支持。

innodb實現了以下兩種類型的行鎖

1、共享鎖 S: 允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。

2、排他鎖 X: 允許獲取排他鎖的事務更新數據，阻止其他事務獲得相同數據集的共享鎖和排他鎖。

insert 語句 排他鎖 自動添加的

update語句 排他鎖 自動添加

delete 語句 排他鎖 自動添加

select 正常查詢語句 不加鎖 。。。

select 。。。lock in share mode 共享鎖 需要手動在select 之后加lock in share mode

select 。。。for update 排他鎖 需要手動在select之后添加for update

默認情況下，innodb在repeatable read事務隔離級別運行，innodb使用next-key鎖進行搜索和索引掃描，以防止幻讀。

間隙鎖唯一目的是防止其它事務插入間隙，間隙鎖可以共存，一個事務采用的間隙鎖不會阻止另一個事務在同一間隙上采用的間隙鎖。