| Bug #111215 | Query in transaction may return rows with same unique key column value | ||
|---|---|---|---|
| Submitted: | 31 May 2023 9:58 | Modified: | 2 Jun 2023 4:32 |
| Reporter: | crazy cs | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 May 2023 12:30]
MySQL Verification Team
Hi Mr. crazy cs, Thank you for your bug report. This bug is actually a duplicate of the following bug: https://bugs.mysql.com/bug.php?id=100328 This bug is a serious one and is still not fixed. Your report is another aspect of the same bug that we have not encountered so far .... However, we have repeated the behaviour and that is one part of your test case that was not known yet, up to now. That is why we have verified this report as a bug. We used the latest release of 8.0. Continuation of the session 2 shows the reason for this behaviour, which is still buggy: mysql> select * from t; +------+------+ | c1 | c2 | +------+------+ | a | 1 | | a | 2 | +------+------+ 2 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from t; +------+------+ | c1 | c2 | +------+------+ | b | 1 | | a | 2 | +------+------+ 2 rows in set (0.00 sec) Verified.
[2 Jun 2023 4:32]
crazy cs
```sql
/* t1 */ drop table if exists t;
/* t1 */ create table t (c1 varchar(10), c2 int, unique key idx(c1));
/* t1 */ insert into t values ('a', 1);
/* t1 */ begin;
/* t1 */ update t set c1='b' where c1='a';
/* t2 */ begin;
/* t2 */ select * from t;
/* t2 */ insert into t values ('a', 2);
/* t1 */ commit;
/* t2 */ select * from t;
```
So what is the expect result of the last query?
Is there any documentation or MySQL source code that gives me a deeper understanding of the behavior of queries within a transaction?
[2 Jun 2023 4:32]
crazy cs
```sql
/* t1 */ drop table if exists t;
/* t1 */ create table t (c1 varchar(10), c2 int, unique key idx(c1));
/* t1 */ insert into t values ('a', 1);
/* t1 */ begin;
/* t1 */ update t set c1='b' where c1='a';
/* t2 */ begin;
/* t2 */ select * from t;
/* t2 */ insert into t values ('a', 2);
/* t1 */ commit;
/* t2 */ select * from t;
```
So what is the expect result of the last query?
Is there any documentation or MySQL source code that gives me a deeper understanding of the behavior of queries within a transaction?
[2 Jun 2023 11:53]
MySQL Verification Team
Hi, Read the entire contents of the bug: https://bugs.mysql.com/bug.php?id=100328 Documentation for the source code is strictly internal. Anyway, this is already documented: https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
Description: Query in transaction may return rows with the same unique key column value. How to repeat: Minimal reproduce step, use 2 connections to execute SQL for t1(transaction 1) and t2(transaction 2) sequentially. ```sql /* t1 */ drop table if exists t; /* t1 */ create table t (c1 varchar(10), c2 int, unique key idx(c1)); /* t1 */ insert into t values ('a', 1); /* t1 */ begin; /* t1 */ update t set c1='b' where c1='a'; /* t2 */ begin; /* t2 */ select * from t; /* t2 */ insert into t values ('a', 2); /* t1 */ commit; /* t2 */ select * from t; ``` The last query result is: ``` +----+----+ | c1 | c2 | +----+----+ | a | 1 | | a | 2 | +----+----+ ``` Since column c1 is a unique index, the query result breaks the unique index constraint.