MySQL 四種事務隔離級的說明

很早之前寫的文章，重新回顧和學習下，也可以看這篇文章說明。

按照SQL:1992 事務隔離級别，InnoDB預設是可重複讀的（REPEATABLE READ）。MySQL/InnoDB 提供SQL标準所描述的所有四個事務隔離級别。你可以在指令行用--transaction-isolation選項，或在選項檔案裡，為所有連接配接設定預設隔離級别。

例如，你可以在my.inf檔案的[mysqld]節裡類似如下設定該選項：

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

使用者可以用SET TRANSACTION語句改變單個會話或者所有新進連接配接的隔離級别。它的文法如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

注意：預設的行為（不帶session和global）是為下一個（未開始）事務設定隔離級别。如果你使用GLOBAL關鍵字，語句在全局對從那點開始建立的所有新連接配接（除了不存在的連接配接）設定預設事務級别。你需要SUPER權限來做這個。使用SESSION 關鍵字為将來在目前連接配接上執行的事務設定預設事務級别。任何用戶端都能自由改變會話隔離級别（甚至在事務的中間），或者為下一個事務設定隔離級别。

你可以用下列語句查詢全局和會話事務隔離級别：

SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

----以上手冊中的理論知識;

===========================================================================================

隔離級别髒讀（Dirty Read）不可重複讀（NonRepeatable Read）幻讀（Phantom Read）

未送出讀（Read uncommitted）可能可能可能

已送出讀（Read committed）不可能可能可能

可重複讀（Repeatable read）不可能不可能可能

可串行化（Serializable ）不可能不可能不可能

·未送出讀(Read Uncommitted)：允許髒讀，也就是可能讀取到其他會話中未送出事務修改的資料

·送出讀(Read Committed)：隻能讀取到已經送出的資料。Oracle等多數資料庫預設都是該級别 (不重複讀)

·可重複讀(Repeated Read)：可重複讀。在同一個事務内的查詢都是事務開始時刻一緻的，InnoDB預設級别。在SQL标準中，該隔離級别消除了不可重複讀，但是還存在幻象讀

·串行讀(Serializable)：完全串行化的讀，每次讀都需要獲得表級共享鎖，讀寫互相都會阻塞

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

用例子說明各個級别的情況：

① 髒讀: 髒讀就是指當一個事務正在通路資料，并且對資料進行了修改，而這種修改還沒有送出到資料庫中，這時，另外一個事務也通路這個資料，然後使用了這個資料。

session 1:
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

