Mysql数据库事务及隔离级别学习测试

参考了这篇文章的一些内容：

http://xm-king.iteye.com/blog/770721

 

记住以下这张表：

 

我在springdemo库里面建了一个表：

CREATE TABLE `tx` (
`id` bigint(11) NOT NULL auto_increment,
`num` bigint(11) default 0 COMMENT '用户名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='事务隔离级别测试表';

 

Mysql通过以下语句可以查询

SELECT @@tx_isolation;

SELECT @@global.tx_isolation; 

SELECT @@session.tx_isolation; 

默认的是 REPEATABLE-READ

可以通过以下方式修改隔离级别：

set tx_isolation='read-committed';

 

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> set tx_isolation='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)

 

但是，注意以上的改动，只能针对当前会话。

另起一个客户端，输入 

select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+

 

————————————————————————————————

以下是各种情况的测试：

READ-UNCOMMITTED

update tx set num=11 where id=1;
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

 

READ-COMMITTED

update tx set num=11 where id=1;
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

 

REPEATABLE-READ

A:

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

mysql> select * from tx;  (B更新前)
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from tx; （B更新后）
+----+------+
| id | num  |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

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

mysql> select * from tx;
+----+------+
| id | num |
+----+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+----+------+

可以看出，A的事务中，看不到B对数据的更新。

 

同样是 REPEATABLE-READ

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

mysql> select * from tx;
+----+------+
| id | num  |
+----+------+
|  1 |   10 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from tx;
+----+------+
| id | num  |
+----+------+
|  1 |   10 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

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

mysql> select * from tx;
+----+------+
| id | num |
+----+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set (0.00 sec)

另一个客户端，insert 了一条记录.

 

对于正常的repeatable-read级别，是有可能出现幻读的情况，也就是说，第二遍的时候，A能够读到新插入的数据。

但是，InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

所以我上面用InnoDB引擎创建的数据库，就没有出现幻读的情况。

 

SERIALIZABLE 

这里注意，只需要对使用事务Transaction的客户端设置SERIALIZABLE ，其他客户端的级别是什么都行，比如REPEATABLE-READ .

A

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

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

mysql> select * from tx;
+----+------+
| id | num  |
+----+------+
|  1 |   10 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+
5 rows in set (0.00 sec)

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

 

B

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> insert into tx values(5,5);
Query OK, 1 row affected (0.09 sec)

mysql> insert into tx values(6,6);

Query OK, 1 row affected (31.60 sec)

 

这里，有2点需要注意：

1. A没有使用(select)表tx的时候，B仍然能够向tx表中插入数据；

2. A使用(select)表tx之后，B再进行insert操作，就会hang住，直到A表transaction结束。所以可以看到B的insert操作耗时31秒。当然了，也可能超时失败。

比如：

mysql> insert into tx values(10,10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

以上这条语句，看起来是锁了整个表。但是其实深层次的目的，是为了保证事务的完整性，以及让A两次操作的REPEATABLE_READ;

比如 使用  select * from tx limit 1; 后面就仍然可以insert。因为不改变之前select的结果。

如果 select * from tx limit 10; 而最终只检出8条。这时候就不能insert，因为insert了的话，下次同样select得到的结果就不一样。

 

同理，如果select了某条记录，那么update同一条记录就不行，update其他的记录就可以。而且在事务中能够读到新更新的数据。

 

所以记住，事务的隔离级别的要求，只对事务过程中已经获取过的数据有关。跟没获取过、其他不可见的数据，无关。