你知道MySQL鎖與事物隔離級別嗎？

相關免費學習推薦：（視訊）

前言

MySQL索引底層資料結構與演演算法
MySQL效能優化原理-前篇
MySQL效能優化-實踐篇1
MySQL效能優化-實踐篇2

前面我們講了MySQL資料庫底層的資料結構與演演算法、MySQL效能優化篇一些內容。我們再來聊聊MySQL的鎖與事務隔離級別，分上下兩篇，本篇重點講MySQL的行鎖與事務隔離級別。

鎖定義

鎖是計算機協調多個程序或執行緒並行存取某一資源的機制。

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

鎖分類

從效能上分為樂觀鎖（用版本對比來實現）和 悲觀鎖；
從資料庫操作型別分為：讀鎖和寫鎖（都屬於悲觀鎖）
- 讀鎖（共用鎖）：針對同一份資料，多個讀操作可以同時進行而不會互相影響；
- 寫鎖（排它鎖）：當前寫操作沒有完成之前，它會阻斷其它寫鎖和讀鎖。
從資料庫操作的粒度分為：表鎖和行鎖。

對於鎖深入的理解，可以檢視《關於Java中鎖的理解》。

MySQL的鎖

行鎖（Record Locks）
間隙鎖（Gap Locks）
臨鍵鎖（Next-key Locks）
共用鎖/排他鎖（Shared and Exclusive Locks）
意向共用鎖/意向排他鎖（Intention Shared and Exclusive Locks）
插入意向鎖（Insert Intention Locks）
自增鎖（Auto-inc Locks）
預測鎖，這種鎖主要用於儲存了空間資料的空間索引。

下篇來分別聊聊，本篇重點是行鎖以及事務隔離級別。

表鎖

每次操作鎖住整張表。

開銷小，加鎖快；
不會出現死鎖；
鎖粒度大，發生鎖衝突的概率最高；
並行度最低。

基本操作

範例表，如下：

# 建表SQLCREATE 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;複製程式碼

案例分析 — 加讀鎖

LOCK TABLE mylock read;複製程式碼

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

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

案例分析 — 加寫鎖

LOCK TABLE mylock WRITE;複製程式碼

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

案例結論

MyISAM 在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖；在執行增刪改查操作前，會自動給涉及的表加寫鎖。

對 MyISAM 表的讀操作（加讀鎖），不會阻塞其他程序同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其他程序的寫操作。
對 MyISAM 表的寫操作（加寫鎖），會阻塞其他程序對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其他程序的讀寫操作。

總結：讀鎖會阻塞寫，但不會阻塞讀；而寫鎖則會把讀和寫都阻塞。

行鎖

每次操作鎖住一行資料。

開銷大，加鎖慢；
會出現死鎖；
鎖定粒度最小，發生鎖衝突的概率最低；
並行度最高。

InnoDB 和 MyISAM 的最大不同點：

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

行鎖支援事務

事務（Transaction）及其 ACID 屬性

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

原子性（Atomicity）：事務是一個原子操作單元，其對資料的修改，要麼全部執行，要麼全部不執行。
一致性（Consistent）：在事務開始和完成時，資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改，以保持資料的完整性；事務結束時，所有的內部資料結構（如B+樹索引或雙向連結串列）也都必須是正確的。
隔離性（Lsolation）：資料庫系統提供一定的隔離機制，保障事務在不受外部並行操作影響的「獨立」環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。
永續性（Durable）：事務完成之後，它對於資料的修改是永久性的，即使出現系統故障也能保持。

並行事務處理帶來的問題

更新丟失（Lost Update）

當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行值，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題，最後的更新覆蓋來其他事務所做的更新。

髒讀（Dirty Reads）

一個事務正在對一條記錄做修改，在這個事務完成並提交前，這個條記錄的資料就處於不一致的狀態；這時另外一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取來這些「髒」資料，並據此做進一步的處理，就會產生未提交的資料依賴關係。這種現象被形象的叫做「髒讀」。

總結：事務A讀取到來事務B已經修改但尚未提交的資料，還在這個資料基礎上做來操作。此時，如果事務B回滾，事務A讀取的資料無效，不符合一致性要求。

不可重複讀（Non-Repeatable Reads）

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

總結：事務A讀取到了事務B已經提交的修改資料，不符合隔離性。

幻讀（Phantom Reads）

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

總結：事務A讀取到了事務B提交的新增資料，不符合隔離性。

事務隔離級別

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

資料庫的事務隔離越嚴格，並行副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上「序列化」進行，這顯然與「並行」是矛盾的。

同時，不同應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對「不可重複讀」和「幻讀」並不敏感，可能更關係資料並行存取的能力。

檢視當前資料庫的事務隔離級別

show variables like 'tx_isolation';複製程式碼

設定事務隔離級別

set tx_isolation='REPEATABLE-READ';複製程式碼

資料庫版本是5.7，隔離級別是Repeatable-Read(可重複讀)，不同的資料庫版本和隔離級別對語句的執行結果影響很大。所以需要說明版本和隔離級別

行鎖與隔離級別案例分析

事務控制語句

BEGIN 或 START TRANSACTION；顯式地開啟一個事務；
COMMIT；也可以使用 COMMIT WORK，不過二者是等價的。COMMIT會提交事務，並使已對資料庫進行的所有修改稱為永久性的；
ROLLBACK；有可以使用 ROLLBACK WORK，不過二者是等價的。回滾會結束使用者的事務，並復原正在進行的所有未提交的修改；
SAVEPOINT identifier；SAVEPOINT允許在事務中建立一個儲存點，一個事務中可以有多個SAVEPOINT；
RELEASE SAVEPOINT identifier；刪除一個事務的儲存點，當沒有指定的儲存點時，執行該語句會丟擲一個異常；
ROLLBACK TO identifier；把事務回滾到標記點；
SET TRANSACTION；用來設定事務的隔離級別。InnoDB儲存引擎提供事務的隔離級別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

