MySQL Forums
Forum List  »  InnoDB

Repeatable Read update sees new value from another session
Posted by: Bill Okara
Date: October 17, 2019 04:23PM

Hi,
with MySQL
version: 5.6.36-82.0
version_compile_os: Linux

When in a REPEATABLE-READ session, in an UPDATE statement, the where clause seems to be able to see the new updated value of a column that's updated in another session. Is this expected or a bug?

e.g., in the following example, with a very simple Test_Isolation table

mysql> describe Test_Isolation;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| product | varchar(255) | NO | | NULL | |
| quantities | int(11) | NO | | NULL | |
+------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into Test_Isolation (product,quantities) values ('pencil',10);
Query OK, 1 row affected (0.00 sec)

Start a new session and a new transaction:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from Test_Isolation where id=1 and quantities=10;
+----+---------+------------+
| id | product | quantities |
+----+---------+------------+
| 1 | pencil | 10 |
+----+---------+------------+
1 row in set (0.00 sec)

// In another session, update the quantities to 11.
// mysql> update Test_Isolation set quantities='11' where id=1;
// Query OK, 1 row affected (0.00 sec)

// Tried to query for the updated 'quantities', still see the OLD value as expected.

mysql> select * from Test_Isolation where id=1 and quantities=11;
Empty set (0.00 sec)

mysql> select * from Test_Isolation where id=1 and quantities=10;
+----+---------+------------+
| id | product | quantities |
+----+---------+------------+
| 1 | pencil | 10 |
+----+---------+------------+
1 row in set (0.00 sec)

// BUT in the UPDATE WHERE clause, it sees the NEW value, not the OLD value

mysql> update Test_Isolation set quantities='20' where id=1 and quantities=10;
Query OK, 0 rows affected (0.00 sec)

mysql> update Test_Isolation set quantities='20' where id=1 and quantities=11;
Query OK, 1 row affected (0.00 sec)

*QUESTION*:

Is this expected behaviour or a bug? that is, why the new value is visible in the *UPDATE WHERE* clause but NOT in the *SELECT WHERE* clause as expected?

Options: ReplyQuote


Subject
Views
Written By
Posted
Repeatable Read update sees new value from another session
94
October 17, 2019 04:23PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.