Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.15
Description
Under the READ COMMITTED isolation level, a SELECT ... LOCK IN SHARE MODE statement does not block a concurrent UPDATE that modifies the same rows.
Although SELECT ... FOR UPDATE correctly blocks the concurrent UPDATE, the shared-locking variant (LOCK IN SHARE MODE) appears to fail to enforce record-level lock conflicts with exclusive locks. This behavior contradicts the expected InnoDB locking semantics, where shared (S) locks should conflict with exclusive (X) locks on the same records, regardless of isolation level.
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), |
('2025-12-19 16:46:56','str_002',1,1,2,1,5), |
('2025-12-19 16:46:56','str_002',1,2,2,1,6); |
-- 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 |
LOCK IN SHARE MODE; |
-- This SELECT reads rows whose col_1_4 = 3
|
 |
-- SESSION2
|
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; |
-- success |