mysql的repeatable read事务隔离级别的测试

刚好做了一个mysql的repeatable read事务隔离级别的测试，帖出来大家看看:
[code]
准备工作:
CREATE TABLE `t` (
`id` int(5) NOT NULL default '0',
`name` varchar(255) default NULL,
`version` int(5) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

insert into t (id,name,version) values (1,'c1',1);


client1:
start transaction;

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

mysql> select * from t where id = 1;
+----+------+---------+
| id | name | version |
+----+------+---------+
| 1 | c1 | 1 |
+----+------+---------+
1 row in set (0.00 sec)


client2:
start transaction;
mysql> select * from t where id = 1;
+----+------+---------+
| id | name | version |
+----+------+---------+
| 1 | c1 | 1 |
+----+------+---------+
1 row in set (0.00 sec)


client1:

mysql> update t set name='c1_1',version=2 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t where id =1;
+----+------+---------+
| id | name | version |
+----+------+---------+
| 1 | c1_1 | 2 |
+----+------+---------+
1 row in set (0.00 sec)

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

client2:

//因为repeatable read,只看到client1提交前的数据
mysql> select * from t where id=1 and version=1;
+----+------+---------+
| id | name | version |
+----+------+---------+
| 1 | c1 | 1 |
+----+------+---------+
1 row in set (0.00 sec)


mysql> update t set name='c2',version=3 where id = 1 and version = 1;
//影响行数为0
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> update t set name='c2',version=3 where id = 1 and version = 2;
//影响行数为1,说明更新语句的执行是看到提交的数据的
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
总结:在mysql,repeatable read级别的事务,只保证看到的数据是一个镜像.
就算该行数据被别的事务修改,当该事务也对数据进行修改,在提交时也不会产生任何错误.
解决的办法:
1.在应用端使用乐观锁的机制
2.或者使用select .. for update或者SELECT ... LOCK IN SHARE MODE来对数据加.[/code]
相关知识见:
[url]http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html[/url]
[url]http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html[/url]