MySQL鎖

對(duì)表的增刪改查，都需要MDL鎖，無(wú)所不在

成都創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供白云網(wǎng)站建設(shè)、白云做網(wǎng)站、白云網(wǎng)站設(shè)計(jì)、白云網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、白云企業(yè)網(wǎng)站模板建站服務(wù)，10多年白云做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

MDL讀鎖之間不互斥，但MDL讀寫(xiě)鎖互斥

#舉個(gè)栗子

假設(shè)t是一張大表

session1對(duì)t執(zhí)行一個(gè)查詢（SR）

session2對(duì)t執(zhí)行一個(gè)DDL（SU，可能升級(jí)到X）

session3對(duì)t執(zhí)行一個(gè)查詢（SR）

可知session1持有t表的MDL讀鎖（SR），session1的查詢還沒(méi)有結(jié)束的時(shí)候，去執(zhí)行session2的DDL（SU），此時(shí)session2需要MDL寫(xiě)鎖（SU升級(jí)到X，需要X鎖），由于MDL讀寫(xiě)鎖互斥，因此session2需要等待session1釋放MDL讀鎖（SR阻塞X）；同時(shí)session2對(duì)后面的所有MDL讀鎖互斥（X阻塞SR），因此session2又繼續(xù)阻塞了session3...

#注釋：一開(kāi)始的DDL能看到的狀態(tài)是SU，但如果SU的某個(gè)階段被阻塞，會(huì)被升級(jí)到X，從而引發(fā)SR阻塞X，達(dá)到實(shí)驗(yàn)的效果。但實(shí)際測(cè)試中，DDL是分階段的，如果沒(méi)有滿足一定的要求，就不會(huì)引發(fā)阻塞，看到的結(jié)果就是SR和SU并沒(méi)有互相阻塞。這個(gè)過(guò)程需要具體的去查看源碼，此處不展開(kāi)。

事務(wù)中的MDL鎖在語(yǔ)句開(kāi)始時(shí)申請(qǐng)，但并不會(huì)在語(yǔ)句結(jié)束后就馬上釋放，而是會(huì)等到事務(wù)結(jié)束時(shí)才進(jìn)行釋放

忙時(shí)對(duì)大表DDL會(huì)產(chǎn)生的災(zāi)難性的結(jié)果就是：如果后續(xù)對(duì)該表有查詢操作，而且web端又有重試機(jī)制的話，那么會(huì)有一個(gè)新的session再次發(fā)起讀請(qǐng)求，反復(fù)如此，線程池就會(huì)在短時(shí)間內(nèi)爆炸

在線執(zhí)行DDL的時(shí)候，需要檢查一下information_schema.innodb_trx表中有沒(méi)有當(dāng)前操作表對(duì)應(yīng)的事務(wù)，此外還可以使用ALTER TABLE tbl_name NOWAIT...進(jìn)行操作（MySQL8.0新特性）

eg.

session1

select * from cpf where payid'xxx'

union

select * from cpf where payid'xxx'

union (union重復(fù)50次，確保查詢時(shí)間幾十秒以上)

session2

alter table cpf modify payer_userid varchar(500);

session3

select * from cpf where payer_userid='18051512003600300034';

#執(zhí)行結(jié)果

session1執(zhí)行了31秒，當(dāng)session1完成的時(shí)候session2和session3相繼完成

在session4中執(zhí)行show processlist，結(jié)果如下

#變種1

如果session1在執(zhí)行select之前，添加一句start transaction

會(huì)發(fā)現(xiàn)session1什么時(shí)候執(zhí)行完commit，sesssion2和session3什么時(shí)候完成

也就是證實(shí)了在事務(wù)中的MDL鎖，在語(yǔ)句查詢完之后并不會(huì)釋放，而是會(huì)隨著事務(wù)的釋放而釋放

#變種2

session1和session3在執(zhí)行select之前，添加一句start transaction，然后session1,2,3依次按順序執(zhí)行

