Bug #119769 The transaction reads the value inserted by the blocked statement

• Submitted: 24 Jan 14:46
• Reporter: Rui Yang
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any
• Tags: block, transaction

[24 Jan 14:46] Rui Yang

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;

[26 Jan 18:21] Jean-François Gagné

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