MySQL Bugs: #119769: The transaction reads the value inserted by the blocked statement

Bug #119769	The transaction reads the value inserted by the blocked statement
Submitted:	24 Jan 14:46	Modified:	8 Mar 15:48
Reporter:	Rose Yang	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	9.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	block, transaction

Description:
Under READ UNCOMMITTED isolation level, the transaction reads the value inserted by the blocked statement.

How to repeat:
/* init */ CREATE TABLE t (c1 INT, c2 INT UNIQUE);

/* t1 */ BEGIN;
/* t2 */ BEGIN;
/* t1 */ INSERT INTO t (c1,c2) VALUE (1,-5);
/* t2 */ REPLACE t (c1,c2) VALUE (2,-5);  -- block
/* t1 */ SELECT * FROM t;  -- [(1, -5), (2, -5)]
/* t1 */ COMMIT;  -- t2 unblock
/* t2 */ COMMIT;

Indeed, it looks like the REPLACE is not atomic.  Reproduction script below for 8.0.45, 8.4.8 and 9.6.0.  I would expect the SELECT 'during replace' to return the same rows as 'before replace' and not show an "intermediary result" of the execution of the REPLACE.

for d in msb_*; do
   echo; echo $d
  ./$d/use -N test <<< "drop table if exists t; CREATE TABLE t (c1 INT, c2 INT UNIQUE); select version(); set global transaction_isolation = 'READ-UNCOMMITTED'"

  # 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); REPLACE t VALUE (2,-5);
                          SELECT 'after replace', now(5), t.* FROM t; commit;"& )

  # Because 'during replace' is followed by an immediate commit, it unblocks REPLACE.
  ./$d/use -N test <<< "BEGIN; do sleep(2); INSERT INTO t VALUE (1,-5); SELECT 'before replace', now(5), t.* FROM t;
                        do sleep(2); SELECT 'during replace', now(5), t.* FROM t; commit;"
done

msb_8_0_45
8.0.45
before replace	2026-01-26 13:20:01.13157	1	-5
during replace	2026-01-26 13:20:03.13392	1	-5
during replace	2026-01-26 13:20:03.13392	2	-5
after replace	2026-01-26 13:20:03.13541	2	-5

msb_8_4_8
8.4.8
before replace	2026-01-26 13:20:05.19546	1	-5
during replace	2026-01-26 13:20:07.19981	1	-5
during replace	2026-01-26 13:20:07.19981	2	-5
after replace	2026-01-26 13:20:07.20149	2	-5

msb_9_6_0
9.6.0
before replace	2026-01-26 13:20:09.25998	1	-5
during replace	2026-01-26 13:20:11.26402	1	-5
during replace	2026-01-26 13:20:11.26402	2	-5
after replace	2026-01-26 13:20:11.26558	2	-5

Maybe related: Bug #119801 and Bug #119949.

READ UNCOMMITTED mode lets you read the very dirty state, and there's no physical way to modify two B-trees exactly at the same moment, so if the conflict is on a secondary index, and detected after already inserting a record to the primary index, then yes, for a moment the two indexes aren't consistent, but this is precisely why InnoDB uses locks, and transaction isolation levels, which R.U. bypasses.
Which part of the documentation gave you have such high expectations for READ UNCOMMITTED?

The REPLACE statement should delete (1, -5) first, then insert (2, -5). When it attempts to delete (1, -5), it should be blocked. At this point, the database should not contain (2, -5) yet. Having both (1, -5) and (2, -5) in the database simultaneously violates the UNIQUE constraint on c2. When t1's SELECT statement  runs while t2's REPLACE statement  is blocked, it should return only (1, -5), not both (1, -5) and (2, -5).

"at this point" is a language which makes sense for SERIALIZABLE, yet you use READ UNCOMMITTED, and still expect the results to be intuitively understood in terms of serialized timeline. Why?

When using READ UNCOMMITTED you will often see various violations of UNIQUENESS constraint - why wouldn't you? There are no locks being used by the select, so of course the results can be inconsistent. You don't even need contrived examples with REPLACE - just have transactions INSERT and DELETE rows in parallel to R.U. SELECT.

Again: which part of the documentation gave you impression that R.U. should present a consistent snapshot?