MySQL中有哪些鎖與事務(wù)隔離級(jí)別

本篇文章為大家展示了MySQL中有哪些鎖與事務(wù)隔離級(jí)別，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過(guò)這篇文章的詳細(xì)介紹希望你能有所收獲。

創(chuàng)新互聯(lián)建站從2013年創(chuàng)立，公司以網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站建設(shè)、系統(tǒng)開(kāi)發(fā)、網(wǎng)絡(luò)推廣、文化傳媒、企業(yè)宣傳、平面廣告設(shè)計(jì)等為主要業(yè)務(wù)，適用行業(yè)近百種。服務(wù)企業(yè)客戶(hù)數(shù)千家，涉及國(guó)內(nèi)多個(gè)省份客戶(hù)。擁有多年網(wǎng)站建設(shè)開(kāi)發(fā)經(jīng)驗(yàn)。為企業(yè)提供專(zhuān)業(yè)的網(wǎng)站建設(shè)、創(chuàng)意設(shè)計(jì)、宣傳推廣等服務(wù)。 通過(guò)專(zhuān)業(yè)的設(shè)計(jì)、獨(dú)特的風(fēng)格，為不同客戶(hù)提供各種風(fēng)格的特色服務(wù)。

1、概述

(1)鎖的定義

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一資源的機(jī)制。

在數(shù)據(jù)庫(kù)中，除了傳統(tǒng)的計(jì)算資源(如CPU、RAM、IO等)的爭(zhēng)用以外，數(shù)據(jù)也是一種供需要用戶(hù)共享的資源。如何保證數(shù)據(jù)并發(fā)訪問(wèn)的一致性、有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問(wèn)題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問(wèn)性能的一個(gè)重要因素。從這個(gè)角度來(lái)說(shuō)，鎖對(duì)數(shù)據(jù)庫(kù)而言顯得尤其重要，也更加復(fù)雜。

(2)鎖的分類(lèi)

從性能上分為樂(lè)觀鎖和悲觀鎖

樂(lè)觀鎖：每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人不會(huì)修改，所以不會(huì)上鎖，但是在更新的時(shí)候會(huì)判斷一下在此期間別人有沒(méi)有去更新這個(gè)數(shù)據(jù)。

例子：① 在數(shù)據(jù)庫(kù)的表中加一個(gè)version字段，用來(lái)記錄每次修改數(shù)據(jù)的版本號(hào)，防止并發(fā)修改數(shù)據(jù)出錯(cuò);② CAS原子類(lèi)。

悲觀鎖：每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人會(huì)修改，所以每次在拿數(shù)據(jù)的時(shí)候都會(huì)上鎖，這樣別人想拿這個(gè)數(shù)據(jù)就會(huì)阻塞直到它拿到鎖。

例子：synchronized關(guān)鍵字。

從對(duì)數(shù)據(jù)庫(kù)操作的類(lèi)型分為讀鎖和寫(xiě)鎖(都屬于悲觀鎖)

讀鎖(共享鎖)：針對(duì)同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響。

寫(xiě)鎖(排它鎖)：當(dāng)前寫(xiě)操作沒(méi)有完成前，它會(huì)阻斷其他session的寫(xiě)鎖和讀鎖。(session：數(shù)據(jù)庫(kù)連接)

從對(duì)數(shù)據(jù)操作的粒度分為表鎖和行鎖。

2、表鎖與行鎖

(1)表鎖(偏讀)

表鎖偏向MyISAM存儲(chǔ)引擎，開(kāi)銷(xiāo)小，加鎖快，無(wú)死鎖，鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。

1)基本操作

建表SQL

CREATE TABLE `mylock` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',

`name` varchar(20) DEFAULT NULL COMMENT '名稱(chēng)',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

插入數(shù)據(jù)

insert into `mylock` (`id`, `name`) values('1','a');

insert into `mylock` (`id`, `name`) values('2','b');

insert into `mylock` (`id`, `name`) values('3','c');

insert into `mylock` (`id`, `name`) values('4','d');

手動(dòng)增加表鎖

lock table 表名稱(chēng) read(write), 表名稱(chēng)2 read(write)

查看表上過(guò)的鎖

show open tables

刪除表鎖

unlock tables

2)案例分析(加讀鎖)

當(dāng)前session和其他session都可以讀該表。

當(dāng)前session中插入或者更新鎖定的表都會(huì)報(bào)錯(cuò)，其他session插入或更新則會(huì)等待。

3)案例分析(加寫(xiě)鎖)

當(dāng)前session對(duì)該表的增刪改查都沒(méi)問(wèn)題，其他session對(duì)該表的索引操作被阻塞。

4)案例結(jié)論

