MySQL - for update 行鎖 表鎖

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

創(chuàng)新互聯(lián)建站主要從事成都網(wǎng)站設(shè)計、成都網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)唐山,10年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18982081108

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

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

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

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

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

InnoDB 既實現(xiàn)了行鎖，也實現(xiàn)了表鎖。

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

假設(shè)表 user，存在有id跟name字段，id是主鍵，有5條數(shù)據(jù)。

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

無主鍵/索引，表級鎖

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

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

參考博文：

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

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

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

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

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

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

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

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

2、釋放鎖 unlock tables 或者關(guān)閉客戶端連接

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

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

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

查看元數(shù)據(jù)鎖:

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

意向鎖，為了避免DML在執(zhí)行時，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖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;

行級鎖，每次操作鎖住對應(yīng)的行數(shù)據(jù)，鎖定粒度最小，發(fā)生鎖沖突的概率最高，并發(fā)讀最高，應(yīng)用在innodb存儲引擎中。

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

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

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

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

innodb實現(xiàn)了以下兩種類型的行鎖

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

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

insert 語句 排他鎖 自動添加的

update語句 排他鎖 自動添加

delete 語句 排他鎖 自動添加

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

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

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

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

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

MySQL中innodb的行鎖算法

眾所周知，innodb是默認行鎖，當(dāng)然也支持表鎖。如下是對于行鎖的算法進行的一些實驗。

鎖的算法為：我知道是行鎖，但是是如何鎖的，鎖多少數(shù)據(jù)

假如有個索引是：[1，2，3，7]

record lock 鎖的是 1，2，3，7

gap lock 鎖的是 (- ，1)，（2，3），（3，7），（7，+ ）反正鎖的就是區(qū)間，不是行

next-key lock鎖的是 (- ，1]，[2，3），[3，7），[ 7，+ ）既鎖范圍也鎖行

Innodb鎖算法規(guī)則如下：

在可重復(fù)讀隔離級別下，innodb默認使用的是next-key lock算法，當(dāng)查詢的索引是主鍵或者唯一索引的情況下，才會退化為record lock，在使用next-key lock算法時，不僅僅會鎖住范圍，還會給范圍最后的一個鍵值加一個gap lock。

其中l(wèi)ockmode中的X鎖為左邊會話中的鎖，因為需要顯式的commit之后才會釋放鎖，第二個S鎖，為右邊的共享鎖，因為主鍵ID為1的已經(jīng)被鎖住了，所以處于鎖等待狀態(tài)，鎖的類型為record lock

使用輔助索引a=8進行操作，這個時候理論應(yīng)該對主鍵索引加record lock 則 主鍵ID=8的被鎖，然后輔助索引被加next-key lock 則為：

(7,8] 然后對下一個鍵值加gap鎖，則為：（8，11）

所以目前被鎖住的記錄為：

1.主鍵為8的被鎖

2.輔助索引8的被鎖

3.輔助索引8到11之間的被鎖，意味著你這個時候往8到11之間寫數(shù)據(jù)會報錯

當(dāng)使用范圍條件進行更新時，此時肯定是需要加X鎖的，我是用的也是主鍵，所以按照理論應(yīng)該是加的record lock ,但是卻加了gap lock，因為插入值為10的阻塞了，查看information 也提示X.GAP

這個有點暈為啥主鍵變成了next-key lock ，不應(yīng)該是record lock么？

update20200515

在知乎看到的一個解釋：

即，在無論使用主鍵索引還是非主鍵索引的時候，請求共享鎖或者排他鎖，innodb會給范圍內(nèi)的記錄加鎖，而范圍內(nèi)的間隙也會被加鎖，

例如一個表t 的 id為1，2，3，7，10

假如執(zhí)行如下：

select * from t where id =3 for update

那么這個時候執(zhí)行

insert into t(id) values(8) 會被阻塞，因為是在請求排他鎖時使用了范圍，所以[3,10],甚至10以后的任何數(shù)據(jù)都無法插入。

執(zhí)行

select * from t where id =3 lock in share mode

insert into t(id) values(8) 會被阻塞，因為是在請求共享鎖時使用了范圍，所以[3,10],甚至10以后的任何數(shù)據(jù)都無法插入。

幻讀是同一事務(wù)下，連續(xù)執(zhí)行兩次同樣的sql可能導(dǎo)致不同的結(jié)果，第二次返回的數(shù)據(jù)可能導(dǎo)致以前不存在的行。

同時一般會問它和臟讀的區(qū)別，臟讀為讀取到其他事務(wù)未提交的數(shù)據(jù)，但是幻讀是讀取的其他事務(wù)已經(jīng)提交的數(shù)據(jù)。

reference：

請教一下mysql 行鎖命令是什么?

MySQL 5.1支持對MyISAM和MEMORY表進行表級鎖定，對BDB表進行頁級鎖定，對InnoDB表進行行級鎖定。

如果不能同時插入，為了在一個表中進行多次INSERT和SELECT操作，可以在臨時表中插入行并且立即用臨時表中的記錄更新真正的表。

這可用下列代碼做到：

mysql LOCK TABLES real_table WRITE, insert_table WRITE;

mysql INSERT INTO real_table SELECT * FROM insert_table;

mysql TRUNCATE TABLE insert_table;

mysql UNLOCK TABLES;

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

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

MYSQL行級鎖使用方法

當(dāng) web 日志中出現(xiàn)行鎖超時錯誤后，很多開發(fā)都會找我來排查問題，這里說下問題定位的難點！

1. MySQL 本身不會主動記錄行鎖等待的相關(guān)信息，所以無法有效的進行事后分析。

2. 鎖爭用原因有多種，很難在事后判斷到底是哪一類問題場景，尤其是事后無法復(fù)現(xiàn)問題的時候。

3. 找到問題 SQL 后，開發(fā)無法有效從代碼中挖掘出完整的事務(wù)，這也和公司框架-產(chǎn)品-項目的架構(gòu)有關(guān)，需要靠 DBA 事后采集完整的事務(wù) SQL 才可以進行分析。