【mysql】關於事務的隔離等級

標籤：

一、鎖的種類

MySQL中鎖的種類很多，有常見的表鎖和行鎖，也有新加入的Metadata Lock等等,表鎖是對一整張表加鎖，雖然可分為讀鎖和寫鎖，但畢竟是鎖住整張表，會導致並發能力下降，一般是做ddl處理時使用。

行鎖則是鎖住資料行，這種加鎖方法比較複雜，但是由於只鎖住有限的資料，對於其它資料不加限制，所以並發能力強，MySQL一般都是用行鎖來處理並發事務

二、鎖粒度

為了儘可能提高資料庫的並發度，每次鎖定的資料範圍越小越好，理論上每次只鎖定當前操作的資料的方案會得到最大的並發度，但是管理鎖是很耗資源的事情（涉及擷取，檢查，釋放鎖等動作），因此資料庫系統需要在高並發響應和系統效能兩方面進行平衡，這樣就產生了“鎖粒度（Lock granularity）”的概念

一種提高共用資源並發發性的方式是讓鎖定對象更有選擇性。盡量只鎖定需要修改的部分資料，而不是所有的資源。更理想的方式是，只對會修改的資料片進行精確的鎖定。任何時候，在給定的資源上，鎖定的資料量越少，則系統的並發程度越高，只要相互之間不發生衝突即可

但是，加鎖也需要消耗資源。鎖的各種操作，包括獲得鎖、檢查鎖和是否已經解除、釋放鎖等，都會增加系統的開銷。所謂鎖策略，就是在鎖的開銷和資料的安全性之間尋求平衡

表鎖：管理鎖的開銷最小，同時允許的並發量也最小的鎖機制。MyIsam儲存引擎使用的鎖機制。當要寫入資料時，把整個表都鎖上，此時其他讀、寫動作一律等待。除了MyIsam儲存引擎使用這種鎖策略外，MySql本身也使用表鎖來執行某些特定動作，比如alter table。另外，寫鎖比讀鎖有更高的優先順序，因此一個寫鎖可能會被插入到讀鎖隊列的前面。

行鎖：可以支援最大並發的鎖策略（同時也帶來了最大的鎖開銷）。InnoDB和Falcon兩種儲存引擎都採用這種策略。行級鎖只在儲存引擎層實現，而MySQL伺服器層沒有實現。伺服器層完全不瞭解儲存引擎中的鎖實現。MySql是一種開放的架構，你可以實現自己的儲存引擎，並實現自己的鎖粒度策略，不像Oracle，你沒有機會改變鎖策略，Oracle採用的是行鎖。

三、死結

死結是指兩個或者多個事務在同一資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性迴圈的假象。多個事務同時鎖定同一個資源時，也會產生死結。資料庫系統實現了各種死結檢測和死結逾時的機制，InnoDB目前處理死結的方法是，將持有最少行級獨佔鎖定的事務進行復原

四、事務ACID原則

從業務角度出發，對資料庫的一組操作要求保持4個特徵：

• Atomicity（原子性）：一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗復原，對於一個事務來說，不可能只執行其中的一部分操作，這就是事務的原子性
• Consistency（一致性）：資料庫總是從一個一致性狀態轉換到另一個一致狀態。下面的銀行列子會說到
• Isolation（隔離性）：通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的
• Durability（持久性）：一旦事務提交，則其所做的修改就會永久儲存到資料庫中。此時即使系統崩潰，修改的資料也不會丟失。（持久性的安全性與重新整理記錄層級也存在一定關係，不同的層級對應不同的資料安全層級。）

為了更好地理解ACID，以銀行賬戶轉賬為例：

BEGIN;SELECT balance FROM checking WHERE customer_id = 10233276;UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;COMMIT;

原子性：要麼完全提交（10233276的checking餘額減少200，savings 的餘額增加200），要麼完全復原（兩個表的餘額都不發生變化）

一致性：這個例子的一致性體現在 200元不會因為資料庫系統運行到第3行之後，第4行之前時崩潰而不翼而飛，因為事物還沒有提交

隔離性：允許在一個事務中的動作陳述式會與其他事務的語句隔離開，比如事務A運行到第3行之後，第4行之前，此時事務B去查詢checking餘額時，它仍然能夠看到在事務A中被減去的200元（賬戶錢不變），因為事務A和B是彼此隔離的。在事務A提交之前，事務B觀察不到資料的改變

五、並發問題可歸納為以下幾類1、丟失更新

撤銷一個事務時，把其他事務已提交的更新資料覆蓋

例子：A和B事務並發執行，A事務執行更新後，提交；B事務在A事務更新後，B事務結束前也做了對該行資料的更新操作，然後復原，則兩次更新操作都丟失了

2、髒讀

一個事務讀到另一個事務未提交的更新資料

例子：A和B事務並發執行，B事務執行更新後，A事務查詢B事務沒有提交的資料，B交易回復，則A事務得到的資料不是資料庫中的真實資料。也就是髒資料，即和資料庫中不一致的資料

3、不可重複讀取

一個事務讀到另一個事務已提交的更新資料

例子：A和B事務並發執行，A事務查詢資料，然後B事務更新該資料，A重新查詢該資料時，發現該資料變化了

4、覆蓋更新

這是不可重複讀取中的特例，一個事務覆蓋另一個事務已提交的更新資料

例子：A事務更新資料，然後B事務更新該資料，A事務查詢發現自己更新的資料變了

5、虛讀（幻讀）

一個事務讀到另一個事務已提交的新插入的資料

例子：A和B事務並發執行，A事務查詢資料，B事務插入或者刪除資料，A事務重新查詢發現結果集中有以前沒有的資料或者以前有的資料消失了

