How Can I Effectively Avoid MySQL Deadlocks in an Online User Table?

Avoiding MySQL Deadlocks in a Table of Online Users

The occurrence of "Deadlock found when trying to get lock; try restarting transaction" errors can be a frustrating issue. Such deadlocks arise when multiple transactions attempt to acquire locks on resources in a conflicting order. To address this problem, consider the following solutions:

Sorting Operations

Deadlocks often occur due to operations that attempt to lock keys in different orders. Establish a consistent order for locking keys by sorting your queries accordingly. For instance, if you have multiple queries that lock both key(1) and key(2), ensure they always obtain the locks in the same order.

Order DELETE Query

Your DELETE query should also be modified to work in an ascending order. The following optimized query will achieve this:

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

Automatic Client Retries

MySQL documentation recommends automatic client retries for deadlocks. Implement this logic in your client code, setting a specific number of retries (e.g., 3) before giving up.

The above is the detailed content of How Can I Effectively Avoid MySQL Deadlocks in an Online User Table?. For more information, please follow other related articles on the PHP Chinese website!