When two transactions (groups of DB operations processed as one unit) each wait for a row the other has locked, the DB forcibly cancels one of them.
Why Trade A locks in item→currency order, trade B in currency→item order → Effect The DB detects the deadlock and rolls back one side → On screen Trades and crafting fail now and then, items revert
Primary owner Game team (Server development) · Also Infra team (DB infrastructure)
Game team action items
Use a consistent lock order, keep transactions short, retry automatically on failure.
Infra team action items
Keep deadlock detection on, collect and share deadlock records, lower the lock wait timeout (default 50 seconds) on MySQL servers with detection turned off.
Ballpark numbers
Detection is almost instant in MySQL (InnoDB), takes 1 second by default in PostgreSQL, and up to about 5 seconds in SQL Server. Both requests stay stuck in the meantime. On a MySQL server with detection turned off because of very high concurrency, requests wait until the lock wait timeout (default 50 seconds).
On the graph
Random spikes · Deadlock count, failed trade count
Where to look
MySQL: LATEST DETECTED DEADLOCK in SHOW ENGINE INNODB STATUS (the most recent one only), every deadlock in the error log with innodb_print_all_deadlocks on, and lock_deadlocks in INFORMATION_SCHEMA.INNODB_METRICS. PostgreSQL: deadlocks in pg_stat_database. SQL Server: xml_deadlock_report from the system_health session, which is on by default. Error codes on the game server side: MySQL 1213, PostgreSQL 40P01, SQL Server 1205
Confirmed if
Deadlock count rises at the times trades and crafting fail, and the two recorded transactions lock the same tables in opposite order
Ruled out if
Failures with an unchanged deadlock count: lock wait timeout exceeded (MySQL error 1205) or a hot row (db-hot-row)
Check with
Infra tools (no game code needed)
Sources
InnoDB Startup Options and System VariablesMySQL With detection on (the default), InnoDB detects deadlocks immediately and rolls one back; innodb_lock_wait_timeout defaults to 50 seconds
Deadlock DetectionMySQL At very high concurrency, detection itself can slow things down, so it is sometimes turned off in favor of the lock wait timeout
Deadlocks guideMicrosoft SQL Server Deadlock checks run every 5 seconds by default, dropping to as little as 100 ms when deadlocks are frequent; the system_health session, on by default, collects xml_deadlock_report; the victim gets error 1205
How to Minimize and Handle DeadlocksMySQL Always modify multiple rows and tables in the same order, retry on failure, log every deadlock with innodb_print_all_deadlocks
InnoDB Standard Monitor and Lock Monitor OutputMySQL LATEST DETECTED DEADLOCK: the two transactions in the most recent deadlock, the locks they held and waited for, and which one was rolled back