MySQL InnoDB并发删除关联表行引发死锁问题咨询

Hey there! Let's break down the behavior, potential pitfalls, and fixes for your concurrent DELETE operations on the tableAB junction table. First, let's align on your setup to make sure we're working with the same context:

• tableAB uses a composite primary key (A_id, B_id), has an index on B_id, but no dedicated single-column index on A_id
• Running MySQL with InnoDB storage engine and READ_COMMITTED isolation level (no gap locks here)
• Two transactions executing DELETE FROM tableAB WHERE A_id IN (someID) simultaneously

1. The Core Issue: No Dedicated Index on A_id = Unnecessary Lock Contention #

While your composite PK includes A_id, InnoDB's clustered index (backed by the PK) is optimized for filters that use the full leading column sequence. When running DELETE with only A_id in the WHERE clause, the optimizer might still fall back to a full table scan (especially if your IN list is large or the table has massive amounts of data). Here's how that impacts locking:

• InnoDB applies an exclusive (X) row lock to every row it scans during the delete.
• In READ_COMMITTED mode, locks on non-matching rows are released immediately after checking them (unlike REPEATABLE READ, which holds locks until the transaction ends). But even so, full table scans force both transactions to touch every row in the table—creating frequent lock collisions.

2. Lock Waits & Deadlock Risks #

• Lock Waits: If both transactions attempt to lock the same matching row at the same time, one will pause waiting for the other to release its X lock. These waits are usually short in READ_COMMITTED, but they add up quickly with frequent deletes or large tables, hurting performance.
• Deadlocks: Deadlocks are rare here but not impossible. For example, if Transaction 1 locks row R1 first then tries to lock R2, while Transaction 2 locks R2 first then tries to lock R1, you'll hit a deadlock. Full table scans typically follow a fixed order (like clustered index order), so this scenario is unlikely—but it becomes more probable with large datasets or other concurrent writes.

3. Practical Fixes to Optimize This Workflow #

Add a Dedicated Index on A_id (Top Priority!) #

This eliminates full table scans entirely, letting InnoDB jump straight to the rows you need to delete. Even though A_id is part of the composite PK, a single-column index on A_id makes the optimizer's job easier for queries filtering only on A_id. Run this:

CREATE INDEX idx_tableab_a_id ON tableAB(A_id);

Batch Large DELETE Operations #

If your someID list contains dozens or hundreds of values, split the DELETE into smaller batches (e.g., delete 50 rows at a time instead of 500). This reduces the time each transaction holds locks, minimizing contention windows.

Keep Transactions Short #

Avoid wrapping this DELETE in a long-running transaction. The shorter the transaction, the less time locks are held, and the lower the chance of conflicts with other concurrent operations.

4. How READ_COMMITTED Isolation Level Affects This #

Your choice of READ_COMMITTED helps a bit with lock contention:

• Locks on non-matching rows are released immediately after scanning, which reduces the total number of locks held at any time.
• You won't face repeatable read anomalies, though phantom reads are possible (but this is rarely a problem for targeted DELETE operations like this).

内容的提问来源于stack exchange，提问作者anuj