MySQL的這些操作中哪些操作會產(chǎn)生鎖？

根據(jù)我之前接觸到的此類問題，大致可以分為以下幾種原因：

十余年的襄汾網(wǎng)站建設經(jīng)驗，針對設計、前端、開發(fā)、售后、文案、推廣等六對一服務，響應快，48小時及時工作處理。網(wǎng)絡營銷推廣的優(yōu)勢是能夠根據(jù)用戶設備顯示端的尺寸不同，自動調(diào)整襄汾建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設計，從而大程度地提升瀏覽體驗。創(chuàng)新互聯(lián)從事“襄汾網(wǎng)站設計”,“襄汾網(wǎng)站推廣”以來，每個客戶項目都認真落實執(zhí)行。

1. 程序中非數(shù)據(jù)庫交互操作導致事務掛起

將接口調(diào)用或者文件操作等這一類非數(shù)據(jù)庫交互操作嵌入在 SQL 事務代碼之中，那么整個事務很有可能因此掛起（接口不通等待超時或是上傳下載大附件）。

2. 事務中包含性能較差的查詢 SQL

事務中存在慢查詢，導致同一個事務中的其他 DML 無法及時釋放占用的行鎖，引起行鎖等待。

3. 單個事務中包含大量 SQL

通常是由于在事務代碼中加入 for 循環(huán)導致，雖然單個 SQL 運行很快，但是 SQL 數(shù)量一大，事務就會很慢。

4. 級聯(lián)更新 SQL 執(zhí)行時間較久

這類 SQL 容易讓人產(chǎn)生錯覺，例如：update A set ... where ...in (select B) 這類級聯(lián)更新，不僅會占用 A 表上的行鎖，也會占用 B 表上的行鎖，當 SQL 執(zhí)行較久時，很容易引起 B 表上的行鎖等待。

5. 磁盤問題導致的事務掛起

極少出現(xiàn)的情形，比如存儲突然離線，SQL 執(zhí)行會卡在內(nèi)核調(diào)用磁盤的步驟上，一直等待，事務無法提交。

綜上可以看出，如果事務長時間未提交，且事務中包含了 DML 操作，那么就有可能產(chǎn)生行鎖等待，引起報錯。

MySQL鎖

對表的增刪改查，都需要MDL鎖，無所不在

MDL讀鎖之間不互斥，但MDL讀寫鎖互斥

#舉個栗子

假設t是一張大表

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

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

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

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

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

事務中的MDL鎖在語句開始時申請，但并不會在語句結(jié)束后就馬上釋放，而是會等到事務結(jié)束時才進行釋放

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

在線執(zhí)行DDL的時候，需要檢查一下information_schema.innodb_trx表中有沒有當前操作表對應的事務，此外還可以使用ALTER TABLE tbl_name NOWAIT...進行操作（MySQL8.0新特性）

eg.

session1

select * from cpf where payid'xxx'

union

select * from cpf where payid'xxx'

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

session2

alter table cpf modify payer_userid varchar(500);

session3

select * from cpf where payer_userid='18051512003600300034';

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

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

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

#變種1

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

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

也就是證實了在事務中的MDL鎖，在語句查詢完之后并不會釋放，而是會隨著事務的釋放而釋放

#變種2

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

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

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

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

單純從變種2的結(jié)果來看，MDL鎖并沒有按照執(zhí)行時間的先后來進行分配，當session1的鎖釋放之后，session3先獲得了讀鎖

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

通過show processlist可以發(fā)現(xiàn)waiting for table metadata lock，但這還遠遠不夠，需要在performance_schema庫中進行設置（MySQL8.0默認開啟）

5.7臨時開啟

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

5.7永久開啟（修改cnf配置）

[mysqld]

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

global：全局級（FTWRL）

schema：庫級（drop database）

table：表級（lock table read/write）

commit：提交級

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

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

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

IX與大部分鎖都是兼容的，除了S，當然了X肯定是不兼容的；但IX與IX之間是兼容的，比如下圖

flush table with read lock會持有這個鎖（在global級別和commit級別）

FTWRL在全局級和事務級上分別加上了S鎖

IX與S是不兼容的

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

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

大表DML（IX鎖）

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

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

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

如下圖是一個select for update語句，start transaction對應的是第2行的SR鎖，而語句本身對應的是SW鎖

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

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

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

S鎖除了邏輯備份時的FTWRL以外，createa table as也會持有這個鎖

目前已知的是desc操作會持有這個SH鎖

SH鎖與絕大部分鎖都兼容，除開X鎖

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

前面提到的start transaction，以及所有的非當前讀都需要持有這個鎖

非當前讀的意思就是快照讀，也就是普通的select

與SR鎖有沖突的有2個，一個是X，另一個是SNRW

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

當前讀需要持有此鎖，常見的DML和select for update都對應此鎖，但不包括DDL

與SW鎖有沖突的有4個，SU,SRO,SNRW,X

看到一種說法是這個鎖僅對MyISAM引擎生效，沖突范圍與SW鎖類似

部分alter語句會持有該鎖。該鎖可能會升級成SNW,SNRW,X；而X鎖也有可能逐步降級到SU鎖

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

表面看起來DML的SW鎖和SU鎖不互斥（DML和DDL），但實際上因為SU鎖存在升級的屬性，SU鎖會升級到SNW鎖，從而和SW產(chǎn)生互斥

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

SU鎖的兼容性如下

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

先SU，再SW，SW被SU阻塞

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

