Description:
During repeatable-read transaction there may be a strange anomaly, when user sees double number of rows. Such an anomaly is not documented.
How to repeat:
conn1> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
conn1> BEGIN;
conn2> BEGIN;
conn1> select * from cities;
+----+----------+
| id | name |
+----+----------+
| 2 | New York |
| 3 | Tokyo |
+----+----------+
2 rows in set (0.00 sec)
conn2> select * from cities;
+----+----------+
| id | name |
+----+----------+
| 2 | New York |
| 3 | Tokyo |
+----+----------+
2 rows in set (0.00 sec)
conn1> UPDATE cities SET id=id+10;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
conn2> UPDATE cities SET id=id+20;
................****WAITS FOR LOCK****
conn1> COMMIT;
conn2>
Query OK, 2 rows affected (33.09 sec)
Rows matched: 2 Changed: 2 Warnings: 0
conn2> SELECT * FROM cities;
+----+----------+
| id | name |
+----+----------+
| 2 | New York |
| 3 | Tokyo |
| 32 | New York |
| 33 | Tokyo |
+----+----------+
4 rows in set (0.00 sec)