會(huì)發(fā)現(xiàn)session1阻塞了session2，而session3在執(zhí)行完start transaction之后就被阻塞，根本沒(méi)有辦法去執(zhí)行后面的select

當(dāng)session1執(zhí)行commit釋放之后，session2仍然處于阻塞狀態(tài)，session3亦是如此

直到session2或者session3當(dāng)中任意一個(gè)執(zhí)行了停止（navicat客戶端操作，類似于rollback）后，另一個(gè)才能完成執(zhí)行

單純從變種2的結(jié)果來(lái)看，MDL鎖并沒(méi)有按照?qǐng)?zhí)行時(shí)間的先后來(lái)進(jìn)行分配，當(dāng)session1的鎖釋放之后，session3先獲得了讀鎖

MySQL是server-engine結(jié)構(gòu)，MDL鎖是server層的鎖

通過(guò)show processlist可以發(fā)現(xiàn)waiting for table metadata lock，但這還遠(yuǎn)遠(yuǎn)不夠，需要在performance_schema庫(kù)中進(jìn)行設(shè)置（MySQL8.0默認(rèn)開(kāi)啟）

5.7臨時(shí)開(kāi)啟

UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME='wait/lock/metadata/sql/mdl';

5.7永久開(kāi)啟（修改cnf配置）

[mysqld]

performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'

global：全局級(jí)（FTWRL）

schema：庫(kù)級(jí)（drop database）

table：表級(jí)（lock table read/write）

commit：提交級(jí)

關(guān)于global對(duì)象，主要作用是防止DDL和寫(xiě)操作的過(guò)程中，執(zhí)行set golbal_read_only = on或flush tables with read lock。

關(guān)于commit對(duì)象鎖，主要作用是執(zhí)行flush tables with read lock后，防止已經(jīng)開(kāi)始在執(zhí)行的寫(xiě)事務(wù)提交。insert/update/delete在提交時(shí)都會(huì)上（COMMIT，MDL_EXPLICIT，MDL_INTENTION_EXCLUSIVE）鎖

DML和DDL在執(zhí)行之前都會(huì)申請(qǐng)IX鎖，DML會(huì)在global級(jí)別上加，而DDL會(huì)在global和schema這2個(gè)級(jí)別上都加IX（也就是2把鎖）

IX與大部分鎖都是兼容的，除了S，當(dāng)然了X肯定是不兼容的；但I(xiàn)X與IX之間是兼容的，比如下圖

flush table with read lock會(huì)持有這個(gè)鎖（在global級(jí)別和commit級(jí)別）

FTWRL在全局級(jí)和事務(wù)級(jí)上分別加上了S鎖

IX與S是不兼容的

所以DML和DDL都會(huì)與FTWRL產(chǎn)生阻塞

邏輯備份第一句：flush table with read lock（S鎖）

大表DML（IX鎖）

先執(zhí)行的阻塞后執(zhí)行的，邏輯備份之前需要檢查是否有在線DDL（X鎖）以及DML（IX鎖），否則邏輯備份產(chǎn)生等待；盡量不要在忙時(shí)進(jìn)行邏輯備份，否則阻礙忙時(shí)DML

如下圖，前面2行是FTWRL持有的S鎖，第3行是一個(gè)update語(yǔ)句，IX直接被阻塞，處于pending的鎖等待狀態(tài)；同時(shí)由于S鎖的持有時(shí)間為EXPLICIT，表明FTWRL需要一個(gè)顯示的釋放（unlock tables）

DML并不是只有IX鎖，DML和select .. for update在執(zhí)行中持有的鎖實(shí)際是SW鎖（DML需要找一個(gè)大一點(diǎn)的表來(lái)驗(yàn)證，目前只驗(yàn)證了select .. for update），IX只是DML初期需要獲得的鎖

如下圖是一個(gè)select for update語(yǔ)句，start transaction對(duì)應(yīng)的是第2行的SR鎖，而語(yǔ)句本身對(duì)應(yīng)的是SW鎖

