一个``实体''特定序列

Question

背景

我有很多不同的“事物”（特定于域的项目/实体/主题），这些东西对“事物”所有者（人类）可见。所有者将用数字标识他们的“事物”。我要显示一个较小的数字（最好是从1开始的序列），而不是显示一个较大的“随机”数字，这对人类来说比较容易。业主很乐于谈论“我的富人37”和“她的酒吧128”。那里的“序列”可以有间隔，但附加的数目必须在“事物”实例的生存期内保持不变。因此，我需要一种生成“事物”
+所有者特定ID（当前称为“可见ID”）的方法。

“事物” +所有者组合的数量为10k +。当前无法动态生成新的“事物”，但所有者可以生成。

每个所有者一个“事物”实例的数量相对较小，每个所有者大约数十个，但是没有可以从业务规则中得出的硬性上限。经常创建和删除新的“事物”实例。

考虑的选项

我在一个SO问题Oracle分区序列中找到了一个很好的讨论，该问题几乎解决了我所遇到的相同问题。

到目前为止，我已经考虑了以下选项：

1. 我认为标准数据库序列会很好，但是这需要我动态创建大量“事物” +所有者专有的序列，并在插入过程中解析序列名称。（并在所有者离开后删除序列。） 我不确定创建大量序列是否是一个好习惯（对我而言，10k +数据库对象是一个庞大的数目）。
2. 我也认为是臭名昭著的，max(visible_id) + 1但是我们会遇到正常的并发问题，所以这是不可行的。
3. 根本不将所有者特定的ID保存到数据库中，而是像Adam Musch所建议的那样在选择中生成它。这是一个很棒的主意，但是不幸的是，在“事物”实例生存期内，ID必须相同。
4. 通过让所有者命名为“事物”来避免整个问题。但是他们根本不喜欢这个主意- “为什么要打扰，说foo 16太容易了。” ！

问题

还有其他解决此问题的方法，还是应该开始动态创建序列？如果序列是答案，请详细说明可能存在的陷阱（例如DDL中的隐式提交）。

我对Oracle 11gR2和12c解决方案都感兴趣（如果它们不同）。

伪代码来说明问题

create table foo (
 id number primary key -- the key for computers
,owner_id number
,visible_id number -- the key for humans
,data_ varchar2(20)
);

create constraint foo_u1 unique foo(owner_id, visible_id);

-- primary key sequence
create sequence foo_id_seq;

insert into foo values(
 foo_id_seq.nextval
,1
,1 -- what to put here ?
,'lorem ipsum'
);

insert into foo values(
 foo_id_seq.nextval
,2
,1 -- what to put here ?
,'dolor sit amet'
);

select visible_id, data_ from foo where owner = 2 order by visible_id;

Answer 1

由于差距尚可，因此您应该实现“选项2”的变体。允许间隔意味着您可以快速完成同步：竞争的会话仅检查并继续，而不必等待其他会话是否提交或回滚。

如果Oracle提供了一个INSERT INTO..NOWAIT选项，这将很容易。实际上，我可能会参与其中DBMS_LOCK。这是我对您的API外观的看法。

它对您拥有的最大可见ID做出了一些假设，因为您是在原始帖子中做出这些假设的。

CREATE OR REPLACE PACKAGE foo_api AS
  PROCEDURE create_foo (p_owner_id NUMBER, p_data VARCHAR2);
END foo_api;

