By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,257 Members | 925 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,257 IT Pros & Developers. It's quick & easy.

Repeateable read isolation level question

P: n/a
I think I understand the concept of repeatable read, but this scenario
doesn't make sense to me, if someone can explain.

Say you have an innodb table test with a string column s = 'blank' and
are using the default isolation level of repeatable read.

Session 1:
begin;

Session 2:
begin;

S1:
update test set s = 'session one' where id = 1;
commit;

S2:
mysql> select * from test;
+-------+------+
| s | id |
+-------+------+
| blank | 1 |
+-------+------+
1 row in set (0.00 sec)

(so we cannot see the changes from S1's transaction yet, only when S2
commits or rolls back will it see the changes)

mysql> update test set s = 'session two' where s = 'blank' and id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> update test set s = 'session two' where s = 'session one' and
id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

So this is very weird to me, that the first update fails. You cannot
see the updates from session 1 in a select due to the isolation level,
yet your where clause sees the updates...???

Mind you, this is good since it avoids lost updates, but I just don't
understand it--it seems inconsistent.

Thanks,
Chris
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.