問題描述
我有一個記錄在線用戶的 innoDB 表.它會在用戶每次刷新頁面時更新，以跟蹤他們所在的頁面以及他們上次訪問站點的日期.然后我有一個 cron，它每 15 分鐘運行一次以刪除舊記錄.
I have a innoDB table which records online users. It gets updated on every page refresh by a user to keep track of which pages they are on and their last access date to the site. I then have a cron that runs every 15 minutes to DELETE old records.
我在嘗試獲取鎖時發現了死鎖"；昨晚嘗試重新啟動事務大約 5 分鐘，似乎是在將 INSERT 運行到此表中時.有人可以建議如何避免此錯誤嗎?
I got a 'Deadlock found when trying to get lock; try restarting transaction' for about 5 minutes last night and it appears to be when running INSERTs into this table. Can someone suggest how to avoid this error?
=== 編輯 ===
以下是正在運行的查詢:
Here are the queries that are running:
首次訪問網站:
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
然后它會記錄一些統計數據(即:在線成員、在線訪問者).
It then does some counts to log some stats (ie: members online, visitors online).
推薦答案
一個可以幫助解決大多數死鎖的簡單技巧是按特定順序對操作進行排序.
One easy trick that can help with most deadlocks is sorting the operations in a specific order.
當兩個事務試圖以相反的順序鎖定兩個鎖時，您會遇到死鎖，即:
You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:
- 連接1:鎖鑰匙(1)，鎖鑰匙(2)；
- 連接2:鎖鑰匙(2)，鎖鑰匙(1)；
如果兩者同時運行，連接 1 將鎖定 key(1)，連接 2 將鎖定 key(2)，并且每個連接都將等待對方釋放密鑰 ->死鎖.
If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock.
現在，如果您更改查詢，使連接以相同的順序鎖定密鑰，即:
Now, if you changed your queries such that the connections would lock the keys at the same order, ie:
- 連接1:鎖鑰匙(1)，鎖鑰匙(2)；
- 連接2:鎖鑰匙(1)，鎖鑰匙(2);
- connection 1: locks key(1), locks key(2);
- connection 2: locks key(1), locks key(2);
不可能陷入僵局.
所以這就是我的建議:
確保除了刪除語句之外，沒有其他查詢會一次鎖定多個鍵的訪問.如果您這樣做(我懷疑您這樣做)，請按升序排列 (k1,k2,..kn) 中的 WHERE.
Make sure you have no other queries that lock access more than one key at a time except for the delete statement. if you do (and I suspect you do), order their WHERE in (k1,k2,..kn) in ascending order.
修復您的刪除語句以按升序工作:
Fix your delete statement to work in ascending order:
改變
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 次).
Another thing to keep in mind is that MySQL documentation suggest that in case of a deadlock the client should retry automatically. you can add this logic to your client code. (Say, 3 retries on this particular error before giving up).
這篇關于如何避免 MySQL '嘗試獲取鎖時發現死鎖；嘗試重新啟動事務'的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！