A couple of days ago we were working on Bug #5892. The reporter complaint about random deadlocks with increasing traffic in OXSEO table where InnoDB is used. After debugging and analysing the InnoDB status message, he sorted out the culprit:
update oxseo set oxexpired = '1' where oxobjectid = '***'
This is because there is no index is set on oxobjectid, the whole table instead of just one row was blocked for the transaction.
As a solution @matene, the reporter, suggested to set the index via
ALTER TABLE oxseo ADD INDEX `OXOBJECTIDONLY` ( `OXOBJECTID` );
However, we couldn’t reproduce this issue. Furthermore, we found out that adding an index results in a major impact on our performance measures. The response time of our performance test setup increased by more than 100%.
It seems that adding an index can help fixing this particular issue, as long as product data etc. don’t change a lot. However, if you have more frequent changes in your basic data, then this could lead to the performance drop we encountered.
Therefore we cannot add this change into the default product setup. If you consider to add this change in your project, make sure to double check how often your data is changed, otherwise you might encounter performance issues.
Thanks @matene for this valuable input!