如果在此時(shí)執(zhí)行一個(gè)FTWRL，我們會(huì)發(fā)現(xiàn)2個(gè)會(huì)話并不會(huì)相互阻塞（因?yàn)镾鎖與SR和SW都是兼容的），如下圖

但如果我們是先執(zhí)行的FTWRL再執(zhí)行的select for update，那么畫(huà)風(fēng)就不是像上圖那樣了

如下圖所示，在先執(zhí)行FTWRL的情況下，select for update壓根沒(méi)有獲得SW鎖，而是在獲取IX鎖的過(guò)程中就受挫了，一直處于pending狀態(tài)。（如果這個(gè)S鎖不釋放，那么后面的IX會(huì)一直等待，直到超時(shí)）

S鎖除了邏輯備份時(shí)的FTWRL以外，createa table as也會(huì)持有這個(gè)鎖

目前已知的是desc操作會(huì)持有這個(gè)SH鎖

SH鎖與絕大部分鎖都兼容，除開(kāi)X鎖

也就是說(shuō)在做rename一類的操作的時(shí)候，你是無(wú)法去執(zhí)行desc的

前面提到的start transaction，以及所有的非當(dāng)前讀都需要持有這個(gè)鎖

非當(dāng)前讀的意思就是快照讀，也就是普通的select

與SR鎖有沖突的有2個(gè)，一個(gè)是X，另一個(gè)是SNRW

研發(fā)有時(shí)候會(huì)很困惑的問(wèn)我，“我這個(gè)表只有幾十行數(shù)據(jù)，select查不出來(lái)？？？”? 這時(shí)候就需要檢查MDL鎖了

當(dāng)前讀需要持有此鎖，常見(jiàn)的DML和select for update都對(duì)應(yīng)此鎖，但不包括DDL

與SW鎖有沖突的有4個(gè)，SU,SRO,SNRW,X

看到一種說(shuō)法是這個(gè)鎖僅對(duì)MyISAM引擎生效，沖突范圍與SW鎖類似

部分alter語(yǔ)句會(huì)持有該鎖。該鎖可能會(huì)升級(jí)成SNW,SNRW,X；而X鎖也有可能逐步降級(jí)到SU鎖

SU鎖和SU,SNW,SNRW,X鎖互斥

表面看起來(lái)DML的SW鎖和SU鎖不互斥（DML和DDL），但實(shí)際上因?yàn)镾U鎖存在升級(jí)的屬性，SU鎖會(huì)升級(jí)到SNW鎖，從而和SW產(chǎn)生互斥

如下圖，SU并沒(méi)有被SW鎖阻塞，但升級(jí)到SNW之后，SNW被SW阻塞，一直處于pending狀態(tài)

SU鎖的兼容性如下

查看改過(guò)源碼的例子，在執(zhí)行alter的時(shí)候，SU會(huì)升級(jí)到X，之后X降級(jí)到SU，然后SU再升級(jí)到X

先SU，再SW，SW被SU阻塞

先SW，再SU，SU并未被SW阻塞，但是SU向上升級(jí)的過(guò)程中產(chǎn)生的SNW被SW阻塞；于是將SW的會(huì)話commit，之后SNW向下降級(jí)成SU，并成功獲得鎖；

所以雖然看起來(lái)SW和SU不是一個(gè)雙向阻塞，但實(shí)際效果就是雙向阻塞，無(wú)論DML和DDL誰(shuí)在前面，都必然會(huì)發(fā)生相互的阻塞

不兼容的有點(diǎn)多，先貼一個(gè)兼容性

SU升級(jí)X的過(guò)程中會(huì)升級(jí)成SNW

SU升級(jí)成X的過(guò)程中，有一個(gè)copy的過(guò)程，這個(gè)過(guò)程就是SNW，在這個(gè)copy的過(guò)程中，允許DML但是不允許select（SR）

copy是一個(gè)非常耗時(shí)的過(guò)程

lock tables read的語(yǔ)句會(huì)持有這個(gè)鎖

SRO阻塞SW，SNRW，X

兼容性如圖

