今天有一个任务报错:
Unable to acquire IMPLICIT, SHARED lock db_ecar@bd_dw_terminal_id_city_info after 100 attempts.
FAILED: Error in acquiring locks: Locks on the underlying objects cannot be acquired. retry after some time
解决方案:
在生成表bd_dw_terminal_id_city_info时,因调用高德地图的第三方接口,导致该任务执行长待几十个小时,所以长时间使表bd_dw_terminal_id_city_info处于EXCLUSIVE LOCK。而本任务需要查询表bd_dw_terminal_id_city_info会失败。
再暂时不能改良生成表bd_dw_terminal_id_city_info任务的情况下,我新增加了表bd_dw_terminal_id_city_info_temp(表结构与bd_dw_terminal_id_city_info一样),将数据先插入表bd_dw_terminal_id_city_info_temp,再从bd_dw_terminal_id_city_info_temp插入表bd_dw_terminal_id_city_info。
这样,在长待几十个小时的执行过程中,表bd_dw_terminal_id_city_info不会处于EXCLUSIVE LOCK。