转自:http://nihongye.javaeye.com/blog/28827
- 准备工作:
- 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