亿级数据表多线程update锁表问题
目录
1、问题描述
2、原因分析
3、问题解决
1、问题描述
在pg数据库，某个业务，有一张数据表test，数据表结果如下： test(sjjbh,wlbid,gzmb,sfzg,zgsj,cjsj,xx...)，这个表没有主键，会有很多重复数据。 test表需要根据另外表（是多张表），动态更新sfzg字段， 加入另外表结构如下 sjj_ckb1(wlbid,sjjbh,xxxxx...)。
方案是：多线程执行下面的步骤
1、根据sjjbh,找到数据集对应的数据物理表，然后查询select wlbid from sjj_ckb1 where 具体的条件
2、更新test表 update test set sfzg = 'Y' where sjjbh='数据集编号' and wlbid not in(第一步的id)
and gzbm = '编码‘
在生成环境，test达到2000w以上数据，存在以下问题：
（1） 问题1 update 操作经常会报canceling statement due to lock timeout
（2） 问题2： 每个update执行速度很慢，可能一个update 就几个小时
2、原因分析
（1）canceling statement due to lock timeout 是因为多个sql，命中了相同的数据，导致锁冲突，update失败
（2）update慢，是因为update没用用到索引，not in都是全表扫描，对于全表更新是灾难性的
（3）单表数据量达到千万级别，而且是持续增长，test表没有主键，就算用到索引也可以预见的不会很快
3、问题解决
1、test表，弄成分区表，并且弄一个自增主键id id定义成bigserial
把普通表变成分区表步骤如下：
(1)新建数据表test1，test1定义成分区表，test1字段和test一样，但是多了一个主键id
CREATE TABLE test1 (
id bigserial ,
"sjjzwm" varchar(128) COLLATE "pg_catalog"."default",
"sjjbh" varchar(128) COLLATE "pg_catalog"."default",
"gzbm" varchar(32) COLLATE "pg_catalog"."default",
"input_time" timestamp(6),
"update_date" timestamp(6),
"wlbid" varchar(200) COLLATE "pg_catalog"."default"
)
with(appendoptimized=true, compresstype=zlib,compresslevel=5,orientation=column)
DISTRIBUTED BY(sjjbh)
PARTITION BY range(input_time)
(
partition pn start('2023-01-01'::date) end ('2030-12-31'::date) every ('1 year' :: interval),
default partition other
);
（2）把test数据同步到test1表
insert into test1(字段信息）select * from test
这样数据表就有了自增主键
（3）test表重命名成test_back
(4) test1表重命名成test
2、update的时候，不用not in，在代码里面查到需要更新的id。
（1）第一步查test表数据， select id，wlbid from test where sjjbh='' and gzbm=''
(2)查参照表，符合条件的wlbid， select wlbid from sjj_ckb1 where 具体的条件
(3) 找到需要更新的id.
wlbid在(2)中存在，在（1）中不存在的id
(4)更具id更新具体的数据。
这样因为用到了id这个唯一标识，不存在多线程，同时更新同一条数据的情况