Database deadlock on characters creation

[walkline]

Current Behaviour

I'm making some load testing, and when approximately 150 clients attempt to create characters, I observe some deadlocks in the database. These deadlocks freeze the server for several seconds, but after some time and once all characters are created, it continues to operate as usual.

SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14761, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction
SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14790, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction
SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14790, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction
SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14790, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction
SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14790, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction
SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14790, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction
SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14790, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction
SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14790, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction
SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14790, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction
SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14766, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction
SQL(p): INSERT INTO character_reputation (guid, faction, standing, flags) VALUES (14778, 87, 0 , 2)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction

SQL(p): INSERT INTO character_achievement_progress (guid, criteria, counter, date) VALUES (15237, 9598, 1, 1710274349)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction

SQL(p): INSERT INTO character_aura (guid, casterGuid, itemGuid, spell, effectMask, recalculateMask, stackcount, amount0, amount1, amount2, base_amount0, base_amount1, base_amount2, maxDuration, remainTime, remainCharges) VALUES (14918, 14918, 0, 2457, 3, 3, 1, 0, 10, 0, -1, 9, 0, -1, -1, 0)
 [ERROR]: [1213] Deadlock found when trying to get lock; try restarting transaction

Expected Blizzlike Behaviour

No deadlock

Source

No response

Steps to reproduce the problem

I'm using modified version of https://github.com/idewave/tentacli to perform load testing.

Extra Notes

No response

AC rev. hash/commit

6b5289f

Operating system

macOS

Custom changes or Modules

No response

[privatecore]

This issue (or better said — behavior) is more about MySQL itself: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-insert-intention-locks

Possible solutions: https://medium.com/assoconnect/dealing-with-deadlocks-on-insert-requests-59eb93ef8dce https://mariadb.com/kb/en/data-warehousing-high-speed-ingestion/

Second link is more interesting in case changes can be made to the core-related logic and transactions. Also, you can try a different DB engine and/or play around with table's schema.