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

最新推荐文章于 2025-05-10 07:45:00 发布

最新推荐文章于 2025-05-10 07:45:00 发布 · 162 阅读

文章标签：

#MySQL #HTML #工作

mysql 专栏收录该内容

1 篇文章

订阅专栏

本文通过一个MySQL的repeatableread事务隔离级别的测试案例，详细展示了在不同客户端中执行SQL语句时，如何受到事务隔离级别影响的具体表现，并提出了两种解决办法。

刚好做了一个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]