MyISAM在執(zhí)行查詢(xún)語(yǔ)句前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行增刪改操作前，會(huì)自動(dòng)給涉及的表加寫(xiě)鎖。

① 對(duì)MyISAM表的讀操作(加讀鎖)，不會(huì)阻塞其他進(jìn)程對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫(xiě)請(qǐng)求。只有當(dāng)讀鎖釋放后，才會(huì)執(zhí)行其他進(jìn)程的寫(xiě)操作。

② 對(duì)MyISAM表的寫(xiě)操作(加寫(xiě)鎖)，會(huì)阻塞其他進(jìn)程對(duì)同一表的讀和寫(xiě)操作，只有當(dāng)寫(xiě)鎖釋放后，才會(huì)執(zhí)行其他進(jìn)程的讀寫(xiě)操作。

總結(jié)：讀鎖會(huì)阻塞寫(xiě)，但不會(huì)阻塞讀。而寫(xiě)鎖會(huì)阻塞讀和寫(xiě)。

(2)行鎖(偏寫(xiě))

行鎖偏向InnoDB存儲(chǔ)引擎，開(kāi)銷(xiāo)大，加鎖慢，會(huì)出現(xiàn)死鎖，鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。InnoDB和MYISAM的最大不同有兩點(diǎn)：① 支持事務(wù);② 采用了行級(jí)鎖。

1)行鎖支持事務(wù)

事務(wù)(Transaction)及其ACID屬性

事務(wù)是由一組SQL語(yǔ)句組成的邏輯處理單元，事務(wù)具有以下4個(gè)屬性，通常簡(jiǎn)稱(chēng)為事務(wù)的ACID屬性。

原子性(Atomicity)：事務(wù)是一個(gè)原子操作單元，其對(duì)數(shù)據(jù)的修改，要么全部執(zhí)都執(zhí)行，要么全都不執(zhí)行。

一致性(Consistent)：在事務(wù)開(kāi)始和完成時(shí)，數(shù)據(jù)都必須保持一致?tīng)顟B(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持?jǐn)?shù)據(jù)的完整性;事務(wù)結(jié)束時(shí)，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)(如B樹(shù)索引或雙向鏈表)也都必須是正確的。

隔離性(Isolation)：數(shù)據(jù)庫(kù)系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的“獨(dú)立”環(huán)境執(zhí)行。這意味著事務(wù)處理過(guò)程中的中間狀態(tài)對(duì)外部是不可見(jiàn)的，反之亦然。

持久性(Durable)：事務(wù)完成之后，它對(duì)于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。

并發(fā)事務(wù)處理帶來(lái)的問(wèn)題

(溫馨提示：以下部分理論解釋看不懂沒(méi)關(guān)系，接著往下看，有例子)

更新丟失(Lost Update)：當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，然后基于最初選定的值更新該行時(shí)，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在，就會(huì)發(fā)生丟失更新問(wèn)題-最后的更新覆蓋了由其他事務(wù)所做的更新。(可以用類(lèi)似樂(lè)觀鎖的方案解決)

臟讀(Dirty Reads)：一個(gè)事務(wù)正在對(duì)一條記錄做修改，在這個(gè)事務(wù)完成并提交前，這條記錄的數(shù)據(jù)就處于不一致的狀態(tài);這時(shí)，另一個(gè)事務(wù)也來(lái)讀取同一條記錄，如果不加控制，第二個(gè)事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此做進(jìn)一步的處理，就會(huì)產(chǎn)生未提交的數(shù)據(jù)依賴(lài)關(guān)系。這種現(xiàn)象被形象的叫做“臟讀”。

一句話(huà)：事務(wù)A讀取到了事務(wù)B已經(jīng)修改但尚未提交的數(shù)據(jù)，還在這個(gè)數(shù)據(jù)基礎(chǔ)上做了操作。此時(shí)，如果B事務(wù)回滾，A讀取的數(shù)據(jù)無(wú)效，不符合一致性的要求。

不可重復(fù)讀(Non-Repeatable Reads)：一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間，再次讀取以前讀過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了!這種現(xiàn)象就叫做“不可重復(fù)讀”。

一句話(huà)：事務(wù)A讀取到了事務(wù)B已經(jīng)提交的修改數(shù)據(jù)，不符合隔離性。

幻讀(Phantom Reads)：一個(gè)事務(wù)按相同的查詢(xún)條件重新讀取以前檢索過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿(mǎn)足其查詢(xún)條件的新數(shù)據(jù)，這種現(xiàn)象就稱(chēng)為“幻讀”。

一句話(huà)：事務(wù)A讀取到了事務(wù)B已經(jīng)提交的新增數(shù)據(jù)，不符合隔離性。

