mysql如何鎖庫？用什么命令

MySQL 5.1支持對(duì)MyISAM和MEMORY表進(jìn)行表級(jí)鎖定，對(duì)BDB表進(jìn)行頁級(jí)鎖定，對(duì)InnoDB表進(jìn)行行級(jí)鎖定。

成都創(chuàng)新互聯(lián)公司成立與2013年，先為錦屏等服務(wù)建站，錦屏等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為錦屏企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。

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

這可用下列代碼做到：

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從入門到精通(九) MySQL鎖，各種鎖

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制，在數(shù)據(jù)庫中，除傳統(tǒng)的計(jì)算資源（CPU、RAM、I/O）爭用外，數(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ù)提交語句都將阻塞。其典型的使用場景就是做全庫的邏輯備份，對(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ì)阻塞其它客戶端的寫。大家可以拿一張表來測試看看。

元數(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ù)在同一間隙上采用的間隙鎖。

mysql 的鎖以及間隙鎖

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

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

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

快照讀：就是select

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

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

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

Innodb自動(dòng)使用間隙鎖的條件：

MySQL數(shù)據(jù)庫表鎖定的幾種方法實(shí)現(xiàn)

如果兩個(gè)程序都向表中寫數(shù)據(jù)顯然會(huì)造成很大的麻煩，甚至?xí)幸馔馇闆r發(fā)生。如果表正由一個(gè)程序?qū)懭耄瑫r(shí)進(jìn)行讀取的另一個(gè)程序也會(huì)產(chǎn)生混亂的結(jié)果。

鎖定表的方法

防止客戶機(jī)的請求互相干擾或者服務(wù)器與維護(hù)程序相互干擾的方法主要有多種。如果你關(guān)閉數(shù)據(jù)庫，就可以保證服務(wù)器

和myisamchk和isamchk之間沒有交互作用。但是停止服務(wù)器的運(yùn)行并不是一個(gè)好注意，因?yàn)檫@樣做會(huì)使得沒有故障的數(shù)據(jù)庫和表也不可用。本節(jié)主

要討論的過程，是避免服務(wù)器和myisamchk或isamchk之間的交互作用。實(shí)現(xiàn)這種功能的方法是對(duì)表進(jìn)行鎖定。

服務(wù)器由兩種表的鎖定方法：

1.內(nèi)部鎖定

內(nèi)部鎖定可以避免客戶機(jī)的請求相互干擾——例如，避免客戶機(jī)的SELECT查詢被另一個(gè)客戶機(jī)的UPDATE查詢所干擾。也可以利用內(nèi)部鎖定機(jī)制防止服務(wù)器在利用myisamchk或isamchk檢查或修復(fù)表時(shí)對(duì)表的訪問。

語法：鎖定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

解鎖表：UNLOCK TABLES

LOCK TABLES為當(dāng)前線程鎖定表。UNLOCK TABLES釋放被當(dāng)前線程持有的任何鎖。當(dāng)線程發(fā)出另外一個(gè)LOCK TABLES時(shí)，或當(dāng)服務(wù)器的連接被關(guān)閉時(shí)，當(dāng)前線程鎖定的所有表自動(dòng)被解鎖。

如果一個(gè)線程獲得在一個(gè)表上的一個(gè)READ鎖，該線程(和所有其他線程)只能從表中讀。如果一個(gè)線程獲得一個(gè)表上的一個(gè)WRITE鎖，那么只有持鎖的線程READ或WRITE表，其他線程被阻止。

每個(gè)線程等待(沒有超時(shí))直到它獲得它請求的所有鎖。

WRITE鎖通常比READ鎖有更高的優(yōu)先級(jí)，以確保更改盡快被處理。這意味著，如果一個(gè)線程獲得READ鎖，并且然后另外一個(gè)線程請求一個(gè)WRITE鎖, 隨后的READ鎖請求將等待直到WRITE線程得到了鎖并且釋放了它。

顯然對(duì)于檢查，你只需要獲得讀鎖。再者鐘情跨下，只能讀取表，但不能修改它，因此他也允許其它客戶機(jī)讀取表。對(duì)于修復(fù)，你必須獲得些所以防止任何客戶機(jī)在你對(duì)表進(jìn)行操作時(shí)修改它。

