MySQL死锁机制及主键列额外创建索引的价值与合理性探讨

Great question—let’s break this down step by step since you’re already deep into InnoDB’s locking mechanics and deadlock resolution. I’ll cover both your core concerns below:

Short answer: No, it doesn’t provide any extra value for deadlock avoidance, and can even introduce unnecessary overhead. Here’s why:

• InnoDB uses a clustered index for the primary key—this means all row data is stored directly in the primary index’s leaf nodes. Any secondary index (including one created on the primary key column) only stores the primary key values in its leaf nodes, and has to perform a "bookmark lookup" to fetch the actual row data from the clustered index.
• When executing update/delete operations, InnoDB always locks the corresponding row in the clustered index, regardless of which index you use to locate the row. So whether you use the primary key directly or a secondary index on the same column, the lock scope and behavior are identical.
• Deadlocks occur when transactions acquire locks in inconsistent order. Since a secondary index on the primary key column has the exact same sort order as the clustered index, using either index will result in the same lock acquisition sequence for rows. There’s no way this duplicate index would change the order of lock requests, so it can’t reduce deadlock risk.
• Worse, maintaining a duplicate index adds unnecessary overhead: every write operation (insert/update/delete) will have to modify both the clustered index and the secondary index, increasing I/O, memory usage, and lock contention on index structures.

MySQL (and specifically InnoDB) allows this for a few key reasons, balancing flexibility, compatibility, and architectural design:

• Historical compatibility: Early versions of MySQL didn’t enforce checks for redundant indexes, and removing this ability could break legacy applications or scripts that rely on creating such indexes intentionally (even if unnecessarily).
• Architectural separation: The MySQL server layer handles index metadata management, while the InnoDB storage engine manages index storage and locking. The server layer doesn’t actively validate if an index is redundant with the primary key—this is left to the database administrator to monitor and clean up.
• Edge case flexibility: While rare, some users might have niche use cases where a duplicate index could serve a purpose (e.g., forcing the optimizer to use a non-clustered index for specific queries, though this is almost never necessary). MySQL prioritizes giving users control over their schema, even if it allows inefficient choices.
• Tooling for cleanup: MySQL provides built-in tools to detect redundant indexes, like the sys.schema_redundant_indexes view. This lets you identify and remove unnecessary indexes without the database enforcing restrictions upfront.

Quick note on your deadlock issue #

Since you already added an index on resource_id (which is the right move—without it, InnoDB would use a full table scan and add gap locks across large ranges, increasing deadlock risk), focusing on consistent lock acquisition order across all transactions is your best bet. For example, always update rows in the same order (e.g., ascending resource_id or primary key) to eliminate circular wait conditions.

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