概述
SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。低级别的隔离级一般支持更高的并发处理,并拥有更低的系统开销。
1. Read Uncommitted(读取未提交内容)
在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。
2. Read Committed(读取提交内容)
这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。
3. Repeatable Read(可重读)
这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。
4. Serializable(可串行化)
这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。
这四种隔离级别采取不同的锁类型来实现,若读取的是同一个数据的话,就容易发生问题。例如:
脏读(Drity Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。
在MySQL中,实现了这四种隔离级别,分别有可能产生问题如下所示:
隔离级别 | 脏读 | 不可重复读 | 幻读 |
Read Uncommitted | √ | √ | √ |
Read Committed | × | √ | √ |
Repeatable Read | × | × | √ |
Serializable | × | × | × |
测试
测试前准备
下面,将利用MySQL的客户端程序,分别测试几种隔离级别。测试数据库为test,表为t1;表结构:
通过mysql -uxxxx -p 打开客户端窗口MYSQL_A
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)
修改隔离级别方法:
用户可以用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;
##设置事务级别为未提交读
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED |
+-----------------------+
1 row in set (0.00 sec)
mysql> select @@tx_isolation;
+-----------------------+
| @@tx_isolation |
+-----------------------+
| READ-UNCOMMITTED |
+-----------------------+
1 row in set (0.00 sec)
修改事务自动提交
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set autocommit = off;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
修改完隔离级别及手动事务后。打开另一个mysql窗口,称为MYSQL_B
开始测试
测试Read Uncommitted(未提交读)
MYSQL_A执行:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1,'aa');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
+------+------+
1 row in set (0.00 sec)
MYSQL_B执行:
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
+------+------+
1 row in set (0.00 sec)
MYSQL_A执行:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(2,'bb');
Query OK, 1 row affected (0.01 sec)
MYSQL_B执行:
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | bb |
+------+------+
2 rows in set (0.00 sec)
MYSQL_A执行:
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
+------+------+
1 row in set (0.00 sec)
MYSQL_B执行:
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
+------+------+
1 row in set (0.00 sec)
经过上面的实验可以得出结论,事务MYSQL_A插入了一条记录,但是没有提交,此时MYSQL_B可以查询出未提交记录。造成脏读现象。未提交读是最低的隔离级别。
注意 不论是【MYSQL_A】还是【MYSQL_B】每次开启事务,CRUD操作结束后要commit。否则影响其他事务操作。
测试Read Committed(已提交读)
MYSQL_A执行:
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
1 row in set (0.00 sec)
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------------+
| @@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 test.t1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | ccFromMB5 |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+------+-----------+
4 rows in set (0.00 sec)
MYSQL_B执行:
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
1 row in set (0.00 sec)
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------------+
| @@tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test.t1 values(5,'ee');
Query OK, 1 row affected (0.00 sec)
MYSQL_A执行:依然是4条记录。
mysql> select * from test.t1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | ccFromMB5 |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+------+-----------+
4 rows in set (0.00 sec)
MYSQL_B执行:commit
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
MYSQL_A执行:
mysql> select * from test.t1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | ccFromMB5 |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
+------+-----------+
5 rows in set (0.00 sec)
#因为开始开启了手动事务,结束后别忘了commit
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
经过上面的实验可以得出结论,已提交读隔离级别解决了脏读的问题,但是出现了不可重复读的问题,即事务A在B commit前后两次查询的数据不一致,因为在两次查询之间事务B插入了一条数据。已提交读只允许读取已提交的记录,但不要求可重复读。
测试Repeatable Read(可重读)
MYSQL_A 和 MYSQL_B执行:
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)
MYSQL_A执行:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test.t1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | ccFromMB5 |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+------+-----------+
4 rows in set (0.00 sec)
MYSQL_B执行:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test.t1 values(6,'fff');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> update test.t1 set name = 'rere' where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test.t1;
+------+------+
| id | name |
+------+------+
| 1 | rere |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 6 | fff |
+------+------+
6 rows in set (0.00 sec)
MYSQL_A执行:
#未执行commit
mysql> select * from test.t1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | ccFromMB5 |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
+------+-----------+
5 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#执行commit后,查询出最新数据
mysql> select * from test.t1;
+------+------+
| id | name |
+------+------+
| 1 | rere |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 6 | fff |
+------+------+
6 rows in set (0.00 sec)
测试传说中的Repeatable Read(可重读)下的幻读
为了让幻读重现,我们修改t1表的id字段为主键
MYSQL_A执行:
mysql> alter table test.t1 add primary key(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test.t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
OK,我们开始测试幻读
MYSQL_A执行:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
| 1 | rere |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 6 | fff |
+----+------+
6 rows in set (0.00 sec)
MYSQL_B执行:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test.t1 values(7,'ggg');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
| 1 | rere |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 6 | fff |
| 7 | ggg |
+----+------+
7 rows in set (0.01 sec)
MYSQL_A执行:
mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
| 1 | rere |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 6 | fff |
+----+------+
6 rows in set (0.00 sec)
mysql> insert into test.t1 values (7,'vvv');
ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'
在未提交当前事务时,查出来就6条,但是事务B已经提交了id为7的记录,这个时候,事务A会莫名的感到无助(提示:ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'),当提交A事务后,再查询就会发现id为7的记录已经存在。这就是Repeatable Read(可重读)下的幻读
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
| 1 | rere |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 6 | fff |
| 7 | ggg |
+----+------+
7 rows in set (0.00 sec)
测试Serializable(串行化)
MYSQL_A 执行:
mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+
1 row in set (0.00 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| SERIALIZABLE |
+-----------------------+
1 row in set (0.00 sec)
MYSQL_B依然是可重复读隔离级别。
MYSQL_A 执行:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
| 1 | rere |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 6 | fff |
| 7 | ggg |
| 8 | hhh |
+----+------+
8 rows in set (0.00 sec)
MYSQL_B 执行:
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)
mysql> insert into test.t1 values(9,'iii');
这时,客户端会一直保持等待状态,不会打印出执行结果,直至A事务提交。
MYSQL_A 执行:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
MYSQL_B 自动结束等待
一下这一句是A端执行commit后打印出的,可以看下执行时间。我等了23秒才手动在A端执行commit。
#这个是刚才insert等待的执行结果
Query OK, 1 row affected (23.41 sec)
#B端commit。
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
MYSQL_A 执行:
mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
| 1 | rere |
| 2 | bb |
| 3 | cc |
| 4 | dd |
| 5 | ee |
| 6 | fff |
| 7 | ggg |
| 8 | hhh |
| 9 | iii |
+----+------+
9 rows in set (6.09 sec)
这里需要注意,如果B端insert后一直没有commit。那么A端同样会进入等待状态,直至B端提交事务。可以看下执行时间,我是先在A端执行查询,进入等待。然后在B端执行commit。随后A端结束等待查出B端插入的id为9的数据。可见,此隔离级别是最高的,也是最消耗资源、性能最差的级别。
serializable完全锁定字段,若一个事务来查询同一份数据就必须等待,直到前一个事务完成并解除锁定为止 。是完整的隔离级别,会锁定对应的数据表格,因而会有效率的问题。
Serializable可能造成B端插入的时候等待超时报错,如:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction