MySQL 四種事務隔離級的說明 · 數據庫學習摘記

來自http://www.cnblogs.com/zhoujinyi/p/3437475.html **很早之前寫的文章，重新回顧和學習下，也可以看[這篇文章](http://tech.meituan.com/innodb-lock.html)說明。** 按照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)：完全串行化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ **用例子說明各個級別的情況：** **① 臟讀:**?臟讀就是指當一個事務正在訪問數據，并且對數據進行了修改，而這種修改還沒有提交到數據庫中，這時，另外一個事務也訪問這個數據，然后使用了這個數據。 ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 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) ![復制代碼](http://common.cnblogs.com/images/copycode.gif) **結論:**session 2 在READ-UNCOMMITTED 下讀取到session 1 中未提交事務修改的數據. **② 不可重復讀:**是指在一個事務內，多次讀同一數據。在這個事務還沒有結束時，另外一個事務也訪問該同一數據。那么，在第一個事務中的兩次讀數據之間，由于第二個事務的修改，那么第一個事務兩次讀到的的數據可能是不一樣的。這樣就發生了在一個事務內兩次讀到的數據是不一樣的，因此稱為是不可重復讀。 ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 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) ![復制代碼](http://common.cnblogs.com/images/copycode.gif) **③ 可重復讀:** ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 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) ![復制代碼](http://common.cnblogs.com/images/copycode.gif) **④ 幻讀:**第一個事務對一個表中的數據進行了修改，這種修改涉及到表中的全部數據行。同時，第二個事務也修改這個表中的數據，這種修改是向表中插入一行新數據。那么，以后就會發生操作第一個事務的用戶發現表中還有沒有修改的數據行，就好象發生了幻覺一樣。 ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 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 | | +------+-------+ ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 本事務中第一次讀取出一行，做了一次更新后，另一個事務里提交的數據就出現了。也可以看做是一種幻讀。當隔離級別是可重復讀，且禁用innodb_locks_unsafe_for_binlog的情況下，在搜索和掃描index的時候使用的next-key locks可以避免幻讀。再看一個實驗，要注意，表t_bitfly里的id為主鍵字段。 ![復制代碼](http://common.cnblogs.com/images/copycode.gif) **實驗三：** 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 | | +------+-------+ ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 可以看到，用id ![復制代碼](http://common.cnblogs.com/images/copycode.gif) **實驗四：一致性讀和提交讀** 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 | | +----+-------+ ![復制代碼](http://common.cnblogs.com/images/copycode.gif) 如果使用普通的讀，會得到一致性的結果，如果使用了加鎖的讀，就會讀到“最新的”“提交”讀的結果。本身，可重復讀和提交讀是矛盾的。在同一個事務里，如果保證了可重復讀，就會看不到其他事務的提交，違背了提交讀；如果保證了提交讀，就會導致前后兩次讀到的結果不一致，違背了可重復讀。可以這么講，InnoDB提供了這樣的機制，在默認的可重復讀的隔離級別里，可以使用加鎖讀去查詢最新的數據（提交讀）。 MySQL InnoDB的可重復讀并不保證避免幻讀，需要應用使用加鎖讀來保證。而這個加鎖度使用到的機制就是[next-key locks](http://www.cnblogs.com/zhoujinyi/p/3435982.html)。 **總結:** 四個級別逐漸增強，每個級別解決一個問題。事務級別越高,性能越差,大多數環境read committed 可以用.記住4個隔離級別的特點(上面的例子); ~~~~~~~~~~~~~~~ 萬物之中,希望至美 ~~~~~~~~~~~~~~~