Why Parallel DELETEs on a MySQL Table Trigger Lock Wait Timeouts

Background

The company needed to convert the primary key of many legacy tables from int to bigint. During the audit a table with about 500 million rows was found. Although the table had a date index and was queried frequently, it never appeared in the slow‑query list.

Proposed Solution

Because the 2021 data was no longer needed, the plan was to archive it to a new table, delete the 2021 rows, and then run a ALTER TABLE … MODIFY COLUMN … BIGINT statement. To avoid a long transaction, the INSERT and DELETE statements were split into 100 separate batches each and handed to the DBA for execution.

Lock Issue Encountered

When the DBA executed the batch of DELETE statements in parallel, several of them failed with a lock‑wait timeout, even though no business process was accessing the 2021 data at that moment. The DBA initially argued that the id ranges were disjoint, but the lock logs showed otherwise.

delete from yes where date < '2022-06-25' and (id >= 1 and id < 10)</code>
<code>delete from yes where date < '2022-06-25' and (id >= 10 and id < 20)</code>
<code>delete from yes where date < '2022-06-25' and (id >= 20 and id < 30)

The information_schema.innodb_locks view revealed an exclusive (X) row lock on the primary‑key index for id = 3, confirming that the concurrent deletes were contending for the same record.

Small‑Data Experiment

A minimal reproducible example was created:

CREATE TABLE `yes` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

With only five rows in the table, two overlapping DELETE statements were run from separate clients:

DELETE FROM yes WHERE date < '2022-06-25' AND (id > 1 AND id < 3);</code>
<code>DELETE FROM yes WHERE date < '2022-06-25' AND (id >= 3 AND id < 5);

The second statement blocked. Querying information_schema.innodb_locks showed an X lock on the primary‑key index for id = 3. An EXPLAIN of the first DELETE confirmed that MySQL used the primary‑key index, not the date index, and that the range lock extended to the first row that failed the id > 1 condition.

Large‑Data Experiment

To verify the behavior on a realistic scale, additional columns were added and 10 million rows were inserted. Two large‑range DELETE statements were executed in parallel:

DELETE FROM yes WHERE date < '2022-06-25' AND (id > 1 AND id < 100000);</code>
<code>DELETE FROM yes WHERE date < '2022-06-25' AND (id >= 100000 AND id < 200000);

Again the second delete waited on the boundary row id = 100000, producing the same lock‑wait timeout as observed in the production incident.

Conclusion

Under the default REPEATABLE READ isolation level, MySQL’s range lock for a DELETE includes the first record that does not satisfy the range predicate. When multiple deletes operate on adjacent id ranges, they inevitably lock the same boundary row, causing lock contention and eventual lock‑wait timeouts. To avoid this, avoid parallel deletes on contiguous ranges, split the work into non‑overlapping transactions, or use a lower isolation level.