MySQL 四種事務(wù)隔離級(jí)別詳解及對(duì)比
MySQL 四種事務(wù)隔離級(jí)別詳解及對(duì)比
按照SQL:1992 事務(wù)隔離級(jí)別，InnoDB默認(rèn)是可重復(fù)讀的（REPEATABLE READ）。MySQL/InnoDB 提供SQL標(biāo)準(zhǔn)所描述的所有四個(gè)事務(wù)隔離級(jí)別。你可以在命令行用--transaction-isolation選項(xiàng)，或在選項(xiàng)文件里，為所有連接設(shè)置默認(rèn)隔離級(jí)別。
例如，你可以在my.inf文件的[mysqld]節(jié)里類似如下設(shè)置該選項(xiàng)：
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}
用戶可以用SET TRANSACTION語(yǔ)句改變單個(gè)會(huì)話或者所有新進(jìn)連接的隔離級(jí)別。它的語(yǔ)法如下：
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
注意：默認(rèn)的行為（不帶session和global）是為下一個(gè)（未開(kāi)始）事務(wù)設(shè)置隔離級(jí)別。如果你使用GLOBAL關(guān)鍵字，語(yǔ)句在全局對(duì)從那點(diǎn)開(kāi)始創(chuàng)建的所有新連接（除了不存在的連接）設(shè)置默認(rèn)事務(wù)級(jí)別。你需要SUPER權(quán)限來(lái)做這個(gè)。使用SESSION 關(guān)鍵字為將來(lái)在當(dāng)前連接上執(zhí)行的事務(wù)設(shè)置默認(rèn)事務(wù)級(jí)別。 任何客戶端都能自由改變會(huì)話隔離級(jí)別（甚至在事務(wù)的中間），或者為下一個(gè)事務(wù)設(shè)置隔離級(jí)別。
你可以用下列語(yǔ)句查詢?nèi)趾蜁?huì)話事務(wù)隔離級(jí)別：
SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;
----以上手冊(cè)中的理論知識(shí);
===========================================================================================
隔離級(jí)別 臟讀（Dirty Read） 不可重復(fù)讀（NonRepeatable Read） 幻讀（Phantom Read）
===========================================================================================
未提交讀（Read uncommitted） 可能 可能 可能
已提交讀（Read committed） 不可能 可能 可能
可重復(fù)讀（Repeatable read） 不可能 不可能 可能
可串行化（Serializable ） 不可能 不可能 不可能
===========================================================================================
·未提交讀(Read Uncommitted)：允許臟讀，也就是可能讀取到其他會(huì)話中未提交事務(wù)修改的數(shù)據(jù)
·提交讀(Read Committed)：只能讀取到已經(jīng)提交的數(shù)據(jù)。Oracle等多數(shù)數(shù)據(jù)庫(kù)默認(rèn)都是該級(jí)別 (不重復(fù)讀)
·可重復(fù)讀(Repeated Read)：可重復(fù)讀。在同一個(gè)事務(wù)內(nèi)的查詢都是事務(wù)開(kāi)始時(shí)刻一致的，InnoDB默認(rèn)級(jí)別。在SQL標(biāo)準(zhǔn)中，該隔離級(jí)別消除了不可重復(fù)讀，但是還存在幻象讀
·串行讀(Serializable)：完全串行化的讀，每次讀都需要獲得表級(jí)共享鎖，讀寫(xiě)相互都會(huì)阻塞
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
用例子說(shuō)明各個(gè)級(jí)別的情況：
① 臟讀: 臟讀就是指當(dāng)一個(gè)事務(wù)正在訪問(wèn)數(shù)據(jù)，并且對(duì)數(shù)據(jù)進(jìn)行了修改，而這種修改還沒(méi)有提交到數(shù)據(jù)庫(kù)中，這時(shí)，另外一個(gè)事務(wù)也訪問(wèn)這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。
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 | --------該隔離級(jí)別下(除了 read uncommitted) +-----------------------+ 1 row in set (0.00 sec) mysql> select * from ttd; Empty set (0.00 sec) --------不會(huì)出現(xiàn)臟讀 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 | --------該隔離級(jí)別下 +------------------------+ 1 row in set (0.00 sec) mysql> select * from ttd; +------+ | id | +------+ | 1 | --------REPEATABLE-READ級(jí)別出現(xiàn)臟讀 +------+ 1 row in set (0.00 sec)
結(jié)論:session 2 在READ-UNCOMMITTED 下讀取到session 1 中未提交事務(wù)修改的數(shù)據(jù).
② 不可重復(fù)讀:是指在一個(gè)事務(wù)內(nèi)，多次讀同一數(shù)據(jù)。在這個(gè)事務(wù)還沒(méi)有結(jié)束時(shí)，另外一個(gè)事務(wù)也訪問(wèn)該同一數(shù)據(jù)。那么，在第一個(gè)事務(wù)中的兩次讀數(shù)據(jù)之間，由于第二個(gè)事務(wù)的修改，那么第一個(gè)事務(wù)兩次讀到的的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的，因此稱為是不可重復(fù)讀。
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); /也可以更新數(shù)據(jù) 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 的結(jié)果; session 1: mysql> select * from ttd; +------+ | id | +------+ | 1 | --------和第一次的結(jié)果不一樣,READ-COMMITTED 級(jí)別出現(xiàn)了不重復(fù)讀 | 2 | +------+ 2 rows in set (0.00 sec)
③ 可重復(fù)讀:
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 的結(jié)果; session 1: mysql> select * from ttd; +------+ | id | +------+ | 1 | --------和第一次的結(jié)果一樣,REPEATABLE-READ級(jí)別出現(xiàn)了重復(fù)讀 | 2 | +------+ 2 rows in set (0.00 sec) (commit session 1 之后 再select * from ttd 可以看到session 2 插入的數(shù)據(jù)3)
④ 幻讀:第一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)進(jìn)行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時(shí)，第二個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。那么，以后就會(huì)發(fā)生操作第一個(gè)事務(wù)的用戶發(fā)現(xiàn)表中還有沒(méi)有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺(jué)一樣。
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 | +-----------------------+-----------------+ 實(shí)驗(yàn)一： 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, 剛剛明明告訴我沒(méi)有這條記錄的) 如此就出現(xiàn)了幻讀，以為表里沒(méi)有數(shù)據(jù)，其實(shí)數(shù)據(jù)已經(jīng)存在了，傻乎乎的提交后，才發(fā)現(xiàn)數(shù)據(jù)沖突了。 實(shí)驗(yàn)二： 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 | (怎么多出來(lái)一行) | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | z | | | 2 | z | | +------+-------+
本事務(wù)中第一次讀取出一行，做了一次更新后，另一個(gè)事務(wù)里提交的數(shù)據(jù)就出現(xiàn)了。也可以看做是一種幻讀。
當(dāng)隔離級(jí)別是可重復(fù)讀，且禁用innodb_locks_unsafe_for_binlog的情況下，在搜索和掃描index的時(shí)候使用的next-key locks可以避免幻讀。
再看一個(gè)實(shí)驗(yàn)，要注意，表t_bitfly里的id為主鍵字段。
實(shí)驗(yàn)三： 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的記錄時(shí)就會(huì)等待鎖的釋放。
實(shí)驗(yàn)四：一致性讀和提交讀 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 | | +----+-------+
如果使用普通的讀，會(huì)得到一致性的結(jié)果，如果使用了加鎖的讀，就會(huì)讀到“最新的”“提交”讀的結(jié)果。
本身，可重復(fù)讀和提交讀是矛盾的。在同一個(gè)事務(wù)里，如果保證了可重復(fù)讀，就會(huì)看不到其他事務(wù)的提交，違背了提交讀；如果保證了提交讀，就會(huì)導(dǎo)致前后兩次讀到的結(jié)果不一致，違背了可重復(fù)讀。
可以這么講，InnoDB提供了這樣的機(jī)制，在默認(rèn)的可重復(fù)讀的隔離級(jí)別里，可以使用加鎖讀去查詢最新的數(shù)據(jù)（提交讀）。
MySQL InnoDB的可重復(fù)讀并不保證避免幻讀，需要應(yīng)用使用加鎖讀來(lái)保證。而這個(gè)加鎖度使用到的機(jī)制就是next-key locks。
總結(jié):
四個(gè)級(jí)別逐漸增強(qiáng)，每個(gè)級(jí)別解決一個(gè)問(wèn)題。事務(wù)級(jí)別越高,性能越差,大多數(shù)環(huán)境read committed 可以用.記住4個(gè)隔離級(jí)別的特點(diǎn)(上面的例子);
感謝閱讀，希望能幫助到大家，謝謝大家對(duì)本站的支持！
相關(guān)文章
最近因?yàn)楣ぷ餍枰?，要使用mysql查詢記錄可如果有時(shí)間戳字段時(shí)，查看結(jié)果不方便，不能即時(shí)看到時(shí)間戳代表的含義，所以這篇文章就提供mysql格式換時(shí)間函數(shù)，可以方便的看到格式化后的時(shí)間。有需要的朋友們可以參考借鑒，下面來(lái)一起看看吧。2016-11-11
作為一名后端程序員，經(jīng)常會(huì)對(duì)慢查詢SQL語(yǔ)句進(jìn)行調(diào)優(yōu)，而SQL語(yǔ)句出現(xiàn)慢查詢，很多情況是由于索引失效造成的，本文為大家整理了14種MySQL索引失效的場(chǎng)景，需要的可以參考一下2023-05-05
最近迭代的產(chǎn)品版本從2.X來(lái)到了3.X,屬于一個(gè)非常大的產(chǎn)品升級(jí),比上個(gè)版本多了很多功能,那么上線之前肯定要在一個(gè)干凈的環(huán)境里進(jìn)行測(cè)試回歸以及性能測(cè)試,本文總結(jié)一下數(shù)據(jù)庫(kù)層面的一些緩存機(jī)制對(duì)查詢速度整體的優(yōu)化,需要的朋友可以參考下2023-08-08
本文詳細(xì)介紹了MySQL索引優(yōu)化、鎖和事物等學(xué)習(xí)記錄，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-12-12
MySQL流程函數(shù)常見(jiàn)用法實(shí)例分析
這篇文章主要介紹了MySQL流程函數(shù)常見(jiàn)用法,結(jié)合實(shí)例形式分析了mysql流程函數(shù)功能、常見(jiàn)使用方法與操作注意事項(xiàng),需要的朋友可以參考下2019-12-12
Mysql存儲(chǔ)二進(jìn)制對(duì)象數(shù)據(jù)問(wèn)題
這篇文章主要介紹了Mysql存儲(chǔ)二進(jìn)制對(duì)象數(shù)據(jù)問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-03-03
MySQL 中 datetime 和 timestamp 的區(qū)別與選擇
MySQL 中常用的兩種時(shí)間儲(chǔ)存類型分別是datetime和 timestamp。如何在它們之間選擇是建表時(shí)必要的考慮。下面就談?wù)勊麄兊膮^(qū)別和怎么選擇,需要的朋友可以參考一下2021-09-09
Mysql內(nèi)置函數(shù)的實(shí)現(xiàn)示例
mysql內(nèi)置了很多的函數(shù),本文主要介紹了Mysql內(nèi)置函數(shù)的實(shí)現(xiàn)示例,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2024-07-07
mysql創(chuàng)建學(xué)生表、課程表及學(xué)生選課表詳細(xì)代碼
這篇文章主要給大家介紹了mysql創(chuàng)建學(xué)生表、課程表及學(xué)生選課表的相關(guān)資料,學(xué)生、課程以及成績(jī)的增刪改查都是建立在連接數(shù)據(jù)庫(kù)的基礎(chǔ)之上,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-12-12
mysql實(shí)現(xiàn)數(shù)據(jù)文件存儲(chǔ)到指定分區(qū)的示例代碼
MySQL中可以使用分區(qū)表來(lái)提高數(shù)據(jù)檢索速度,本文主要介紹了mysql實(shí)現(xiàn)數(shù)據(jù)文件存儲(chǔ)到指定分區(qū)的示例代碼,具有一定的參考價(jià)值,感興趣的可以了解一下2024-01-01