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

2022-03-29 00:00:00 函数 多个 测试 重复 存储过程


问题背景

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

报错信息

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

排查问题

查看该存储过程

  1. drop function "db_dataquality"."pr_select_bak"();
  2. CREATE OR REPLACE FUNCTION "db_dataquality"."pr_select_bak"()
  3.   RETURNS "pg_catalog"."void" AS $BODY$
  4.   BEGIN
  5.     truncate table DB_DATAQUALITY.result_select_bak;
  6.     insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,
  7.         CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD)
  8.     select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,T1.AJBSID,
  9.         T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,T1.JARQ,T1.XGSJ,T1.AJJZJDID,T1.YZCD
  10.      from (   
  11.          SelectdistinctAJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  12.          from (select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  13.             from  DB_DATAQUALITY.RESULT_SELECT_QT
  14.             where  AJLBID = 1
  15.             union all
  16.             select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  17.              from  DB_DATAQUALITY.RESULT_SELECT_SF where  AJLBID = 1
  18.             union all
  19.             select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  20.              from   DB_DATAQUALITY.RESULT_SELECT_ZX where  AJLBID = 1
  21.             union all
  22.             select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  23.              from   DB_DATAQUALITY.RESULT_SELECT_WS where  AJLBID = 1
  24.             ) T2
  25.         ) T1;    
  26.     insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,
  27.         CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD)
  28.     select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,T1.AJBSID,
  29.         T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,T1.JARQ,T1.XGSJ,T1.AJJZJDID,T1.YZCD
  30.      from (
  31.      select distinct AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  32.         from (select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  33.              from    DB_DATAQUALITY.RESULT_SELECT_QT where  AJLBID = 2
  34. --后面还有许多where条件不一样insert 的就不一一列举了
  35.   ......
  36.   END
  37. $BODY$
  38.   LANGUAGE 'plpgsql' VOLATILE COST 100;
  39. 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重复的可能原因

  1. 服务器生成uuid太快、导致重复？

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

疑问

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

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

测试过程

  1. DROP TABLE IF EXISTS "db_dataquality"."result_select_bak";
  2. CREATE TABLE "db_dataquality"."result_select_bak" (
  3. "c_id" varchar(35) COLLATE "default" NOT NULL,
  4. --中间字段不一一列举
  5. "yzcd" int4,
  6. --添加序号
  7. "xh" int4
  8. )
  9. WITH (OIDS=FALSE);
  10. CREATE OR REPLACE FUNCTION "db_dataquality"."pr_select_bak"()
  11.   RETURNS "pg_catalog"."void" AS $BODY$
  12.   BEGIN
  13.     truncate table DB_DATAQUALITY.result_select_bak;
  14.     insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,
  15.                                                   CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD,XH)
  16.     select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,
  17.             T1.AJBSID,T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,T1.JARQ,
  18.             T1.XGSJ,T1.AJJZJDID,T1.YZCD,1
  19.      from (   
  20.       select distinct AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  21.        from 
  22.         (
  23.         select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  24.              from    DB_DATAQUALITY.RESULT_SELECT_QT where  AJLBID = 1
  25.             union all
  26.             select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  27.              from    DB_DATAQUALITY.RESULT_SELECT_SF where  AJLBID = 1
  28.             union all
  29.             select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  30.              from    DB_DATAQUALITY.RESULT_SELECT_ZX  where  AJLBID = 1
  31.             union all
  32.             select AJLBID,AJBSID,AJBS,AH,JBFYID,CBSPTID,CBRID,LARQ,JARQ,XGSJ,AJJZJDID,YZCD
  33.              from    DB_DATAQUALITY.RESULT_SELECT_WS where  AJLBID = 1
  34.            ) T2
  35.         ) T1;  
  36.     insert into DB_DATAQUALITY.result_select_bak(C_ID,AJLBID,AJBSID,AJBS,AH,JBFYID, CBSPTID,
  37.                                                   CBRID, LARQ,JARQ,XGSJ,AJJZJDID,YZCD,XH)
  38.     select replace(cast(uuid_generate_v4() as varchar),'-','') as C_ID,T1.AJLBID,
  39.                     T1.AJBSID,T1.AJBS,T1.AH,T1.JBFYID,T1.CBSPTID,T1.CBRID,T1.LARQ,
  40.                     T1.JARQ,T1.XGSJ,T1.AJJZJDID,T1.YZCD,2
  41.     .....
  42.   END
  43. $BODY$
  44.   LANGUAGE 'plpgsql' VOLATILE COST 100;
  45. ALTER FUNCTION "db_dataquality"."pr_select_bak"() OWNER TO "atybase";

测试结果

  1. abase2=# select c_id from DB_DATAQUALITY.result_select_bak group by c_id having count(*)>1;
  2.                c_id               
  3. ----------------------------------
  4.  69d74a5ed31b8d51a59cf6d244cef763
  5. (1 row)
  6. --相同序号、说明是一个insert里面产生了相同的uuid
  7. abase2=# select c_id,xh from DB_DATAQUALITY.result_select_bak where c_id = '69d74a5ed31b8d51a59cf6d244cef763';
  8.                c_id               | xh 
  9. ----------------------------------+----
  10.  69d74a5ed31b8d51a59cf6d244cef763 |  2
  11.  69d74a5ed31b8d51a59cf6d244cef763 |  2
  12. (2 rows)
  13. abase2=# select c_id,xh from DB_DATAQUALITY.result_select_bak where c_id = '0cac29558223c7b3cd72f53116d62a2d';
  14.                c_id               | xh 
  15. ----------------------------------+----
  16.  0cac29558223c7b3cd72f53116d62a2d |  2
  17.  0cac29558223c7b3cd72f53116d62a2d |  1
  18. (2 rows)
  19. abase2=# select c_id,xh from DB_DATAQUALITY.result_select_bak where c_id = '1ea8c12e58169105fa93ec1d838b6f07';
  20.                c_id               | xh 
  21. ----------------------------------+----
  22.  1ea8c12e58169105fa93ec1d838b6f07 |  9
  23.  1ea8c12e58169105fa93ec1d838b6f07 |  1
  24. (2 rows)
  25. ...

经测试发现不管是同一个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下面的函数

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

对比自带uuid函数

  1. CREATE OR REPLACE FUNCTION "public"."uuid_generate_v4"()
  2.   RETURNS "pg_catalog"."uuid" AS '$libdir/uuid-ossp', 'uuid_generate_v4'
  3.   LANGUAGE 'c' VOLATILE STRICT  COST 1;
  4. 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

相关文章