[解決済み】MySQLの「ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してみてください」を回避する方法

質問

オンラインユーザーを記録する innoDB テーブルがあります。これは、ユーザーがページを更新するたびに更新され、どのページにいるか、サイトに最後にアクセスした日を記録しています。私は、古いレコードを削除するために15分ごとに実行されるクーロンを持っています。

昨夜、5分ほど「Deadlock found when trying to get lock; try restarting transaction」というメッセージが表示されましたが、このテーブルへのINSERTを実行したときに発生したようです。このエラーを回避する方法をどなたか教えていただけませんか？

=== EDIT ===編集

以下は実行中のクエリです。

サイトへの初回訪問。

INSERT INTO onlineusers SET
ip = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3

ページ更新の都度

UPDATE onlineusers SET
ips = 123.456.789.123,
datetime = now(),
userid = 321,
page = '/thispage',
area = 'thisarea',
type = 3
WHERE id = 888

15分ごとにクーロン。

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND

そして、いくつかの統計（例えば、オンライン・メンバー、オンライン・ビジター）を記録するために、いくつかのカウントを行います。

どのように解決するのですか？

ほとんどのデッドロックに対処できる簡単なトリックとして、操作を特定の順序に並べ替えることがあります。

2つのトランザクションが2つのロックを逆の順番でロックしようとすると、デッドロックが発生します（例:

• connection 1: キー(1)をロックし、キー(2)をロックします。
• コネクション2：キー(2)をロック、キー(1)をロック。

両方が同時に実行された場合、接続1は鍵(1)をロックし、接続2は鍵(2)をロックし、それぞれの接続は相手が鍵を解放するのを待ちます -> デッドロックとなります。

ここで、接続が同じ順番でキーをロックするようにクエリを変更した場合、例えば、以下のようになります。

• 接続1：キー(1)をロックし、キー(2)をロックする。
• connection 2: ロックキー( 1 ), ロックキー( 2 );

を使えば、デッドロックになることはないでしょう。

そこで、こんな提案をしています。

1. deleteステートメントを除いて、一度に複数のキーにアクセスするようなクエリがないことを確認してください。

2. delete文が昇順で動作するように修正する。

変更

DELETE FROM onlineusers 
WHERE datetime <= now() - INTERVAL 900 SECOND

への

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

もうひとつ覚えておきたいのは、MySQLのドキュメントによると、デッドロックが発生した場合、クライアントは自動的に再試行する必要があるようです。(例えば、この特定のエラーに対して、あきらめる前に3回リトライする)。