如何实现多维序列 [英] How to implement multidimensional sequences

问题描述

例如，这是一个年度序列. no以year递增:

For example, here is a yearly sequence. The no increments with year:

| no | year |
+----+------+
|  1 | 2016 |
|  2 | 2016 |
|  3 | 2016 |
|  1 | 2017 |
|  2 | 2017 |
|  4 | 2016 |

到目前为止，我已经为每年创建了序列
但是问题是Oracle不会在明年自动创建新序列.

For now I have created sequence for each year
but the problem is Oracle will not automatically create new sequence in next year.

另一个问题是，如果我想使用3D序列，在year和type内递增:

Another problem is if I want to use a 3D sequence, incrementing within year and type:

| no | year | type |
+----+------+------+
|  1 | 2016 |    a |
|  2 | 2016 |    a |
|  1 | 2016 |    b |
|  1 | 2017 |    b |
|  2 | 2017 |    b |
|  1 | 2017 |    c |

数据库中的序列过多

由于并行访问问题，我不建议使用max(no). 我试图在触发器中获取max(no)之前锁定表，但导致死锁.

I do not recommend max(no) because of parallel access issue. I tried to lock table before getting max(no) in a trigger but it resulted in deadlock.

推荐答案

执行此操作的唯一方法是使用代码控制表...

The only way to do this is with a code control table ...

create table code_control
    (year number(4,0) not null
     , type varchar2(1) not null
     , last_number number(38,0) default 1 not null
     , primary key (year,type)
    )
organization index
/   

...这样维护的...

... which is maintained like this ...

create or replace function get_next_number
    (p_year in number, p_type in varchar2)
    return number
is
    pragma autonomous_transaction;
    cursor cur_cc is
        select last_number + 1
        from code_control cc
        where cc.year= p_year
        and cc.type = p_type
        for update of last_number;
    next_number number;
begin
    open cur_cc;
    fetch cur_cc into next_number;
    if cur_cc%found then
        update code_control
        set last_number = next_number
        where current of cur_cc;
    else
        insert into code_control (year,type)
        values (p_year, p_type)
        returning last_number into next_number;
    end if;    
    commit;
    return next_number;
end;
/

重要的是SELECT ... FOR UPDATE.悲观锁定可确保在多用户环境中的唯一性. PRAGMA确保维持code_control不会污染更广泛的交易.它使我们可以在没有死锁的情况下在触发器中调用该函数.

The important thing is the SELECT ... FOR UPDATE. Pessimistic locking guarantees uniqueness in a multi-user environment. The PRAGMA ensures that maintaining code_control doesn't pollute the broader transaction. It allows us to call the function in a trigger without deadlocks.

这是一张桌子，上面有你的钥匙:

Here is a table with a key like yours:

create table t42
     (year number(4,0) not null
     , type varchar2(1) not null
     , id number(38,0) 
     , primary key (year,type, id)
)
/
create or replace trigger t42_trg
    before insert on t42 for each row
begin
    :new.id := get_next_number(:new.year, :new.type);
end;
/

在填充t42之前，我什么都没有袖手旁观:

There's nothing up my sleeves before I populate t42:

SQL> select * from code_control;

no rows selected

SQL> select * from t42;

no rows selected

SQL> insert into t42 (year, type) values (2016, 'A');

1 row created.

SQL> insert into t42 (year, type) values (2016, 'A');

1 row created.

SQL> insert into t42 (year, type) values (2016, 'A');

1 row created.

SQL> insert into t42 (year, type) values (2016, 'B');

1 row created.

SQL> insert into t42 (year, type) values (2016, 'A');

1 row created.

SQL> insert into t42 (year, type) values (2017, 'A');

1 row created.

SQL> select * from t42;

      YEAR T         ID
---------- - ----------
      2016 A          1
      2016 A          2
      2016 A          3
      2016 A          4
      2016 B          1
      2017 A          1

6 rows selected.

SQL> select * from code_control;

      YEAR T LAST_NUMBER
---------- - -----------
      2016 A           4
      2016 B           1
      2017 A           1

SQL> 

因此，此实现的明显反对是可伸缩性.插入事务在code_control表上序列化.没错但是，该锁定将保持在最短的时间内，因此即使t42表每秒被填充多次，这也不应该成为问题.

So the obvious objection to this implementation is scalability. Inserting transactions are serialized on the code_control table. That's absolutely true. However the lock is held for the shortest possible time, so this should not be an issue even if the t42 table is populated many times a second.

但是，如果表受到大量并发插入操作，则锁定可能会成为问题.该表具有足够的兴趣交易"时隙(INITRANS，MAXTRANS)以应付并发需求至关重要.但是非常繁忙的系统可能需要更智能的实现(可能会批量生成ID)；否则，放弃复合键而使用序列(因为序列在多用户环境中确实可以缩放).

However, if the table is subjected to massive numbers of concurrent inserts the locking may become an issue. It is crucial the table has sufficient Interested Transaction slots (INITRANS, MAXTRANS) to cope with concurrent demands. But very busy systems may need a smarter implementation (perhaps generating the IDs in batches); otherwise abandon the compound key in favour of a sequence (because sequences do scale in multi-user environments).

这篇关于如何实现多维序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！