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
-
-
-
- Stalled
-
(2 relates to)
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Isolation Level: Read Uncommitted.
The behaviors of UPDATE for different rows are inconsistent. Just some of the rows are not updated while others are. {code:sql} /* 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 | +------+------+ {code} 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. |
Isolation Level: Read Uncommitted.
The behaviors of UPDATE for different rows are inconsistent. Just some of the rows are not updated while others are. {code:sql} /* 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 | +------+------+ {code} 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: {code:sql} +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | null | null | | null | 3 | | 4 | null | +------+------+ {code} then, the result will be {code:sql} +------+------+ | a | b | +------+------+ | 10 | 20 | | 10 | 20 | | 10 | 20 | | 10 | 20 | | 10 | 20 | +------+------+ {code} 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. |
| Description |
Isolation Level: Read Uncommitted.
The behaviors of UPDATE for different rows are inconsistent. Just some of the rows are not updated while others are. {code:sql} /* 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 | +------+------+ {code} 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: {code:sql} +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | null | null | | null | 3 | | 4 | null | +------+------+ {code} then, the result will be {code:sql} +------+------+ | a | b | +------+------+ | 10 | 20 | | 10 | 20 | | 10 | 20 | | 10 | 20 | | 10 | 20 | +------+------+ {code} 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. |
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. {code:sql} /* 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 | +------+------+ {code} 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: {code:sql} +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | null | null | | null | 3 | | 4 | null | +------+------+ {code} then, the result will be {code:sql} +------+------+ | a | b | +------+------+ | 10 | 20 | | 10 | 20 | | 10 | 20 | | 10 | 20 | | 10 | 20 | +------+------+ {code} 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. |
| Summary | Inconsistent behaviors of UPDATE under Read Uncommitted | Inconsistent behaviors of UPDATE under RU & RC isolation level |
| Labels | innodb need_feedback | innodb |
| Epic/Theme | server |
| Link |
This issue relates to |
| Assignee | Marko Mäkelä [ marko ] |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] |
| Link |
This issue relates to |
| Workflow | MariaDB v3 [ 125162 ] | MariaDB v4 [ 143182 ] |
| Assignee | Marko Mäkelä [ marko ] | Vladislav Lesin [ vlad.lesin ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Fix Version/s | N/A [ 14700 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Resolution | Not a Bug [ 6 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Assignee | Vladislav Lesin [ vlad.lesin ] | Ian Gilfillan [ greenman ] |
| Resolution | Not a Bug [ 6 ] | |
| Status | Closed [ 6 ] | Stalled [ 10000 ] |
| Component/s | Documentation [ 10903 ] | |
| Component/s | Locking [ 10900 ] |
| Status | Stalled [ 10000 ] | Open [ 1 ] |
| Link |
This issue relates to |
| Assignee | Ian Gilfillan [ greenman ] | Marko Mäkelä [ marko ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Assignee | Marko Mäkelä [ marko ] | Vladislav Lesin [ vlad.lesin ] |
| Fix Version/s | 10.6.18 [ 29627 ] | |
| Fix Version/s | 10.11.8 [ 29630 ] | |
| Fix Version/s | 11.0.6 [ 29628 ] | |
| Fix Version/s | 11.1.5 [ 29629 ] | |
| Fix Version/s | 11.2.4 [ 29631 ] | |
| Fix Version/s | 11.4.2 [ 29633 ] | |
| Fix Version/s | N/A [ 14700 ] | |
| Assignee | Vladislav Lesin [ vlad.lesin ] | Marko Mäkelä [ marko ] |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link |
This issue blocks |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-35140 [ MDEV-35140 ] |