MySQL 事务处理级别不严格

Question

以前在做软件开发时（我使用的是 Java 进行软件开发），数据库一直用 MySQL。但是一直没有使用 MySQL 的事务处理。
这两天学习了一下。不过，MySQL 的事务处理级别，似乎并不严格。
Java 中有这么几种事务级别：
1： TRANSACTION_READ_UNCOMMITTED  在这个事务级别下，可以发生脏度，不可重读，虚读
2： TRANSACTION_READ_COMMITTED    在这个事务界别下，不能发生脏度，但允许不可重读和虚读
3： TRANSACTION_REPEATABLE_READ   在这个事务界别下，不能发生脏度，和不可重读，但可以存在虚读
4： TRANSACTION_SERIALIZABLE      最高的事务级别，脏度，不可重读，虚读 都不能发生。UNCOMMITTED ，COMMITTED 和 SERIALIZABLE 都没有问题。
但是在 TRANSACTION_REPEATABLE_READ 这个事务级别下，就有一点让人不解。MySQL 对于这个事务级别的处理是“脏度，不可重读，和虚读都不会发生”以下是我对这个事务级别的测试逻辑：
1：与 MySQL 建立 Connection_1 采用 TRANSACTION_REPEATABLE_READ 事务级别
   与 MySQL 建立 Connection_2 同样采用 TRANSACTION_REPEATABLE_READ 事务级别
2：Connection_1 执行 SELECT * FROM atable 得到一个结果集
3: Connection_2 执行 INSERT INTO atable ... 想 atable 中添加一条数据
   Connection_2 执行 commit
4: Connection_1 再次执行 SELECT * FROM atable 此时，得到的结果集，与之前得到的完全一致，可见没有发生“虚读”从上面的结论，我认为 MySQL 并不区分不可重读和虚读。但是如果真是这样，TRANSACTION_REPEATABLE_READ 就可以防止虚读，那 MySQL 最好级别的事务用来做什么呢（测试中，能够看出，如果启用了 MySQL 最高级别的事务处理，则这些事务与其它事务的确是串行执行）。

Answer 1

楼上的测试有问题吧。
connection_2执行commit之后，connection_1马上就能得到它提交的结果啊。
1. 验证事务级别，均为
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)2. 连接1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select * from tt;
Empty set (0.00 sec)3. 接着，使用连接2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> insert into tt values(1);
Query OK, 1 row affected (0.00 sec)4. 接着，再在连接1中select
mysql> select * from tt;
Empty set (0.00 sec)
仍然为空
5. 接着，在连接中commit;
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
6. 5执行完之后，在连接1中select
mysql> select * from tt;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
它这个事务没结束之前，读到了幻像数据。
刚好与可重复读隔离级吻合。
如果事务隔离级设为可串行化，就读不到了。

Answer 2

楼上的 MySQL 的事务处理级别是 READ-COMMITTED ，在这个级别的事务处理中，是可以发生虚读的。
而我所说的 “事务级别” 是 “REPEATABLE_READ”

Answer 3

大版能给说一下 MySQL 的事务的处理级别，是怎样的一个机制么。如果它的“不可重读”和“虚读”为同一级别的事务处理，那么，它最高级别的事务处理又解决了怎样的问题呢？

Answer 4

Sorry，我看错了原文的意思。用读已提交测试，不妥。事务处理级别的支持还是有差异性的。尤其是对于幻像读。可重复读隔离级，不能避免纪像读的。
1. 连接1
mysql> set @@tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tt values(1);
Query OK, 1 row affected (0.00 sec)
2. 连接2
mysql>  set @@tx_isolation='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> begin
    -> ;
Query OK, 0 rows affected (0.00 sec)mysql> select * from tt where id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.02 sec)
已经有一条记录，这里它只会锁定这条记录
3. 连接 1，插入另一条记录(幻像记录), 并commit
mysql> insert into tt values(2);
Query OK, 1 row affected (0.00 sec)mysql> commit;
Query OK, 0 rows affected (0.02 sec)
(2)这条记录并不被连接2锁定
4. 连接2
mysql> select * from tt;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)读到了幻像数据。