事務(wù)的隔離級(jí)別

“臟讀”、“不可重復(fù)讀”、“幻讀”，其實(shí)都是數(shù)據(jù)庫(kù)讀一致性問(wèn)題，必須由數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決。

數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別越嚴(yán)格，并發(fā)副作用越小，但付出的性能代價(jià)也就越大，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行，這顯然與“并發(fā)”是矛盾的。

查看當(dāng)前數(shù)據(jù)的事務(wù)隔離級(jí)別：show variables like 'tx_isolation'

設(shè)置事務(wù)隔離級(jí)別：set tx_isolation='REPEATABLE-READ'

MySQL默認(rèn)的隔離級(jí)別是可重復(fù)讀。

2)行鎖案例分析

用下面的表演示，需要開(kāi)啟事務(wù)，session_1更新某一行，session_2同時(shí)更新某一行將被阻塞，但是更新其他行正常

3)隔離級(jí)別案例分析

CREATE TABLE `account` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',

`name` varchar(255) DEFAULT NULL COMMENT '名稱(chēng)',

`balance` int(11) DEFAULT NULL COMMENT '金額',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

insert into `account` (`id`, `name`, `balance`) values('1','lilei','450');

insert into `account` (`id`, `name`, `balance`) values('2','hanmei','16000');

insert into `account` (`id`, `name`, `balance`) values('3','lucy','2400');

① 讀未提交：

a. 打開(kāi)一個(gè)客戶(hù)端A，并設(shè)置當(dāng)前事務(wù)隔離級(jí)別為read uncommitted(讀未提交)，set tx_isolation='read-uncommitted'，查詢(xún)account表的初始值。

b. 在客戶(hù)端A的事務(wù)提交之前，打開(kāi)另一個(gè)客戶(hù)端B，并設(shè)置當(dāng)前事務(wù)隔離級(jí)別為read uncommitted，更新account表。

c. 這時(shí)，雖然客戶(hù)端B的事務(wù)還沒(méi)提交，但是客戶(hù)端A就可以查詢(xún)到B已經(jīng)更新的數(shù)據(jù)。

d. 一旦客戶(hù)端B的事務(wù)因?yàn)槟撤N原因回滾，所有的操作都將會(huì)被撤銷(xiāo)，那客戶(hù)端A查詢(xún)到的數(shù)據(jù)其實(shí)就是臟數(shù)據(jù)。

e. 臟數(shù)據(jù)展現(xiàn)：在客戶(hù)端A執(zhí)行更新語(yǔ)句update account set balance=balance-50 where id=1，lilei的balance沒(méi)有變成350，而是400，數(shù)據(jù)??一致，因?yàn)樵谶@過(guò)程中，并不知道客戶(hù)端B會(huì)話(huà)回滾了，行數(shù)據(jù)實(shí)際上是450，450-50=400，可以采用讀已提交的隔離級(jí)別。

② 讀已提交：

a. 打開(kāi)一個(gè)客戶(hù)端A，并設(shè)置當(dāng)前事務(wù)隔離級(jí)別為read committed(讀已提交)，set tx_isolation='read-committed'，查詢(xún)account表的初始值。

b. 在客戶(hù)端A的事務(wù)提交之前，打開(kāi)另一個(gè)客戶(hù)端B，并設(shè)置當(dāng)前事務(wù)隔離級(jí)別為read committed，更新account表。

c. 這時(shí)，客戶(hù)端B的事務(wù)還沒(méi)提交，客戶(hù)端A不能查詢(xún)到B已經(jīng)更新的數(shù)據(jù)，解決了臟讀的問(wèn)題。

d. 客戶(hù)端B的事務(wù)提交。

e. 客戶(hù)端A執(zhí)行與上一步相同的查詢(xún)，結(jié)果與上一步不一致，即產(chǎn)生了不可重復(fù)讀的問(wèn)題。

③ 可重復(fù)讀

a. 打開(kāi)一個(gè)客戶(hù)端A，并設(shè)置當(dāng)前事務(wù)隔離級(jí)別為repeatable read(可重復(fù)讀)，set tx_isolation='repeatable-read'，查詢(xún)account表的初始值：

b. 在客戶(hù)端A的事務(wù)提交之前，打開(kāi)另一個(gè)客戶(hù)端B，并設(shè)置當(dāng)前事務(wù)隔離級(jí)別為repeatable read，更新account表：

c. 在客戶(hù)端A查詢(xún)account表的所有記錄，與步驟a的查詢(xún)結(jié)果一致，沒(méi)有出現(xiàn)不可重復(fù)讀的問(wèn)題。

