Description:
With InnoDB at READ COMMITTED, a DELETE with an exclusive primary-key upper bound waits for an X record lock on the first record outside that bound. Consequently, DELETE statements on adjacent, non-overlapping primary-key ranges can block and fail with ERROR 1205.
For example, transaction A deletes id >= 100 AND id < 200 and remains open. Session B deletes id >= 1 AND id < 100. Although these predicates do not overlap, B requests X,REC_NOT_GAP on PRIMARY record id=100 and waits for A. With innodb_lock_wait_timeout=3, B fails after approximately 3 seconds.
This also reproduces when A only executes SELECT ... WHERE id=100 FOR UPDATE, without deleting or modifying any row, while B deletes id >= 50 AND id < 100. Thus the symptom does not require a delete-marked boundary record.
Verified on unmodified Oracle MySQL Community Server - GPL binaries:
- 8.4.11: mysql-8.4.11-linux-glibc2.28-x86_64-minimal.tar.xz
- 9.7.2: mysql-9.7.2-linux-glibc2.28-x86_64-minimal.tar.xz
Each version used a fresh data directory and the same InnoDB table containing primary keys 1 through 250. SQL identity returned Linux / x86_64. Both versions produced the same results:
1. RC, adjacent DELETE ranges: ERROR 1205; waiting on id=100.
2. RR, the same adjacent DELETE ranges: succeeds, deleting 99 rows.
3. RC, B uses UPDATE on [1,100) instead of DELETE, while A still holds its DELETE transaction: succeeds, updating 99 rows.
4. RC, A only locks id=100 with SELECT FOR UPDATE; B deletes [50,100): ERROR 1205; waiting on id=100.
5. RR, the same point-lock case: succeeds, deleting 50 rows.
6. RC, no blocker: DELETE on [1,100) succeeds, deleting 99 rows.
RC = READ COMMITTED; RR = REPEATABLE READ. Both sessions explicitly use the indicated isolation level. The table is reset before every case.
Expected result:
For this simple ascending primary-key range, the exclusive upper bound id < 100 should allow the DELETE to stop without waiting for an X record lock on id=100. The matching rows should be deleted while A remains open, as in the RR control. This request concerns a range boundary known from the index condition; it is not a claim that arbitrary nonmatching rows can never be locked during DML.
Actual result:
B waits for an exclusive record lock on id=100, outside its predicate, then fails with ERROR 1205. Performance Schema identifies a RECORD lock with X,REC_NOT_GAP, not a gap-lock conflict. After the failed autocommit DELETE, all 99 matching rows remain (50 rows in the point-lock variant).
Impact:
Concurrent batch deletes over disjoint adjacent primary-key ranges can serialize or time out under READ COMMITTED.
Additional fork verification:
Percona Server (GPL) 8.0.27-18 and 8.0.42-33 also reproduce the same two RC cases and pass the same controls. These are supplementary fork results, not verification of Oracle MySQL Community Server 8.0.27 or 8.0.42.
Only the ascending integer primary-key scenarios described here were tested. Secondary or descending indexes, partitions and multi-range predicates were not tested. The reproduction uses ordinary DELETE, without extensions, foreign keys or triggers.
How to repeat:
Use two independent connections, A and B, to the same test server. Connection C is optional and is used only to observe the wait. Run each numbered block at the indicated time; do not run the whole section sequentially in one connection. The setup re-creates t1 in the dedicated test schema.
1. In a setup connection, create and populate the table (all ids 1..250, v=0):
SET autocommit=1;
CREATE DATABASE IF NOT EXISTS rc_delete_boundary;
DROP TABLE IF EXISTS rc_delete_boundary.t1;
CREATE TABLE rc_delete_boundary.t1 (
id INT NOT NULL PRIMARY KEY,
v INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO rc_delete_boundary.t1 VALUES
(1,0),(2,0),(3,0),(4,0),(5,0),(6,0),(7,0),(8,0),(9,0),(10,0),(11,0),(12,0),(13,0),(14,0),(15,0),(16,0),(17,0),(18,0),(19,0),(20,0),(21,0),(22,0),(23,0),(24,0),(25,0),
(26,0),(27,0),(28,0),(29,0),(30,0),(31,0),(32,0),(33,0),(34,0),(35,0),(36,0),(37,0),(38,0),(39,0),(40,0),(41,0),(42,0),(43,0),(44,0),(45,0),(46,0),(47,0),(48,0),(49,0),(50,0),
(51,0),(52,0),(53,0),(54,0),(55,0),(56,0),(57,0),(58,0),(59,0),(60,0),(61,0),(62,0),(63,0),(64,0),(65,0),(66,0),(67,0),(68,0),(69,0),(70,0),(71,0),(72,0),(73,0),(74,0),(75,0),
(76,0),(77,0),(78,0),(79,0),(80,0),(81,0),(82,0),(83,0),(84,0),(85,0),(86,0),(87,0),(88,0),(89,0),(90,0),(91,0),(92,0),(93,0),(94,0),(95,0),(96,0),(97,0),(98,0),(99,0),(100,0),
(101,0),(102,0),(103,0),(104,0),(105,0),(106,0),(107,0),(108,0),(109,0),(110,0),(111,0),(112,0),(113,0),(114,0),(115,0),(116,0),(117,0),(118,0),(119,0),(120,0),(121,0),(122,0),(123,0),(124,0),(125,0),
(126,0),(127,0),(128,0),(129,0),(130,0),(131,0),(132,0),(133,0),(134,0),(135,0),(136,0),(137,0),(138,0),(139,0),(140,0),(141,0),(142,0),(143,0),(144,0),(145,0),(146,0),(147,0),(148,0),(149,0),(150,0),
(151,0),(152,0),(153,0),(154,0),(155,0),(156,0),(157,0),(158,0),(159,0),(160,0),(161,0),(162,0),(163,0),(164,0),(165,0),(166,0),(167,0),(168,0),(169,0),(170,0),(171,0),(172,0),(173,0),(174,0),(175,0),
(176,0),(177,0),(178,0),(179,0),(180,0),(181,0),(182,0),(183,0),(184,0),(185,0),(186,0),(187,0),(188,0),(189,0),(190,0),(191,0),(192,0),(193,0),(194,0),(195,0),(196,0),(197,0),(198,0),(199,0),(200,0),
(201,0),(202,0),(203,0),(204,0),(205,0),(206,0),(207,0),(208,0),(209,0),(210,0),(211,0),(212,0),(213,0),(214,0),(215,0),(216,0),(217,0),(218,0),(219,0),(220,0),(221,0),(222,0),(223,0),(224,0),(225,0),
(226,0),(227,0),(228,0),(229,0),(230,0),(231,0),(232,0),(233,0),(234,0),(235,0),(236,0),(237,0),(238,0),(239,0),(240,0),(241,0),(242,0),(243,0),(244,0),(245,0),(246,0),(247,0),(248,0),(249,0),(250,0);
SELECT COUNT(*), SUM(id), SUM(v) FROM rc_delete_boundary.t1;
-- Expected: 250, 31375, 0
The original tests used sql_mode=NO_ENGINE_SUBSTITUTION, collation_server=utf8mb4_0900_ai_ci, performance_schema=ON, and binary logging disabled. Both sessions explicitly set their isolation level below; B uses autocommit.
2. In connection A, execute the following and leave the transaction open. Wait until A's DELETE has completed before starting B:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
DELETE FROM rc_delete_boundary.t1 WHERE id >= 100 AND id < 200;
-- 100 rows deleted, still uncommitted. Keep this connection open.
3. In connection B, execute:
SET autocommit=1;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION innodb_lock_wait_timeout=3;
DELETE FROM rc_delete_boundary.t1 WHERE id >= 1 AND id < 100;
Observed in both 8.4.11 and 9.7.2, after approximately 3 seconds:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
(The batch mysql client includes "at line 1" in this diagnostic.)
After the error, in B:
SELECT COUNT(*) FROM rc_delete_boundary.t1 WHERE id >= 1 AND id < 100;
-- Actual: 99. The failed autocommit statement was rolled back.
4. Optional lock observation: while B's DELETE is waiting, run this in connection C with privileges to read the Performance Schema lock tables. The automated tests used a 3-second timeout; for manual observation, increase B's innodb_lock_wait_timeout before issuing the DELETE to give C more time.
SELECT r.ENGINE_TRANSACTION_ID AS requesting_trx,
r.LOCK_TYPE, r.INDEX_NAME,
r.LOCK_MODE AS requesting_mode,
r.LOCK_STATUS AS requesting_status,
r.LOCK_DATA AS requesting_key,
b.ENGINE_TRANSACTION_ID AS blocking_trx,
b.LOCK_TYPE AS blocking_type,
b.INDEX_NAME AS blocking_index,
b.LOCK_MODE AS blocking_mode,
b.LOCK_STATUS AS blocking_status,
b.LOCK_DATA AS blocking_key
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
ON r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
ON b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
WHERE r.OBJECT_SCHEMA = 'rc_delete_boundary';
Original captured rows (test schema name changed for this report; transaction IDs naturally vary):
Columns: requesting_trx, LOCK_TYPE, INDEX_NAME, requesting_mode, requesting_status, requesting_key, blocking_trx, blocking_type, blocking_index, blocking_mode, blocking_status, blocking_key
8.4.11:
1302 RECORD PRIMARY X,REC_NOT_GAP WAITING 100 1301 RECORD PRIMARY X,REC_NOT_GAP GRANTED 100
9.7.2:
1560 RECORD PRIMARY X,REC_NOT_GAP WAITING 100 1559 RECORD PRIMARY X,REC_NOT_GAP GRANTED 100
Both show B requesting an X record lock on id=100 even though B's predicate is id < 100.
5. After B finishes/times out, release A's transaction:
-- Connection A:
ROLLBACK;
6. Point-lock variant (no uncommitted deletion of the boundary row):
Close out any previous transactions and run the setup in step 1 again. In A:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT id FROM rc_delete_boundary.t1 WHERE id=100 FOR UPDATE;
-- Returns 100. Keep A open; it has not deleted or modified this row.
In B:
SET autocommit=1;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION innodb_lock_wait_timeout=3;
DELETE FROM rc_delete_boundary.t1 WHERE id >= 50 AND id < 100;
Actual on both versions: ERROR 1205 after approximately 3 seconds. The same observation query shows a waiting X,REC_NOT_GAP lock on PRIMARY id=100. After the error, B's SELECT COUNT(*) with this DELETE predicate returns 50. Expected: delete the 50 matching rows without waiting on id=100.
After B finishes, ROLLBACK in A.
7. Controls (finish A's transaction and rerun step 1 before each control):
- Repeat steps 2-3 with BOTH A and B set to REPEATABLE READ. B completes while A remains open; SELECT ROW_COUNT() immediately after B's DELETE returns 99.
- Repeat step 6 with BOTH sessions set to REPEATABLE READ. B completes while A remains open; SELECT ROW_COUNT() immediately after B's DELETE returns 50.
- Repeat steps 2-3 at READ COMMITTED, replacing only B's DELETE with:
UPDATE rc_delete_boundary.t1 SET v=v+1 WHERE id >= 1 AND id < 100;
SELECT ROW_COUNT();
-- Actual: 99; completes while A remains open.
SELECT SUM(v) FROM rc_delete_boundary.t1 WHERE id < 100;
-- Actual: 99.
- With no open transaction A, run B's original READ COMMITTED DELETE on a freshly populated table. SELECT ROW_COUNT() immediately after the DELETE returns 99.
ROLLBACK A after each control that uses it. Do not reset the table while A still holds locks.
8. Complete executable MTR reproducer
The following two files provide an automated reproduction of the same issue and controls. Create them under the mysql-test directory of a matching MySQL source/build or installation with its test framework available. If using the minimal binary distribution, install the matching MySQL Test Suite package as well; mysqltest and mysql-test-run.pl are required. No debug build, DEBUG_SYNC or server modification is needed.
Create file mysql-test/t/rc_range_delete_boundary_repro.test with EXACTLY the contents between the next BEGIN/END markers (do not copy the markers):
----- BEGIN rc_range_delete_boundary_repro.test -----
# Reproducer: PASS means the reported RC lock-wait behavior was reproduced.
# No debug build, DEBUG_SYNC or server modification is required.
if (!`SELECT @@performance_schema`)
{
--skip Requires performance_schema=ON
}
--disable_query_log
CREATE TABLE rc_boundary_seed (id INT NOT NULL PRIMARY KEY, v INT NOT NULL)
ENGINE=InnoDB;
--let $i=1
while ($i <= 250)
{
eval INSERT INTO rc_boundary_seed VALUES ($i, 0);
--inc $i
}
CREATE TABLE rc_boundary_t1 LIKE rc_boundary_seed;
INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id;
connect (a,localhost,root,,test);
connect (b,localhost,root,,test);
connection b;
SET autocommit=1;
SET SESSION innodb_lock_wait_timeout=5;
connection default;
--enable_query_log
--echo # 1. RC: adjacent DELETE ranges wait on excluded boundary id=100.
connection a;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200;
connection b;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--send DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100
connection default;
--let $wait_timeout=3
--let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1' AND r.INDEX_NAME='PRIMARY' AND r.LOCK_MODE='X,REC_NOT_GAP' AND r.LOCK_STATUS='WAITING' AND r.LOCK_DATA='100' AND b.LOCK_MODE='X,REC_NOT_GAP' AND b.LOCK_STATUS='GRANTED' AND b.LOCK_DATA='100'
--source include/wait_condition.inc
--sorted_result
SELECT r.LOCK_TYPE, r.INDEX_NAME, r.LOCK_MODE AS requested_mode,
r.LOCK_STATUS AS requested_status, r.LOCK_DATA AS requested_key,
b.LOCK_MODE AS blocking_mode, b.LOCK_DATA AS blocking_key
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks r
ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks b
ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID
WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1';
connection b;
--error ER_LOCK_WAIT_TIMEOUT
--reap
SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 1 AND id < 100;
connection a;
ROLLBACK;
--echo # 2. RC: locking only the unchanged boundary row also reproduces.
connection a;
BEGIN;
SELECT id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE;
connection b;
--send DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id < 100
connection default;
--let $wait_timeout=3
--let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1' AND r.INDEX_NAME='PRIMARY' AND r.LOCK_MODE='X,REC_NOT_GAP' AND r.LOCK_STATUS='WAITING' AND r.LOCK_DATA='100' AND b.LOCK_MODE='X,REC_NOT_GAP' AND b.LOCK_STATUS='GRANTED' AND b.LOCK_DATA='100'
--source include/wait_condition.inc
SELECT r.LOCK_DATA AS requested_key, r.LOCK_MODE AS requested_mode,
b.LOCK_DATA AS blocking_key, b.LOCK_MODE AS blocking_mode
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks r
ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks b
ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID
WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1';
connection b;
--error ER_LOCK_WAIT_TIMEOUT
--reap
SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 50 AND id < 100;
connection a;
ROLLBACK;
--echo # 3. RR control: adjacent DELETE completes while A remains open.
connection a;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200;
connection b;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100;
SELECT ROW_COUNT() AS deleted;
connection a;
ROLLBACK;
connection default;
--disable_query_log
TRUNCATE TABLE rc_boundary_t1;
INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id;
--enable_query_log
--echo # 4. RR control: boundary-point lock does not block the range DELETE.
connection a;
BEGIN;
SELECT id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE;
connection b;
DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id < 100;
SELECT ROW_COUNT() AS deleted;
connection a;
ROLLBACK;
connection default;
--disable_query_log
TRUNCATE TABLE rc_boundary_t1;
INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id;
--enable_query_log
--echo # 5. RC UPDATE control: completes while A's DELETE remains open.
connection a;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200;
connection b;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE rc_boundary_t1 SET v=v+1 WHERE id >= 1 AND id < 100;
SELECT ROW_COUNT() AS updated;
SELECT SUM(v) AS sum_v FROM rc_boundary_t1 WHERE id < 100;
connection a;
ROLLBACK;
connection default;
--disable_query_log
TRUNCATE TABLE rc_boundary_t1;
INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id;
--enable_query_log
--echo # 6. RC DELETE without a blocker succeeds.
connection b;
DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100;
SELECT ROW_COUNT() AS deleted;
disconnect a;
disconnect b;
connection default;
DROP TABLE rc_boundary_t1, rc_boundary_seed;
----- END rc_range_delete_boundary_repro.test -----
Create file mysql-test/r/rc_range_delete_boundary_repro.result with EXACTLY the contents between the next BEGIN/END markers (do not copy the markers). Preserve the tab-separated result columns:
----- BEGIN rc_range_delete_boundary_repro.result -----
# 1. RC: adjacent DELETE ranges wait on excluded boundary id=100.
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100;
SELECT r.LOCK_TYPE, r.INDEX_NAME, r.LOCK_MODE AS requested_mode,
r.LOCK_STATUS AS requested_status, r.LOCK_DATA AS requested_key,
b.LOCK_MODE AS blocking_mode, b.LOCK_DATA AS blocking_key
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks r
ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks b
ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID
WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1';
LOCK_TYPE INDEX_NAME requested_mode requested_status requested_key blocking_mode blocking_key
RECORD PRIMARY X,REC_NOT_GAP WAITING 100 X,REC_NOT_GAP 100
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 1 AND id < 100;
remaining
99
ROLLBACK;
# 2. RC: locking only the unchanged boundary row also reproduces.
BEGIN;
SELECT id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE;
id
100
DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id < 100;
SELECT r.LOCK_DATA AS requested_key, r.LOCK_MODE AS requested_mode,
b.LOCK_DATA AS blocking_key, b.LOCK_MODE AS blocking_mode
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks r
ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks b
ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID
WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1';
requested_key requested_mode blocking_key blocking_mode
100 X,REC_NOT_GAP 100 X,REC_NOT_GAP
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 50 AND id < 100;
remaining
50
ROLLBACK;
# 3. RR control: adjacent DELETE completes while A remains open.
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100;
SELECT ROW_COUNT() AS deleted;
deleted
99
ROLLBACK;
# 4. RR control: boundary-point lock does not block the range DELETE.
BEGIN;
SELECT id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE;
id
100
DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id < 100;
SELECT ROW_COUNT() AS deleted;
deleted
50
ROLLBACK;
# 5. RC UPDATE control: completes while A's DELETE remains open.
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE rc_boundary_t1 SET v=v+1 WHERE id >= 1 AND id < 100;
SELECT ROW_COUNT() AS updated;
updated
99
SELECT SUM(v) AS sum_v FROM rc_boundary_t1 WHERE id < 100;
sum_v
99
ROLLBACK;
# 6. RC DELETE without a blocker succeeds.
DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100;
SELECT ROW_COUNT() AS deleted;
deleted
99
DROP TABLE rc_boundary_t1, rc_boundary_seed;
----- END rc_range_delete_boundary_repro.result -----
From the matching mysql-test directory, run:
perl mysql-test-run.pl --parallel=1 --retry=0 --mysqld=--skip-mysqlx --mysqld=--performance-schema=ON main.rc_range_delete_boundary_repro
PASS means the reported bug was reproduced, NOT that the server was fixed. This reproducer explicitly expects ER_LOCK_WAIT_TIMEOUT for the two READ COMMITTED DELETE cases and checks the waiting X,REC_NOT_GAP lock on PRIMARY id=100. It also checks the REPEATABLE READ, UPDATE and unblocked DELETE controls. The MTR uses innodb_lock_wait_timeout=5, so its two intentional timeouts take approximately 10 seconds in total; the earlier manual/automated SQL examples used 3 seconds.
The same .test and .result were validated without --record on unmodified official MySQL Community Server 8.4.11 and 9.7.2. Both runs returned rc=0 with main.rc_range_delete_boundary_repro [ pass ].
The separate main.rc_range_delete_boundary test in Suggested fix is a regression test for the proposed corrected behavior. It is distinct from this bug-preserving reproducer and fails on both unmodified versions at the first adjacent DELETE with ERROR 1205. No rebuilt, patched-server pass is claimed.
Suggested fix:
Candidate patch for review; not compiled or post-fix runtime validated.
Base: Oracle MySQL 8.4.11, commit 99960bf74fa919347e4f4e3ca47672f333d6e91f.
Adds a pre-lock range-end check for RC forward scans of an ascending clustered index with a complete unique-key end point. HANDLER and semi-consistent reads are excluded; inclusive bounds retain their locking behavior.
Checks: git diff --check and git apply --check against the clean base passed. The included regression fails with ERROR 1205 on unmodified 8.4.11 and 9.7.2. No patched-server pass or 9.7.2 source-port validation is claimed.
diff --git a/mysql-test/r/rc_range_delete_boundary.result b/mysql-test/r/rc_range_delete_boundary.result
new file mode 100644
index 0000000000000000000000000000000000000000..ca44cdef8d0ef3f68afa3afe57413117b24f08c7
--- /dev/null
+++ b/mysql-test/r/rc_range_delete_boundary.result
@@ -0,0 +1,27 @@
+# RC adjacent DELETE: 99 rows deleted while A remains open.
+affected
+99
+remaining
+0
+# RC unchanged boundary row: 50 rows deleted while A remains open.
+affected
+50
+remaining
+0
+# RR control: adjacent DELETE still succeeds.
+affected
+99
+# RC UPDATE control: semi-consistent read behavior is unchanged.
+affected
+99
+sum_v
+99
+# An inclusive upper bound must still wait for the matching id=100.
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+remaining
+51
+# A missing end key: the next existing record id=101 is outside range.
+affected
+99
+remaining
+0
diff --git a/mysql-test/t/rc_range_delete_boundary.test b/mysql-test/t/rc_range_delete_boundary.test
new file mode 100644
index 0000000000000000000000000000000000000000..e8b03926c780463872ea33e873ffbd4554adf21a
--- /dev/null
+++ b/mysql-test/t/rc_range_delete_boundary.test
@@ -0,0 +1,109 @@
+# Regression: out-of-range records must not block a READ COMMITTED DELETE.
+# Unlike rc_range_delete_boundary_repro, this test expects the fix.
+--disable_query_log
+CREATE TABLE rc_boundary_seed (id INT NOT NULL PRIMARY KEY, v INT NOT NULL)
+ ENGINE=InnoDB;
+--let $i=1
+while ($i <= 250)
+{
+ eval INSERT INTO rc_boundary_seed VALUES ($i, 0);
+ --inc $i
+}
+CREATE TABLE rc_boundary_t1 LIKE rc_boundary_seed;
+INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id;
+connect (a,localhost,root,,test);
+connect (b,localhost,root,,test);
+connection a;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+connection b;
+SET autocommit=1;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SET SESSION innodb_lock_wait_timeout=1;
+
+--echo # RC adjacent DELETE: 99 rows deleted while A remains open.
+connection a;
+BEGIN;
+DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200;
+connection b;
+DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100;
+SELECT ROW_COUNT() AS affected;
+SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id < 100;
+connection a;
+ROLLBACK;
+connection default;
+TRUNCATE TABLE rc_boundary_t1;
+INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id;
+
+--echo # RC unchanged boundary row: 50 rows deleted while A remains open.
+connection a;
+BEGIN;
+SELECT id INTO @locked_id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE;
+connection b;
+DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id < 100;
+SELECT ROW_COUNT() AS affected;
+SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 50 AND id < 100;
+connection a;
+ROLLBACK;
+connection default;
+TRUNCATE TABLE rc_boundary_t1;
+INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id;
+
+--echo # RR control: adjacent DELETE still succeeds.
+connection a;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
+DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200;
+connection b;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100;
+SELECT ROW_COUNT() AS affected;
+connection a;
+ROLLBACK;
+connection default;
+TRUNCATE TABLE rc_boundary_t1;
+INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id;
+
+--echo # RC UPDATE control: semi-consistent read behavior is unchanged.
+connection a;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+BEGIN;
+DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200;
+connection b;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+UPDATE rc_boundary_t1 SET v=v+1 WHERE id >= 1 AND id < 100;
+SELECT ROW_COUNT() AS affected;
+SELECT SUM(v) AS sum_v FROM rc_boundary_t1 WHERE id < 100;
+connection a;
+ROLLBACK;
+connection default;
+TRUNCATE TABLE rc_boundary_t1;
+INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id;
+
+--echo # An inclusive upper bound must still wait for the matching id=100.
+connection a;
+BEGIN;
+SELECT id INTO @locked_id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE;
+connection b;
+--error ER_LOCK_WAIT_TIMEOUT
+DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id <= 100;
+SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 50 AND id <= 100;
+connection a;
+ROLLBACK;
+
+--echo # A missing end key: the next existing record id=101 is outside range.
+connection default;
+DELETE FROM rc_boundary_t1 WHERE id=100;
+connection a;
+BEGIN;
+SELECT id INTO @locked_id FROM rc_boundary_t1 WHERE id=101 FOR UPDATE;
+connection b;
+DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100;
+SELECT ROW_COUNT() AS affected;
+SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id < 100;
+connection a;
+ROLLBACK;
+disconnect a;
+disconnect b;
+connection default;
+DROP TABLE rc_boundary_t1, rc_boundary_seed;
+--enable_query_log
diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc
index 66263ae7af587e4dd06cd7d0791619cac8ba04fd..c88247c2879390fa082916856d8822287eb169f8 100644
--- a/storage/innobase/row/row0sel.cc
+++ b/storage/innobase/row/row0sel.cc
@@ -4289,6 +4289,35 @@ static row_to_range_relation_t row_compare_row_to_range(
return (row_to_range_relation);
}
+ /* At READ COMMITTED a locking range scan must not wait for a record
+ that is already known to be beyond its end. The gap-lock shortcut below
+ otherwise returns before comparing the record with m_stop_tuple.
+
+ Keep this check limited to forward scans of an ascending clustered index
+ with a complete unique-key end point. In particular, do not compare an
+ older version obtained by a semi-consistent read with the physical key. */
+ if (trx->isolation_level == TRX_ISO_READ_COMMITTED &&
+ prebuilt->row_read_type == ROW_READ_WITH_LOCKS &&
+ !prebuilt->used_in_HANDLER && index == clust_index &&
+ !dict_index_has_desc(index) && moves_up &&
+ (mode == PAGE_CUR_GE || mode == PAGE_CUR_G) &&
+ (direction == 0 || direction == ROW_SEL_NEXT) &&
+ prebuilt->is_reading_range()) {
+ const auto stop_len = dtuple_get_n_fields_cmp(prebuilt->m_stop_tuple);
+ if (stop_len > 0 && stop_len == dict_index_get_n_unique(index)) {
+ ut_ad(prebuilt->m_mysql_handler->end_range != nullptr);
+ const auto cmp =
+ cmp_dtuple_rec(prebuilt->m_stop_tuple, rec, index, offsets);
+ if (cmp < 0 ||
+ (cmp == 0 && prebuilt->m_mysql_handler->end_range->flag ==
+ HA_READ_BEFORE_KEY)) {
+ row_to_range_relation.row_can_be_in_range = false;
+ row_to_range_relation.gap_can_intersect_range = false;
+ return (row_to_range_relation);
+ }
+ }
+ }
+
/* Try to place a lock on the index record; note that delete
marked records are a special case in a unique search. If there
is a non-delete marked record, then it is enough to lock its
Description: With InnoDB at READ COMMITTED, a DELETE with an exclusive primary-key upper bound waits for an X record lock on the first record outside that bound. Consequently, DELETE statements on adjacent, non-overlapping primary-key ranges can block and fail with ERROR 1205. For example, transaction A deletes id >= 100 AND id < 200 and remains open. Session B deletes id >= 1 AND id < 100. Although these predicates do not overlap, B requests X,REC_NOT_GAP on PRIMARY record id=100 and waits for A. With innodb_lock_wait_timeout=3, B fails after approximately 3 seconds. This also reproduces when A only executes SELECT ... WHERE id=100 FOR UPDATE, without deleting or modifying any row, while B deletes id >= 50 AND id < 100. Thus the symptom does not require a delete-marked boundary record. Verified on unmodified Oracle MySQL Community Server - GPL binaries: - 8.4.11: mysql-8.4.11-linux-glibc2.28-x86_64-minimal.tar.xz - 9.7.2: mysql-9.7.2-linux-glibc2.28-x86_64-minimal.tar.xz Each version used a fresh data directory and the same InnoDB table containing primary keys 1 through 250. SQL identity returned Linux / x86_64. Both versions produced the same results: 1. RC, adjacent DELETE ranges: ERROR 1205; waiting on id=100. 2. RR, the same adjacent DELETE ranges: succeeds, deleting 99 rows. 3. RC, B uses UPDATE on [1,100) instead of DELETE, while A still holds its DELETE transaction: succeeds, updating 99 rows. 4. RC, A only locks id=100 with SELECT FOR UPDATE; B deletes [50,100): ERROR 1205; waiting on id=100. 5. RR, the same point-lock case: succeeds, deleting 50 rows. 6. RC, no blocker: DELETE on [1,100) succeeds, deleting 99 rows. RC = READ COMMITTED; RR = REPEATABLE READ. Both sessions explicitly use the indicated isolation level. The table is reset before every case. Expected result: For this simple ascending primary-key range, the exclusive upper bound id < 100 should allow the DELETE to stop without waiting for an X record lock on id=100. The matching rows should be deleted while A remains open, as in the RR control. This request concerns a range boundary known from the index condition; it is not a claim that arbitrary nonmatching rows can never be locked during DML. Actual result: B waits for an exclusive record lock on id=100, outside its predicate, then fails with ERROR 1205. Performance Schema identifies a RECORD lock with X,REC_NOT_GAP, not a gap-lock conflict. After the failed autocommit DELETE, all 99 matching rows remain (50 rows in the point-lock variant). Impact: Concurrent batch deletes over disjoint adjacent primary-key ranges can serialize or time out under READ COMMITTED. Additional fork verification: Percona Server (GPL) 8.0.27-18 and 8.0.42-33 also reproduce the same two RC cases and pass the same controls. These are supplementary fork results, not verification of Oracle MySQL Community Server 8.0.27 or 8.0.42. Only the ascending integer primary-key scenarios described here were tested. Secondary or descending indexes, partitions and multi-range predicates were not tested. The reproduction uses ordinary DELETE, without extensions, foreign keys or triggers. How to repeat: Use two independent connections, A and B, to the same test server. Connection C is optional and is used only to observe the wait. Run each numbered block at the indicated time; do not run the whole section sequentially in one connection. The setup re-creates t1 in the dedicated test schema. 1. In a setup connection, create and populate the table (all ids 1..250, v=0): SET autocommit=1; CREATE DATABASE IF NOT EXISTS rc_delete_boundary; DROP TABLE IF EXISTS rc_delete_boundary.t1; CREATE TABLE rc_delete_boundary.t1 ( id INT NOT NULL PRIMARY KEY, v INT NOT NULL ) ENGINE=InnoDB; INSERT INTO rc_delete_boundary.t1 VALUES (1,0),(2,0),(3,0),(4,0),(5,0),(6,0),(7,0),(8,0),(9,0),(10,0),(11,0),(12,0),(13,0),(14,0),(15,0),(16,0),(17,0),(18,0),(19,0),(20,0),(21,0),(22,0),(23,0),(24,0),(25,0), (26,0),(27,0),(28,0),(29,0),(30,0),(31,0),(32,0),(33,0),(34,0),(35,0),(36,0),(37,0),(38,0),(39,0),(40,0),(41,0),(42,0),(43,0),(44,0),(45,0),(46,0),(47,0),(48,0),(49,0),(50,0), (51,0),(52,0),(53,0),(54,0),(55,0),(56,0),(57,0),(58,0),(59,0),(60,0),(61,0),(62,0),(63,0),(64,0),(65,0),(66,0),(67,0),(68,0),(69,0),(70,0),(71,0),(72,0),(73,0),(74,0),(75,0), (76,0),(77,0),(78,0),(79,0),(80,0),(81,0),(82,0),(83,0),(84,0),(85,0),(86,0),(87,0),(88,0),(89,0),(90,0),(91,0),(92,0),(93,0),(94,0),(95,0),(96,0),(97,0),(98,0),(99,0),(100,0), (101,0),(102,0),(103,0),(104,0),(105,0),(106,0),(107,0),(108,0),(109,0),(110,0),(111,0),(112,0),(113,0),(114,0),(115,0),(116,0),(117,0),(118,0),(119,0),(120,0),(121,0),(122,0),(123,0),(124,0),(125,0), (126,0),(127,0),(128,0),(129,0),(130,0),(131,0),(132,0),(133,0),(134,0),(135,0),(136,0),(137,0),(138,0),(139,0),(140,0),(141,0),(142,0),(143,0),(144,0),(145,0),(146,0),(147,0),(148,0),(149,0),(150,0), (151,0),(152,0),(153,0),(154,0),(155,0),(156,0),(157,0),(158,0),(159,0),(160,0),(161,0),(162,0),(163,0),(164,0),(165,0),(166,0),(167,0),(168,0),(169,0),(170,0),(171,0),(172,0),(173,0),(174,0),(175,0), (176,0),(177,0),(178,0),(179,0),(180,0),(181,0),(182,0),(183,0),(184,0),(185,0),(186,0),(187,0),(188,0),(189,0),(190,0),(191,0),(192,0),(193,0),(194,0),(195,0),(196,0),(197,0),(198,0),(199,0),(200,0), (201,0),(202,0),(203,0),(204,0),(205,0),(206,0),(207,0),(208,0),(209,0),(210,0),(211,0),(212,0),(213,0),(214,0),(215,0),(216,0),(217,0),(218,0),(219,0),(220,0),(221,0),(222,0),(223,0),(224,0),(225,0), (226,0),(227,0),(228,0),(229,0),(230,0),(231,0),(232,0),(233,0),(234,0),(235,0),(236,0),(237,0),(238,0),(239,0),(240,0),(241,0),(242,0),(243,0),(244,0),(245,0),(246,0),(247,0),(248,0),(249,0),(250,0); SELECT COUNT(*), SUM(id), SUM(v) FROM rc_delete_boundary.t1; -- Expected: 250, 31375, 0 The original tests used sql_mode=NO_ENGINE_SUBSTITUTION, collation_server=utf8mb4_0900_ai_ci, performance_schema=ON, and binary logging disabled. Both sessions explicitly set their isolation level below; B uses autocommit. 2. In connection A, execute the following and leave the transaction open. Wait until A's DELETE has completed before starting B: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; DELETE FROM rc_delete_boundary.t1 WHERE id >= 100 AND id < 200; -- 100 rows deleted, still uncommitted. Keep this connection open. 3. In connection B, execute: SET autocommit=1; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION innodb_lock_wait_timeout=3; DELETE FROM rc_delete_boundary.t1 WHERE id >= 1 AND id < 100; Observed in both 8.4.11 and 9.7.2, after approximately 3 seconds: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction (The batch mysql client includes "at line 1" in this diagnostic.) After the error, in B: SELECT COUNT(*) FROM rc_delete_boundary.t1 WHERE id >= 1 AND id < 100; -- Actual: 99. The failed autocommit statement was rolled back. 4. Optional lock observation: while B's DELETE is waiting, run this in connection C with privileges to read the Performance Schema lock tables. The automated tests used a 3-second timeout; for manual observation, increase B's innodb_lock_wait_timeout before issuing the DELETE to give C more time. SELECT r.ENGINE_TRANSACTION_ID AS requesting_trx, r.LOCK_TYPE, r.INDEX_NAME, r.LOCK_MODE AS requesting_mode, r.LOCK_STATUS AS requesting_status, r.LOCK_DATA AS requesting_key, b.ENGINE_TRANSACTION_ID AS blocking_trx, b.LOCK_TYPE AS blocking_type, b.INDEX_NAME AS blocking_index, b.LOCK_MODE AS blocking_mode, b.LOCK_STATUS AS blocking_status, b.LOCK_DATA AS blocking_key FROM performance_schema.data_lock_waits AS w JOIN performance_schema.data_locks AS r ON r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID JOIN performance_schema.data_locks AS b ON b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID WHERE r.OBJECT_SCHEMA = 'rc_delete_boundary'; Original captured rows (test schema name changed for this report; transaction IDs naturally vary): Columns: requesting_trx, LOCK_TYPE, INDEX_NAME, requesting_mode, requesting_status, requesting_key, blocking_trx, blocking_type, blocking_index, blocking_mode, blocking_status, blocking_key 8.4.11: 1302 RECORD PRIMARY X,REC_NOT_GAP WAITING 100 1301 RECORD PRIMARY X,REC_NOT_GAP GRANTED 100 9.7.2: 1560 RECORD PRIMARY X,REC_NOT_GAP WAITING 100 1559 RECORD PRIMARY X,REC_NOT_GAP GRANTED 100 Both show B requesting an X record lock on id=100 even though B's predicate is id < 100. 5. After B finishes/times out, release A's transaction: -- Connection A: ROLLBACK; 6. Point-lock variant (no uncommitted deletion of the boundary row): Close out any previous transactions and run the setup in step 1 again. In A: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; SELECT id FROM rc_delete_boundary.t1 WHERE id=100 FOR UPDATE; -- Returns 100. Keep A open; it has not deleted or modified this row. In B: SET autocommit=1; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET SESSION innodb_lock_wait_timeout=3; DELETE FROM rc_delete_boundary.t1 WHERE id >= 50 AND id < 100; Actual on both versions: ERROR 1205 after approximately 3 seconds. The same observation query shows a waiting X,REC_NOT_GAP lock on PRIMARY id=100. After the error, B's SELECT COUNT(*) with this DELETE predicate returns 50. Expected: delete the 50 matching rows without waiting on id=100. After B finishes, ROLLBACK in A. 7. Controls (finish A's transaction and rerun step 1 before each control): - Repeat steps 2-3 with BOTH A and B set to REPEATABLE READ. B completes while A remains open; SELECT ROW_COUNT() immediately after B's DELETE returns 99. - Repeat step 6 with BOTH sessions set to REPEATABLE READ. B completes while A remains open; SELECT ROW_COUNT() immediately after B's DELETE returns 50. - Repeat steps 2-3 at READ COMMITTED, replacing only B's DELETE with: UPDATE rc_delete_boundary.t1 SET v=v+1 WHERE id >= 1 AND id < 100; SELECT ROW_COUNT(); -- Actual: 99; completes while A remains open. SELECT SUM(v) FROM rc_delete_boundary.t1 WHERE id < 100; -- Actual: 99. - With no open transaction A, run B's original READ COMMITTED DELETE on a freshly populated table. SELECT ROW_COUNT() immediately after the DELETE returns 99. ROLLBACK A after each control that uses it. Do not reset the table while A still holds locks. 8. Complete executable MTR reproducer The following two files provide an automated reproduction of the same issue and controls. Create them under the mysql-test directory of a matching MySQL source/build or installation with its test framework available. If using the minimal binary distribution, install the matching MySQL Test Suite package as well; mysqltest and mysql-test-run.pl are required. No debug build, DEBUG_SYNC or server modification is needed. Create file mysql-test/t/rc_range_delete_boundary_repro.test with EXACTLY the contents between the next BEGIN/END markers (do not copy the markers): ----- BEGIN rc_range_delete_boundary_repro.test ----- # Reproducer: PASS means the reported RC lock-wait behavior was reproduced. # No debug build, DEBUG_SYNC or server modification is required. if (!`SELECT @@performance_schema`) { --skip Requires performance_schema=ON } --disable_query_log CREATE TABLE rc_boundary_seed (id INT NOT NULL PRIMARY KEY, v INT NOT NULL) ENGINE=InnoDB; --let $i=1 while ($i <= 250) { eval INSERT INTO rc_boundary_seed VALUES ($i, 0); --inc $i } CREATE TABLE rc_boundary_t1 LIKE rc_boundary_seed; INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id; connect (a,localhost,root,,test); connect (b,localhost,root,,test); connection b; SET autocommit=1; SET SESSION innodb_lock_wait_timeout=5; connection default; --enable_query_log --echo # 1. RC: adjacent DELETE ranges wait on excluded boundary id=100. connection a; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200; connection b; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; --send DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100 connection default; --let $wait_timeout=3 --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1' AND r.INDEX_NAME='PRIMARY' AND r.LOCK_MODE='X,REC_NOT_GAP' AND r.LOCK_STATUS='WAITING' AND r.LOCK_DATA='100' AND b.LOCK_MODE='X,REC_NOT_GAP' AND b.LOCK_STATUS='GRANTED' AND b.LOCK_DATA='100' --source include/wait_condition.inc --sorted_result SELECT r.LOCK_TYPE, r.INDEX_NAME, r.LOCK_MODE AS requested_mode, r.LOCK_STATUS AS requested_status, r.LOCK_DATA AS requested_key, b.LOCK_MODE AS blocking_mode, b.LOCK_DATA AS blocking_key FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1'; connection b; --error ER_LOCK_WAIT_TIMEOUT --reap SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 1 AND id < 100; connection a; ROLLBACK; --echo # 2. RC: locking only the unchanged boundary row also reproduces. connection a; BEGIN; SELECT id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE; connection b; --send DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id < 100 connection default; --let $wait_timeout=3 --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1' AND r.INDEX_NAME='PRIMARY' AND r.LOCK_MODE='X,REC_NOT_GAP' AND r.LOCK_STATUS='WAITING' AND r.LOCK_DATA='100' AND b.LOCK_MODE='X,REC_NOT_GAP' AND b.LOCK_STATUS='GRANTED' AND b.LOCK_DATA='100' --source include/wait_condition.inc SELECT r.LOCK_DATA AS requested_key, r.LOCK_MODE AS requested_mode, b.LOCK_DATA AS blocking_key, b.LOCK_MODE AS blocking_mode FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1'; connection b; --error ER_LOCK_WAIT_TIMEOUT --reap SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 50 AND id < 100; connection a; ROLLBACK; --echo # 3. RR control: adjacent DELETE completes while A remains open. connection a; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200; connection b; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100; SELECT ROW_COUNT() AS deleted; connection a; ROLLBACK; connection default; --disable_query_log TRUNCATE TABLE rc_boundary_t1; INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id; --enable_query_log --echo # 4. RR control: boundary-point lock does not block the range DELETE. connection a; BEGIN; SELECT id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE; connection b; DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id < 100; SELECT ROW_COUNT() AS deleted; connection a; ROLLBACK; connection default; --disable_query_log TRUNCATE TABLE rc_boundary_t1; INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id; --enable_query_log --echo # 5. RC UPDATE control: completes while A's DELETE remains open. connection a; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200; connection b; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE rc_boundary_t1 SET v=v+1 WHERE id >= 1 AND id < 100; SELECT ROW_COUNT() AS updated; SELECT SUM(v) AS sum_v FROM rc_boundary_t1 WHERE id < 100; connection a; ROLLBACK; connection default; --disable_query_log TRUNCATE TABLE rc_boundary_t1; INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id; --enable_query_log --echo # 6. RC DELETE without a blocker succeeds. connection b; DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100; SELECT ROW_COUNT() AS deleted; disconnect a; disconnect b; connection default; DROP TABLE rc_boundary_t1, rc_boundary_seed; ----- END rc_range_delete_boundary_repro.test ----- Create file mysql-test/r/rc_range_delete_boundary_repro.result with EXACTLY the contents between the next BEGIN/END markers (do not copy the markers). Preserve the tab-separated result columns: ----- BEGIN rc_range_delete_boundary_repro.result ----- # 1. RC: adjacent DELETE ranges wait on excluded boundary id=100. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100; SELECT r.LOCK_TYPE, r.INDEX_NAME, r.LOCK_MODE AS requested_mode, r.LOCK_STATUS AS requested_status, r.LOCK_DATA AS requested_key, b.LOCK_MODE AS blocking_mode, b.LOCK_DATA AS blocking_key FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1'; LOCK_TYPE INDEX_NAME requested_mode requested_status requested_key blocking_mode blocking_key RECORD PRIMARY X,REC_NOT_GAP WAITING 100 X,REC_NOT_GAP 100 ERROR HY000: Lock wait timeout exceeded; try restarting transaction SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 1 AND id < 100; remaining 99 ROLLBACK; # 2. RC: locking only the unchanged boundary row also reproduces. BEGIN; SELECT id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE; id 100 DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id < 100; SELECT r.LOCK_DATA AS requested_key, r.LOCK_MODE AS requested_mode, b.LOCK_DATA AS blocking_key, b.LOCK_MODE AS blocking_mode FROM performance_schema.data_lock_waits w JOIN performance_schema.data_locks r ON r.ENGINE_LOCK_ID=w.REQUESTING_ENGINE_LOCK_ID JOIN performance_schema.data_locks b ON b.ENGINE_LOCK_ID=w.BLOCKING_ENGINE_LOCK_ID WHERE r.OBJECT_SCHEMA='test' AND r.OBJECT_NAME='rc_boundary_t1'; requested_key requested_mode blocking_key blocking_mode 100 X,REC_NOT_GAP 100 X,REC_NOT_GAP ERROR HY000: Lock wait timeout exceeded; try restarting transaction SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 50 AND id < 100; remaining 50 ROLLBACK; # 3. RR control: adjacent DELETE completes while A remains open. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100; SELECT ROW_COUNT() AS deleted; deleted 99 ROLLBACK; # 4. RR control: boundary-point lock does not block the range DELETE. BEGIN; SELECT id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE; id 100 DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id < 100; SELECT ROW_COUNT() AS deleted; deleted 50 ROLLBACK; # 5. RC UPDATE control: completes while A's DELETE remains open. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; UPDATE rc_boundary_t1 SET v=v+1 WHERE id >= 1 AND id < 100; SELECT ROW_COUNT() AS updated; updated 99 SELECT SUM(v) AS sum_v FROM rc_boundary_t1 WHERE id < 100; sum_v 99 ROLLBACK; # 6. RC DELETE without a blocker succeeds. DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100; SELECT ROW_COUNT() AS deleted; deleted 99 DROP TABLE rc_boundary_t1, rc_boundary_seed; ----- END rc_range_delete_boundary_repro.result ----- From the matching mysql-test directory, run: perl mysql-test-run.pl --parallel=1 --retry=0 --mysqld=--skip-mysqlx --mysqld=--performance-schema=ON main.rc_range_delete_boundary_repro PASS means the reported bug was reproduced, NOT that the server was fixed. This reproducer explicitly expects ER_LOCK_WAIT_TIMEOUT for the two READ COMMITTED DELETE cases and checks the waiting X,REC_NOT_GAP lock on PRIMARY id=100. It also checks the REPEATABLE READ, UPDATE and unblocked DELETE controls. The MTR uses innodb_lock_wait_timeout=5, so its two intentional timeouts take approximately 10 seconds in total; the earlier manual/automated SQL examples used 3 seconds. The same .test and .result were validated without --record on unmodified official MySQL Community Server 8.4.11 and 9.7.2. Both runs returned rc=0 with main.rc_range_delete_boundary_repro [ pass ]. The separate main.rc_range_delete_boundary test in Suggested fix is a regression test for the proposed corrected behavior. It is distinct from this bug-preserving reproducer and fails on both unmodified versions at the first adjacent DELETE with ERROR 1205. No rebuilt, patched-server pass is claimed. Suggested fix: Candidate patch for review; not compiled or post-fix runtime validated. Base: Oracle MySQL 8.4.11, commit 99960bf74fa919347e4f4e3ca47672f333d6e91f. Adds a pre-lock range-end check for RC forward scans of an ascending clustered index with a complete unique-key end point. HANDLER and semi-consistent reads are excluded; inclusive bounds retain their locking behavior. Checks: git diff --check and git apply --check against the clean base passed. The included regression fails with ERROR 1205 on unmodified 8.4.11 and 9.7.2. No patched-server pass or 9.7.2 source-port validation is claimed. diff --git a/mysql-test/r/rc_range_delete_boundary.result b/mysql-test/r/rc_range_delete_boundary.result new file mode 100644 index 0000000000000000000000000000000000000000..ca44cdef8d0ef3f68afa3afe57413117b24f08c7 --- /dev/null +++ b/mysql-test/r/rc_range_delete_boundary.result @@ -0,0 +1,27 @@ +# RC adjacent DELETE: 99 rows deleted while A remains open. +affected +99 +remaining +0 +# RC unchanged boundary row: 50 rows deleted while A remains open. +affected +50 +remaining +0 +# RR control: adjacent DELETE still succeeds. +affected +99 +# RC UPDATE control: semi-consistent read behavior is unchanged. +affected +99 +sum_v +99 +# An inclusive upper bound must still wait for the matching id=100. +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +remaining +51 +# A missing end key: the next existing record id=101 is outside range. +affected +99 +remaining +0 diff --git a/mysql-test/t/rc_range_delete_boundary.test b/mysql-test/t/rc_range_delete_boundary.test new file mode 100644 index 0000000000000000000000000000000000000000..e8b03926c780463872ea33e873ffbd4554adf21a --- /dev/null +++ b/mysql-test/t/rc_range_delete_boundary.test @@ -0,0 +1,109 @@ +# Regression: out-of-range records must not block a READ COMMITTED DELETE. +# Unlike rc_range_delete_boundary_repro, this test expects the fix. +--disable_query_log +CREATE TABLE rc_boundary_seed (id INT NOT NULL PRIMARY KEY, v INT NOT NULL) + ENGINE=InnoDB; +--let $i=1 +while ($i <= 250) +{ + eval INSERT INTO rc_boundary_seed VALUES ($i, 0); + --inc $i +} +CREATE TABLE rc_boundary_t1 LIKE rc_boundary_seed; +INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id; +connect (a,localhost,root,,test); +connect (b,localhost,root,,test); +connection a; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +connection b; +SET autocommit=1; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +SET SESSION innodb_lock_wait_timeout=1; + +--echo # RC adjacent DELETE: 99 rows deleted while A remains open. +connection a; +BEGIN; +DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200; +connection b; +DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100; +SELECT ROW_COUNT() AS affected; +SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id < 100; +connection a; +ROLLBACK; +connection default; +TRUNCATE TABLE rc_boundary_t1; +INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id; + +--echo # RC unchanged boundary row: 50 rows deleted while A remains open. +connection a; +BEGIN; +SELECT id INTO @locked_id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE; +connection b; +DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id < 100; +SELECT ROW_COUNT() AS affected; +SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 50 AND id < 100; +connection a; +ROLLBACK; +connection default; +TRUNCATE TABLE rc_boundary_t1; +INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id; + +--echo # RR control: adjacent DELETE still succeeds. +connection a; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200; +connection b; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100; +SELECT ROW_COUNT() AS affected; +connection a; +ROLLBACK; +connection default; +TRUNCATE TABLE rc_boundary_t1; +INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id; + +--echo # RC UPDATE control: semi-consistent read behavior is unchanged. +connection a; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +DELETE FROM rc_boundary_t1 WHERE id >= 100 AND id < 200; +connection b; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +UPDATE rc_boundary_t1 SET v=v+1 WHERE id >= 1 AND id < 100; +SELECT ROW_COUNT() AS affected; +SELECT SUM(v) AS sum_v FROM rc_boundary_t1 WHERE id < 100; +connection a; +ROLLBACK; +connection default; +TRUNCATE TABLE rc_boundary_t1; +INSERT INTO rc_boundary_t1 SELECT * FROM rc_boundary_seed ORDER BY id; + +--echo # An inclusive upper bound must still wait for the matching id=100. +connection a; +BEGIN; +SELECT id INTO @locked_id FROM rc_boundary_t1 WHERE id=100 FOR UPDATE; +connection b; +--error ER_LOCK_WAIT_TIMEOUT +DELETE FROM rc_boundary_t1 WHERE id >= 50 AND id <= 100; +SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id >= 50 AND id <= 100; +connection a; +ROLLBACK; + +--echo # A missing end key: the next existing record id=101 is outside range. +connection default; +DELETE FROM rc_boundary_t1 WHERE id=100; +connection a; +BEGIN; +SELECT id INTO @locked_id FROM rc_boundary_t1 WHERE id=101 FOR UPDATE; +connection b; +DELETE FROM rc_boundary_t1 WHERE id >= 1 AND id < 100; +SELECT ROW_COUNT() AS affected; +SELECT COUNT(*) AS remaining FROM rc_boundary_t1 WHERE id < 100; +connection a; +ROLLBACK; +disconnect a; +disconnect b; +connection default; +DROP TABLE rc_boundary_t1, rc_boundary_seed; +--enable_query_log diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc index 66263ae7af587e4dd06cd7d0791619cac8ba04fd..c88247c2879390fa082916856d8822287eb169f8 100644 --- a/storage/innobase/row/row0sel.cc +++ b/storage/innobase/row/row0sel.cc @@ -4289,6 +4289,35 @@ static row_to_range_relation_t row_compare_row_to_range( return (row_to_range_relation); } + /* At READ COMMITTED a locking range scan must not wait for a record + that is already known to be beyond its end. The gap-lock shortcut below + otherwise returns before comparing the record with m_stop_tuple. + + Keep this check limited to forward scans of an ascending clustered index + with a complete unique-key end point. In particular, do not compare an + older version obtained by a semi-consistent read with the physical key. */ + if (trx->isolation_level == TRX_ISO_READ_COMMITTED && + prebuilt->row_read_type == ROW_READ_WITH_LOCKS && + !prebuilt->used_in_HANDLER && index == clust_index && + !dict_index_has_desc(index) && moves_up && + (mode == PAGE_CUR_GE || mode == PAGE_CUR_G) && + (direction == 0 || direction == ROW_SEL_NEXT) && + prebuilt->is_reading_range()) { + const auto stop_len = dtuple_get_n_fields_cmp(prebuilt->m_stop_tuple); + if (stop_len > 0 && stop_len == dict_index_get_n_unique(index)) { + ut_ad(prebuilt->m_mysql_handler->end_range != nullptr); + const auto cmp = + cmp_dtuple_rec(prebuilt->m_stop_tuple, rec, index, offsets); + if (cmp < 0 || + (cmp == 0 && prebuilt->m_mysql_handler->end_range->flag == + HA_READ_BEFORE_KEY)) { + row_to_range_relation.row_can_be_in_range = false; + row_to_range_relation.gap_can_intersect_range = false; + return (row_to_range_relation); + } + } + } + /* Try to place a lock on the index record; note that delete marked records are a special case in a unique search. If there is a non-delete marked record, then it is enough to lock its