Mysql鎖與事務隔離級别

今天學習了Mysql鎖與事務隔離級别，這裡記錄一下學習筆記，有錯誤，還望指出。

文章目錄

• 概述
• 事務及其ACID屬性
• • 并發事務處理帶來的問題
  • 事務隔離級别
• 鎖詳解
• • 鎖分類
  • 表鎖
  • 行鎖
• 行鎖與事務隔離級别分析
• • 讀未送出
  • 讀已送出
  • 可重複讀
  • 串行化
• 間隙鎖(Gap Lock)
• 臨鍵鎖(Next-key Locks)
• • 行鎖分析
  • 死鎖
  • 鎖優化建議

概述

我們的資料庫一般都會并發執行多個事務，多個事務可能會并發的對相同的一批資料進行增删改查操作，可能 就會導緻我們說的髒寫、髒讀、不可重複讀、幻讀這些問題。

這些問題的本質都是資料庫的多事務并發問題，為了解決多事務并發問題，資料庫設計了事務隔離機制、鎖機制、MVCC多版本并發控制隔離機制，用一整套機制來解決多事務并發問題。接下來，我們會深入講解這些機 制，讓大家徹底了解資料庫内部的執行原理。

事務及其ACID屬性

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

• 原子性(Atomicity) ：事務是一個原子操作單元,其對資料的修改,要麼全都執行,要麼全都不執行。
• 一緻性(Consistent) ：在事務開始和完成時,資料都必須保持一緻狀态。這意味着所有相關的資料規 則都必須應用于事務的修改,以保持資料的完整性。
• 隔離性(Isolation) ：資料庫系統提供一定的隔離機制,保證事務在不受外部并發操作影響的“獨 立”環境執行。這意味着事務處理過程中的中間狀态對外部是不可見的,反之亦然。
• 持久性(Durable) ：事務完成之後,它對于資料的修改是永久性的,即使出現系統故障也能夠保持。

并發事務處理帶來的問題

1. 更新丢失(Lost Update)或髒寫

   當兩個或多個事務選擇同一行，然後基于最初標明的值更新該行時，由于每個事務都不知道其他事務的存 在，就會發生丢失更新問題–最後的更新覆寫了由其他事務所做的更新。

2. 髒讀（Dirty Reads）

   一個事務正在對一條記錄做修改，在這個事務完成并送出前，這條記錄的資料就處于不一緻的狀态；這 時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“髒”資料，并據此作進一步的處理，就會産生未送出的資料依賴關系。這種現象被形象的叫做“髒讀”。

   一句話：事務A讀取到了事務B已經修改但尚未送出的資料，還在這個資料基礎上做了操作。此時，如果B 事務復原，A讀取的資料無效，不符合一緻性要求。

3. 不可重讀（Non-Repeatable Reads）

   一個事務在讀取某些資料後的某個時間，再次讀取以前讀過的資料，卻發現其讀出的資料已經發生了改 變、或某些記錄已經被删除了！這種現象就叫做“不可重複讀”。

   一句話：事務A内部的相同查詢語句在不同時刻讀出的結果不一緻，不符合隔離性

4. 幻讀（Phantom Reads）

   一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新數 據，這種現象就稱為“幻讀”。

   一句話：事務A讀取到了事務B送出的新增資料，不符合隔離性

事務隔離級别

“髒讀”、“不可重複讀”和“幻讀”,其實都是資料庫讀一緻性問題,必須由資料庫提供一定的事務隔離機制 來解決。

資料庫的事務隔離越嚴格,并發副作用越小,但付出的代價也就越大,因為事務隔離實質上就是使事務在一定程度 上“串行化”進行,這顯然與“并發”是沖突的。

同時,不同的應用對讀一緻性和事務隔離程度的要求也是不同的,比如許多應用對“不可重複讀"和“幻讀”并不 敏感,可能更關心資料并發通路的能力。

常看目前資料庫的事務隔離級别: show variables like ‘tx_isolation’;

設定事務隔離級别：set tx_isolation=‘REPEATABLE-READ’;

Mysql預設的事務隔離級别是可重複讀，用Spring開發程式時，如果不設定隔離級别預設用Mysql設定的隔 離級别，如果Spring設定了就用已經設定的隔離級别

鎖詳解

鎖是計算機協調多個程序或線程并發通路某一資源的機制。

在資料庫中，除了傳統的計算資源（如CPU、RAM、I/O等）的争用以外，資料也是一種供需要使用者共享的資 源。如何保證資料并發通路的一緻性、有效性是所有資料庫必須解決的一個問題，鎖沖突也是影響資料庫并發 通路性能的一個重要因素。

鎖分類

• 從性能上分為樂觀鎖(用版本對比來實作)和悲觀鎖

