Bug #111153 Confusing update skipping when two transaction update a same record
Submitted: 25 May 2023 13:01 Modified: 25 May 2023 14:21
Reporter: Siyang Weng Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: repeatable read, transaction isolation

[25 May 2023 13:01] Siyang Weng
Description:
When two tranasction update a same record, the second update will be skipped if it's in repeatable read isolation.

How to repeat:
initialize

create table t1 (a int, b int);
insert into t1 values(1,1);
insert into t1 values(1,2);

session1 > begin; -- no isolation level requirement for this session
session1 > update t1 set a=2 where b=1;
session2 > begin; -- this session is in repeatable read
session2 > select * from t1; -- gets (1,2)(1,1)
session2 > update t1 set b=3 where a=1; -- blocked util session1 commit
session1 > commit;
session2 > select * from t1; -- gets (1,3)(1,1)

Suggested fix:
It's confusing since (1,3) is the version after session2's update; and (1,1) is the initial version without session2's update.

Maybe a warning should be added when session2 executes update, mentions that some records in the select view will not be updated.
[25 May 2023 13:18] MySQL Verification Team
Hi Mr. Weng,

Thank for your bug report.

However, what you report is not a bug.

With REPEATABLE READ, each transaction sees only the versions of all tables and its rows as they have been after the BEGIN of the transaction. As that transaction proceeds, it sees only the rows before its BEGIN, plus the changes in the same transaction. It does NOT see any changes in other committed transactions. To have that behaviour, you should use READ COMMITTED multi-version-concurrency model.

Please read our entire Reference Manual for all explanations.

Not a bug.
[25 May 2023 13:47] Siyang Weng
Thank you for your reply!

But the behaviour in my case is not the same as the behaviour in your reply.

The reply said:"As that transaction proceeds, it sees only the rows before its BEGIN, plus the changes in the same transaction." In my case, the first select in session2 sees (1,2) and (1,1), then the update sql should update TWO rows because they all meet the predicate "a = 1". However, only the first row is updated.

As Doc[1] said:"If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction." The behaviour above may be expected, but I think it's still confusing for a user who does not see session1.

[1]https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
[25 May 2023 14:07] MySQL Verification Team
Expected behaviour.

You are not using 8.0.33, so you do not see things correctly.

We have done the commands in the same order.

This is session 1:

---------------------------

mysql> create table t1 (a int, b int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.07 sec)

mysql> insert into t1 values(1,2);
Query OK, 1 row affected (0.00 sec)

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

mysql> update t1 set a=2 where b=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    2 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.02 sec)

---------------------------

This is session 2:

---------------------------

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

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> update t1 set b=3 where a=1;
Query OK, 1 row affected (15.27 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    3 |
+------+------+
2 rows in set (0.00 sec)

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

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    2 |    1 |
|    1 |    3 |
+------+------+
2 rows in set (0.00 sec)
---------------------------

It is everything perfect, since both of your transactions are in the REPEATABLE READ mode.

We followed the order of your commands from  your test case to the last point. We just added two queries after commit , so that you see how fine our server works.

Not a bug.
[25 May 2023 14:21] Siyang Weng
Got it.

Thanks a lot!
[25 May 2023 14:53] Jakub Lopuszanski
FWIW in cases like this you can use SELECT...FOR SHARE.
Mixing a non-locking SELECT (i.e. one which doesn't have FOR SHARE, nor FOR UPDATE) with UPDATEs/DELETEs/INSERTs in a single REPEATABLE READ transaction is rarely a correct thing to do.
REPEATABLE READ is mostly useful for doing read-only transactions (in which case it behaves in well defined way, by simply doing, well, repeatable reads), or write-only transactions (in which case it also behaves in a well behaved way, essentially reusing logic and behaviour of SERIALIZABLE).  
But when you mix the two kinds of statements, the SELECTs will try use a read-view (+own changes), while writes will try to lock and use most recently commited versions of rows.
Nothing good can come up from this.

Changing this behaviour, to for example emit a warning when RR transaction is editing a row which has a header with TRX_ID not seen by its read-view, or perhaps even reporting a DEADLOCK in such a case is an interesting idea (I believe Postgres does something like that), but looks more like a feature request than a bug fix to me.