作者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/cn.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