MySQL 四種事務隔離等級詳解及對比
- 黄舟原創
- 2017-01-18 11:29:421557瀏覽
Mysql關係型資料庫管理系統
MySQL是一個開放式原始碼的小型關聯式資料庫管理系統，開發者為瑞典MySQL AB公司。 MySQL被廣泛地應用在Internet上的中小型網站。由於其體積小、速度快、總體擁有成本低，尤其是開放原始碼這一特點，許多中小型網站為了降低網站總體擁有成本而選擇了MySQL作為網站資料庫。
這篇文章主要介紹了MySQL 四種事務隔離級別詳解及對比的相關資料,這裡對Mysql 的基礎知識做了詳細介紹及對其事務隔離進行了比較，需要的朋友可以參考下
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）
====== ================================================== ===================================
未提交讀（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。
以上就是MySQL 四种事务隔离级别详解及对比的内容，更多相关内容请关注PHP中文网（www.php.cn）！