オンライン ユーザーを記録する innoDB テーブルがあります。このテーブルは、ユーザーがページを更新するたびに更新され、ユーザーが現在どのページにいるか、またサイトへの最終アクセス日が記録されます。また、15 分ごとに実行される cron を実行して、古いレコードを削除します。
昨夜、約 5 分間「ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください」というメッセージが表示されました。このエラーは、このテーブルに INSERT を実行しているときに発生したようです。このエラーを回避する方法を提案してもらえますか?
=== 編集 ===
実行中のクエリは次のとおりです。
サイトへの最初の訪問:
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分ごとにCronを実行:
DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
次に、いくつかのカウントを行って、いくつかの統計情報（オンラインのメンバー、オンラインの訪問者など）を記録します。
ベストアンサー1
ほとんどのデッドロックを解決するのに役立つ簡単なトリックの 1 つは、操作を特定の順序で並べ替えることです。
2 つのトランザクションが反対の順序で 2 つのロックをロックしようとすると、デッドロックが発生します。
- 接続1: キー(1)をロックし、キー(2)をロックします。
- 接続2: キー(2)をロックし、キー(1)をロックします。
両方が同時に実行されると、接続 1 はキー (1) をロックし、接続 2 はキー (2) をロックし、各接続は他方がキーを解放するまで待機します -> デッドロック。
ここで、接続が同じ順序でキーをロックするようにクエリを変更した場合、次のようになります。
- 接続1: キー(1)をロックし、キー(2)をロックします。
- 接続2: キー( 1 )をロックし、キー( 2 )をロックします。
デッドロックを起こすことは不可能になります。
そこで私が提案するのは次のことです:
削除ステートメントを除いて、一度に複数のキーへのアクセスをロックする他のクエリがないことを確認してください。そのようなクエリがある場合 (おそらくあると思います)、WHERE in (k1,k2,..kn) を昇順に並べ替えます。
削除ステートメントを昇順で動作するように修正します。
変化
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;
覚えておくべきもう 1 つの点は、MySQL のドキュメントでは、デッドロックが発生した場合、クライアントは自動的に再試行する必要があると示されていることです。このロジックをクライアント コードに追加できます (たとえば、この特定のエラーでは、諦める前に 3 回再試行します)。