lock tables write的語(yǔ)句會(huì)持有這個(gè)鎖

阻塞的鎖非常多，除開(kāi)SH和S以外，其他的都阻塞，連SR都阻塞了

兼容性如下

換句話說(shuō)flush tables with read lock; （S）會(huì)堵塞lock table write; （SNRW）

但是flush tables with read lock;（S）卻不會(huì)堵塞lock table read （SRO）

阻塞一切

各種DDL均屬于這個(gè)范疇

create,drop,rename? （alter table add column也屬于這個(gè)范疇）

SW鎖阻塞X鎖，（X鎖是為了去執(zhí)行一個(gè)drop）

X鎖阻塞SH

thread104在做一個(gè)create table as的表復(fù)制操作，在表里面并沒(méi)有發(fā)現(xiàn)X鎖的信息，在thread95上對(duì)新表做一個(gè)desc操作，可以看到SH鎖處于等待狀態(tài)，然而這里阻礙SH的并不是X鎖

只有1行的select被堵住

thread95做一個(gè)start transaction之后不提交，thread107對(duì)95的表做出一個(gè)rename操作，X鎖被前面的SR鎖阻塞，這時(shí)候thread108對(duì)該表發(fā)起一個(gè)limit僅僅為1的查詢，但被X鎖阻塞。由于lock_wait_timeout這個(gè)參數(shù)通常是1年，所以一連串查詢被堵死

alter開(kāi)頭的幾個(gè)SQL，無(wú)論是modify還是add，查詢出來(lái)都是SU鎖，但DDL是一個(gè)過(guò)程，其中的有一部分如果發(fā)生了阻塞，可能會(huì)發(fā)現(xiàn)是X鎖阻塞；拿SR阻塞X鎖的實(shí)驗(yàn)來(lái)說(shuō)，SR阻塞X的過(guò)程非常短暫，如果沒(méi)有剛好卡到那個(gè)點(diǎn)，看到的結(jié)果可能就是SR和SU互不干涉，但如果卡到那個(gè)點(diǎn)，就會(huì)觀測(cè)到X被SR所阻塞。具體的需要讀源碼，這里不展開(kāi)

SELECT

locked_schema,

locked_table,

locked_type,

waiting_processlist_id,

waiting_age,

waiting_query,

waiting_state,

blocking_processlist_id,

blocking_age,

substring_index(sql_text,"transaction_begin;" ,-1)ASblocking_query,

sql_kill_blocking_connection

FROM

(

SELECT

b.OWNER_THREAD_IDASgranted_thread_id,

a.OBJECT_SCHEMAASlocked_schema,

a.OBJECT_NAMEASlocked_table,

"Metadata Lock"ASlocked_type,

c.PROCESSLIST_IDASwaiting_processlist_id,

c.PROCESSLIST_TIMEASwaiting_age,

c.PROCESSLIST_INFOASwaiting_query,

c.PROCESSLIST_STATEASwaiting_state,

d.PROCESSLIST_IDASblocking_processlist_id,

d.PROCESSLIST_TIMEASblocking_age,

d.PROCESSLIST_INFOASblocking_query,

concat('KILL', d.PROCESSLIST_ID)ASsql_kill_blocking_connection

FROM

performance_schema.metadata_locks a

JOINperformance_schema.metadata_locks bONa.OBJECT_SCHEMA=b.OBJECT_SCHEMA

ANDa.OBJECT_NAME=b.OBJECT_NAME

ANDa.lock_status='PENDING'

ANDb.lock_status='GRANTED'

ANDa.OWNER_THREAD_IDb.OWNER_THREAD_ID

ANDa.lock_type='EXCLUSIVE'

JOINperformance_schema.threads cONa.OWNER_THREAD_ID=c.THREAD_ID

JOINperformance_schema.threads dONb.OWNER_THREAD_ID=d.THREAD_ID

) t1,

