主頁 > 知識庫 > MySQL 四種事務隔離級別詳解及對比

MySQL 四種事務隔離級別詳解及對比

熱門標簽:桂林云電銷機器人收費 東莞外呼企業(yè)管理系統(tǒng) 谷歌地圖標注位置圖解 南通電銷外呼系統(tǒng)哪家強 如何選擇優(yōu)質(zhì)的外呼系統(tǒng) 清遠申請400電話 手機外呼系統(tǒng)違法嗎 沈陽智能外呼系統(tǒng)供應商 地圖簡圖標注

MySQL 四種事務隔離級別詳解及對比

按照SQL:1992 事務隔離級別,InnoDB默認是可重復讀的(REPEATABLE READ)。MySQL/InnoDB 提供SQL標準所描述的所有四個事務隔離級別。你可以在命令行用--transaction-isolation選項,或在選項文件里,為所有連接設置默認隔離級別。
例如,你可以在my.inf文件的[mysqld]節(jié)里類似如下設置該選項:

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關鍵字,語句在全局對從那點開始創(chuàng)建的所有新連接(除了不存在的連接)設置默認事務級別。你需要SUPER權限來做這個。使用SESSION 關鍵字為將來在當前連接上執(zhí)行的事務設置默認事務級別。 任何客戶端都能自由改變會話隔離級別(甚至在事務的中間),或者為下一個事務設置隔離級別。

你可以用下列語句查詢?nèi)趾蜁捠聞崭綦x級別:

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):允許臟讀,也就是可能讀取到其他會話中未提交事務修改的數(shù)據(jù)

·提交讀(Read Committed):只能讀取到已經(jīng)提交的數(shù)據(jù)。Oracle等多數(shù)數(shù)據(jù)庫默認都是該級別 (不重復讀)

·可重復讀(Repeated Read):可重復讀。在同一個事務內(nèi)的查詢都是事務開始時刻一致的,InnoDB默認級別。在SQL標準中,該隔離級別消除了不可重復讀,但是還存在幻象讀

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

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

用例子說明各個級別的情況:

① 臟讀: 臟讀就是指當一個事務正在訪問數(shù)據(jù),并且對數(shù)據(jù)進行了修改,而這種修改還沒有提交到數(shù)據(jù)庫中,這時,另外一個事務也訪問這個數(shù)據(jù),然后使用了這個數(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  |    --------該隔離級別下(除了 read uncommitted)
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from ttd;
Empty set (0.00 sec)       --------不會出現(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    |  --------該隔離級別下
+------------------------+
1 row in set (0.00 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |                    --------REPEATABLE-READ級別出現(xiàn)臟讀

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

結(jié)論:session 2 在READ-UNCOMMITTED 下讀取到session 1 中未提交事務修改的數(shù)據(jù).

② 不可重復讀:是指在一個事務內(nèi),多次讀同一數(shù)據(jù)。在這個事務還沒有結(jié)束時,另外一個事務也訪問該同一數(shù)據(jù)。那么,在第一個事務中的兩次讀數(shù)據(jù)之間,由于第二個事務的修改,那么第一個事務兩次讀到的的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個事務內(nèi)兩次讀到的數(shù)據(jù)是不一樣的,因此稱為是不可重復讀。

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 級別出現(xiàn)了不重復讀
|  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 的結(jié)果;

session 1:

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

(commit session 1 之后 再select * from ttd 可以看到session 2 插入的數(shù)據(jù)3)

④ 幻讀:第一個事務對一個表中的數(shù)據(jù)進行了修改,這種修改涉及到表中的全部數(shù)據(jù)行。同時,第二個事務也修改這個表中的數(shù)據(jù),這種修改是向表中插入一行新數(shù)據(jù)。那么,以后就會發(fā)生操作第一個事務的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行,就好象發(fā)生了幻覺一樣。

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, 剛剛明明告訴我沒有這條記錄的)

如此就出現(xiàn)了幻讀,以為表里沒有數(shù)據(jù),其實數(shù)據(jù)已經(jīng)存在了,傻乎乎的提交后,才發(fā)現(xiàn)數(shù)據(jù)沖突了。

實驗二:

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   |
| +------+-------+

本事務中第一次讀取出一行,做了一次更新后,另一個事務里提交的數(shù)據(jù)就出現(xiàn)了。也可以看做是一種幻讀。

當隔離級別是可重復讀,且禁用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   |
| +----+-------+

如果使用普通的讀,會得到一致性的結(jié)果,如果使用了加鎖的讀,就會讀到“最新的”“提交”讀的結(jié)果。

本身,可重復讀和提交讀是矛盾的。在同一個事務里,如果保證了可重復讀,就會看不到其他事務的提交,違背了提交讀;如果保證了提交讀,就會導致前后兩次讀到的結(jié)果不一致,違背了可重復讀。

可以這么講,InnoDB提供了這樣的機制,在默認的可重復讀的隔離級別里,可以使用加鎖讀去查詢最新的數(shù)據(jù)(提交讀)。
MySQL InnoDB的可重復讀并不保證避免幻讀,需要應用使用加鎖讀來保證。而這個加鎖度使用到的機制就是next-key locks。

總結(jié):

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

感謝閱讀,希望能幫助到大家,謝謝大家對本站的支持!

您可能感興趣的文章:
  • MySQL查看和修改事務隔離級別的實例講解
  • Mysql事務隔離級別之讀提交詳解
  • MySQL四種事務隔離級別詳解
  • 深入解析MySQL的事務隔離及其對性能產(chǎn)生的影響
  • MySQL中Innodb的事務隔離級別和鎖的關系的講解教程
  • MySQL數(shù)據(jù)庫事務隔離級別介紹(Transaction Isolation Level)
  • MySQL InnoDB中的鎖機制深入講解
  • MySQL鎖機制與用法分析
  • 深入理解Mysql事務隔離級別與鎖機制問題

標簽:臨沂 成都 天津 貴州 內(nèi)蒙古 重慶 湖州 常德

巨人網(wǎng)絡通訊聲明:本文標題《MySQL 四種事務隔離級別詳解及對比》,本文關鍵詞  MySQL,四種,事務,隔離,級別,;如發(fā)現(xiàn)本文內(nèi)容存在版權問題,煩請?zhí)峁┫嚓P信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡,涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《MySQL 四種事務隔離級別詳解及對比》相關的同類信息!
  • 本頁收集關于MySQL 四種事務隔離級別詳解及對比的相關信息資訊供網(wǎng)民參考!
  • 推薦文章