d. 在客戶(hù)端A執(zhí)行update account set balance=balance-50 where id=1，balance沒(méi)有變成350-50=300，lilei的balance值用的是步驟b中的300來(lái)算的，所以是250，數(shù)據(jù)的一致性倒是沒(méi)有被破壞?？芍貜?fù)讀的隔離級(jí)別下使用了MVCC機(jī)制，select操作不會(huì)更新版本號(hào)，是快照讀(歷史版本);insert、update和delete會(huì)更新版本號(hào)，是當(dāng)前讀(當(dāng)前版本)。

e. 重新打開(kāi)客戶(hù)端B，插入一條新數(shù)據(jù)后提交：

f. 在客戶(hù)端A查詢(xún)account表的所有記錄，沒(méi)有查出新增數(shù)據(jù)，所以沒(méi)有出現(xiàn)幻讀：

g. 驗(yàn)證幻讀：在客戶(hù)端A執(zhí)行update account set balance=666 where id=4;能更新成功，再次查詢(xún)能查到客戶(hù)端B新增的數(shù)據(jù)：

④ 串行化

a. 打開(kāi)一個(gè)客戶(hù)端A，并設(shè)置當(dāng)前事務(wù)隔離級(jí)別為serializable(串行化)，set tx_isolation='serializable'，查詢(xún)account表的初始值：

b. 打開(kāi)一個(gè)客戶(hù)端B，并設(shè)置當(dāng)前事務(wù)隔離級(jí)別為serializable，插入一條記錄報(bào)錯(cuò)，表被鎖了插入失敗，MySQL中事務(wù)隔離級(jí)別為serializable時(shí)會(huì)鎖表，因此不會(huì)出現(xiàn)幻讀的情況，但這種隔離級(jí)別并發(fā)性能極低，開(kāi)發(fā)中很少會(huì)用到。

提問(wèn)：MySQL默認(rèn)級(jí)別是repeatable-read，有辦法解決幻讀問(wèn)題嗎?

間隙鎖在某些情況下可以解決幻讀問(wèn)題。要避免幻讀可以用間隙鎖在session_1下面執(zhí)行update account set name='Zeki' where id>3 and id<=20，則其他session沒(méi)法插入這個(gè)范圍內(nèi)的數(shù)據(jù)。 　　InnoDB的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖。并且該索引不能失效，否則都會(huì)從行鎖升級(jí)為表鎖。 　　無(wú)索引行鎖升級(jí)為表鎖：varchar如果不加 ' '，將導(dǎo)致系統(tǒng)自動(dòng)轉(zhuǎn)換類(lèi)型，行鎖變表鎖，例如：update table set name=Zeki where id=1，這個(gè)語(yǔ)句會(huì)導(dǎo)致行鎖變表鎖，其他session無(wú)法對(duì)這個(gè)表做操作，5.7之后的版本這樣寫(xiě)SQL會(huì)報(bào)錯(cuò)。 　　鎖定某一行還可以用lock in share mode(共享鎖)和for update(排他鎖)，例如：select * from table where id=1 for update，這樣其他session只能讀這行數(shù)據(jù)，修改則會(huì)被阻塞，直到鎖定行的session提交。 　　4)案例結(jié)論

InnoDB存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面所帶來(lái)的性能損耗可能比表級(jí)鎖定會(huì)要更高一下，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MyISAM的表級(jí)鎖定的。當(dāng)系統(tǒng)并發(fā)量高的時(shí)候，InnoDB的整體性能和MyISAM相比就會(huì)有比較明顯的優(yōu)勢(shì)了。但是，InnoDB的行級(jí)鎖同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候，可能會(huì)讓InnoDB的整體性能表現(xiàn)不僅不能比MyISAM高，甚至可能會(huì)更差。

5)行鎖分析

通過(guò)檢查InnoDB_row_lock狀態(tài)變量來(lái)分析系統(tǒng)上的行鎖的爭(zhēng)奪情況：show status like 'innodb_row_lock%'。

6)死鎖

set tx_isolation='repeatable-read';

session_1執(zhí)行：select * from account where id=1 for update;

session_2執(zhí)行：select * from account where id=2 for update;

session_1執(zhí)行：select * from account where id=2 for update;

session_2執(zhí)行：select * from account where id=1 for update;

查看近期死鎖日志信息：show engine innodb status\G。

上述內(nèi)容就是MySQL中有哪些鎖與事務(wù)隔離級(jí)別，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

文章名稱(chēng)：MySQL中有哪些鎖與事務(wù)隔離級(jí)別
文章地址：http://bm7419.com/article20/igsico.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供云服務(wù)器、關(guān)鍵詞優(yōu)化、服務(wù)器托管、網(wǎng)站收錄、靜態(tài)網(wǎng)站、企業(yè)網(wǎng)站制作

廣告

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