(

SELECT

thread_id,

group_concat(CASEWHENEVENT_NAME='statement/sql/begin'THEN"transaction_begin"ELSEsql_textENDORDERBYevent_id SEPARATOR ";" )ASsql_text

FROM

performance_schema.events_statements_history

GROUPBYthread_id

) t2

WHERE

t1.granted_thread_id=t2.thread_id

MDL鎖處理

MDL元數(shù)據(jù)鎖

快速處理MDL鎖

mysql讀數(shù)據(jù)時(shí)怎么加寫(xiě)鎖

加鎖情況與死鎖原因分析

為方便大家復(fù)現(xiàn)，完整表結(jié)構(gòu)和數(shù)據(jù)如下：

CREATE TABLE `t3` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

UNIQUE KEY `c2` (`c2`)

) ENGINE=InnoDB

insert into t3 values(1,1),(15,15),(20,20);

在 session1 執(zhí)行 commit 的瞬間，我們會(huì)看到 session2、session3 的其中一個(gè)報(bào)死鎖。這個(gè)死鎖是這樣產(chǎn)生的：

1.?session1 執(zhí)行 delete ?會(huì)在唯一索引 c2 的 c2 = 15 這一記錄上加 X lock（也就是在MySQL 內(nèi)部觀測(cè)到的：X Lock but not gap）；

2.?session2 和 session3 在執(zhí)行 insert 的時(shí)候，由于唯一約束檢測(cè)發(fā)生唯一沖突，會(huì)加 S Next-Key Lock，即對(duì) (1,15] 這個(gè)區(qū)間加鎖包括間隙，并且被 seesion1 的 X Lock 阻塞，進(jìn)入等待；

3.?session1 在執(zhí)行 commit 后，會(huì)釋放 X Lock，session2 和 session3 都獲得 S Next-Key Lock；

4.?session2 和 session3 繼續(xù)執(zhí)行插入操作，這個(gè)時(shí)候 INSERT INTENTION LOCK（插入意向鎖）出現(xiàn)了，并且由于插入意向鎖會(huì)被 gap 鎖阻塞，所以 session2 和 session3 互相等待，造成死鎖。

死鎖日志如下：

請(qǐng)點(diǎn)擊輸入圖片描述

INSERT INTENTION LOCK

在之前的死鎖分析第四點(diǎn)，如果不分析插入意向鎖，也是會(huì)造成死鎖的，因?yàn)椴迦胱罱K還是要對(duì)記錄加 X Lock 的，session2 和 session3 還是會(huì)互相阻塞互相等待。

但是插入意向鎖是客觀存在的，我們可以在官方手冊(cè)中查到，不可忽略：

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

插入意向鎖其實(shí)是一種特殊的 gap lock，但是它不會(huì)阻塞其他鎖。假設(shè)存在值為 4 和 7 的索引記錄，嘗試插入值 5 和 6 的兩個(gè)事務(wù)在獲取插入行上的排它鎖之前使用插入意向鎖鎖定間隙，即在（4，7）上加 gap lock，但是這兩個(gè)事務(wù)不會(huì)互相沖突等待。

當(dāng)插入一條記錄時(shí)，會(huì)去檢查當(dāng)前插入位置的下一條記錄上是否存在鎖對(duì)象，如果下一條記錄上存在鎖對(duì)象，就需要判斷該鎖對(duì)象是否鎖住了 gap。如果 gap 被鎖住了，則插入意向鎖與之沖突，進(jìn)入等待狀態(tài)（插入意向鎖之間并不互斥）?？偨Y(jié)一下這把鎖的屬性：

1. 它不會(huì)阻塞其他任何鎖；

2. 它本身僅會(huì)被 gap lock 阻塞。

在學(xué)習(xí) MySQL 過(guò)程中，一般只有在它被阻塞的時(shí)候才能觀察到，所以這也是它常常被忽略的原因吧...

GAP LOCK

