Running ranking aggregation, mass mail sends, or old-data cleanup during live service ties up locks and the disk.
Why Bulk jobs run during service hours → Effect Wide-range locks, disk and CPU tied up → On screen Failed trades and saves, slow loading at certain times of day
Primary owner Game team (Server development) · Also Infra team (DB infrastructure)
Game team action items
Split jobs into small chunks and run them a little at a time, run aggregation on a replica.
Infra team action items
Provide a replica for aggregation, schedule batches for quiet hours, watch for lock escalation and gap lock waits.
On the graph
Periodic spikes · DB query latency, lock waits
Where to look
Find long queries running at the time of the lag. MySQL: slow query log. PostgreSQL: query_start and query in pg_stat_activity. Match them against lock wait metrics at the same time and the batch schedule (cron, DB event scheduler). SQL Server: record lock escalation with the lock_escalation extended event
Confirmed if
Large UPDATE, DELETE, or aggregation queries run at the same time every time, and lock waits and disk utilization rise together meanwhile
Ruled out if
No long queries at that time: checkpoints (db-checkpoint) or a server backup (dk-backup)
Check with
Infra tools (no game code needed)
Learn more
SQL Server converts to a table lock when one statement holds more than about 5,000 row locks (lock escalation). At that moment, every request using the same table stops. MySQL, with default settings, also locks the gaps between rows when modifying by a range condition (gap locks), blocking inserts of new rows.
Sources
Transaction Locking and Row Versioning GuideMicrosoft SQL Server Lock escalation when one statement holds 5,000 or more locks on one table (or index); recorded with the lock_escalation extended event
InnoDB LockingMySQL At InnoDB’s default isolation level, REPEATABLE READ, searches and scans use next-key locks, so gap locks block inserts of new rows into those gaps
The Slow Query LogMySQL Logs queries exceeding long_query_time with execution time (Query_time), lock time (Lock_time), and rows read