I’m working with an InnoDB table that tracks active users on my website. The table gets updated whenever someone loads a page to record their current location and last activity time. I also have a scheduled job that runs every 15 minutes to clean up old entries.
Yesterday I encountered a deadlock error that lasted about 5 minutes during INSERT operations. How can I prevent this from happening again?
New user session:
INSERT INTO active_sessions SET
client_ip = '192.168.1.100',
last_seen = now(),
user_id = 456,
current_url = '/dashboard',
section = 'admin',
status = 1
Page navigation updates:
UPDATE active_sessions SET
client_ip = '192.168.1.100',
last_seen = now(),
user_id = 456,
current_url = '/dashboard',
section = 'admin',
status = 1
WHERE session_id = 777
Cleanup task:
DELETE FROM active_sessions WHERE last_seen <= now() - INTERVAL 900 SECOND
After cleanup, the system runs some queries to generate statistics like total active users and guest count.
Your cleanup is probably scanning the whole table without proper ordering - that’s what’s causing the deadlocks. I had the same issue with a booking system where sessions would timeout during busy periods. Fixed it by restructuring the cleanup query to process records in primary key order. This makes InnoDB grab locks predictably and cuts down on deadlocks. Add ORDER BY session_id to your DELETE and use LIMIT for smaller chunks. Also helped to separate stats generation from cleanup - run your stats queries before cleanup starts, not after. The table’s more stable then. Check your innodb_deadlock_detect setting too. Sometimes turning it off and using lock timeouts works better for high-concurrency stuff like this.
Deadlocks happen when your cleanup process fights with active session updates. The DELETE grabs locks that mess with concurrent INSERTs and UPDATEs. I hit this same issue last year - fixed it by indexing the last_seen column first. Made the cleanup query way faster and cut down lock time. Also, don’t delete all expired records at once. Break it into smaller batches like DELETE FROM active_sessions WHERE last_seen <= now() - INTERVAL 900 SECOND LIMIT 1000 in a loop. Works much better. Another trick: use INSERT … ON DUPLICATE KEY UPDATE for session tracking instead of separate INSERT/UPDATE operations. Cuts down lock conflicts during heavy traffic.
Your cleanup job timing is causing the deadlock. When cleanup runs while users are browsing, the DELETE operation fights with session updates for locks. I hit this same issue on a high-traffic app and fixed it by moving cleanup to early morning when traffic’s low. Try a two-phase approach - mark records as expired with a status flag first, then delete them later in the background. This cuts down how long locks are held. Make sure you’ve got indexes on user_id and last_seen columns. Your UPDATE should use session_id as the primary key, but if it’s not, index that too. Also check your innodb_lock_wait_timeout setting - it defaults to 50 seconds but you might need to adjust it for your app.
I’ve dealt with the same session tracking deadlocks. Switch to INSERT IGNORE instead of regular INSERT - it won’t block when the record’s already there. Also try partitioning your table by date so cleanup only hits older partitions. Fixed it for me with thousands of users hammering the same table.