• 從對資料庫操作的類型分，分為**讀鎖和寫鎖(**都屬于悲觀鎖)

  讀鎖（共享鎖，S鎖(Shared)）：針對同一份資料，多個讀操作可以同時進行而不會互相影響

  寫鎖（排它鎖，X鎖(eXclusive)）：目前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖

• 從對資料操作的粒度分，分為表鎖和行鎖

表鎖

每次操作鎖住整張表。開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的機率最高，并發度最低； 一般用在整表資料遷移的場景。

--建表SQL
CREATE TABLE `mylock` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR (20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

--插入資料
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;

案例分析(加讀鎖）

目前session和其他session都可以讀該表（多個session就是多個用戶端操作）

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

案例分析(加寫鎖）

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

案例結論

1、對MyISAM表的讀操作(加讀鎖) ,不會阻寒其他程序對同一表的讀請求,但會阻賽對同一表的寫請求。隻有當 讀鎖釋放後,才會執行其它程序的寫操作。

2、對MylSAM表的寫操作(加寫鎖) ,會阻塞其他程序對同一表的讀和寫操作,隻有當寫鎖釋放後,才會執行其它進 程的讀寫操作

其實我們一般不會使用表鎖，幾乎使用行鎖。

行鎖

每次操作鎖住一行資料。開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度最高。

InnoDB與MYISAM的最大不同有兩點：

• InnoDB支援事務（TRANSACTION）
• InnoDB支援行級鎖

一個session開啟事務更新不送出，另一個session更新同一條記錄會阻塞，更新不同記錄不會阻塞

總結：

MyISAM在執行查詢語句SELECT前，會自動給涉及的所有表加讀鎖,在執行update、insert、delete操作會自 動給涉及的表加寫鎖。

InnoDB在執行查詢語句SELECT時(非串行隔離級别)，不會加鎖。但是update、insert、delete操作會加行 鎖。

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

行鎖與事務隔離級别分析

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）打開一個用戶端A，并設定目前事務模式為read uncommitted（未送出讀），查詢表account的初始值：

set tx_isolation=‘read-uncommitted’;

（2）在用戶端A的事務送出之前，打開另一個用戶端B，更新表account：

（3）這時，雖然用戶端B的事務還沒送出，但是用戶端A就可以查詢到B已經更新的資料：

（4）一旦用戶端B的事務因為某種原因復原，所有的操作都将會被撤銷，那用戶端A查詢到的資料其實就是髒資料：

（5）在用戶端A執行更新語句update account set balance = balance - 50 where id =1，lilei的balance沒有變成350，居然是400，是不是很奇怪，資料不一緻啊，如果你這麼想就太天真 了，在應用程式中，我們會用400-50=350，并不知道其他會話復原了，要想解決這個問題可以采用讀已送出的隔離級别

讀已送出

（1）打開一個用戶端A，并設定目前事務模式為read committed（未送出讀），查詢表account的所有記錄：

set tx_isolation=‘read-committed’;

（2）在用戶端A的事務送出之前，打開另一個用戶端B，更新表account：

（3）這時，用戶端B的事務還沒送出，用戶端A不能查詢到B已經更新的資料，解決了髒讀問題：

（4）用戶端B的事務送出

（5）用戶端A執行與上一步相同的查詢，結果 與上一步不一緻，即産生了不可重複讀的問題

可重複讀

（1）打開一個用戶端A，并設定目前事務模式為repeatable read，查詢表account的所有記錄

set tx_isolation=‘repeatable-read’;

（2）在用戶端A的事務送出之前，打開另一個用戶端B，更新表account并送出

（3）在用戶端A查詢表account的所有記錄，與步驟（1）查詢結果一緻，沒有出現不可重複讀的問題

（4）在用戶端A，接着執行update account set balance = balance - 50 where id = 1，balance沒有變成 400-50=350，lilei的balance值用的是步驟2中的350來算的，是以是300，資料的一緻性倒是沒有被破壞。可 重複讀的隔離級别下使用了MVCC(multi-version concurrency control)機制，select操作不會更新版本号， 是快照讀（曆史版本）；insert、update和delete會更新版本号，是目前讀（目前版本）。

（5）重新打開用戶端B，插入一條新資料後送出

（6）在用戶端A查詢表account的所有記錄，沒有查出新增資料，是以沒有出現幻讀

（7)驗證幻讀

在用戶端A執行update account set balance=888 where id = 4;能更新成功，再次查詢能查到用戶端B新增 的資料

串行化

（1）打開一個用戶端A，并設定目前事務模式為serializable，查詢表account的初始值：

set tx_isolation=‘serializable’;

（2）打開一個用戶端B，并設定目前事務模式為serializable，更新相同的id為1的記錄會被阻塞等待，更新id 為2的記錄可以成功，說明在串行模式下innodb的查詢也會被加上行鎖。

如果用戶端A執行的是一個範圍查詢，那麼該範圍内的所有行包括每行記錄所在的間隙區間範圍(就算該行資料 還未被插入也會加鎖，這種是間隙鎖)都會被加鎖。此時如果用戶端B在該範圍内插入資料都會被阻塞，是以就 避免了幻讀。

這種隔離級别并發性極低，開發中很少會用到。

間隙鎖(Gap Lock)

間隙鎖，鎖的就是兩個值之間的空隙。Mysql預設級别是repeatable-read，有辦法解決幻讀問題嗎？間隙鎖 在某些情況下可以解決幻讀問題。

假設account表裡資料如下：

那麼間隙就有 id 為 (3,10)，(10,20)，(20,正無窮) 這三個區間，

在Session_1下面執行 update account set name = ‘zhuge’ where id > 8 and id <18;，則其他Session沒 法在這個範圍所包含的所有行記錄(包括間隙行記錄)以及行記錄所在的間隙裡插入或修改任何資料，即id在 (3,20]區間都無法修改資料，注意最後那個20也是包含在内的。

間隙鎖是在可重複讀隔離級别下才會生效。

臨鍵鎖(Next-key Locks)

Next-Key Locks是行鎖與間隙鎖的組合。像上面那個例子裡的這個(3,20]的整個區間可以叫做臨鍵鎖。

無索引行鎖會更新為表鎖

鎖主要是加在索引上，如果對非索引字段更新，行鎖可能會變表鎖

session1 執行：update account set balance = 800 where name = ‘lilei’;

session2 對該表任一行操作都會阻塞住

InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖。并且該索引不能失效，否則都會從行鎖更新為表鎖。

鎖定某一行還可以用lock in share mode(共享鎖) 和for update(排它鎖)，例如：select * from test_innodb_lock where a = 2 for update; 這樣其他session隻能讀這行資料，修改則會被阻塞，直到鎖定 行的session送出

結論

Innodb存儲引擎由于實作了行級鎖定，雖然在鎖定機制的實作方面所帶來的性能損耗可能比表級鎖定會要更 高一下，但是在整體并發處理能力方面要遠遠優于MYISAM的表級鎖定的。當系統并發量高的時候，Innodb 的整體性能和MYISAM相比就會有比較明顯的優勢了。

但是，Innodb的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓Innodb的整體性能表現 不僅不能比MYISAM高，甚至可能會更差。

行鎖分析

通過檢查InnoDB_row_lock狀态變量來分析系統上的行鎖的争奪情況

show status like ‘innodb_row_lock%’;

對各個狀态量的說明如下：

Innodb_row_lock_current_waits: 目前正在等待鎖定的數量

Innodb_row_lock_time: 從系統啟動到現在鎖定總時間長度

Innodb_row_lock_time_avg: 每次等待所花平均時間

Innodb_row_lock_time_max：從系統啟動到現在等待最長的一次所花時間

Innodb_row_lock_waits:系統啟動後到現在總共等待的次數

對于這5個狀态變量，比較重要的主要是：

Innodb_row_lock_time_avg （等待平均時長）

Innodb_row_lock_waits （等待總次數）

Innodb_row_lock_time（等待總時長）

尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什麼會有如此多的等待， 然後根據分析結果着手制定優化計劃。

檢視INFORMATION_SCHEMA系統庫鎖相關資料表

– 檢視事務

select * from INFORMATION_SCHEMA.INNODB_TRX;

– 檢視鎖

select * from INFORMATION_SCHEMA.INNODB_LOCKS;

– 檢視鎖等待

select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

– 釋放鎖，trx_mysql_thread_id可以從INNODB_TRX表裡檢視到

kill trx_mysql_thread_id

– 檢視鎖等待詳細資訊

show engine innodb status\G;

死鎖

set tx_isolation=‘repeatable-read’;

Session_1執行：select * from account where id=1 for update;

Session_2執行：select * from account where id=2 for update;

Session_1執行：select * from account where id=2 for update;

Session_2執行：select * from account where id=1 for update;

檢視近期死鎖日志資訊：show engine innodb status\G;

大多數情況mysql可以自動檢測死鎖并復原産生死鎖的那個事務，但是有些情況mysql沒法自動檢測死鎖

鎖優化建議

• 盡可能讓所有資料檢索都通過索引來完成，避免無索引行鎖更新為表鎖合理設計索引，盡量縮小鎖的範圍
• 盡可能減少檢索條件範圍，避免間隙鎖
• 盡量控制事務大小，減少鎖定資源量和時間長度，涉及事務加鎖的sql盡量放在事務最後執行
• 盡可能低級别事務隔離

一切偉大的行動和思想，都有一個微不足道的開始。