MySQL Bugs: #102676: In REPEATABLE READ, the behavior after executing select for update is strange.

Bug #102676	In REPEATABLE READ, the behavior after executing select for update is strange.
Submitted:	21 Feb 2021 18:29	Modified:	22 Feb 2021 21:03
Reporter:	jou jou	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.7.12, 8.0.11	OS:	Linux (RDS of AWS)
Assigned to:		CPU Architecture:	Any

Description:
isolation level is repeatable-read.

I tried the following:

[case1]
mysql tx1 > use db1;
mysql tx2 > use db1;
mysql tx1 > select val from t1 where id = 1;
+-----+
| val |
+-----+
|  1  |
+-----+
mysql tx1 > begin;
mysql tx2 > begin;
mysql tx1 > select val from t1 where id = 1 for update;
+-----+
| val |
+-----+
|  1  |
+-----+
mysql tx2 > select val from t1 where id = 1 for update;
*waiting*
mysql tx1 > update t1 set val = 2 where id = 1;
mysql tx1 > commit;
mysql tx2 > result of *waiting* is
+-----+
| val |
+-----+
|  2  |
+-----+
mysql tx2 > select val from t1 where id = 1;
+-----+
| val |
+-----+
|  2  |
+-----+

maybe this is correct.

Then I tried the following:

[case2]
mysql tx1 > use db1;
mysql tx2 > use db1;
mysql tx1 > select val from t1 where id = 1;
+-----+
| val |
+-----+
|  1  |
+-----+
mysql tx1 > begin;
mysql tx2 > begin;
mysql tx2 > select count(*) from t1;
+----------+
| count(*) |
+----------+
|    1     |
+----------+
mysql tx1 > select val from t1 where id = 1 for update;
+-----+
| val |
+-----+
|  1  |
+-----+
mysql tx2 > select val from t1 where id = 1 for update;
*waiting*
mysql tx1 > update t1 set val = 2 where id = 1;
mysql tx1 > commit;
mysql tx2 > result of *waiting* is
+-----+
| val |
+-----+
|  2  |
+-----+
mysql tx2 > select val from t1 where id = 1;
+-----+
| val |
+-----+
|  1  |
+-----+

Why is the result 1 of last SQL?
I don't think this is a MySQL spec.

How to repeat:
Please try the steps of Description.

Hi ふじもと-san,
I'm from Japan MySQL User Group(I'm not Oracle person)

MySQL Documentation describes,

> A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.4 Locking Reads https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

"latest available data" means "like a READ-COMMITTED even if transaction_isolation is REPEATABLE-READ", this is known behavior.

/* from Japan MySQL User Group */

tanaka-san

Thank you for your reply.

I saw that page.
I understood it.

The difference between case1 and case2 I wrote above is,

In case2, there is:
mysql tx2 > select count(*) from t1;

But the final result was different.

Do you know the reason for this?

Fujimoto-san,

This is just my opinion from my experiment, (this behavior is bug or not would be decided by MySQL Verification Team.)

mysql tx2 > result of *waiting* is -- by trx1> select val from t1 where id = 1 for update;
+-----+
| val |
+-----+
|  2  |
+-----+

This is blocking-read (because, you use FOR UPDATE) and reads "latest available data" but,

mysql tx2 > select val from t1 where id = 1;
+-----+
| val |
+-----+
|  1  |
+-----+

This statement is NOT blocking read and reads depending on TRANSACTION ISOLATION LEVEL.

I agree these behaviors are strange, not intuitive, and doesn't have transaction consistency, but MySQL treats like this from the old days.

Fujimoto-san,

This is just my opinion from my experiment, (this behavior is bug or not would be decided by MySQL Verification Team.)

mysql tx2 > result of *waiting* is
+-----+
| val |
+-----+
|  2  |
+-----+

First statement, this is blocking-read (because, you use FOR UPDATE) and reads "latest available data" but,

mysql tx2 > select val from t1 where id = 1;
+-----+
| val |
+-----+
|  1  |
+-----+

Second statement is NOT blocking read and reads depending on TRANSACTION ISOLATION LEVEL.

I agree these behaviors are strange, not intuitive, and doesn't have transaction consistency, but MySQL treats like this from the old days.

tanaka-san

Thank you very much.

>MySQL treats like this from the old days.
I was a little relieved to hear this.

I was really worried about unexplained behaviors.

I found a similar behaviors, so I'll share it.

[case1]
mysql tx1 > use db1;
mysql tx2 > use db1;
mysql tx1 > select count(*) from t1 where id = 1;
+----------+
| count(*) |
+----------+
|    1     |
+----------+
mysql tx1 > begin;
mysql tx2 > begin;
mysql tx1 > insert into t1 (id, val) values (1, 123);
mysql tx1 > commit;
mysql tx2 > select count(*) from t1 where id = 1;
+----------+
| count(*) |
+----------+
|    2     |
+----------+

This is right.

[case2]
mysql tx1 > use db1;
mysql tx2 > use db1;
mysql tx1 > select count(*) from t1 where id = 1;
+----------+
| count(*) |
+----------+
|    1     |
+----------+
mysql tx1 > begin;
mysql tx2 > begin;
mysql tx2 > select count(*) from t2;
mysql tx1 > insert into t1 (id, val) values (1, 123);
mysql tx1 > commit;
mysql tx2 > select count(*) from t1 where id = 1;
+----------+
| count(*) |
+----------+
|    1     |
+----------+

This is strange.

The difference between case1 and case2 I wrote above is,
In case2, there is:
mysql tx2 > select count(*) from t2;

I am grateful for your quick reply.

Hi Mr. jo,

Thank you very much for your bug report.

However, this is not a bug.

Mr. Tanaka is quite right in his analysis. Let me add, that in all MVCC engines, the current view must be respected, regardless of what other sessions are doing. This is a reason why we have lock waits, mutex waits, and all kind of locking, like S, SX etc .....

In your case, count is different, because the other row is not visible to other sessions.

Not a bug.

@MySQL Verification Team

> that in all MVCC engines, the current view must be respected,

Yes.
So why "locking-read statement reads latest available data?", why "locking-read statement is not honest its transaction isolation level?"

I think this report indicates this point.
(My explanation, "That is MySQL way", does not explain the reason why at all)

@MySQL Verification Team
tanaka-san

Thank you for confirmation.
I understood it.