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

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

1、概述

(1)鎖的定義

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

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

(2)鎖的分類

從性能上分為樂觀鎖和悲觀鎖

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

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

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

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

從對數(shù)據(jù)庫操作的類型分為讀鎖和寫鎖(都屬于悲觀鎖)

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

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

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

2、表鎖與行鎖

(1)表鎖(偏讀)

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

1)基本操作

建表SQL

CREATE TABLE `mylock` (

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

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

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');

手動增加表鎖

lock table 表名稱 read(write), 表名稱2 read(write)

查看表上過的鎖

show open tables

刪除表鎖

unlock tables

2)案例分析(加讀鎖)

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

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

3)案例分析(加寫鎖)

當(dāng)前session對該表的增刪改查都沒問題，其他session對該表的索引操作被阻塞。

4)案例結(jié)論

MyISAM在執(zhí)行查詢語句前，會自動給涉及的所有表加讀鎖，在執(zhí)行增刪改操作前，會自動給涉及的表加寫鎖。

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

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

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

(2)行鎖(偏寫)

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

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

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

事務(wù)是由一組SQL語句組成的邏輯處理單元，事務(wù)具有以下4個屬性，通常簡稱為事務(wù)的ACID屬性。

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

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

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

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

并發(fā)事務(wù)處理帶來的問題

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

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

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

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

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

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

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

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

事務(wù)的隔離級別

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

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

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

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

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

2)行鎖案例分析

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

3)隔離級別案例分析

CREATE TABLE `account` (

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

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

`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. 打開一個客戶端A，并設(shè)置當(dāng)前事務(wù)隔離級別為read uncommitted(讀未提交)，set tx_isolation='read-uncommitted'，查詢account表的初始值。

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

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

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

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

② 讀已提交：

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

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

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

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

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

③ 可重復(fù)讀

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

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

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

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

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

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

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

④ 串行化

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

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

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

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

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

5)行鎖分析

通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒ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ù)隔離級別，你們學(xué)到知識或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識儲備，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。