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

准备工作:

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来对数据加.

相关知识见:

http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html

http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html

分享到：

2006-10-20 16:27

浏览 8341

评论

2 楼

medicine

2013-05-14

1 楼

atomicsun

2011-06-22

写的太好了 赞个，正需要这个测试 参考 ，多谢博主