CREATE OR REPLACE PACKAGE BODY foo_api AS
  -- We need to call allocate_unique in an autonomous transaction because
  -- it commits and the calling program may not want to commit at this time
  FUNCTION get_lock_handle (p_owner_id NUMBER, p_visible_id NUMBER)
    RETURN VARCHAR2 IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    l_lock_handle   VARCHAR2 (128);
  BEGIN
    DBMS_LOCK.allocate_unique (
      lockname  => 'INSERT_FOO_' || p_owner_id || '_' || p_visible_id,
      lockhandle => l_lock_handle
    );
    COMMIT;
    RETURN l_lock_handle;
  END;


  PROCEDURE create_foo (p_owner_id NUMBER, p_data VARCHAR2) IS
    -- This is the highest visible ID you'd ever want.
    c_max_visible_id   NUMBER := 1000;
  BEGIN
   <<id_loop>>
    FOR r_available_ids IN (SELECT a.visible_id
                            FROM   (SELECT ROWNUM visible_id
                                    FROM   DUAL
                                    CONNECT BY ROWNUM <= c_max_visible_id) a
                                   LEFT JOIN foo
                                     ON foo.owner_id = p_owner_id
                                     AND foo.visible_id = a.visible_id
                            WHERE  foo.visible_id IS NULL) LOOP
      -- We found a gap
      -- We could try to insert into it.  If another session has already done so and
      -- committed, we'll get an ORA-00001.  If another session has already done so but not 
      -- yet committed, we'll wait.  And waiting is bad.
      -- We'd like an INSERT...NO WAIT, but Oracle doesn't provide that.
      -- Since this is the official API for creating foos and we have good application 
      -- design to ensure that foos are not created outside this API, we'll manage 
      -- the concurrency ourselves.
      --
      -- Try to acquire a user lock on the key we're going to try an insert.
      DECLARE
        l_lock_handle       VARCHAR2 (128);
        l_lock_result       NUMBER;
        l_seconds_to_wait   NUMBER := 21600;
      BEGIN
        l_lock_handle := get_lock_handle (
          p_owner_id => p_owner_id,
          p_visible_id => r_available_ids.visible_id
        );

        l_lock_result := DBMS_LOCK.request (lockhandle => l_lock_handle,
                                            lockmode   => DBMS_LOCK.x_mode,
                                            timeout    => 0, -- Do not wait
                                            release_on_commit => TRUE);

        IF l_lock_result = 1 THEN
          -- 1 => Timeout -- this could happen.
          -- In this case, we want to move onto the next available ID.
          CONTINUE id_loop;
        END IF;

        IF l_lock_result = 2 THEN
          -- 2 => Deadlock (this should never happen, but scream if it does).
          raise_application_error (
            -20001,
               'A deadlock occurred while trying to acquire Foo creation lock for '
            || p_owner_id
            || '_'
            || r_available_ids.visible_id
            || '.  This is a programming error.');
        END IF;

        IF l_lock_result = 3 THEN
          -- 3 => Parameter error (this should never happen, but scream if it does).
          raise_application_error (
            -20001,
               'A parameter error occurred while trying to acquire Foo creation lock for '
            || p_owner_id
            || '_'
            || r_available_ids.visible_id
            || '.  This is a programming error.');
        END IF;

        IF l_lock_result = 4 THEN
          -- 4 => Already own lock (this should never happen, but scream if it does).
          raise_application_error (
            -20001,
               'Attempted to create a Foo creation lock and found lock already held by session for '
            || p_owner_id
            || '_'
            || r_available_ids.visible_id
            || '.  This is a programming error.');
        END IF;

        IF l_lock_result = 5 THEN
          -- 5 => Illegal lock handle (this should never happen, but scream if it does).
          raise_application_error (
            -20001,
               'An illegal lock handle error occurred while trying to acquire Foo creation lock for '
            || p_owner_id
            || '_'
            || r_available_ids.visible_id
            || '.  This is a programming error.');
        END IF;
      END;

      -- If we get here, we have an exclusive lock on the owner_id / visible_id 
      -- combination.  Attempt the insert
      BEGIN
        INSERT INTO foo (id,
                         owner_id,
                         visible_id,
                         data_)
        VALUES (foo_id_seq.NEXTVAL,
                p_owner_id,
                r_available_ids.visible_id,
                p_data);

        -- If we get here, we are done.
        EXIT id_loop;
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          -- Unfortunately, if this happened, we would have waited until the competing 
          -- session committed or rolled back.  But the only way it
          -- could have happened if the competing session did not use our API to create 
          -- or update the foo.
          -- TODO: Do something to log or alert a programmer that this has happened, 
          -- but don't fail.
          CONTINUE id_loop;
      END;
    END LOOP;
  END create_foo;
END foo_api;