事務處理方法

MYSQL 事務處理主要有兩種方法：

用 BEGIN, ROLLBACK, COMMIT來實現
- BEGIN 開始一個事務
- ROLLBACK 事務回滾
- COMMIT 事務確認

直接用 SET 來改變 MySQL 的自動提交模式:
- SET AUTOCOMMIT=0 禁止自動提交
- SET AUTOCOMMIT=1`` 開啟自動提交

範例表，如下：

CREATE TABLE `user` (    `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`.`user` (`name`,`balance`) VALUES ('zhangsan','450');INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('lisi', '16000');INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('wangwu','2400');複製程式碼

行鎖演示

一個 session 開啟事務更新不提交，另一個 seesion 更新同一條記錄會阻塞，更新不同記錄u會阻塞。

讀未提交

（1）開啟一個使用者端A，並設定當前事務模式為 read uncommitted (讀未提交)，查詢表 user 的初始化值

set tx_isolation='read-uncommitted';複製程式碼

（2）在使用者端A的事務提交之前，開啟另一個使用者端B，更新表 user

（3）這時，雖然使用者端B的事務還沒提交，但是在使用者端A就可以查詢到B已經更新的資料

（4）一旦使用者端B的事務因為某種原因回滾，所有的操作都將會被複原，那麼使用者端A查詢到的資料其實就是髒資料。

（5）在使用者端A執行更新語句 update user set balance = balance - 50 where id = 1; zhangsan 的 balance沒有變成350，居然是400，是不是很奇怪，資料不一致啊。如果你這麼想就太天真了，在應用程式中，我們會用400-50=350，並不知道其他對談回滾了，要想解決這個問題可以採用讀已提交的隔離級別。

讀已提交

（1）開啟一個使用者端A，並設定當前事務模式為 read committed (讀已提交)，查詢表 user 的所有記錄

set tx_isolation='read-committed';複製程式碼

（2）在使用者端A的事務提交之前，開啟另一個使用者端B，更新表 user

（3）這時，使用者端B的事務還沒提交，使用者端A不能查詢到B已經更新的資料，解決了髒讀問題。

（4）使用者端B的事務提交

（5）使用者端A執行與上一步相同的查詢，結果與上一步不一致，即產生了不可重複讀的問題。

可重複讀

（1）開啟一個使用者端A，並設定當前的事務模式為 repeatable read ，查詢表 user 的所有記錄。

set tx_isolation='repeatable-read';複製程式碼

（2）在使用者端A的事務提交之前，開啟另一個使用者端B，更新表 user 並提交。

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

（4）在使用者端A，接著執行 update user set balance = balance - 50 where id = 1 , balance 沒有變成 400 - 50 = 350， zhangsan 的 balance 的值用的是步驟(2) 中的 350 來計算的，所以是300，資料的一致性倒是沒有被破壞。可重複讀的隔離級別下使用了 MVCC（multi-version concurrency control）機制，select 操作不會更新版本號，是快照讀（歷史版本）；insert、update、delete 會更新版本號，是當前讀（當前版本）。

我們下篇來講 MVCC。

（5）重新開啟使用者端B，插入一條新資料後提交。

（6）在使用者端A查詢表user 的所有記錄，沒有查出新增資料，所以沒有出現幻讀。

（7）驗證幻讀在使用者端A執行 update user set balance = 8888 where id = 4; ，能更新成功，再次查詢到使用者端B新增的資料。

序列化

（1）開啟一個使用者端A，並設定當前事務模式為 serializable ，查詢表 user 的初始值

set tx_isolation='serializable';複製程式碼

（2）開啟一個使用者端B，並設定當前事務模式為 serializable ，插入一條記錄報錯，表被鎖了插入失敗，MySQL 中事務隔離級別為 serializable 時會鎖表，因此不會出現幻讀的情況，這種隔離級別並行性極低，開發中很少會用到。

案例結論

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（等待總時長）

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

死鎖

set tx_isolation='repeatable-read';複製程式碼

Session_1執行：select * from user where id=1 for update;
Session_2執行：select * from user where id=2 for update;
Session_1執行：select * from user where id=2 for update;
Session_2執行：select * from user where id=1 for update;複製程式碼

檢視近期死鎖紀錄檔資訊：

show engine innodb status\G;複製程式碼

大多數情況mysql可以自動檢測死鎖並回滾產生死鎖的那個事務，但是有些情況 mysql沒法自動檢測死鎖

優化建議

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

問答

MySQL 預設級別是 repeatable-read，有什麼辦法可以解決幻讀媽？

間隙鎖（Gap Lock）在某些情況下可以解決幻讀問題，它是 Innodb 在 可重複讀 提交下為解決幻讀問題時引入的鎖機制。要避免幻讀可以用間隙鎖在Session_1 下面執行 update user set name = 'hjh' where id > 10 and id <= 20; ，則其他 Session 沒法在這個範圍鎖包含的間隙裡插入或修改任何資料。

如：user 表有3條資料， id > 2 and id <=3 會把第三條記錄鎖住，其他對談對則無法對第三條記錄做操作。

無索引鎖會升級為表鎖，鎖主要是加在索引上，如果對非索引欄位更新，行鎖可能會變變鎖。

使用者端A執行： update user set balance = 800 where name = 'zhangsan';

使用者端B對該表任一行執行修改、刪除操作都會阻塞

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

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

以上就是你知道MySQL鎖與事物隔離級別嗎？的詳細內容，更多請關注TW511.COM其它相關文章！