MySQL InnoDB Deadlock Analysis and Resolution for a Video‑Conference Service

The video‑conference service occasionally failed to create meetings because MySQL reported a deadlock. By running show engine innodb status the full deadlock log was extracted, showing two transactions: Transaction 1 holding a row lock on session while waiting for a lock, and Transaction 2 holding a lock on session_endpoint while waiting for an insert‑intention lock.

Key observations from the log:

Transaction 1 deletes rows from session and is blocked on an X lock.

Transaction 2 inserts a row into session_endpoint and is blocked on a gap lock (insert‑intention).

The database isolation level is REPEATABLE‑READ (default) on MySQL 5.7 with InnoDB.

The business code deletes session endpoints first and then sessions within a single @Transactional method:

@Transactional(rollbackFor = Exception.class)
public void clearSessions(List<UUID> sessionIds) {
    sessionEndpointRepository.deleteSessionEndpointBySessionIds(sessionIds);
    sessionRepository.deleteSessionByIds(sessionIds);
}

Because the DELETE on session_endpoint acquires a record lock on the rows being removed, and the subsequent INSERT on the same table acquires an insert‑intention lock, a conflict occurs when the INSERT tries to place a new record into a page that still contains a deleted (but not yet purged) row. The log shows the deleted‑record flag (info bits 32) confirming this situation.

To verify the hypothesis, a reproducible test was built:

Insert a set of sessions with known binary IDs.

Start two transactions: T2 inserts a new session, T1 then attempts the batch delete.

Observe that T1 blocks on the delete of the last ID because the page still contains the just‑inserted row from T2.

Debugging the MySQL source (files storage/innobase/lock/lock0lock.cc, sql/sql_delete.cc, etc.) showed that the delete operation acquires LOCK_X | LOCK_ORDINARY on each record. When the cursor reaches the newly inserted record (still locked by T2), lock_rec_lock_slow detects the conflict and blocks the thread.

The root cause is the order of operations on the two tables. By swapping the delete order so that session rows are removed before session_endpoint rows, the conflicting lock scenario disappears:

@Transactional(rollbackFor = Exception.class)
public void clearSessions(List<UUID> sessionIds) {
    sessionRepository.deleteSessionByIds(sessionIds);
    sessionEndpointRepository.deleteSessionEndpointBySessionIds(sessionIds);
}

After applying the change, automated stress tests that repeatedly create and delete empty meetings no longer produce deadlocks, confirming the fix.

Open questions remain about InnoDB’s internal cursor behavior when processing the last record of a batch delete, but the primary deadlock has been resolved.

References:

https://www.aneasystone.com/archives/2018/04/solving-dead-locks-four.html

https://juejin.cn/post/6844903848385970189

https://github.com/aneasystone/mysql-deadlocks