mysql如何用事務(wù)和鎖 鎖住某一行數(shù)據(jù)，使得不允許兩個(gè)用戶同時(shí)讀取一行數(shù)據(jù)??！

1、在mysql數(shù)據(jù)庫中如何鎖定一行數(shù)據(jù)，保證不被其他的操作影響。

公司主營(yíng)業(yè)務(wù)：成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、移動(dòng)網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)推出禮縣免費(fèi)做網(wǎng)站回饋大家。

2、從對(duì)數(shù)據(jù)的操作類型分為讀鎖和寫鎖。從對(duì)數(shù)據(jù)操作的粒度來分：表鎖和行鎖。

3、現(xiàn)在我們建立一個(gè)表來演示數(shù)據(jù)庫的行鎖講解。

4、行鎖基本演示如下圖所示。

5、如果兩個(gè)會(huì)話操作的是不同的行，就不會(huì)互相阻塞了。

(25)行鎖實(shí)現(xiàn)

行鎖在 InnoDB 中 基于索引實(shí)現(xiàn) ，如加鎖沒用索引，退化為表鎖

概要：2 相同索引，訪問不同行，鎖沖突? ?

? ? ? ?3 多索引，不同索引鎖定不同行

? ? ? ?4 小表或數(shù)據(jù)轉(zhuǎn)換，可能表鎖，不走索引

三種行鎖：Record Lock（唯一索引/主鍵列 精確查找，否則退化）

? Gap Lock （非唯一索引，不包含記錄本身）

? Next-Key Lock （非唯一索引，鎖左開右閉，包含記錄本身）

沒索引，加排他鎖，等待? ??

加索引后

id有索引，name沒索引

主鍵、唯一或普通索引都用行鎖：id主鍵索引，name普通索引

檢索值的 數(shù)據(jù)類型 與 索引 字段 不同 ，雖MySQL能轉(zhuǎn)換，但不用索引， 導(dǎo)致表鎖

name是varchar，不和varchar比較，轉(zhuǎn)換name

1） id?列必須為 唯一索引 或 主鍵列 ? ?? 2）?必須為精準(zhǔn)匹配（=） ，不能為?、、like等

否則 退化臨鍵鎖

基于 非唯一索引 ， 鎖間，不包含記錄本身 ?；贜ext-Key Locking?算法

SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

鎖?。?，10）， 2、3、4、5、6、7、8、9阻塞，1 和 10 不被鎖

ps：除手動(dòng)加鎖 ，執(zhí)行完某些 SQL ，InnoDB 自動(dòng)加間隙鎖

特殊間隙鎖 ， 包含記錄本身 ， 解決幻讀

1） 每個(gè) 非唯一索引 列都有臨鍵鎖（只與非唯一索引列有關(guān)）， 鎖左開右閉

2） UPDATE、FOR UPDATE、LOCK IN SHARE MODE操作時(shí)，InnoDB 獲取 記錄行 臨鍵鎖

3）例：age 臨鍵鎖 ：(-∞, 10],(10, 20],(20, 30],(30, +∞]? ?? 鎖住 (10, 30)

事務(wù) A：?UPDATE table SET name = Vladimir WHERE age = 20? 或???? SELECT * FROM table WHERE age = 20 FOR UPDATE ;??????? ?獲取(20, 30] 臨鍵鎖

事務(wù) B ： INSERT INTO table VALUES(100, 20 , 'Aragorn');? ? ? 或? INSERT INTO table VALUES(100, 26 , 'Aragorn');? ?被阻塞

INSERT INTO table VALUES(100, 20, 'Aragorn');

MySQL中innodb的行鎖算法

眾所周知，innodb是默認(rèn)行鎖，當(dāng)然也支持表鎖。如下是對(duì)于行鎖的算法進(jìn)行的一些實(shí)驗(yàn)。

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

假如有個(gè)索引是：[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ù)讀隔離級(jí)別下，innodb默認(rèn)使用的是next-key lock算法，當(dāng)查詢的索引是主鍵或者唯一索引的情況下，才會(huì)退化為record lock，在使用next-key lock算法時(shí)，不僅僅會(huì)鎖住范圍，還會(huì)給范圍最后的一個(gè)鍵值加一個(gè)gap lock。

其中l(wèi)ockmode中的X鎖為左邊會(huì)話中的鎖，因?yàn)樾枰@式的commit之后才會(huì)釋放鎖，第二個(gè)S鎖，為右邊的共享鎖，因?yàn)橹麈IID為1的已經(jīng)被鎖住了，所以處于鎖等待狀態(tài)，鎖的類型為record lock

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

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

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

1.主鍵為8的被鎖

2.輔助索引8的被鎖

3.輔助索引8到11之間的被鎖，意味著你這個(gè)時(shí)候往8到11之間寫數(shù)據(jù)會(huì)報(bào)錯(cuò)

當(dāng)使用范圍條件進(jìn)行更新時(shí)，此時(shí)肯定是需要加X鎖的，我是用的也是主鍵，所以按照理論應(yīng)該是加的record lock ,但是卻加了gap lock，因?yàn)椴迦胫禐?0的阻塞了，查看information 也提示X.GAP

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

update20200515

在知乎看到的一個(gè)解釋：

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

例如一個(gè)表t 的 id為1，2，3，7，10

假如執(zhí)行如下：

select * from t where id =3 for update

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

insert into t(id) values(8) 會(huì)被阻塞，因?yàn)槭窃谡?qǐng)求排他鎖時(shí)使用了范圍，所以[3,10],甚至10以后的任何數(shù)據(jù)都無法插入。

執(zhí)行

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

insert into t(id) values(8) 會(huì)被阻塞，因?yàn)槭窃谡?qǐng)求共享鎖時(shí)使用了范圍，所以[3,10],甚至10以后的任何數(shù)據(jù)都無法插入。

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

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

reference：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

意向鎖，為了避免DML在執(zhí)行時(shí)，加的行鎖與表鎖的沖突，在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;

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

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

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

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

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

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

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

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

insert 語句 排他鎖 自動(dòng)添加的

update語句 排他鎖 自動(dòng)添加

delete 語句 排他鎖 自動(dòng)添加

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

select 。。。lock in share mode 共享鎖 需要手動(dòng)在select 之后加lock in share mode

select 。。。for update 排他鎖 需要手動(dòng)在select之后添加for update

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

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