In a period of two weeks, I had two cases with the exact same symptoms.
Database users were experiencing low responsiveness. DBAs were seeing locks occurring on seemingly normal tables. In particular, looking at Innotop, it seemed that INSERTs were causing the locks.
In both cases, tables were InnoDB. In both cases, there was a PRIMARY KEY on the combination of all 5 columns. And in both cases, there was no clear explanation as for why the PRIMARY KEY was chosen as such.
Choosing a proper PRIMARY KEY
Especially with InnoDB, which uses clustered index structure, the PRIMARY KEY is of particular importance. Besides the fact that a bloated PRIMARY KEY bloats the entire clustered index and secondary keys (see: The depth of an index: primer), it is also a source for locks. It’s true that any UNIQUE KEY can serve as a PRIMARY KEY. But not all such keys are good candidates.
Reducing the locks
In both described cases, the solution was to add an AUTO_INCREMENT column to serve as the PRIMARY KEY, and have that 5 column combination under a secondary UNIQUE KEY. The impact was immediate: no further locks on that table were detected, and query responsiveness turned very high.
It doesn’t appear that we fully understand this problem even though we have a solution that seems to work. I don’t see why a clustered index on the entire column should lead to more locking. also, without secondary indexes, a hug primary index won’t lead to more bloat.
@Seun,
You are right that without a secondary index there wouldn’t be a bloat. The two issues I tackled did have secondary keys. Thanks for pointing this out.
I’m not an expert on the InnoDB internals, so let me explain this only in general: the reason is that InnoDB manager locks on the PK level, locking branches in the BTree. If you happen to attempt using the same branch (this does not necessarily mean you are now INSERTing two very close rows), then you are susceptible to locking.
I should look more closely into this myself.