2.外部鎖定

服務(wù)器還可以使用外部鎖定(文件級(jí)鎖)來防止其它程序在服務(wù)器使用表時(shí)修改文件。通常，在表的檢查操作中服務(wù)器

將外部鎖定與myisamchk或isamchk作合使用。但是，外部鎖定在某些系統(tǒng)中是禁用的，因?yàn)樗荒芸煽康倪M(jìn)行工作。對(duì)運(yùn)行myisamchk或

isamchk所選擇的過程取決于服務(wù)器是否能使用外部鎖定。如果不使用，則必修使用內(nèi)部鎖定協(xié)議。

如果服務(wù)器用--skip-locking選項(xiàng)運(yùn)行，則外部鎖定禁用。該選項(xiàng)在某些系統(tǒng)中是缺省的，如Linux。可以通過運(yùn)行mysqladmin variables命令確定服務(wù)器是否能夠使用外部鎖定。檢查skip_locking變量的值并按以下方法進(jìn)行：◆如果skip_locking為off，則外部鎖定有效您可以繼續(xù)并運(yùn)行人和一個(gè)實(shí)用程序來檢查表。服務(wù)器和實(shí)用程序?qū)⒑献鲗?duì)表進(jìn)行訪問。但是，運(yùn)行任何

一個(gè)實(shí)用程序之前，應(yīng)該使用mysqladmin flush-tables。為了修復(fù)表，應(yīng)該使用表的修復(fù)鎖定協(xié)議。

◆如果skip_locaking為on，則禁用外部鎖定，所以在myisamchk或isamchk檢查修復(fù)表示服務(wù)器并不知道，最好關(guān)閉服務(wù)器。如果堅(jiān)

持是服務(wù)器保持開啟狀態(tài)，月確保在您使用此表示沒有客戶機(jī)來訪問它。

MySQL鎖

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

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

#舉個(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的查詢還沒有結(jié)束的時(shí)候，去執(zhí)行session2的DDL（SU），此時(shí)session2需要MDL寫鎖（SU升級(jí)到X，需要X鎖），由于MDL讀寫鎖互斥，因此session2需要等待session1釋放MDL讀鎖（SR阻塞X）；同時(shí)session2對(duì)后面的所有MDL讀鎖互斥（X阻塞SR），因此session2又繼續(xù)阻塞了session3...

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

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

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

在線執(zhí)行DDL的時(shí)候，需要檢查一下information_schema.innodb_trx表中有沒有當(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鎖，在語句查詢完之后并不會(huì)釋放，而是會(huì)隨著事務(wù)的釋放而釋放

#變種2

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

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

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

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

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

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

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

5.7臨時(shí)開啟

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：全局級(jí)（FTWRL）

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

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

commit：提交級(jí)

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

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

DML和DDL在執(zhí)行之前都會(huì)申請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語句，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)的表來驗(yàn)證，目前只驗(yàn)證了select .. for update），IX只是DML初期需要獲得的鎖

如下圖是一個(gè)select for update語句，start transaction對(duì)應(yīng)的是第2行的SR鎖，而語句本身對(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，那么畫風(fēng)就不是像上圖那樣了

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SU鎖的兼容性如下

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

先SU，再SW，SW被SU阻塞

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

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

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

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

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

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

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

SRO阻塞SW，SNRW，X

兼容性如圖

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

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

兼容性如下

換句話說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ù)制操作，在表里面并沒有發(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開頭的幾個(gè)SQL，無論是modify還是add，查詢出來都是SU鎖，但DDL是一個(gè)過程，其中的有一部分如果發(fā)生了阻塞，可能會(huì)發(fā)現(xiàn)是X鎖阻塞；拿SR阻塞X鎖的實(shí)驗(yàn)來說，SR阻塞X的過程非常短暫，如果沒有剛好卡到那個(gè)點(diǎn)，看到的結(jié)果可能就是SR和SU互不干涉，但如果卡到那個(gè)點(diǎn)，就會(huì)觀測到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鎖

網(wǎng)頁名稱：mysql后碼鎖怎么鎖,鎖 mysql
分享地址：http://chinadenli.net/article44/hedoee.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站改版、用戶體驗(yàn)、營銷型網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)公司

廣告

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