469,167 Members | 1,150 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Repeateable read isolation level question

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
0 1621

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by francis70 | last post: by
11 posts views Thread by Markus Breuer | last post: by
8 posts views Thread by Gregor Kovač | last post: by
5 posts views Thread by m0002a | last post: by
5 posts views Thread by william.david.anderson | last post: by
3 posts views Thread by RG | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.