自定义自增序列的并发问题怎么解决

阿吉2009

wmxcn2000 发表于 2017-2-27 11:02
我都这样干：
假定从 1 开始，我会先写入一个从 0 开始的记录，防并且的效果还是不错的，你可以参考一下 ...

-- 生成序列（ 1、2、3、4、5） 的记录
update t set num = num + 1 where id = 100 returning num into v_num;

此句UPDATE时因并发使NUM+1的运算结果不等于NUM+UPDATE的次数。
如现在是1，会话A执行UPDATE变2，COMMIT前会话B执行UPDATE变2，两会话COMMIT还是2。

wmxcn2000

阿吉2009 发表于 2017-2-27 13:52
-- 生成序列（ 1、2、3、4、5） 的记录
update t set num = num + 1 where id = 100 returning num into ...

不会的，会话1 先执行的  update ， 会话2后执行的 update ，如果 会话 1 没有 commit （或rollback），会话2 会等待； 这个是基本的行锁。

阿吉2009

wmxcn2000 发表于 2017-2-27 14:06
不会的，会话1 先执行的  update ， 会话2后执行的 update ，如果 会话 1 没有 commit （或rollback）， ...

恩，对，我看懵了。

fage090

感觉思路还是要将并发变成排队运行，如果不想锁表的话，可以考虑新建一个事务处理状态表，每次调用生成序列的过程时，先检查是否有其他会话正在调用此过程。

neozhu

fage090 发表于 2017-2-27 14:48
感觉思路还是要将并发变成排队运行，如果不想锁表的话，可以考虑新建一个事务处理状态表，每次调用生成序列 ...

感觉一个小小递增序列学问还不小呢

newkid

这种做法会使得所有并发事务排队，影响并发性能。如果追求无间隙的序列号这就是必然后果，要么放弃这种需求采用sequence, 要么就忍受性能问题。
在写法上要把update放到第一行，sql%rowcount=0则进行插入，插入时捕获dup_val_on_index异常，发生异常时改做update。

alexandre2008

用自治事务，加锁，根据规则获取序列号，这样序列号不会重复，如果要连续的话业务数据提交时再做检验或者锁之类处理

阿吉2009

CREATE OR REPLACE PROCEDURE NEXTSEQNO
(
  P_PREFIX  IN VARCHAR2,
  P_NEXTVAL OUT NUMBER
) IS

BEGIN
  P_NEXTVAL := 1;
  begin
   
    --若前缀不存在则插入，但插入时若因并发前缀已存在则引发违反唯一索引的错误；
    --若前缀已存在则跳过。
    INSERT INTO SEQUENCERS
      SELECT SEQ_ACTION_TYPE.NEXTVAL,
             1,
             P_PREFIX
        FROM DUAL
       WHERE NOT EXISTS (SELECT 1 FROM SEQUENCERS WHERE PREFIX = P_PREFIX);
  
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX then  --违反唯一索引
      UPDATE SEQUENCERS T SET T.SEED = T.SEED + 1 WHERE PREFIX = P_PREFIX RETURNING T.SEED INTO P_NEXTVAL;
   
      COMMIT;
      RETURN;
    WHEN OTHERS THEN
      RAISE;
  END;

  --若因已存在而没插入新记录，则更新后获取新值
  IF SQL%ROWCOUNT = 0 THEN
    UPDATE SEQUENCERS T SET T.SEED = T.SEED + 1 WHERE PREFIX = P_PREFIX RETURNING T.SEED INTO P_NEXTVAL;
  END IF;
  
   COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END NEXTSEQNO;


--表结构-- Create table
create table SEQUENCERS
(
  id     NUMBER,
  seed   NUMBER,
  prefix VARCHAR2(1)
)
tablespace MES_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table SEQUENCERS
  add constraint UQ_PREFIX unique (PREFIX)
  using index
  tablespace MES_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

昨天试写了这个过程，多会话测试没问题。大家看有无BUG.

阿吉2009

象这种带有未知前缀的需求，能否通过用序列等方法解决性能吗？

newkid

alexandre2008 发表于 2017-2-28 08:24
用自治事务，加锁，根据规则获取序列号，这样序列号不会重复，如果要连续的话业务数据提交时再做检验或者锁 ...

自治事务和SEQUENCE一样不能保证无间隙。