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

加鎖情況與死鎖原因分析

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

為方便大家復(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 的瞬間，我們會看到 session2、session3 的其中一個報死鎖。這個死鎖是這樣產(chǎn)生的：

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

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

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

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

死鎖日志如下：

請點擊輸入圖片描述

INSERT INTENTION LOCK

在之前的死鎖分析第四點，如果不分析插入意向鎖，也是會造成死鎖的，因為插入最終還是要對記錄加 X Lock 的，session2 和 session3 還是會互相阻塞互相等待。

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

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.

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

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

1. 它不會阻塞其他任何鎖；

2. 它本身僅會被 gap lock 阻塞。

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

GAP LOCK

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

通過下面這個例子就能驗證：

請點擊輸入圖片描述

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

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

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

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

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

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

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

鎖沖突矩陣

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

請點擊輸入圖片描述

MySQL數(shù)據(jù)庫如何鎖定和解鎖數(shù)據(jù)庫表

第一步，創(chuàng)建數(shù)據(jù)庫表writer和查看表結(jié)構(gòu)，利用SQL語句：

create table writer(

wid int(10),

wno int(10),

wname varchar(20),

wsex varchar(2),

wage int(2)

第二步，向數(shù)據(jù)庫表writer插入五條數(shù)據(jù)，插入后查看表里數(shù)據(jù)

第三步，利用鎖定語句鎖定數(shù)據(jù)庫表writer，利用SQL語句：

lock table writer read;

讓數(shù)據(jù)庫表只讀不能進行寫

第四步，為了驗證鎖定效果，可以查看數(shù)據(jù)庫表數(shù)據(jù)，利用SQL語句：

select * from writer;

第五步，利用update語句對id=5進行更新，SQL語句為：

update writer set wname = '胡思思' where id = 5;

第六步，利用unlock進行解鎖，SQL語句為：

unlock tables;

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 的鎖以及間隙鎖

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

1）使用 mvcc 的方法，實現(xiàn)多事務(wù)的并發(fā)讀寫，但是這種讀只是“快照讀”，一般讀的是歷史版本數(shù)據(jù)，還有一種是“當(dāng)前讀”，一般加鎖實現(xiàn)“當(dāng)前讀”，或者 insert、update、delete 也是當(dāng)前讀。

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

快照讀：就是select

當(dāng)前讀：特殊的讀操作，插入/更新/刪除操作，屬于當(dāng)前讀，處理的都是當(dāng)前的數(shù)據(jù)，需要加鎖。

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

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

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

40 - MySQL之insert語句的鎖

MySQL 對自增主鍵鎖做了優(yōu)化，盡量在申請到自增 id 以后，就釋放自增鎖。

因此，insert 語句是一個很輕量的操作。不過，這個結(jié)論對于“普通的 insert 語句”才有效。也就是說，還有些 insert 語句是屬于“特殊情況”的，在執(zhí)行過程中需要給其他資源加鎖，或者無法在申請到自增 id 以后就立馬釋放自增鎖。

MySQL鎖

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

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

#舉個栗子

假設(shè)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并沒有互相阻塞。這個過程需要具體的去查看源碼，此處不展開。

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

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

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

eg.

session1

select * from cpf where payid'xxx'

union

select * from cpf where payid'xxx'

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

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完成的時候session2和session3相繼完成

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

#變種1

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

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

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

#變種2

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

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

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

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

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

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

通過show processlist可以發(fā)現(xiàn)waiting for table metadata lock，但這還遠遠不夠，需要在performance_schema庫中進行設(shè)置（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：提交級

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

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

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

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

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

FTWRL在全局級和事務(wù)級上分別加上了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對應(yīng)的是第2行的SR鎖，而語句本身對應(yīng)的是SW鎖

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

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

如下圖所示，在先執(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，以及所有的非當(dāng)前讀都需要持有這個鎖

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

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

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

當(dāng)前讀需要持有此鎖，常見的DML和select for update都對應(yīng)此鎖，但不包括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ù)制操作，在表里面并沒有發(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語句怎么鎖,mysql 加鎖 sql語句怎么寫
文章分享：http://chinadenli.net/article22/dsgdjjc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、做網(wǎng)站、手機網(wǎng)站建設(shè)、ChatGPT、網(wǎng)站改版、面包屑導(dǎo)航

廣告

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