Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.12
-
OS: Ubuntu-20.04
Description
Isolation Level: Read Uncommitted & Read Committed.
The behaviors of UPDATE for different rows are inconsistent. Just some of the rows are not updated while others are.
/* init */ drop table if exists t; |
/* init */ create table t(a int, b int); |
/* init */ insert into t values(null, 1), (2, 2), (null, null), (null, 3), (4, null); |
|
|
/* s1 */ begin; |
/* s1 */ update t set a = 10 where 1; |
/* s2 */ begin; |
/* s2 */ update t set b = 20 where a; -- blocked |
/* s1 */ commit; -- s2 unblocked |
/* s2 */ commit; |
|
|
select * from t; |
|
|
+------+------+ |
| a | b |
|
+------+------+ |
| 10 | 1 |
|
| 10 | 20 |
|
| 10 | 20 |
|
| 10 | 20 |
|
| 10 | 20 |
|
+------+------+ |
The field b of the first row is not updated but other rows are updated.
After several attempts, I found that the results depend on the distribution of the data in the table rather than the timing.
If the initial table is like:
+------+------+ |
| a | b |
|
+------+------+ |
| 1 | 1 |
|
| 2 | 2 |
|
| null | null | |
| null | 3 | |
| 4 | null | |
+------+------+ |
then, the result will be
+------+------+ |
| a | b |
|
+------+------+ |
| 10 | 20 |
|
| 10 | 20 |
|
| 10 | 20 |
|
| 10 | 20 |
|
| 10 | 20 |
|
+------+------+ |
More specifically, if there exists a row whose column `a` is not NULL, and it is the first record from top to bottom whose column `a` is not NULL. Then, all rows before it with a NULL column `a` will not be updated, while all rows after it with a NULL column `a` will be updated. This means that even the execution of a single statement is not atomic.
This bug can be reproduced under Read Uncommitted and Read Committed.
Attachments
Issue Links
- blocks
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-35124 Set innodb_snapshot_isolation=ON by default
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- In Progress
-