Description:
Hello MySQL Team,
I would like to clarify whether the following behavior is expected, or whether it may indicate a bug in InnoDB.
**Environment**
* MySQL 8.0.45
* InnoDB
* Transaction isolation: REPEATABLE READ (default)
**Scenario**
Two concurrent transactions execute an `INSERT` into the same table (`shipment_details`).
The table has:
* A primary key (`shipment_details_id`)
* A non-unique secondary index:
```sql
KEY job_item_id (job_item_id)
```
* **No foreign key constraints**, either referencing other tables or referenced by other tables.
The inserted values are different (for example, `25023551` and `25023552`), but they appear to fall on the same secondary index page.
Each transaction is part of an explicit transaction:
```sql
BEGIN;
UPDATE table_a ...
UPDATE table_b ...
UPDATE table_c ...
INSERT INTO shipment_details (...);
COMMIT;
```
The preceding `UPDATE` statements modify other tables only. They do **not** reference `shipment_details` or any of its indexes.
There are **no preceding `SELECT ... FOR UPDATE` or `SELECT ... FOR SHARE` statements** involving `shipment_details`.
**Observed deadlock**
The deadlock shows both transactions holding a shared lock on the same secondary index page's `supremum` record and waiting for an insert intention lock:
```
HOLDS:
lock mode S
index job_item_id
heap no 1
supremum
WAITING:
lock_mode X insert intention waiting
```
Both transactions show the same lock pattern, and InnoDB rolls back one transaction due to deadlock.
**Questions**
1. Is it expected that two concurrent `INSERT` statements, without any preceding locking reads (`SELECT ... FOR UPDATE` or `SELECT ... FOR SHARE`), can deadlock in this manner?
2. Given that the table has **no foreign key constraints**, is it expected that an `INSERT` alone can acquire a shared (`S`) lock on the `supremum` pseudo-record of a secondary index?
3. Is the shared (`S`) lock on the `supremum` pseudo-record acquired as part of the normal B-tree insertion algorithm, or does it imply that another operation earlier in the transaction contributed to the lock?
4. Is this behavior expected for concurrent inserts into a non-unique secondary index when both inserts target the same leaf page?
5. Is there any documentation that explains why an `INSERT` alone can hold an `S` lock on the `supremum` record before requesting the insert intention lock?
My understanding from the InnoDB documentation is that next-key locks are commonly associated with locking reads and range scans. Since this table has no foreign keys and there are no locking reads against it within the transaction, I would like to better understand whether this locking pattern is an expected part of InnoDB's insert algorithm.
Thank you for any clarification.
How to repeat:
This issue is not reliably reproducible in a minimal or single-machine test environment.
It occurs in production under concurrent load when multiple application instances insert into the same InnoDB table simultaneously.
Conditions observed in production:
MySQL 8.0.45 (InnoDB)
REPEATABLE READ isolation level (default)
Multiple concurrent application nodes (stateless services)
High insert throughput into the same table
Inserts contain sequentially increasing values for a secondary index (job_item_id)
No preceding SELECT ... FOR UPDATE or SELECT ... FOR SHARE on the affected table
No foreign key constraints on the table
Observed behavior:
Concurrent INSERT statements deadlock on the same secondary index (job_item_id)
InnoDB reports both transactions holding an S lock on the supremum record of the same index page
Both transactions wait for insert intention (X) lock
One transaction is rolled back by InnoDB deadlock detection
Notes:
The issue is intermittent and depends on concurrency timing
Attempts to reproduce using simplified local test cases have not succeeded
The deadlock is consistently observed only in production traffic patterns
© 2026, Oracle Corporation and/or its affiliates