How Can I Prevent 'Deadlock Found When Trying to Get Lock' Errors in My MySQL Online User Tracking System?

Avoiding 'Deadlock Found When Trying to Get Lock' in MySQL

Issue:

A developer encounters intermittent 'Deadlock found when trying to get lock; try restarting transaction' errors when running INSERTs into an InnoDB table that tracks online user activities. The table is updated on page refresh and cleared every 15 minutes by a cron job.

Queries in Question:

• First visit: INSERT INTO onlineusers (ip, datetime, userid, page, area, type) VALUES (ip, now(), userid, '/thispage', 'thisarea', 3)
• Page refresh: UPDATE onlineusers SET ip = ip, datetime = now(), userid = userid, page = '/thispage', area = 'thisarea', type = 3 WHERE id = 888
• Cron job every 15 minutes: DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND

Solution:

To resolve deadlocks, it's crucial to ensure that transactions lock keys in a consistent order. Here are the recommended steps:

1. Order Keys in Queries:

   • Ensure that all queries that lock multiple keys do so in ascending order for the keys.

2. Sort Delete Statements:

   • Modify the DELETE statement to include a subquery that retrieves the primary key values (in this case, id) in ascending order, like this:

   DELETE FROM onlineusers
   WHERE id IN (
       SELECT id
       FROM onlineusers
       WHERE datetime <= now() - INTERVAL 900 SECOND
       ORDER BY id
   ) u;

3. Retry Mechanism:

   • Consider implementing a retry mechanism in the client code for this specific error, allowing it to automatically retry a limited number of times.

The above is the detailed content of How Can I Prevent 'Deadlock Found When Trying to Get Lock' Errors in My MySQL Online User Tracking System?. For more information, please follow other related articles on the PHP Chinese website!