【案例】ArteryBase数据库uuid重复引发的血案

2022-03-29 15:07:48

问题背景

某省法院xx决策支持系统的定时任务调用存储过程将数据插入临时表时，出现了uuid重复的报错。

报错信息

[SQL]select db_dataquality.PR_SELECT()
[Err] ERROR: duplicate key value violates unique constraint "pk_result_select"
DETAIL: Key (c_id)=(3d0e61c6615092883cc5e29198aaffb7) already exists.
CONTEXT: SQL statement "insert into DB_DATAQUALITY.RESULT_SELECT(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD)
select replace(cast(uuid_generate_v4() as varchar),'-','') as

排查问题

查看该存储过程

drop function "db_dataquality"."pr_select_bak"();
CREATE OR REPLACE FUNCTION "db_dataquality"."pr_select_bak"()
  RETURNS "pg_catalog"."void" AS $BODY$
  BEGIN
    truncate table DB_DATAQUALITY.result_select_bak;
    insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,
        CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD)
    select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,T1.AJBSID,
        T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,T1.JARQ,T1.XGSJ,T1.AJJZJDID,T1.YZCD
     from (   
         SelectdistinctAJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
         from (select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
            from  DB_DATAQUALITY.RESULT_SELECT_QT
            where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from  DB_DATAQUALITY.RESULT_SELECT_SF where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from   DB_DATAQUALITY.RESULT_SELECT_ZX where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from   DB_DATAQUALITY.RESULT_SELECT_WS where  AJLBID = 1
            ) T2
        ) T1;    
    insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,
        CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD)
    select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,T1.AJBSID,
        T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,T1.JARQ,T1.XGSJ,T1.AJJZJDID,T1.YZCD
     from (
     select distinct AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
        from (select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from    DB_DATAQUALITY.RESULT_SELECT_QT where  AJLBID = 2
--后面还有许多where条件不一样insert 的就不一一列举了
  ......
  END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION "db_dataquality"."pr_select_bak"() OWNER TO "atybase";

查看该存储过程并没有什么特别之处

观察uuid重复的规律

环境linux、数据库版本abase3.5.1、每次插入表总数：76824

调用15次存储过程操作查看uuid重复的条数：

无重复：3次
重复一条：5次
重复两条：4次
重复三条：2次
重复四条：1次

上网查了下uuid重复的概率:每秒产生10亿笔UUID，100年后只产生一次重复的机率是50%.如果地球上每个人都各有6亿笔UUID，发生一次重复的机率是50%

关于postgresql uuid重复的一篇文章：当机器每微秒可以产生多个UUID时，在多个进程中有可能产生重复值。

原因就是前面对uuid.c的分析。因为本机码必须确保同一个微秒内不能产生多个UUID，因此应尽可能避免并行产生UUID。

猜测uuid重复的可能原因

服务器生成uuid太快、导致重复？
还是说在服务器正常但是真的同一时刻产生了重复的uuid。(这种情况就像被陨石击中一样、从实验结果的高命中可以基本排除)

疑问

这些重复的uuid是不同的insert生成的、还是一个insert里面就能生成重复的uuid？

为了解开疑问：首先将临时表result_select_bak去掉主键约束、添加一个序号(XH)字段用于记录是哪个insert插入的数据。

测试过程

DROP TABLE IF EXISTS "db_dataquality"."result_select_bak";
CREATE TABLE "db_dataquality"."result_select_bak" (
"c_id" varchar(35) COLLATE "default" NOT NULL,
--中间字段不一一列举
"yzcd" int4,
--添加序号
"xh" int4
)
WITH (OIDS=FALSE);
CREATE OR REPLACE FUNCTION "db_dataquality"."pr_select_bak"()
  RETURNS "pg_catalog"."void" AS $BODY$
  BEGIN
    truncate table DB_DATAQUALITY.result_select_bak;
    insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,
                                                  CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD,XH)
    select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,
            T1.AJBSID,T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,T1.JARQ,
            T1.XGSJ,T1.AJJZJDID,T1.YZCD,1
     from (   
      select distinct AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
       from 
        (
        select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from    DB_DATAQUALITY.RESULT_SELECT_QT where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from    DB_DATAQUALITY.RESULT_SELECT_SF where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from    DB_DATAQUALITY.RESULT_SELECT_ZX  where  AJLBID = 1
            union all
            select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
             from    DB_DATAQUALITY.RESULT_SELECT_WS where  AJLBID = 1
           ) T2
        ) T1;  
    insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID, CBSPTID,
                                                  CBRID, LARQ,JARQ,XGSJ,AJJZJDID,YZCD,XH)
    select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,
                    T1.AJBSID,T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,
                    T1.JARQ,T1.XGSJ,T1.AJJZJDID,T1.YZCD,2
    .....
  END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION "db_dataquality"."pr_select_bak"() OWNER TO "atybase";