在此例中，另外一個(gè)重要的點(diǎn)就是 gap lock，通常情況下我們說(shuō)到 gap lock 都只會(huì)聯(lián)想到 REPEATABLE-READ 隔離級(jí)別利用其解決幻讀。但實(shí)際上在 READ-COMMITTED 隔離級(jí)別，也會(huì)存在 gap lock ，只發(fā)生在：唯一約束檢查到有唯一沖突的時(shí)候，會(huì)加 S Next-key Lock，即對(duì)記錄以及與和上一條記錄之間的間隙加共享鎖。

通過(guò)下面這個(gè)例子就能驗(yàn)證：

請(qǐng)點(diǎn)擊輸入圖片描述

這里 session1 插入數(shù)據(jù)遇到唯一沖突，雖然報(bào)錯(cuò)，但是對(duì) (15,20] 加的 S Next-Key Lock 并不會(huì)馬上釋放，所以 session2 被阻塞。另外一種情況就是本文開(kāi)始的例子，當(dāng) session2 插入遇到唯一沖突但是因?yàn)楸?X Lock 阻塞，并不會(huì)立刻報(bào)錯(cuò) “Duplicate key”，但是依然要等待獲取 S Next-Key Lock 。

有個(gè)困惑很久的疑問(wèn)：出現(xiàn)唯一沖突需要加 S Next-Key Lock 是事實(shí)，但是加鎖的意義是什么？還是說(shuō)是通過(guò) S Next-Key Lock 來(lái)實(shí)現(xiàn)的唯一約束檢查，但是這樣意味著在插入沒(méi)有遇到唯一沖突的時(shí)候，這個(gè)鎖會(huì)立刻釋放，這不符合二階段鎖原則。這點(diǎn)希望能與大家一起討論得到好的解釋。

如果是在 REPEATABLE-READ，除以上所說(shuō)的唯一約束沖突外，gap lock 的存在是這樣的：

普通索引（非唯一索引）的S/X Lock，都帶 gap 屬性，會(huì)鎖住記錄以及前1條記錄到后1條記錄的左閉右開(kāi)區(qū)間，比如有[4,6,8]記錄，delete 6，則會(huì)鎖住[4,8）整個(gè)區(qū)間。

對(duì)于 gap lock，相信 DBA 們的心情是一樣一樣的，所以我的建議是：

1. 在絕大部分的業(yè)務(wù)場(chǎng)景下，都可以把 MySQL 的隔離界別設(shè)置為 READ-COMMITTED；

2. 在業(yè)務(wù)方便控制字段值唯一的情況下，盡量減少表中唯一索引的數(shù)量。

鎖沖突矩陣

前面我們說(shuō)的 GAP LOCK 其實(shí)是鎖的屬性，另外我們知道 InnoDB 常規(guī)鎖模式有：S 和 X，即共享鎖和排他鎖。鎖模式和鎖屬性是可以隨意組合的，組合之后的沖突矩陣如下，這對(duì)我們分析死鎖很有幫助：

請(qǐng)點(diǎn)擊輸入圖片描述

mysql怎么設(shè)置悲觀鎖

在mysql中可以使用select…for update實(shí)現(xiàn)悲觀鎖。這樣那條數(shù)據(jù)就被我們鎖定了，其它的事務(wù)必須等本次事務(wù)提交之后才能執(zhí)行。從而保證數(shù)據(jù)不會(huì)被其他事務(wù)更改從而導(dǎo)致數(shù)據(jù)的異常。但是select…for update不會(huì)阻塞select的查詢。

需要注意的是mysql在采用InnoDB時(shí)，默認(rèn)為行鎖，且只有明確額指定主鍵，MySQL 才會(huì)執(zhí)行行鎖，鎖住對(duì)應(yīng)的那條數(shù)據(jù)，否則MySQL 將會(huì)執(zhí)行表鎖(將整個(gè)數(shù)據(jù)表單給鎖住)。

新聞名稱：mysql鎖怎么設(shè)置,mysql鎖有幾種方式
分享URL：http://m.rwnh.cn/article30/dsdhppo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信公眾號(hào)、網(wǎng)站設(shè)計(jì)公司、企業(yè)建站、定制開(kāi)發(fā)、做網(wǎng)站、商城網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)