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

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

1、概述

(1)鎖的定義

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

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

(2)鎖的分類

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

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

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

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

例子：synchronized關鍵字。

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

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

寫鎖(排它鎖)：當前寫操作沒有完成前，它會阻斷其他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)案例分析(加讀鎖)

當前session和其他session都可以讀該表。

當前session中插入或者更新鎖定的表都會報錯，其他session插入或更新則會等待。

3)案例分析(加寫鎖)

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

4)案例結論

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

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

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

總結：讀鎖會阻塞寫，但不會阻塞讀。而寫鎖會阻塞讀和寫。

(2)行鎖(偏寫)

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

1)行鎖支持事務

事務(Transaction)及其ACID屬性

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

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

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

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

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

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

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

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

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

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

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

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

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

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

事務的隔離級別

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

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

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

設置事務隔離級別：set tx_isolation='REPEATABLE-READ'

MySQL默認的隔離級別是可重復讀。

2)行鎖案例分析

用下面的表演示，需要開啟事務，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，并設置當前事務隔離級別為read uncommitted(讀未提交)，set tx_isolation='read-uncommitted'，查詢account表的初始值。

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

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

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

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

② 讀已提交：

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

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

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

d. 客戶端B的事務提交。

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

③ 可重復讀

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

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

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

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

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

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

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

④ 串行化

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

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

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

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

InnoDB存儲引擎由于實現(xiàn)了行級鎖定，雖然在鎖定機制的實現(xiàn)方面所帶來的性能損耗可能比表級鎖定會要更高一下，但是在整體并發(fā)處理能力方面要遠遠優(yōu)于MyISAM的表級鎖定的。當系統(tǒng)并發(fā)量高的時候，InnoDB的整體性能和MyISAM相比就會有比較明顯的優(yōu)勢了。但是，InnoDB的行級鎖同樣也有其脆弱的一面，當我們使用不當?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中有哪些鎖與事務隔離級別，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

文章名稱：MySQL中有哪些鎖與事務隔離級別
文章地址：http://aaarwkj.com/article20/igsico.html

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

廣告

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