六、隔離等級1、SERIALIZABLE（序列化）

一個事務在執行過程中完全看不到其他事務對資料庫所做的更新，事務執行的時候不允許別的事務並發執行。完全序列化執行，只能一個接著一個地執行，每次讀都需要獲得表級共用鎖定，讀寫相互都會阻塞

2、REPEATABLE READ（可重複讀）

一個事務在執行過程中可以看到其他事務已經提交的新插入的記錄，但是不能看到其他其他事務對已有記錄的更新

對於讀出的記錄，添加共用鎖定直到transaction A結束。其它transaction B對這個記錄的試圖修改會一直等待直到transaction A結束

在同一個事務內的查詢都是事務開始時刻一致的，InnoDB預設層級。在SQL標準中，該隔離等級消除了不可重複讀取，但是還存在幻讀

3、READ COMMITTED（提交讀）

一個事務在執行過程中可以看到其他事務已經提交的新插入的記錄，而且能看到其他事務已經提交的對已有記錄的更新

在transaction A中讀取資料時對記錄添加共用鎖定，但讀取結束立即釋放。其它transaction B對這個記錄的試圖修改會一直等待直到A中的讀取過程結束，而不需要整個transaction A的結束。所以，在transaction A的不同階段對同一記錄的讀取結果可能是不同的。

可能發生的問題：不可重複讀取

4、READ UNCOMMITTED(未提交讀)

一個事務在執行過程中可以看到其他事務沒有提交的新插入的記錄，而且能看到其他事務沒有提交的對已有記錄的更新

不添加共用鎖定。所以其它transaction B可以在transaction A對記錄的讀取過程中修改同一記錄，可能會導致A讀取的資料是一個被破壞的或者說不完整不正確的資料。

另外，在transaction A中可以讀取到transaction B（未提交）中修改的資料。比如transaction B對R記錄修改了，但未提交。此時，在transaction A中讀取R記錄，讀出的是被B修改過的資料。

隔離等級	髒讀（Dirty Read）	不可重複讀取（NonRepeatable Read）	幻讀（Phantom Read）
未提交讀（Read uncommitted）	可能	可能	可能
已提交讀（Read committed）	不可能	可能	可能
可重複讀（Repeatable read）	不可能	不可能	可能
可序列化（Serializable ）	不可能	不可能	不可能

 

由於MySQL的InnoDB預設是使用的RR層級，所以我們先要將該session開啟成RC層級，並且設定binlog的模式

mysql> select @@session.tx_isolation;+-----------------------+| @@global.tx_isolation |+-----------------------+| REPEATABLE-READ       |+-----------------------+SET sessionbinlog_format = ‘ROW‘; //MIXED

表結構

CREATE TABLE `users` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,  `age` tinyint(11) NOT NULL,  PRIMARY KEY (`id`),  KEY `id_age` (`age`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;INSERT INTO users VALUES ( 1 , ‘Bob‘ , 27 ), ( 2 , ‘Mike‘ , 7 ),( 3 , ‘Tony‘ , 40 ),( 4 , ‘Bill‘ , 21 ),( 5 , ‘Mark‘ , 18 );

幻讀

SET session transaction isolation level  Repeatable read;

幻讀發生在當兩個完全相同的查詢執行時，第二次查詢所返回的結果集跟第一個查詢不相同。發生的情況：沒有範圍鎖

事務1	事務2
SELECT * FROM users WHERE age BETWEEN 10 AND 30
	INSERT INTO users VALUES ( 3 , ‘Bob‘ , 27 );
SELECT * FROM users WHERE age BETWEEN 10 AND 30;

 

如何避免：實行序列化隔離模式，在任何一個低層級的隔離中都可能會發生。

不可重複讀取

SET session transaction isolation level read committed;

在基於鎖的並行控制方法中，如果在執行select時不添加讀鎖，就會發生不可重複讀取問題。在多版本並行控制機制中，當一個遇到提交衝突的事務需要回退但卻被釋放時，會發生不可重複讀取問題。

事務1	事務2
SELECT * FROM users WHERE id = 1;
	UPDATE users SET age = 21 WHERE id = 1 ;
SELECT * FROM users WHERE id = 1;

 

在上面這個例子中，事務2提交成功，它所做的修改已經可見。然而，事務1已經讀取了一個其它的值。在序列化和可重複讀的隔離等級中，資料庫管理系統會返回舊值，即在被事務2修改之前的值。在提交讀和未提交讀隔離等級下，可能會返回被更新的值，這就是“不可重複讀取”。

有兩個策略可以防止這個問題的發生：

1. 延遲事務2的執行，直至事務1提交或者回退。這種策略在使用鎖時應用。(悲觀鎖機制，比如用select for update為資料行加上一個獨佔鎖定)

2. 而在多版本並行控制中，事務2可以被先提交。而事務1，繼續執行在舊版本的資料上。當事務1終於嘗試提交時，資料庫會檢驗它的結果是否和事務1、事務2順序執行時一樣。如果是，則事務1提交成功。如果不是，事務1會被回退。（樂觀鎖機制）

髒讀

SET session transaction isolation level read uncommitted;

髒讀發生在一個事務A讀取了被另一個事務B修改，但是還未提交的資料。假如B回退，則事務A讀取的是無效的資料。這跟不可重複讀取類似，但是第二個事務不需要執行提交。 

事務1	事務2
SELECT * FROM users WHERE id = 1；
	UPDATE users SET age = 21 WHERE id = 1
SELECT FROM users WHERE id = 1；

【mysql】關於事務的隔離等級