Answer 5

理解的关键在于select * from t只是锁定当前选出来的那些行，还是包括未来的可能插入的那些行。mysql在这上面处理的方式不一样。

Answer 6

按 5 楼朋友的逻辑做了测试，但仍旧没有读到“幻象”。我用的是 MySQL 版本是 5.0.21-community-nt 不过我想由版本造成这个显现的可能性比较小啊。

Answer 7

对了 5 楼的朋友，你的“连接2”没有 set autocommit = 0

Answer 8

不会吧。你用的是InnoDB引擎吧
这应该是比较稳定不变的。

Answer 9

测试完了，的确是因为 5 楼的朋友没有进行 set autocommit = 0;的设置

Answer 10

谢提醒。:-)
嗯，重新试了一下，你的结论是正确的。可重复读在mysql中的表现似乎与可串行化基本一样。居然能避过幻像读。

Answer 11

其实这也可以理解，并不是所有数据库都按照上述的四个隔离级来实现的。没有实现的部分，可能用一个级别表示两类。
Oracle就没有严格按照上述级别来划分。SQL Server2008又引入了几个新的隔离级。也许我们查看一下SQL2008标准，可以看到最新标准要求支持几种隔离级。关键还在于实际的应用需要用到哪种隔离级。用到可串行化级别的应用，还是比较少的。毕竟，对性能是一个大考验。

Answer 12

貌似是这样~~但应该与 Serialize 有区别才对。MySQL 的研发是不会犯这样的错误的。
等高人指教

Answer 13

我觉得是两个概念，Oracle 只提供两个事务级别。
而 MySQL 提供了多个，且 REPEATABLE_READ 和 SERIALIZABLE 是两个不同的事务级别啊，但却效果一样（我能看到的，但不应该这样）。

Answer 14

还是有点区别的。
在于connection2中如果使用mysql> begin;
Query OK, 0 rows affected (0.00 sec)mysql> select * from tt where id < 2 for update;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)mysql> select * from tt;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)mysql> select * from tt;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)mysql>
这样它可以读到幻像数据3.参看原文：
REPEATABLE READ This is the default isolation level of InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE statements that use a unique index with a unique search condition lock only the index record found, not the gap before it. With other search conditions, these operations employ next-key locking, locking the index range scanned with next-key or gap locks, and block new insertions by other users. In consistent reads, there is an important difference from the previous isolation level: In this level, all consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 15.11.4, “Consistent Non-Locking Read”. SERIALIZABLE This level is like REPEATABLE READ, but all plain SELECT statements are implicitly converted to SELECT ... LOCK IN SHARE MODE. 

Answer 15

第一：我觉得这个不是“虚读”。因为两次的查询条件并不一样。
不同的就是，如果你没有使用 for update ，在最后这个的查询中，你仍然看不到另外事务所插入的数据。
第二：在 SERIALIZABLE 事务级别中，进行 16 楼的操作，且结果与 REPEATABLE READ 事务级别下是一样的。也就是说，16楼的现象并不是 SERIALIZABLE 与 REPEATABLE READ 的区别。
第三：
这段话似乎在 MySQL 中没有实现。如果我在 SERIALIZABLE 事务级别下进行了两次查询
1：select * from tt where id < 2 得到结果集1（然后另外的事务插入 id = 3 的记录）
2：select * from tt 得到结果集为2.
此时如果按照 这个文档所说。那么在 结果集 2 中应该“包含”id 为 3 的记录。但是结果并非如此。所以啊总结一下啊：
1：在 MySQL 中的 REPEATABLE READ 事务级别下，不会发生“脏度，不可重读，和虚读”
2：SERIALIZABLE 级别，（我认为应该是）为了保证当前查询或要修改的数据时“最新鲜”的。
呵呵，结贴了。感谢 iihero