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的悲觀鎖：

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

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

關(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ù)存在，則鎖定當前行數(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ù)進行提交更新的時候，才會對數(shù)據(jù)的沖突與否進行檢測，如果發(fā)現(xiàn)沖突，則讓返回用戶錯誤的信息，讓用戶決定如何去做。

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

例如有表student，字段：

id,name,version

1 a 1

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

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

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

mysql數(shù)據(jù)庫鎖MDL鎖的解釋

在我們系統(tǒng)中有一張表它的查詢概率非常高。 最近有個需求，需要對這個表增加一個字段 ，然而在增加字段的時候發(fā)現(xiàn)系統(tǒng)中有多個業(yè)務(wù)出現(xiàn)了超時操作，那么這個是什么原因?qū)е碌哪?？?jīng)過查閱資料發(fā)現(xiàn)是數(shù)據(jù)庫的 MDL鎖+事務(wù)導(dǎo)致 的。

MDL鎖屬于表級別的元數(shù)據(jù)鎖。 表級別鎖分為數(shù)據(jù)鎖和元數(shù)據(jù)鎖，通常我們說的加鎖一般指的是加的數(shù)據(jù)鎖。跟數(shù)據(jù)鎖一樣，元數(shù)據(jù)鎖也分讀鎖和讀寫鎖。

MDL不需要顯示使用，在進行表操作時會自動加上 。當對表進行增刪改查時，會自動加上MDL讀鎖；當要對表進行加減字段的結(jié)構(gòu)修改時，會自動加上MDL寫鎖。

MDL鎖的存在，其實是為了保證數(shù)據(jù)的一致性。 想象一下，假如沒有MDL鎖，一個查詢在遍歷表數(shù)據(jù)的過程中，另外一個線程執(zhí)行了ALTER TABLE t DELETE COLUMN 'col_1'把col_1這一列刪掉了，那查詢結(jié)果就亂了，結(jié)果中是否應(yīng)該有這一列數(shù)據(jù)？

：表示正常往下執(zhí)行

：表示卡住了，即無法往下執(zhí)行。

解釋：

步驟 1 2 正常執(zhí)行。執(zhí)行步驟2 時，會申請表customer的MDL的 SHARED_READ 鎖。

步驟3 會卡住， 因為此時會申請表customer的MDL的EXCLUSIVE鎖，但是事物一的事物沒有提交，此時是無法申請到EXCLUSIVE鎖，因為它們是互斥的。

步驟4 也會卡住，因為EXCLUSIVE鎖和SHARE_READ鎖是互斥的， 且EXCLUSIVE鎖的優(yōu)先級更高 ，所以步驟4 也會卡住。

步驟5 事物提交，釋放表的SHARE_READ鎖，之后就可以執(zhí)行6 和7 的操作了。

如果先執(zhí)行事務(wù)二，在執(zhí)行事務(wù)三，則是可以成功的，因為alter數(shù)據(jù)ddl語句，和事物無關(guān)。

因此在我們開發(fā)的過程中，需要避免大的事務(wù)操作，防止占有鎖的時間過長。

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;