| Bug #119801 | If two transactions concurrently execute updates on the same row, the transaction that performs the update later will fa | ||
|---|---|---|---|
| Submitted: | 28 Jan 13:20 | Modified: | 3 Mar 12:30 |
| Reporter: | nikki Zhang | Email Updates: | |
| Status: | Need Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.39 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 Jan 7:42]
Jean-François Gagné
I added a way to automatically test this, and show this behavior is like this for a long time.
In READ-COMMITTED, we see "after 2nd update" and "after 2nd commit" to be "0 37 2", while we would expect the 0 to have been updated to 10.
And even more weird (result in next comment because too long), in REPEATABLE-READ, we see a 3rd row in "after 2nd update", and this extra row disappearing after commit.
Thanks nikki Zhang for the bug report, and MySQL Verification Team for investigating this,
J-F Gagne
---
vs="5.6.41 5.7.31 8.0.45 8.4.8 9.6.0"
for v in $vs; do
dbdeployer deploy single $v &
done; wait
for l in "READ-COMMITTED" "REPEATABLE-READ"; do
for d in msb_*; do
echo; echo $d $l
grep -q "_5_[56]_" <<< "$d" && var="tx_isolation" || var="transaction_isolation"
./$d/use test <<< "
set global $var = '$l';
DROP TABLE IF EXISTS t;
CREATE TABLE t(x INT DEFAULT 0, c0 INT PRIMARY KEY, c1 INT);
INSERT INTO t VALUES (0, 1, 1), (0, 99, 2);"
# Below in a sub-shell to avoid polluting test result with shell job output.
( ./$d/use -N test <<< 'do sleep(1); BEGIN; do sleep(2); SELECT "before 2nd update", now(5), t.* FROM t;
UPDATE t SET x = x + 10; SELECT "after 2nd update", now(5), t.* FROM t; COMMIT;
SELECT "after 2nd commit", now(5), t.* FROM t; ' & )
./$d/use -N test <<< 'BEGIN; do sleep(2); SELECT "before 1st update", now(5), t.* FROM t; UPDATE t SET c0=37 WHERE c1=2;
SELECT "after 1st update", now(5), t.* FROM t; do sleep(2); COMMIT;'
done
done
msb_5_6_41 READ-COMMITTED
before 1st update 2026-01-30 18:18:24.11584 0 1 1
before 1st update 2026-01-30 18:18:24.11584 0 99 2
after 1st update 2026-01-30 18:18:24.11710 0 1 1
after 1st update 2026-01-30 18:18:24.11710 0 37 2
before 2nd update 2026-01-30 18:18:25.12053 0 1 1
before 2nd update 2026-01-30 18:18:25.12053 0 99 2
after 2nd update 2026-01-30 18:18:26.12221 10 1 1
after 2nd update 2026-01-30 18:18:26.12221 0 37 2
after 2nd commit 2026-01-30 18:18:26.12314 10 1 1
after 2nd commit 2026-01-30 18:18:26.12314 0 37 2
msb_5_7_31 READ-COMMITTED
before 1st update 2026-01-30 18:18:28.24124 0 1 1
before 1st update 2026-01-30 18:18:28.24124 0 99 2
after 1st update 2026-01-30 18:18:28.24275 0 1 1
after 1st update 2026-01-30 18:18:28.24275 0 37 2
before 2nd update 2026-01-30 18:18:29.24667 0 1 1
before 2nd update 2026-01-30 18:18:29.24667 0 99 2
after 2nd update 2026-01-30 18:18:30.24722 10 1 1
after 2nd update 2026-01-30 18:18:30.24722 0 37 2
after 2nd commit 2026-01-30 18:18:30.24844 10 1 1
after 2nd commit 2026-01-30 18:18:30.24844 0 37 2
msb_8_0_45 READ-COMMITTED
before 1st update 2026-01-30 18:18:32.32827 0 1 1
before 1st update 2026-01-30 18:18:32.32827 0 99 2
after 1st update 2026-01-30 18:18:32.32890 0 1 1
after 1st update 2026-01-30 18:18:32.32890 0 37 2
before 2nd update 2026-01-30 18:18:33.33271 0 1 1
before 2nd update 2026-01-30 18:18:33.33271 0 99 2
after 2nd update 2026-01-30 18:18:34.33435 10 1 1
after 2nd update 2026-01-30 18:18:34.33435 0 37 2
after 2nd commit 2026-01-30 18:18:34.33558 10 1 1
after 2nd commit 2026-01-30 18:18:34.33558 0 37 2
msb_8_4_8 READ-COMMITTED
before 1st update 2026-01-30 18:18:36.41435 0 1 1
before 1st update 2026-01-30 18:18:36.41435 0 99 2
after 1st update 2026-01-30 18:18:36.41527 0 1 1
after 1st update 2026-01-30 18:18:36.41527 0 37 2
before 2nd update 2026-01-30 18:18:37.41929 0 1 1
before 2nd update 2026-01-30 18:18:37.41929 0 99 2
after 2nd update 2026-01-30 18:18:38.41736 10 1 1
after 2nd update 2026-01-30 18:18:38.41736 0 37 2
after 2nd commit 2026-01-30 18:18:38.41833 10 1 1
after 2nd commit 2026-01-30 18:18:38.41833 0 37 2
msb_9_6_0 READ-COMMITTED
before 1st update 2026-01-30 18:18:40.49936 0 1 1
before 1st update 2026-01-30 18:18:40.49936 0 99 2
after 1st update 2026-01-30 18:18:40.50002 0 1 1
after 1st update 2026-01-30 18:18:40.50002 0 37 2
before 2nd update 2026-01-30 18:18:41.49939 0 1 1
before 2nd update 2026-01-30 18:18:41.49939 0 99 2
after 2nd update 2026-01-30 18:18:42.50165 10 1 1
after 2nd update 2026-01-30 18:18:42.50165 0 37 2
after 2nd commit 2026-01-30 18:18:42.50213 10 1 1
after 2nd commit 2026-01-30 18:18:42.50213 0 37 2
(to be continued in next comment)
[31 Jan 7:43]
Jean-François Gagné
(continuation of previous comment) msb_5_6_41 REPEATABLE-READ before 1st update 2026-01-30 18:18:48.70627 0 1 1 before 1st update 2026-01-30 18:18:48.70627 0 99 2 after 1st update 2026-01-30 18:18:48.70749 0 1 1 after 1st update 2026-01-30 18:18:48.70749 0 37 2 before 2nd update 2026-01-30 18:18:49.71108 0 1 1 before 2nd update 2026-01-30 18:18:49.71108 0 99 2 after 2nd update 2026-01-30 18:18:50.71346 10 1 1 after 2nd update 2026-01-30 18:18:50.71346 10 37 2 after 2nd update 2026-01-30 18:18:50.71346 0 99 2 after 2nd commit 2026-01-30 18:18:50.71434 10 1 1 after 2nd commit 2026-01-30 18:18:50.71434 10 37 2 msb_5_7_31 REPEATABLE-READ before 1st update 2026-01-30 18:18:52.82394 0 1 1 before 1st update 2026-01-30 18:18:52.82394 0 99 2 after 1st update 2026-01-30 18:18:52.82538 0 1 1 after 1st update 2026-01-30 18:18:52.82538 0 37 2 before 2nd update 2026-01-30 18:18:53.82690 0 1 1 before 2nd update 2026-01-30 18:18:53.82690 0 99 2 after 2nd update 2026-01-30 18:18:54.82798 10 1 1 after 2nd update 2026-01-30 18:18:54.82798 10 37 2 after 2nd update 2026-01-30 18:18:54.82798 0 99 2 after 2nd commit 2026-01-30 18:18:54.82883 10 1 1 after 2nd commit 2026-01-30 18:18:54.82883 10 37 2 msb_8_0_45 REPEATABLE-READ before 1st update 2026-01-30 18:18:56.89964 0 1 1 before 1st update 2026-01-30 18:18:56.89964 0 99 2 after 1st update 2026-01-30 18:18:56.90026 0 1 1 after 1st update 2026-01-30 18:18:56.90026 0 37 2 before 2nd update 2026-01-30 18:18:57.90513 0 1 1 before 2nd update 2026-01-30 18:18:57.90513 0 99 2 after 2nd update 2026-01-30 18:18:58.90682 10 1 1 after 2nd update 2026-01-30 18:18:58.90682 10 37 2 after 2nd update 2026-01-30 18:18:58.90682 0 99 2 after 2nd commit 2026-01-30 18:18:58.90774 10 1 1 after 2nd commit 2026-01-30 18:18:58.90774 10 37 2 msb_8_4_8 REPEATABLE-READ before 1st update 2026-01-30 18:19:00.97843 0 1 1 before 1st update 2026-01-30 18:19:00.97843 0 99 2 after 1st update 2026-01-30 18:19:00.97961 0 1 1 after 1st update 2026-01-30 18:19:00.97961 0 37 2 before 2nd update 2026-01-30 18:19:01.98311 0 1 1 before 2nd update 2026-01-30 18:19:01.98311 0 99 2 after 2nd update 2026-01-30 18:19:02.98630 10 1 1 after 2nd update 2026-01-30 18:19:02.98630 10 37 2 after 2nd update 2026-01-30 18:19:02.98630 0 99 2 after 2nd commit 2026-01-30 18:19:02.98726 10 1 1 after 2nd commit 2026-01-30 18:19:02.98726 10 37 2 msb_9_6_0 REPEATABLE-READ before 1st update 2026-01-30 18:19:05.05674 0 1 1 before 1st update 2026-01-30 18:19:05.05674 0 99 2 after 1st update 2026-01-30 18:19:05.05784 0 1 1 after 1st update 2026-01-30 18:19:05.05784 0 37 2 before 2nd update 2026-01-30 18:19:06.06175 0 1 1 before 2nd update 2026-01-30 18:19:06.06175 0 99 2 after 2nd update 2026-01-30 18:19:07.06419 10 1 1 after 2nd update 2026-01-30 18:19:07.06419 10 37 2 after 2nd update 2026-01-30 18:19:07.06419 0 99 2 after 2nd commit 2026-01-30 18:19:07.06497 10 1 1 after 2nd commit 2026-01-30 18:19:07.06497 10 37 2
[26 Feb 16:27]
Jean-François Gagné
Maybe related: Bug #119769 and Bug #119949.
[2 Mar 13:01]
Jakub Lopuszanski
https://dev.mysql.com/doc/refman/9.6/en/innodb-transaction-isolation-levels.html > READ COMMITTED > [...] > For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it. This applies here, because when the s1's `UPDATE mtest SET x = x + 10 WHERE True;` visits row with c0==37, which s2 is creating, it notices it is (implicitly) locked by the s2, converts that lock from implicit to explicit, notices that this s2's LOCK_X conflicts with the LOCK_X s1 would need to access and modify the row, and therefore triggers semi-consistent read, i.e. tries to see how the row with c0==37 looked like before s2 and if it matches the query. Turns out, before s2 there was no row with c0==37, so "obviously" it can't match the query. I think the documentation could be improved here, to provide an example inspired by yours, in which a row didn't even exist, and to clarify that in such case it doesn't match the WHERE clause. Other than that, I think the behaviour is as intended. I guess, this specific example might be additionally confusing to some, because it involves updating the value of the PRIMARY KEY, c0, which InnoDB handles as a combination of DELETE+INSERT, so from the point of view of InnoDB, row with c0=99 is being DELETED, and a new row with c0=37 is being INSERTed by s2. I realize this might be non-intuitive, as the user's intent was expressed as an UPDATE. But, if you think about it deeply, "the thingy that gets modified", i.e "the row" has to be *identified* somehow, and the PRIMARY KEY is the way we do it in InnoDB. So, if you modify the very thing which identifies a row, InnoDB has no good way of tracking that the thingy with c0=99 is "the same as" the thingy with c0=37 - if it had, it wouldn't be InnoDB anymore, but something else :/ For InnoDB if something has a different PRIMARY KEY then it is *by definition* not the same row. Anyway, I don't see anything we can do about this behaviour, other than document it. Also, please note, that using SERIALIZABLE or even REPEATABLE READ would give more intuitive results here, as they don't involve semi-consitent read, and s1 would block on the lock for c0=37.
[6 Mar 6:09]
Kaiming Zhang
Hi Jakub,
Thank you for the detailed explanation regarding the "DELETE + INSERT" mechanism and semi-consistent reads. I understand that updating a Primary Key creates a new row identity.
However, I have found a scenario where this logic seems inconsistent. If the behavior were solely dictated by "different PK = different row," the following case should also fail/skip, but it actually blocks and succeeds.
The Counter-Example:
CREATE TABLE mtest(x INT DEFAULT 0, c0 INT PRIMARY KEY, c1 INT);
INSERT INTO mtest VALUES (0, 1, 1), (0, 2 , 2);
-- Initial Table:
View{
1:[0, 1, 1]
2:[0, 2, 2]
}
CREATE TABLE mtest(x INT DEFAULT 0, c0 INT PRIMARY KEY, c1 INT);
INSERT INTO mtest VALUES (0, 1, 1), (0, 2 , 2);
-- Initial Table:
-- 1:[0, 1, 1]
-- 2:[0, 2, 2]
/* s1 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* s1 */ BEGIN;
/* s2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* s2 */ BEGIN;
/* s2 */ UPDATE mtest SET c0=37 WHERE c1=2;
/* s1 */ UPDATE mtest SET x = x + 10 WHERE True; -- (blocks/waits here)
/* s2 */ COMMIT;
/* s1 */ COMMIT;
mysql> SELECT * FROM mtest;
+------+----+------+
| x | c0 | c1 |
+------+----+------+
| 10 | 1 | 1 |
| 10 | 37 | 2 |
+------+----+------+
Result:
In this case, s1 blocks (waits) instead of using semi-consistent read to skip. After s2 commits, s1 successfully updates both rows.
Result: [(10, 1, 1), (10, 37, 2)].
My Concern:
In the previous case (99 -> 37), s1 effectively scanned the "New Row" (37) first, saw it didn't exist in the snapshot, and skipped it.
In this case (2 -> 37), s1 scans the "Old Row" (2) first, sees it exists, waits for the lock, and eventually updates it correctly.
This implies that the reliability of UPDATE statements in READ COMMITTED depends entirely on the physical scan order (whether we encounter the old PK or the new PK first).
If updating the Primary Key is officially supported (even if discouraged), shouldn't the behavior be deterministic regardless of whether the ID increases or decreases? The current behavior suggests that UPDATE operations are unsafe in RC isolation if PKs are mutable, specifically when the PK value decreases.
Could you confirm if this data-dependent inconsistency is also expected behavior?
[6 Mar 11:13]
Jakub Lopuszanski
Thanks for providing a clear example and your interpretation and doubts clearly. If you want a behaviour which is intuitive to humans and corresponds to some serial order of transactions taking place, then this is exactly what SERIALIZABLE is for. Also, the default isolation level, REPEATABLE READ, behaves very similar to SERIALIZABLE in case a transaction does just UPDATE/DELETE/INSERT statements, so if you used in your examples, the results would be reasonable, too. If you *volunteer* to change the isolation level to non-default READ COMMITTED, you need to understand you are sacrificing serializability, so you no longer should expect what you will see to be easily explainable as corresponding to some serial execution of transactions. All we can do is document what exactly gets sacrificed and what the behaviour is, but don't expect it to be similar to SERIALIZABLE as this was never the goal of such modes. The goal is to improve speed by compromising on semantics. The new example you show matches my expectations: when visiting the PK record with c0=2, which is currently being modified by s2, and thus implicitly x-locked by s2, InnoDB in READ COMMITTED first checks if s1 could avoid waiting for a lock, by doing semi-consistent read to see if the row even matches the WHERE clause. That is, it temporarily ignores the lock, and peeks the previous content of the row, i.e. before the current owner (s2) started, to see if it that older version matches the WHERE clause. Your WHERE clause is trivial: "WHERE True". As we saw in your previous example it may still happen it doesn't match, simply because the row was completely missing. But in your new example, the row with c0=2 wasn't missing before s2. So, given that the row existed before s2, "WHERE True" matches, and the trick to skip this row, and move on, doesn't work. So, InnoDB forces s1 to wait for s2 to decide to COMMIT (in which case the record vanishes and thus no longer matches the query) or ROLLBACK (in which case the record re-appears and matches the query and gets modfied by s1). Yes, this example shows that it matters which direction you scan, what's the actual timing of events etc. But, this is to be expected in READ COMMITTED - not taking the gap locks, means that phantom rows can appear on the other side of the cursor, and using semi-consistent trickery means that your results are, well, semi-consistent. One justification for even having this semi-consistent read behaviour is that it is in some sense equivalent to pretending that s1 got to see the row faster than s2 got to modify it, which is kinda-ok-ish if you already lowered your expectations about serializability to the point you permit phantom reads and races between transactions. We try to be frank about it in the documentation, so let me know if you found some part of https://dev.mysql.com/doc/refman/9.6/en/innodb-transaction-isolation-levels.html or https://dev.mysql.com/doc/refman/9.6/en/innodb-locking.html misleading. I would love to understand why you are using READ COMMITTED at all, given you care about consistent behaviour? What problem are you trying to solve? Maybe I'll be able to propose a solution
Description: Under the Read Committed isolation level, if two transactions concurrently execute updates on the same row, the transaction that performs the update later will fail in its attempt to modify that specific row. DROP TABLE IF EXISTS mtest; CREATE TABLE mtest(x INT DEFAULT 0, c0 INT PRIMARY KEY, c1 INT); INSERT INTO mtest VALUES (0, 1, 1), (0, 99, 2); -- Initial Table: View{ 1:[0, 1, 1] 2:[0, 99, 2] } /* s1 */SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; /* s1 */BEGIN; /* s2 */SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; /* s2 */BEGIN; /* s2 */UPDATE mtest SET c0=37 WHERE c1=2; /* s1 */UPDATE mtest SET x = x + 10 WHERE True;（block） /* s2 */COMMIT; /* s1 */SELECT * FROM mtest; -- Actual result: [(10, 1, 1), (0, 37, 2)] -- Expected result: [(10, 1, 1), (10, 37, 2)] -- Both rows should have x increased by 10 /* s1 */COMMIT; mysql> SELECT * FROM mtest; +------+----+------+ | x | c0 | c1 | +------+----+------+ | 10 | 1 | 1 | | 0 | 37 | 2 | +------+----+------+ How to repeat: DROP TABLE IF EXISTS mtest; CREATE TABLE mtest(x INT DEFAULT 0, c0 INT PRIMARY KEY, c1 INT); INSERT INTO mtest VALUES (0, 1, 1), (0, 99, 2); -- Initial Table: View{ 1:[0, 1, 1] 2:[0, 99, 2] } /* s1 */SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; /* s1 */BEGIN; /* s2 */SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; /* s2 */BEGIN; /* s2 */UPDATE mtest SET c0=37 WHERE c1=2; /* s1 */UPDATE mtest SET x = x + 10 WHERE True;（block） /* s2 */COMMIT; /* s1 */SELECT * FROM mtest; -- Actual result: [(10, 1, 1), (0, 37, 2)] -- Expected result: [(10, 1, 1), (10, 37, 2)] -- Both rows should have x increased by 10 /* s1 */COMMIT; mysql> SELECT * FROM mtest; +------+----+------+ | x | c0 | c1 | +------+----+------+ | 10 | 1 | 1 | | 0 | 37 | 2 | +------+----+------+