How to Resolve MySQL Deadlock Errors Caused by Conflicting Transaction Locks?

MySQL 'Deadlock' Error Resolution

MySQL's "Deadlock found when trying to get lock" error occurs when concurrent transactions attempt to lock resources in opposing sequences. To resolve this, ensure that all queries locking multiple keys do so in an ascending order.

In your case, the issue may lie with the INSERT and UPDATE queries that lock multiple keys. Consider ordering the WHERE clause in ascending order for both queries:

First Visit to Site:

INSERT INTO onlineusers (ip, datetime, userid, page, area, type)
VALUES (123.456.789.123, now(), 321, '/thispage', 'thisarea', 3)
ORDER BY id;

On Each Page Refresh:

UPDATE onlineusers (ip, datetime, page, area, type)
SET ip = 123.456.789.123,
    datetime = now(),
    page = '/thispage',
    area = 'thisarea',
    type = 3
WHERE id = 888
ORDER BY id;

Additionally, rewrite the DELETE query to order by id:

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

By enforcing an ascending order on WHERE clauses, you ensure that all transactions lock keys in the same sequence, preventing deadlocks. Consider incorporating client-side retry logic to handle potential retries in case of deadlocks.

The above is the detailed content of How to Resolve MySQL Deadlock Errors Caused by Conflicting Transaction Locks?. For more information, please follow other related articles on the PHP Chinese website!