MySQL事务隔离级别初探

MySQL有四种隔离级别,分别是:

 

READ UNCOMMITTED(未提交读)

READ COMMITTED(提交读)

REPEATABLE READ (可重复读)

SERIALIZABLE(可串行化)

 

下面会分别用一些例子来解释各种隔离级别,在开始之前,首先要了解一些前提知识:

 

AUTOCOMMIT:MySQL默认开启自动提交(AUTOCOMMIT),如果不是显式地开启一个事务,则每个查询都被当做一个事务执行提交操作。在当前连接中,可以通过设置 AUTOCOMMIT 变量来启用或者禁用自动提交:

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)

1 或者 ON 表示启用,0 或者 OFF 表示禁用。当 AUTOCOMMIT 禁用时,所有的查询都是在一个事务中,直到显式地执行 COMMIT 提交或者 ROLLBACK 回滚,该事务结束,同时又开始了另一个新事务。修改 AUTOCOMMIT 对于费事务型的表,比如 MyISAM 或者内存表,不会有任何影响。对这类表来说,没有 COMMIT 或者 ROLLBACK 的概念,也可以说是相当于一直处于 AUTOCOMMIT 启用的状态;

还有一些命令,在执行之前会强制执行 COMMIT 提交当前活动的事务。比如 ALTER TABLE , LOCK TABLES 等,如有需要,请检查对应版本官方文档来确认可能导致自动提交的语句;

查看和设置隔离级别:可以通过 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别,或者在配置文件中设置整个数据库的隔离级别;InnoDB支持所有的隔离级别;新的隔离级别会在下一个事务开始的时候生效;

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 |
+-----------------------+
1 row in set (0.00 sec)

 

mysql> set session tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

 

mysql> set global tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

 

下面我用一些例子来演示一下各种隔离级别的表现:

 

mysql> create table test (i int,primary key(i)) engine=innodb;
Query OK, 0 rows affected (0.02 sec) 

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`i` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------+

下文的例子中,表格左边和表格右边分别是两个session中的行为和表现

 

READ UNCOMMITTED(未提交读)

在  READ UNCOMMITTED 级别,事务中的修改,即使没有被提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称为脏读(Dirty Read)。这个级别会导致很多的问题,从性能上来说,这个级别不会比其他的级别好太多,但是却缺乏其他级别的种种好处,除非真的有非常充足的理由,在实际应用中,一般很少使用;

 

mysql> set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec) 
mysql> set session tx_isolation='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 @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec) 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) 
mysql> select * from test;
Empty set (0.00 sec) 
 
 
mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec) 
mysql> select * from test;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec) 
在右边的session中未提交的数据,
在左边session中可以读到,
这就是所谓的脏读;
 
 
mysql> rollback;
Query OK, 0 rows affected (0.00 sec) 
mysql> select * from test;
Empty set (0.00 sec) 
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec) 
 

  

READ COMMITTED(提交读)

大多数数据库的默认隔离级别都是 READ COMMITTED ,但 MySQL 不是。这个级别满足了ACID原则中隔离性的简单定义:一个事务开始时,只能“看见”已经提交的事务所做的修改。换句话说,一个事务从开始到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫作不可重复读(nonrepeatable read),因为两次执行同样的查询,可能会得到不一样的结果;

mysql> set session tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set (0.01 sec) 
mysql> set session tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set (0.01 sec) 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) 
mysql> select * from test;
Empty set (0.00 sec) 
 
 
mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec) 
mysql> select * from test;
Empty set (0.00 sec) 
右边 session 未提交的数据不能被读取到,避免了脏读
 
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec) 
mysql> select * from test;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec) 
右边 session 已经提交,此时可以读到
 
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into test values(2);
Query OK, 1 row affected (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec) 
mysql> select * from test;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec) 
左边 session 在一个事务的两次查询中,
出现了数据不一致的情况,
这在某些场景下是会有问题的,
所以这个隔离级别有时也被叫做 不可重复读;
 
mysql> commit;
Query OK, 0 rows affected (0.01 sec) 
 

 

REPEATABLE READ(可重复读)

该级别是 MySQL 的默认隔离级别,这个隔离级别解决了上述的脏读和不可重复读的问题。在该级别下,同一个事务中多次读取同样的记录的结果是一致的。不过可重复读级别还是没有解决另外一个幻读(Phantom Read)的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,会产生幻行(Phantom Row)。

mysql> set session tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec) 
mysql> set session tx_isolation='repeatable-read';
Query OK, 0 rows affected (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> start transaction;
Query OK, 0 rows affected (0.00 sec) 
mysql> select * from test;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec) 
 
 
mysql> insert into test values (3);
Query OK, 1 row affected (0.00 sec) 
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec) 
mysql> select * from test;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec) 
即使右边 session 已经提交,同一事务中读取的数据不会变
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from test;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec) 
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) 
 
 
mysql> insert into test values (4);
Query OK, 1 row affected (0.00 sec) 
mysql> select * from test;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec) 
 
mysql> insert into test values (4);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
明明没有‘4’这条数据,却显示主键冲突,
就好像有一个幻影数据的值为‘4’ 
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec) 
 
mysql> select * from test;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec) 
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec) 
 
 
mysql> insert into test values (5);
Query OK, 1 row affected (0.00 sec) 
mysql> select * from test;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec) 
 
mysql> update test set i = i*10;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0 
一共就4条数据,这里却显示匹配了5条,
多出来的1条就像幻影一样
 
mysql> select * from test;
+----+
| i  |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set (0.00 sec) 
刚才还是4条数据,现在却变成了5条数据
 
mysql> commit ;
Query OK, 0 rows affected (0.00 sec) 
 

 

SERIALIZABLE(可串行化)

SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行,避免了前面说的幻读的问题。简单来说,这个隔离级别会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据一致性且可以接受没有并发的情况下,才考虑使用此级别;读取时相当于加了S锁、更新时加X锁;

mysql> set session tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| SERIALIZABLE           |
+------------------------+
1 row in set (0.00 sec) 
mysql> set session tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| SERIALIZABLE           |
+------------------------+
1 row in set (0.00 sec) 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from test;
+----+
| i  |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set (0.00 sec) 
 
 

mysql> update test set i = i+5 where i = 10;

阻塞...

mysql> commit;
Query OK, 0 rows affected (0.00 sec) 
 
 
Query OK, 1 row affected (11.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
修改成功,可以看到阻塞了11.02秒

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set i = i - 5 where i = 15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

 
 

mysql> select * from test;
+----+
| i |
+----+
| 15 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set (0.00 sec)

在autocommit为1且不显式开启事务的情况下,不会被左边 session 中的更新操作阻塞,

不会获取S锁;

 

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

超时了...

mysql> select * from test;

阻塞...

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

 
 

+----+
| i |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set (9.63 sec)

读到了...

posted @ 2016-03-24 01:54  hheedat  阅读(296)  评论(0编辑  收藏  举报