Bug #119608 UPDATE is not blocked by SELECT ... FOR SHARE under READ COMMITTED isolation level

• Submitted: 30 Dec 2025 3:39
• Reporter: Siyang Weng
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 9.3.0
• OS: Any
• CPU Architecture: Any

[30 Dec 2025 3:39] Siyang Weng

Description:
Under the READ COMMITTED isolation level, an UPDATE statement is not blocked by a concurrent SELECT ... FOR SHARE that reads and locks the same rows.

According to InnoDB locking semantics, SELECT ... FOR SHARE is a locking read and should acquire shared (S) record locks. An UPDATE requires exclusive (X) locks on the same records, which are incompatible with S-locks. Therefore, the UPDATE should wait until the first transaction commits or rolls back.

However, in the following test case, the UPDATE proceeds without blocking, which appears to violate InnoDB’s locking guarantees.

How to repeat:
```SQL
DROP TABLE IF EXISTS tbl_1;
CREATE TABLE tbl_1 (
  col_1_1 TIMESTAMP,
  col_1_2 VARCHAR(100),
  col_1_3 MEDIUMINT,
  col_1_4 INT,
  col_1_5 DOUBLE,
  col_1_6 NUMERIC,
  pkid_1 INT,
  INDEX idx_1_0 (col_1_2, col_1_1, col_1_5, col_1_4),
  INDEX idx_1_1 (col_1_4, col_1_2, col_1_3, col_1_5),
  INDEX idx_1_2 (col_1_2, col_1_3, col_1_6),
  PRIMARY KEY (col_1_4, pkid_1)
) ENGINE=InnoDB;

INSERT INTO tbl_1 VALUES
( '2025-12-19 16:46:56', 'str_002', 2, 3, 1, 1, 0),
( '2025-12-19 16:46:56', 'str_002', 2, 3, 2, 1, 1),
( '2025-12-19 16:46:57', 'str_002', 1, 3, 2, 1, 2),
( '2025-12-19 16:46:56', 'str_002', 1, 3, 2, 1, 3),
( '2025-12-19 16:46:57', 'str_002', 1, 3, 2, 1, 4);

-- SESSION 1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT col_1_4, col_1_2, pkid_1
FROM tbl_1
WHERE col_1_3 > 1
FOR SHARE;
-- This SELECT reads rows whose col_1_4=3

-- SESSION 2
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE tbl_1
SET col_1_1 = '2025-12-19 16:46:56'
WHERE col_1_4 = 3;

```