0

I have beed reading quite a lot about he InnoDB engine internals and its locking mechanism (different locks and types of deadlocks)

I've seen this error when doing update operation:

'Deadlock found when trying to get lock; try restarting transaction'

when performing update operation and followed what is advised here to create an index on field called resource_id.

I wonder if there is a difference/ added value to avoid deadlocks in MySql DB, by adding new Index on a column that is already defined as PrimaryKey.

Why MySql does not block you to add Index on the same column that is already primary key? this leads me to the understanding that there is a difference.

Tal Avissar
  • 10,088
  • 6
  • 45
  • 70
  • So there's really two questions here: the deadlock question, and using an index on top of a primary key. For the latter, note that a primary key [automatically has an associated index](https://dev.mysql.com/doc/refman/8.0/en/primary-key-optimization.html), and for InnoDB will store the primary key data in a btree (much like an index). I believe there would not really be a benefit, but you're correct that an index can be added on top of a primary key. It may be a quirk of the engine's design, since restricting it in that case may have added unnecesssary complexity. – Rogue Jul 29 '22 at 17:56

1 Answers1

1

There has been some talk about disallowing any truly redundant indexes.

It is, however, debatable whether PRIMARY KEY(x), INDEX(x) is really redundant. For example, if you have a query that only needs x, then the INDEX would be preferable to the seeming equivalent PK.

There is a big difference. The PK is "clustered" with the data. That is, the PK is not stored separately, but, instead, the data is stored in PK order. This obviates the need for a separate BTree for just the PK.

Except for the rare example I gave above.

On the other hand, I can think of no justification for INDEX(x), INDEX(x) except for failure to catch it as redundant. Note that in creating a FOREIGN KEY, which does create an INDEX, there is a real attempt to avoid creating another index.

Note that INDEX(x) should in virtually all cases be removed if you also have INDEX(x,y) That does not apply with UNIQUE(x), INDEX(x,y). And, since the PK is effectively a UNIQUE index, there is an obvious correlary.

Shall I go on? There may be a dozen more subtle points that are somewhat related to your question.

I think the developers see your request as "low priority". There's an old saying "If it ain't broke, don't fix it." For MySQL, I will paraphrase it as "If it ain't broke much, don't bother fixing it; find something more important to work on."

Rick James
  • 135,179
  • 13
  • 127
  • 222