作者paranoia5201 (社會黑暗、行路難)
看板Database
標題[SQL ] 資料更新 Lock wait timeout 問題
時間Tue Aug 31 08:31:22 2021
資料庫名稱：MySQL
資料庫版本：8.0
內容/問題描述：一個資料表更新的問題，懇請大家建議。
有一個主表 MainTable，是紀錄每位使用者進入網頁的 lifetime 紀錄：
columns (user_id, first_at, last_at, device, landing_page, domain)
user_id: user_id,
first_at: 第一次進入時間,
last_at: 近一次進入時間,
device: 最常使用的 device,
landing_page: 最常進入的首頁,
domain: 最常使用的網域
在這裡的 last_at, device, landing_page, domain 是要求每天更新，
後三者要填入的資訊更是會頻繁改變。現在是針對後三者各建立一個表，
每天計算 count 並利用 row_number 重新排名，如下：
deviceTable (user_id, device, freq, ranking)
landingTable (user_id, landing_page, freq, ranking)
domainTable (user_id, domain, freq, ranking)
而後再用 UPDATE 語法更新 MainTable，如下（以 deviceTable 為例）：
UPDATE MainTable a
INNER JOIN (
select user_id, device
from deviceTable b
where ranking = 1 ) b
ON a.user_id = b.user_id
SET a.device = b.device
問題來了！這些過程如果是手動執行都不會有問題，雖然會稍微花時間（幾百萬user）。
但若是透過排程執行，常會卡住，主要錯誤碼：
Lock wait timeout exceeded; try restarting transaction
了解原因，每天更新 deviceTable、landingTable、與 domainTable，
它們的來源表的確是 prod 的同一張，但是都只有 SELECT 之後就各自寫到自己的表，
且是依序進行而非同時。
參考網友建議，已經在更新三表的 procedure 中，加大 lock 等待時間：
SET innodb_lock_wait_timeout=5000
並且在每個運算之間，加入暫停時間：sleep(10)。
如果是手動執行，遇到 Lock wait timeout 時，還可透過
information_schema.innodb_trx 查詢並且 kill 掉執行緒，
但是進入排程就直接死在那裡......。
求建議：
1. 可能還有什麼造成 Lock wait timeout 的原因，可以再進一步處理？
2. 關於更新 MainTable 的流程，有無更好的方式？
先感謝大家了！
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 223.137.110.139 (臺灣)
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Database/M.1630369884.A.EBF.html
1F：→ Catbert: 只更新last_at在最近兩天的User_ID如何? 09/07 04:08
2F：→ MOONY135: 先撈第一次 今天有登入的user資料出來 然後程式排序 09/08 08:30
3F：→ MOONY135: 或者 撈完總人數之後用limit分批取出 反正你可以自己切 09/08 08:31