4.MySQL--事務隔離

LLSS發表於2019-09-12
MySql

2. 事務的隔離級別

事務的隔離性是多個使用者併發訪問資料庫時，資料庫為每個使用者開啟的事務，不能被其他事務的操作所干擾，多個事務之間要相互隔離。\
在併發下事務會容易出現一些問題：

  • 資料更新丟失：兩個事務同時操作一條資料，一個事務因為異常導致資料更新丟失\
  • 髒讀：一個事務開始讀取某一行資料，另外一個事務已經更新了此條資料但是沒有及時提交，這是相當危險的，因為所有的操作都被回滾。
  • 不可重複讀：一個事務對同一條資料讀取兩次，但是結果卻不一樣。例如，在讀取的過程中，另外的一個事務進行的修改，並提交。
  • 幻讀：事務在操作過程中進行兩次查詢，第二次查詢的結果在第一次查詢中未出現的資料（查詢的語句不一定一樣），這是因為在讀取過程中有另一個事務進行了插入。\

MySQL中存在（Innodb）存在4種隔離級別，不同的隔離級別對事務的處理不同。\

  • 未授權讀取（未提交讀取 Read Uncommitted）:如果一個事務已經開始寫資料，則另外一個資料則不會允許同時進行寫操作，但允許其他事務讀此條資料；READ-UNCOMMITTED | 0:存在髒讀，不可重複讀，幻讀的問題。，隔離級別是可以通過“排它鎖”實現。
  • 授權讀取（已讀提交 Read Commited）：:讀取資料的事務允許其他事務繼續訪問該資料，但是未提交的寫事務將會禁止其他事務訪問該數；READ-COMMITTED |1：解決髒讀、幻讀的問題，存在不可重複讀、幻讀的問題。
  • 可重複讀取（Repeatable Read）:讀取資料的事務將會禁止寫事務（但允許讀事務），寫事務則禁止任何其他的事務；REPEATABKLE-READ | 2：解決髒讀、不可重複讀的問題，存在幻讀的問題，預設的隔離級別。可以通過 “排他鎖“，“共享鎖”實現。
  • 序列化（Serializable）:提供嚴格的事務隔離，它要求事務序列化執行，事務只能一個挨著一個地執行，不能併發執行；SERIALIZABLE | 3 解決髒讀、不可重複讀、幻讀，它保證事務安全，但完全序列執行，效能最低；如果僅僅通過“行級鎖”是無法實現事務的序列化的，必須通過其他的機制保證新插入的資料不會被剛執行查詢操作的事務訪問到。

事務的隔離級別

隔離級別 讀資料一致性 髒讀 不可重複讀的問題 幻讀
未提交讀 Read Uncommitted 最低階別，只能保證不讀取物理上損壞的資料 是 是 是
已讀提交 Read committed 語句級 否 是 是
可重複讀取 Repeatable Read 事務隔離級別 否 否 是
序列化 Serializable 最高階別，事務級 否 否 否

可以通過 “show variables like '%iso'; ” 檢視當前的隔離級別

SELECT @@global.tx_isolation, @@tx_isolation; --檢視
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| REPEATABLE-READ       | READ-UNCOMMITTED |
+-----------------------+------------------+
-- 設定全域性的隔離級別 設定會話 global 替換為 session 即可 
常用的set語法
-- SET [GLOABL] config_name = 'foobar';
-- SET @@[session|global].config_name = 'foobar';
-- SELECT @@[global.]config_name;

SET @@gloabl.tx_isolation = 'READ-UNCOMMITTED';
SET @@gloabl.tx_isolation = 'READ-COMMITTED';
SET @@gloabl.tx_isolation = 'REPEATABLE-READ';
SET @@gloabl.tx_isolation = 'SERIALIZABLE';

2.1 事務異常現象展示與解決

2.1.1 級聯回滾

隔離級別：任意
在程式的實際執行中，我們的事務往往是會併發執行的，可能在某個時間段是執行幾個事務；這就出現了級聯回滾的現象。\
級聯回滾：就是在一段時間同時有兩個以上的事務在執行，由於最先執行的事務出現了 “異常” 導致事務不得不回滾的情況。

演示：下面的表格每一行可理解為執行時間

session1 session2 session3
begin; begin; begin;
select * from count where prefix = 'dz10021'
update count set count = 1 where prefix = 'dz10021';
select * from count where prefix = 'dz10021'
update count set count = 2 where prefix = 'dz10021'; select * from count where prefix = 'dz10021'
update count set count = 3 where prefix = 'dz10021';
這裡新增一條錯誤的資訊
insert into count values('dz10021', 0, 2);
這裡因為異常回滾 因為session1異常回滾 因為session1異常回滾
[Err] 1062 - Duplicate entry 'dz10021' for key 'PRIMARY' [Err] 1213 - Deadlock found when trying to get lock; try restarting transaction [Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

解釋：如上的情況就是因為與session1發生了異常，但是因為session2與session3依賴於session1實現某一些業務，為了保證資料的一致性的話，是會對於相互依賴操作的資料進行級聯的回滾這是很有必要的，但是對於程式來說體驗不好，事情快要做好了，突然因為別人的一個岔子打斷，會很不舒服。

解決：可以再 select * from count where prefix ='dz10021' 加上 for update

2.1.2 幻讀

隔離級別：RR

session1 session2
start transaction start transaction
select * from count where prefix = 'dz1'
沒有發現這條資料準備新增 select * from count where prefix = 'dz1'
這也沒發現這條資料新增
這裡因為其他事情耽擱了 insert into count values('dz1', 1, 1)
commit
處理之後新增
insert into count values('dz1', 0, 1)
出現主鍵衝突，如果沒有設定主鍵就是資料重複提交

T1事務：查詢資料庫中是否存在prefix(主鍵)為dd的資料，如果沒有就新增一條

T2事務：干擾的情況就是直接新增資料嗎。

在程式的執行過程中，然後T1執行完了判斷操作進行新增資料，因為t2在t1延時的時候執行了新增操作；這個時候t1再執行新增的時候失敗了，然後再去讀資料發現並沒有這條資料，這個時候t1就出現了所謂的 ‘幻讀’

解決：

  1. select * from count where prefix = 'dz1' for update;
  2. SET @@gloabl.tx_isolation = 'SERIALIZABLE';
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章