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