MySQL實戰(zhàn)16 深入理解Mysql鎖與事務隔離級別

MySQL實戰(zhàn) 目錄

1. 概述

1.1 定義

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

1.2 鎖的分類
  • 從性能上分為樂觀鎖(用版本對比來實現(xiàn))和悲觀鎖
  • 從對數(shù)據(jù)庫操作的類型上分為讀鎖和寫鎖（都屬于悲觀鎖）
    讀鎖（共享鎖）：針對同一份數(shù)據(jù)，多個讀操作可以同時進行而不會互相影響
    寫鎖（排它鎖）：當前寫操作沒有完成前，它會阻斷其它寫鎖和讀鎖
  • 從對數(shù)據(jù)庫操作的粒度分為表鎖和行鎖

2. 三鎖

2.1 表鎖（偏讀）

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

2.1.1 基本操作
  • 建表SQL
CREATE TABLE `mylock` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR (20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
  • 插入數(shù)據(jù)
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');
  • 手動增加表鎖
lock table 表名稱 read(write),表名稱2 read(write);
  • 查看表上加過的鎖
show open tables;
  • 刪除表鎖
unlock tables;
2.1.2 案例分析(加讀鎖）
  • 當前session和其他session都可以讀該表
  • 當前session中插入或者更新鎖定的表都會報錯，其他session插入或更新則會等待
注意：當前session會話，也就是當前連接，一個IP可以進行多個連接
同一個mysql 兩個session

（1）session1執(zhí)行sql語句

lock table mylock read;
執(zhí)行時間

（2）session2執(zhí)行sql語句

update mylock set name = 'a' where id =1;

發(fā)現(xiàn)session2sql語句一直在等待
（3）session1執(zhí)行解鎖sql語句

unlock table;

（4）session1解鎖后 session2 sql語句就執(zhí)行了


執(zhí)行時間

注意：加讀鎖一般用來數(shù)據(jù)遷移使用。

2.1.3 案例分析(加寫鎖）

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

lock table mylock write;

update mylock set name = 'a' where id =1;
image.png
2.1.4 案例結(jié)論

MyISAM在執(zhí)行查詢語句(SELECT)前,會自動給涉及的所有表加讀鎖,在執(zhí)行增刪改操作前,會自動給涉及的表加寫鎖。
1、對MyISAM表的讀操作(加讀鎖) ,不會阻寒其他進程對同一表的讀請求,但會阻賽對同一表的寫請求。只有當讀鎖釋放后,才會執(zhí)行其它進程的寫操作。
2、對MylSAM表的寫操作(加寫鎖) ,會阻塞其他進程對同一表的讀和寫操作,只有當寫鎖釋放后,才會執(zhí)行其它進程的讀寫操作
總結(jié)：

簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖則會把讀和寫都阻塞。
2.2 行鎖（偏寫）

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

2.2.1 行鎖支持事務
  • 事務（Transaction）及其ACID屬性

事務是由一組SQL語句組成的邏輯處理單元,事務具有以下4個屬性,通常簡稱為事務的ACID屬性。
原子性(Atomicity) ：事務是一個原子操作單元,其對數(shù)據(jù)的修改,要么全都執(zhí)行,要么全都不執(zhí)行。
一致性(Consistent) ：在事務開始和完成時,數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應用于事務的修改,以保持數(shù)據(jù)的完整性;事務結(jié)束時,所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)(如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ù)依賴關(guān)系。這種現(xiàn)象被形象的叫做“臟讀”。
　　一句話：事務A讀取到了事務B已經(jīng)修改但尚未提交的數(shù)據(jù)，還在這個數(shù)據(jù)基礎(chǔ)上做了操作。此時，如果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提交的新增數(shù)據(jù)，不符合隔離性
臟讀是事務B里面修改了數(shù)據(jù)
幻讀是事務B里面新增了數(shù)據(jù)

  • 事務隔離級別

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

隔離級別 臟讀(Dirty Reads) 不可重復讀（Non-Repeatable Reads） 幻讀(Phantom Reads）
Serializable(串行化) 不可能 不可能 不可能
Repeatable Read(可重復讀) 不可能 不可能 可能
Read Commited（讀已提交數(shù)據(jù)） 不可能 可能 可能
Read Uncomitted（讀未提交數(shù)據(jù)) 可能 可能 可能

數(shù)據(jù)庫的事務隔離越嚴格,并發(fā)副作用越小,但付出的代價也就越大,因為事務隔離實質(zhì)上就是使事務在一定程度上“串行化”進行,這顯然與“并發(fā)”是矛盾的。
同時,不同的應用對讀一致性和事務隔離程度的要求也是不同的,比如許多應用對“不可重復讀"和“幻讀”并不敏感,可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力。
查看當前數(shù)據(jù)庫的事務隔離級別:

select @@tx_isolation;

Mysql 8+ 執(zhí)行語句
select @@transaction_isolation;

設置事務隔離級別：

set tx_isolation='REPEATABLE-READ';

Mysql 8+ 執(zhí)行語句
set transaction_isolation='REPEATABLE-READ';
2.2.2 行鎖案例分析

用下面的表演示，需要開啟事務，Session_1更新某一行，Session_2更新同一行被阻塞，但是更新其他行正常

2.2.3 隔離級別案例分析
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lilei', '450');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '16000');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '2400');

(1) Session_1 執(zhí)行語句

BEGIN;
update account set name = 'lilei999' where id =1;

(2) Session_2 查詢語句

SELECT * FROM account;
image.png
發(fā)現(xiàn)id=1的name并未修改,因為Session_1 事務并未執(zhí)行（commit）。

(3) Session_2 執(zhí)行update語句

update account set name = 'hanmei22' where id =2;
修改id=2成功
update account set name = 'lilei22' where id =1;

發(fā)現(xiàn) id =1時，update一直處于等待狀態(tài)，說明InnoDB默認行鎖（表中某一行或多行加鎖）

1.讀未提交 READ-UNCOMMITTED

(1)Session_1執(zhí)行SQL語句

set transaction_isolation='READ-UNCOMMITTED';
BEGIN;
SELECT * FROM account;
image.png

(2)Session_2執(zhí)行SQL語句

set transaction_isolation='READ-UNCOMMITTED';
BEGIN;
UPDATE account SET balance = balance + 50 where id = 1;
SELECT * FROM account;
image.png

(3)Session_1再執(zhí)行查詢語句


image.png
發(fā)現(xiàn)Session_2 未提交事務的情況下，Session_1能讀到Session_2 未提交事務的數(shù)據(jù)
注意：一旦客戶端B的事務因為某種原因回滾，所有的操作都將會被撤銷，那客戶端A查詢到的數(shù)據(jù)其實就是臟數(shù)據(jù)：

結(jié)論：讀未提交事務隔離級別（READ-UNCOMMITTED）未出現(xiàn)臟讀

2.讀已提交 READ-COMMITTED

(1)Session_1執(zhí)行SQL語句

set transaction_isolation='READ-COMMITTED';
BEGIN;
SELECT * FROM account;
image.png

(2)Session_2執(zhí)行SQL語句

set transaction_isolation='READ-COMMITTED';
BEGIN;
UPDATE account SET balance = balance + 50 where id = 1;
SELECT * FROM account;
image.png

(3)Session_1再執(zhí)行查詢語句

image.png

(4)Session_2 提交事務

COMMIT;

(4)Session_1繼續(xù)執(zhí)行查詢語句


image.png
發(fā)現(xiàn)客戶端A（Session_1事務未COMMIT提交）執(zhí)行與上一步相同的查詢，結(jié)果 與上一步不一致，即產(chǎn)生了不可重復讀的問題

結(jié)論：讀已提交事務隔離級別（READ-COMMITTED）未出現(xiàn)臟讀，但是產(chǎn)生了不可重復讀的問題

3.可重復讀 REPEATABLE-READ

(1)Session_1執(zhí)行SQL語句

set transaction_isolation='REPEATABLE-READ';
BEGIN;
SELECT * FROM account;
image.png

(2)Session_2執(zhí)行SQL語句


image.png

(3)Session_1執(zhí)行查詢語句


image.png

(4)Session_2提交事務 
COMMIT;
image.png

(5)Session_1執(zhí)行查詢語句


image.png

(6)Session_1執(zhí)行update語句


image.png
發(fā)現(xiàn)在客戶端A，接著執(zhí)行update balance = balance + 50 where id = 1，balance沒有變成500+50=550，lilei的balance值用的是步驟（4）中的550來算的，所以是600，數(shù)據(jù)的一致性倒是沒有被破壞?？芍貜妥x的隔離級別下使用了MVCC機制，select操作不會更新版本號，是快照讀（歷史版本）；insert、update和delete會更新版本號，是當前讀（當前版本）。

（7）重新打開Session_2 客戶端B，插入一條新數(shù)據(jù)后提交

INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('wang', '1111');
SELECT * FROM account;
image.png

（8）在Session_1客戶端A查詢表account的所有記錄，沒有 查出 新增數(shù)據(jù)，奇怪沒有出現(xiàn)幻讀？？


image.png

（9）更新操作出現(xiàn)幻讀

UPDATE account SET balance = balance + 50 where id = 5;
SELECT * FROM account;
image.png

結(jié)論：可重復讀事務隔離級別（REPEATABLE-READ）未出現(xiàn)臟讀和不可重復讀的問題,但是出現(xiàn)幻讀問題。

4..串行化 Serializable

串行化就是把事務串行執(zhí)行而非并發(fā)執(zhí)行，但是執(zhí)行效率低。
mysql中事務隔離級別為serializable時會鎖表，因此不會出現(xiàn)幻讀的情況，這種隔離級別并發(fā)性極低，開發(fā)中很少會用到。

Mysql默認級別是repeatable-read，有辦法解決幻讀問題嗎？

間隙鎖在某些情況下可以解決幻讀問題
要避免幻讀可以用間隙鎖在Session_1下面執(zhí)行update account set name = 'zhuge' where id > 10 and id <=20;，則其他Session沒法插入這個范圍內(nèi)的數(shù)據(jù)

2.2.4 案例結(jié)論

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

2.2.5 行鎖分析

通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況
show status like'innodb_row_lock%';

對各個狀態(tài)量的說明如下：
Innodb_row_lock_current_waits: 當前正在等待鎖定的數(shù)量
Innodb_row_lock_time: 從系統(tǒng)啟動到現(xiàn)在鎖定總時間長度
Innodb_row_lock_time_avg: 每次等待所花平均時間
Innodb_row_lock_time_max：從系統(tǒng)啟動到現(xiàn)在等待最長的一次所花時間
Innodb_row_lock_waits:系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù)

對于這5個狀態(tài)變量，比較重要的主要是：
Innodb_row_lock_time_avg （等待平均時長）
Innodb_row_lock_waits （等待總次數(shù)）
Innodb_row_lock_time（等待總時長）

尤其是當?shù)却螖?shù)很高，而且每次等待時長也不小的時候，我們就需要分析系統(tǒng)中為什么會有如此多的等待，然后根據(jù)分析結(jié)果著手制定優(yōu)化計劃。

2.2.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;
大多數(shù)情況mysql可以自動檢測死鎖并回滾產(chǎn)生死鎖的那個事務，但是有些情況mysql沒法自動檢測死鎖

2.2.7 優(yōu)化建議
  • 盡可能讓所有數(shù)據(jù)檢索都通過索引來完成，避免無索引行鎖升級為表鎖
  • 合理設計索引，盡量縮小鎖的范圍
  • 盡可能減少檢索條件，避免間隙鎖
  • 盡量控制事務大小，減少鎖定資源量和時間長度
  • 盡可能低級別事務隔離
特別感謝：

圖靈學院

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

友情鏈接更多精彩內(nèi)容