所以雖然看起來SW和SU不是一個雙向阻塞，但實際效果就是雙向阻塞，無論DML和DDL誰在前面，都必然會發(fā)生相互的阻塞

不兼容的有點多，先貼一個兼容性

SU升級X的過程中會升級成SNW

SU升級成X的過程中，有一個copy的過程，這個過程就是SNW，在這個copy的過程中，允許DML但是不允許select（SR）

copy是一個非常耗時的過程

lock tables read的語句會持有這個鎖

SRO阻塞SW，SNRW，X

兼容性如圖

lock tables write的語句會持有這個鎖

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

兼容性如下

換句話說flush tables with read lock; （S）會堵塞lock table write; （SNRW）

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

阻塞一切

各種DDL均屬于這個范疇

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

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

X鎖阻塞SH

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

只有1行的select被堵住

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

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

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 哪些表正在被鎖狀態(tài)

1.查看表是否被鎖：

（1）直接在mysql命令行執(zhí)行:showengineinnodbstatus\G。

（2）查看造成死鎖的sql語句，分析索引情況，然后優(yōu)化sql。

（3）然后showprocesslist,查看造成死鎖占用時間長的sql語句。

（4）showstatuslike‘%lock%。

2.查看表被鎖狀態(tài)和結(jié)束死鎖步驟：

（1）查看表被鎖狀態(tài)：showOPENTABLESwhereIn_use0;這個語句記錄當前鎖表狀態(tài)。

（2）查詢進程：showprocesslist查詢表被鎖進程；查詢到相應進程killid。

（3）分析鎖表的SQL：分析相應SQL，給表加索引，常用字段加索引，表關聯(lián)字段加索引。

（4）查看正在鎖的事物：SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS。

（5）查看等待鎖的事物：SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS。

擴展資料

MySQL鎖定狀態(tài)查看命令：

Checkingtable：正在檢查數(shù)據(jù)表（這是自動的）。

Closingtables：正在將表中修改的數(shù)據(jù)刷新到磁盤中，同時正在關閉已經(jīng)用完的表。這是一個很快的操作，如果不是這樣的話，就應該確認磁盤空間是否已經(jīng)滿了或者磁盤是否正處于重負中。

ConnectOut：復制從服務器正在連接主服務器。

Copyingtotmptableondisk：由于臨時結(jié)果集大于tmp_table_size，正在將臨時表從內(nèi)存存儲轉(zhuǎn)為磁盤存儲以此節(jié)省內(nèi)存。

Creatingtmptable：正在創(chuàng)建臨時表以存放部分查詢結(jié)果。

deletingfrommaintable：服務器正在執(zhí)行多表刪除中的第一部分，剛刪除第一個表。

deletingfromreferencetables：服務器正在執(zhí)行多表刪除中的第二部分，正在刪除其他表的記錄。

Flushingtables：正在執(zhí)行FLUSHTABLES，等待其他線程關閉數(shù)據(jù)表。

Killed：發(fā)送了一個kill請求給某線程，那么這個線程將會檢查kill標志位，同時會放棄下一個kill請求。MySQL會在每次的主循環(huán)中檢查kill標志位，不過有些情況下該線程可能會過一小段才能死掉。如果該線程程被其他線程鎖住了，那么kill請求會在鎖釋放時馬上生效。

Locked：被其他查詢鎖住了。

Sendingdata：正在處理SELECT查詢的記錄，同時正在把結(jié)果發(fā)送給客戶端。

Sortingforgroup：正在為GROUPBY做排序。

Sortingfororder：正在為ORDERBY做排序。

Openingtables：這個過程應該會很快，除非受到其他因素的干擾。例如，在執(zhí)ALTERTABLE或LOCKTABLE語句行完以前，數(shù)據(jù)表無法被其他線程打開。正嘗試打開一個表。

Removingduplicates：正在執(zhí)行一個SELECTDISTINCT方式的查詢，但是MySQL無法在前一個階段優(yōu)化掉那些重復的記錄。因此，MySQL需要再次去掉重復的記錄，然后再把結(jié)果發(fā)送給客戶端。

Reopentable：獲得了對一個表的鎖，但是必須在表結(jié)構(gòu)修改之后才能獲得這個鎖。已經(jīng)釋放鎖，關閉數(shù)據(jù)表，正嘗試重新打開數(shù)據(jù)表。

Repairbysorting：修復指令正在排序以創(chuàng)建索引。

Repairwithkeycache：修復指令正在利用索引緩存一個一個地創(chuàng)建新索引。它會比Repairbysorting慢些。

Searchingrowsforupdate：正在講符合條件的記錄找出來以備更新。它必須在UPDATE要修改相關的記錄之前就完成了。

Sleeping：正在等待客戶端發(fā)送新請求。

Systemlock：正在等待取得一個外部的系統(tǒng)鎖。如果當前沒有運行多個mysqld服務器同時請求同一個表，那么可以通過增加--skip-external-locking參數(shù)來禁止外部系統(tǒng)鎖。

Upgradinglock：INSERTDELAYED正在嘗試取得一個鎖表以插入新記錄。

Updating：正在搜索匹配的記錄，并且修改它們。

UserLock：正在等待GET_LOCK()。

Waitingfortables：該線程得到通知，數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改了，需要重新打開數(shù)據(jù)表以取得新的結(jié)構(gòu)。然后，為了能的重新打開數(shù)據(jù)表，必須等到所有其他線程關閉這個表。

waitingforhandlerinsert：INSERTDELAYED已經(jīng)處理完了所有待處理的插入操作，正在等待新的請求。