mysql中的樂觀鎖和悲觀鎖怎么用

關(guān)于mysql中的樂觀鎖和悲觀鎖面試的時候被問到的概率還是比較大的。

成都創(chuàng)新互聯(lián)公司主營日照網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,成都App定制開發(fā),日照h5小程序定制開發(fā)搭建,日照網(wǎng)站營銷推廣歡迎日照等地區(qū)企業(yè)咨詢

mysql的悲觀鎖：

其實理解起來非常簡單，當(dāng)數(shù)據(jù)被外界修改持保守態(tài)度，包括自身系統(tǒng)當(dāng)前的其他事務(wù)，以及來自外部系統(tǒng)的事務(wù)處理，因此，在整個數(shù)據(jù)處理過程中，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀鎖的實現(xiàn)，往往依靠數(shù)據(jù)庫提供的鎖機(jī)制，但是也只有數(shù)據(jù)庫層提供的鎖機(jī)制才能真正保證數(shù)據(jù)訪問的排他性，否則，即使在自身系統(tǒng)中實現(xiàn)了加鎖機(jī)制，也無法保證外部系統(tǒng)不會修改數(shù)據(jù)。

來點(diǎn)實際的，當(dāng)我們使用悲觀鎖的時候我們首先必須關(guān)閉mysql數(shù)據(jù)庫的自動提交屬性，因為MySQL默認(rèn)使用autocommit模式，也就是說，當(dāng)你執(zhí)行一個更新操作后，MySQL會立刻將結(jié)果進(jìn)行提交。

關(guān)閉命令為：set autocommit=0;

悲觀鎖可以使用select…for update實現(xiàn)，在執(zhí)行的時候會鎖定數(shù)據(jù)，雖然會鎖定數(shù)據(jù)，但是不影響其他事務(wù)的普通查詢使用。此處說普通查詢就是平時我們用的：select * from table 語句。在我們使用悲觀鎖的時候事務(wù)中的語句例如：

//開始事務(wù)

begin;/begin work;/start transaction; (三選一)

//查詢信息

select * from order where id=1 for update;

//修改信息

update order set name='names';

//提交事務(wù)

commit;/commit work;(二選一)

此處的查詢語句for update關(guān)鍵字，在事務(wù)中只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一條數(shù)據(jù)時會等待其它事務(wù)結(jié)束后才執(zhí)行，一般的SELECT查詢則不受影響。

執(zhí)行事務(wù)時關(guān)鍵字select…for update會鎖定數(shù)據(jù)，防止其他事務(wù)更改數(shù)據(jù)。但是鎖定數(shù)據(jù)也是有規(guī)則的。

查詢條件與鎖定范圍：

1、具體的主鍵值為查詢條件

比如查詢條件為主鍵ID=1等等，如果此條數(shù)據(jù)存在，則鎖定當(dāng)前行數(shù)據(jù)，如果不存在，則不鎖定。

2、不具體的主鍵值為查詢條件

比如查詢條件為主鍵ID1等等，此時會鎖定整張數(shù)據(jù)表。

3、查詢條件中無主鍵

會鎖定整張數(shù)據(jù)表。

4、如果查詢條件中使用了索引為查詢條件

明確指定索引并且查到，則鎖定整條數(shù)據(jù)。如果找不到指定索引數(shù)據(jù)，則不加鎖。

悲觀鎖的確保了數(shù)據(jù)的安全性，在數(shù)據(jù)被操作的時候鎖定數(shù)據(jù)不被訪問，但是這樣會帶來很大的性能問題。因此悲觀鎖在實際開發(fā)中使用是相對比較少的。

mysql的樂觀鎖：

相對悲觀鎖而言，樂觀鎖假設(shè)數(shù)據(jù)一般情況下不會造成沖突，所以在數(shù)據(jù)進(jìn)行提交更新的時候，才會對數(shù)據(jù)的沖突與否進(jìn)行檢測，如果發(fā)現(xiàn)沖突，則讓返回用戶錯誤的信息，讓用戶決定如何去做。

