亿级数据表多线程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这个唯一标识，不存在多线程，同时更新同一条数据的情况