Duplicate Often when a username is changed lock timeout mysql errors are generated

[HJW]

Affected version

2.1

Hi,

It seems that most of the time when a username is updated I get lots of lock timeout errors.

This is generated when the user name is changed:

XF\Db\Exception: Job XF:UserRenameCleanUp: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction src/XF/Db/AbstractStatement.php:228
Generated by: Unknown account Dec 21, 2020 at 4:01 PM
Stack trace
            UPDATE (
                SELECT content_id FROM xf_reaction_content
                WHERE content_type = ?
                AND reaction_user_id = ?
            ) AS temp
            INNER JOIN xf_post AS reaction_table ON (reaction_table.`post_id` = temp.content_id)
            SET reaction_table.`reaction_users` = REPLACE(reaction_table.`reaction_users`, ?, ?)

and then often get about 20 lock wait timeout's all for inserting a reaction:

 

[Mike]

The manifestation is a little different here, but this is already changed in 2.2:

Fixed - Database error when attempting to merge users

XF\Db\DeadlockException: MySQL query error [1213]: Deadlock found when trying to get lock; try restarting transaction src/XF/Db/AbstractStatement.php:217 UPDATE ( SELECT content_id FROM xf_reaction_content WHERE content_type = ? AND...

xenforo.com

(Which the similar threads list actually pointed me to.)

The new indexes would improve the speed and locking characteristics of this query.