如何對(duì)MySQL數(shù)據(jù)庫表進(jìn)行鎖定

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

創(chuàng)新互聯(lián)提供高防服務(wù)器租用、云服務(wù)器、香港服務(wù)器、成都西信服務(wù)器托管等

1.內(nèi)部鎖定

內(nèi)部鎖定可以避免客戶機(jī)的請(qǐng)求相互干擾——例如，避免客戶機(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í))直到它獲得它請(qǐng)求的所有鎖。

WRITE鎖通常比READ鎖有更高的優(yōu)先級(jí)，以確保更改盡快被處理。這意味著，如果一個(gè)線程獲得READ鎖，并且然后另外一個(gè)線程請(qǐng)求一個(gè)WRITE鎖, 隨后的READ鎖請(qǐng)求將等待直到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ī)來訪問它。必須使用卡黨的鎖定協(xié)議告訴服務(wù)器是該表不被其他客戶機(jī)訪問。

檢查表的鎖定協(xié)議

本節(jié)只介紹如果使用表的內(nèi)部鎖定。對(duì)于檢查表的鎖定協(xié)議，此過程只針對(duì)表的檢查，不針對(duì)表的修復(fù)。

1.調(diào)用mysql發(fā)布下列語句：

$mysql –u root –p db_namemysqlLOCK TABLE tbl_name READ;mysqlFLUSH TABLES;

該鎖防止其它客戶機(jī)在檢查時(shí)寫入該表和修改該表。FLUSH語句導(dǎo)致服務(wù)器關(guān)閉表的文件，它將刷新仍在告訴緩存中的任何為寫入的改變。

2.執(zhí)行檢查過程

$myisamchk tbl_name$ isamchk tbl_name

3.釋放表鎖

mysqlUNLOCK TABLES;

如果myisamchk或isamchk指出發(fā)現(xiàn)該表的問題，將需要執(zhí)行表的修復(fù)。

修復(fù)表的鎖定協(xié)議

這里只介紹如果使用表的內(nèi)部鎖定。修復(fù)表的鎖定過程類似于檢查表的鎖定過程，但有兩個(gè)區(qū)別。第一，你必須得到寫鎖而非讀鎖。由于你需要修改表，因此根本不允許客戶機(jī)對(duì)其進(jìn)行訪問。第二，必須在執(zhí)行修復(fù)之后發(fā)布FLUSH

TABLE語句，因?yàn)閙yisamchk和isamchk建立的新的索引文件，除非再次刷新改表的高速緩存，否則服務(wù)器不會(huì)注意到這個(gè)改變。本例同樣適合優(yōu)化表的過程。

1.調(diào)用mysql發(fā)布下列語句：

$mysql –u root –p db_namemysqlLOCK TABLE tbl_name WRITE;mysqlFLUSH TABLES;

2.做數(shù)據(jù)表的拷貝，然后運(yùn)行myisamchk和isamchk：

$cp tbl_name.* /some/other/dir$myisamchk --recover tbl_name$ isamchk --recover tbl_name

--recover選項(xiàng)只是針對(duì)安裝而設(shè)置的。這些特殊選項(xiàng)的選擇將取決與你執(zhí)行修復(fù)的類型。

3.再次刷新高速緩存，并釋放表鎖：

mysqlFLUSH TABLES;mysqlUNLOCK TABLES;

mysql如何鎖庫？用什么命令

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

如果不能同時(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鎖

對(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í)申請(qǐng)，但并不會(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ā)起讀請(qǐng)求，反復(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鎖并沒有按照?qǐng)?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ì)申請(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語句，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)頁標(biāo)題：mysql怎么鎖庫,數(shù)據(jù)庫鎖表如何解鎖 mysql
文章鏈接：http://chinadenli.net/article31/dsgsgpd.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App開發(fā)、網(wǎng)站導(dǎo)航、網(wǎng)站設(shè)計(jì)公司、小程序開發(fā)、電子商務(wù)、搜索引擎優(yōu)化

廣告

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