一般來說，實現(xiàn)樂觀鎖的方法是在數(shù)據(jù)表中增加一個version字段，每當(dāng)數(shù)據(jù)更新的時候這個字段執(zhí)行加1操作。這樣當(dāng)數(shù)據(jù)更改的時候，另外一個事務(wù)訪問此條數(shù)據(jù)進(jìn)行更改的話就會操作失敗，從而避免了并發(fā)操作錯誤。當(dāng)然，還可以將version字段改為時間戳，不過原理都是一樣的。

例如有表student，字段：

id,name,version

1 a 1

當(dāng)事務(wù)一進(jìn)行更新操作：update student set name='ygz' where id = #{id} and version = #{version};

此時操作完后數(shù)據(jù)會變?yōu)閕d = 1,name = ygz,version = 2，當(dāng)另外一個事務(wù)二同樣執(zhí)行更新操作的時候，卻發(fā)現(xiàn)version != 1，此時事務(wù)二就會操作失敗，從而保證了數(shù)據(jù)的正確性。

悲觀鎖和樂觀鎖都是要根據(jù)具體業(yè)務(wù)來選擇使用，本文僅作簡單介紹。

密碼鎖如何使用

密碼器怎么用：

1、首次使用密碼器需要激活,先打開電子密碼器,長按住開關(guān)鍵3-5秒,屏幕會出現(xiàn)6個小橫線。

2、然后輸入客戶單據(jù)中提供的“電子密碼器證書激活碼”,然后按確認(rèn)鍵,激活電子密碼器成功。

3、激活電子密碼器后,密碼器提示用戶設(shè)置開機(jī)密碼,連續(xù)輸入兩次密碼即可設(shè)置成功。需要填6位數(shù)的密碼。

4、用戶通過手機(jī)或電腦操作時,如果要繳費(fèi)支付、轉(zhuǎn)賬匯款等,需要使用電子密碼器。

5、在電子密碼器中輸入支付頁面

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，達(dá)到實驗的效果。但實際測試中，DDL是分階段的，如果沒有滿足一定的要求，就不會引發(fā)阻塞，看到的結(jié)果就是SR和SU并沒有互相阻塞。這個過程需要具體的去查看源碼，此處不展開。

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

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

在線執(zhí)行DDL的時候，需要檢查一下information_schema.innodb_trx表中有沒有當(dāng)前操作表對應(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次，確保查詢時間幾十秒以上)

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í)行時間的先后來進(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臨時開啟

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肯定是不兼容的；但I(xiàn)X與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)生等待；盡量不要在忙時進(jì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需要找一個大一點(diǎn)的表來驗證，目前只驗證了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ā)生相互的阻塞

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

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的過程非常短暫，如果沒有剛好卡到那個點(diǎn)，看到的結(jié)果可能就是SR和SU互不干涉，但如果卡到那個點(diǎn)，就會觀測到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鎖機(jī)制 什么用。如何使用。通俗點(diǎn)講

mysql鎖機(jī)制是在并發(fā)操作的時候，避免多人同時操作而發(fā)生錯誤。

先說一下表級鎖吧

表級鎖 一般引擎都支持，資源消耗小。申請鎖的時候 整表鎖定（分讀寫鎖），其它線程或操作不能進(jìn)行操作

行級鎖 INNODB引擎支持。資源消耗大 鎖定的時候 被鎖的行只能進(jìn)行一個操作 其它均不能操作些行。

新聞名稱：mysql密碼鎖怎么使用,mysql鎖是怎么實現(xiàn)的
轉(zhuǎn)載注明：http://chinadenli.net/article5/dsedcoi.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供建站公司、小程序開發(fā)、品牌網(wǎng)站制作、定制開發(fā)、云服務(wù)器、外貿(mào)網(wǎng)站建設(shè)

廣告

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