测试结果

abase2=# select c_id from DB_DATAQUALITY.result_select_bak group by c_id having count(*)>1;
               c_id               
----------------------------------
 69d74a5ed31b8d51a59cf6d244cef763
(1 row)
--相同序号、说明是一个insert里面产生了相同的uuid
abase2=# select c_id,xh from DB_DATAQUALITY.result_select_bak where c_id = '69d74a5ed31b8d51a59cf6d244cef763';
               c_id               | xh 
----------------------------------+----
 69d74a5ed31b8d51a59cf6d244cef763 |  2
 69d74a5ed31b8d51a59cf6d244cef763 |  2
(2 rows)
abase2=# select c_id,xh from DB_DATAQUALITY.result_select_bak where c_id = '0cac29558223c7b3cd72f53116d62a2d';
               c_id               | xh 
----------------------------------+----
 0cac29558223c7b3cd72f53116d62a2d |  2
 0cac29558223c7b3cd72f53116d62a2d |  1
(2 rows)
abase2=# select c_id,xh from DB_DATAQUALITY.result_select_bak where c_id = '1ea8c12e58169105fa93ec1d838b6f07';
               c_id               | xh 
----------------------------------+----
 1ea8c12e58169105fa93ec1d838b6f07 |  9
 1ea8c12e58169105fa93ec1d838b6f07 |  1
(2 rows)
...

经测试发现不管是同一个insert还是不同的insert都有可能生成相同的uuid。到这一步我开始怀疑是不是服务器有问题了。但是这种小概率事件真的就发生在我身上了吗？我还是不太相信小概率事件会发生

转换角度

想到默认abase安装扩展会有三个uuid函数：uuid_generate_v1()、uuid_generate_v4()、uuid_generate_v1mc()。所以考虑使用uuid_generate_v1()替换掉uuid_generate_v4()看结果如何，然而却找不到这个函数！

开始怀疑

是不是插件的问题呢？

将abase3.5.1自带的uuid插件uuid-ossp.so。替换掉/opt/thunisoft/arterybase/3.5/lib/postgresql/uuid-ossp.so、然后重启数据库。在db_dataquality下面创建扩展函数：create extension “uuid_ossp”

再次测试

执行开始的存储过程，没有发现重复的uuid，并且多测试了几次做验证，这个时候感觉找到问题所在了，应该就是插件的问题。

为了验证正确性，然后测试修改后添加了序号的存储过程，发现居然还是有重复的数据！开始纳闷了! 怎么会如此奇怪？详细对比这两函数获取uuid的不同形式：
正常：uuid:replace(cast(uuid_generate_v4() as varchar,’-’,’’))
异常：uuid:replace(public.uuid_generate_v4():text,’-’,’’)
正常：不加schema通过当前db_dataquality下面的uuid_generate_v4()函数获取
异常：通过public下面的uuid_generate_v4()获取

查看public下面的函数

CREATE OR REPLACE FUNCTION "public"."uuid_generate_v4()"
    RETURNS "pg_catalog"."varchar" AS $BODY$BEGIN
            --Routne body goes here...
                RETURN  md5(random()::text || now::text);
END
$BODY
    LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION "public"."uuid_generate_v4"() OWNER TO "atybase";

对比自带uuid函数

CREATE OR REPLACE FUNCTION "public"."uuid_generate_v4"()
  RETURNS "pg_catalog"."uuid" AS '$libdir/uuid-ossp', 'uuid_generate_v4'
  LANGUAGE 'c' VOLATILE STRICT  COST 1;
ALTER FUNCTION "public"."uuid_generate_v4"() OWNER TO "sa";

发现问题

观察可以看到该函数被重新定义了，没有使用基础动态链接库，而是使用了随机数和当前时间组合md5散列的方式，这导致uuid重复。

结语

在安装abase3.5.1以上版本时默认会在public下面创建uuid函数，直接调用即可，不需要再去手动创建。如果在脚本中使用了set search_path to db_xxx;然后去调用uuid_generate_v4(),会报错找不到该函数，可以使用set search_path to public,db_xxx;同时指定多个schema。

原文链接：https://mp.weixin.qq.com/s/GtcX-DZvjJQh72ArBK_R8A

分享好友

分享这个小栈给你的朋友们，一起进步吧。

ArteryBase

创建时间：2022-03-29 14:58:09

ArteryBase

展开

订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询

• 专区发布评论属默认订阅所评论专区（除付费小栈外）

技术专家

查看更多

gaokeke123
专家