mysql> select @@session.tx_isolation;
+-----------------------+
| @@session.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ttd values(1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ   |        --------該隔離級别下(除了 read uncommitted)
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from ttd;
Empty set (0.00 sec)              --------不會出現髒讀

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |   --------該隔離級别下
+------------------------+
1 row in set (0.00 sec)

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |                                       --------REPEATABLE-READ級别出現髒讀

+------+
1 row in set (0.00 sec)

結論:session 2 在READ-UNCOMMITTED 下讀取到session 1 中未送出事務修改的資料.

② 不可重複讀:是指在一個事務内，多次讀同一資料。在這個事務還沒有結束時，另外一個事務也通路該同一資料。那麼，在第一個事務中的兩次讀資料之間，由于第二個事務的修改，那麼第一個事務兩次讀到的的資料可能是不一樣的。這樣就發生了在一個事務内兩次讀到的資料是不一樣的，是以稱為是不可重複讀。

session 1:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

session 2 :

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into ttd values(2);  /也可以更新資料
Query OK, 1 row affected (0.00 sec)

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

session 2 送出後,檢視session 1 的結果;

session 1:

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |                             --------和第一次的結果不一樣,READ-COMMITTED 級别出現了不重複讀
|    2 |
+------+
2 rows in set (0.00 sec)

③ 可重複讀:

session 1:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

session 2 :

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ttd values(3);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

session 2 送出後,檢視session 1 的結果;

session 1:

mysql> select * from ttd;
+------+
| id   |
+------+
|    1 |                                      --------和第一次的結果一樣,REPEATABLE-READ級别出現了重複讀
|    2 |
+------+
2 rows in set (0.00 sec)

(commit session 1 之後 再select * from ttd 可以看到session 2 插入的資料3)

④ 幻讀:第一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，第二個事務也修改這個表中的資料，這種修改是向表中插入一行新資料。那麼，以後就會發生操作第一個事務的使用者發現表中還有沒有修改的資料行，就好象發生了幻覺一樣。

mysql>CREATE TABLE `t_bitfly` (
`id` bigint(20) NOT NULL default '0',
`value` varchar(32) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+

實驗一：

t Session A                   Session B
|
| START TRANSACTION;          START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| empty set
|                             INSERT INTO t_bitfly
|                             VALUES (1, 'a');
|
| SELECT * FROM t_bitfly;
| empty set
|                             COMMIT;
|
| SELECT * FROM t_bitfly;
| empty set
|
| INSERT INTO t_bitfly VALUES (1, 'a');
| ERROR 1062 (23000):
| Duplicate entry '1' for key 1
v (shit, 剛剛明明告訴我沒有這條記錄的)

如此就出現了幻讀，以為表裡沒有資料，其實資料已經存在了，傻乎乎的送出後，才發現資料沖突了。

實驗二：

t Session A                  Session B
|
| START TRANSACTION;         START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a     |
| +------+-------+
|                            INSERT INTO t_bitfly
|                            VALUES (2, 'b');
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a     |
| +------+-------+
|                            COMMIT;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a     |
| +------+-------+
|
| UPDATE t_bitfly SET value='z';
| Rows matched: 2  Changed: 2  Warnings: 0
| (怎麼多出來一行)
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | z     |
| |    2 | z     |
| +------+-------+

本事務中第一次讀取出一行，做了一次更新後，另一個事務裡送出的資料就出現了。也可以看做是一種幻讀。

當隔離級别是可重複讀，且禁用innodb_locks_unsafe_for_binlog的情況下，在搜尋和掃描index的時候使用的next-key locks可以避免幻讀。

再看一個實驗，要注意，表t_bitfly裡的id為主鍵字段。

實驗三：
t Session A                 Session B
|
| START TRANSACTION;        START TRANSACTION;
|
| SELECT * FROM t_bitfly
| WHERE id<=1
| FOR UPDATE;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a     |
| +------+-------+
|                           INSERT INTO t_bitfly
|                           VALUES (2, 'b');
|                           Query OK, 1 row affected
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a     |
| +------+-------+
|                           INSERT INTO t_bitfly
|                           VALUES (0, '0');
|                           (waiting for lock ...then timeout)
|                           ERROR 1205 (HY000):
|                           Lock wait timeout exceeded;
|                           try restarting transaction
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a     |
| +------+-------+
|                           COMMIT;
|
| SELECT * FROM t_bitfly;

| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a     |
| +------+-------+

可以看到，用id<=1加的鎖，隻鎖住了id<=1的範圍，可以成功添加id為2的記錄，添加id為0的記錄時就會等待鎖的釋放。

實驗四：一緻性讀和送出讀
t Session A                      Session B
|
| START TRANSACTION;             START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| |  1 | a     |
| +----+-------+
|                                INSERT INTO t_bitfly
|                                VALUES (2, 'b');
|                                COMMIT;
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| |  1 | a     |
| +----+-------+
|
| SELECT * FROM t_bitfly LOCK IN SHARE MODE;
| +----+-------+
| | id | value |
| +----+-------+
| |  1 | a     |
| |  2 | b     |
| +----+-------+
|
| SELECT * FROM t_bitfly FOR UPDATE;
| +----+-------+
| | id | value |
| +----+-------+
| |  1 | a     |
| |  2 | b     |
| +----+-------+
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| |  1 | a     |
| +----+-------+

如果使用普通的讀，會得到一緻性的結果，如果使用了加鎖的讀，就會讀到“最新的”“送出”讀的結果。

本身，可重複讀和送出讀是沖突的。在同一個事務裡，如果保證了可重複讀，就會看不到其他事務的送出，違背了送出讀；如果保證了送出讀，就會導緻前後兩次讀到的結果不一緻，違背了可重複讀。

可以這麼講，InnoDB提供了這樣的機制，在預設的可重複讀的隔離級别裡，可以使用加鎖讀去查詢最新的資料（送出讀）。

MySQL InnoDB的可重複讀并不保證避免幻讀，需要應用使用加鎖讀來保證。而這個加鎖度使用到的機制就是next-key locks。

總結:

四個級别逐漸增強，每個級别解決一個問題。事務級别越高,性能越差,大多數環境read committed 可以用.記住4個隔離級别的特點(上面的例子);

~~~~~~~~~~~~~~~

萬物之中,希望至美

~~~~~~~~~~~~~~~

MySQL 四種事務隔離級的說明

繼續閱讀

【MySQL】MySQL的鎖與事務隔離級别詳解１. 鎖的概述2. 三種鎖（表鎖、行鎖、間隙鎖） 3. 讀鎖和寫鎖 4. 悲觀鎖和樂觀鎖

(一) 深入 mysql----read repeatable與 read committed差別1. 事務隔離級别分類2. 事務的特性3. 髒讀、不可重複讀、幻讀

Spring（五）：事務的傳播行為和隔離級别

基礎架構 spring事務的傳播機制與隔離級别

事務的隔離級别和傳播屬性

檢視和修改MySQL資料庫的事物隔離級别檢視mysql的事務隔離級别修改mysql資料庫事務隔離級别

事務隔離并發問題隔離級别 (圖文詳解)

Mysql資料庫事務隔離級别這是一個真實的故事資料庫系統的事務

SSM架構（Spring+SpringMVC+MyBatis）之 Spring 事務、事務、事務（面試題）SSM架構（Spring+SpringMVC+MyBatis）之 Spring 事務、事務、事務事務概念：完成某個獨立行為的一個或者一組步驟的集合，要麼全部執行成功，要麼全部失敗

事務隔離級别-Transaction Isolation Levels

Spring @Transactional - 隔離，傳播

面試公司：蔚來汽車面試職位：後端開發面試官：是做蔚來商城業務的面試時間：45min；面試内容如下：1.項目背景(目的，實

淺談Spring事務的隔離級别

PostgreSQL 基于heap表引擎的事務實作原理

「資料庫」兩小時整理萬字圖文詳解MySQL事務隔離級别和MVCC

關于資料庫操作的事務 1.事務的概念：