【mysql】關於事務的隔離級別，mysql隔離級別

【mysql】關於事務的隔離級別，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；