sql - An "entity" specific sequence

Question

Background

I have a lot of different "things" (a domain specific item/entity/subject) that are visible to the "thing" owners (humans). The owners are going to identify their "things" with a number. Instead showing a big "random" number I want to show them a small number (preferably a sequence starting from 1) that is easier for humans. The owners are very comfortable to talk about "my foo 37" and "her bar 128". There "sequence" can have gaps but the attached number have to remain the same during the "thing" intance's lifetime. So I need a way to generate "thing" + owner specific id (currently called as "visible id").

The number of "thing" + owner combinations is in scale of 10k+. Currently new "things" can't be dynamically generated but the owners can be.

The number of one "thing" instances per owner is a relatively small, about tens per owner, but there is no hard cap that can be derived from the business rules. New "thing" instances are created and deleted frequently.

Considered options

I found a good discussion in a SO question Oracle Partitioned Sequence that addresses pretty much the same issue I do have.

So far I have considered the following options:

1. I think a standard database sequence would be a perfectly fine but that would require me to dynamically create a big number of "thing" + owner specific sequences and also resolve the sequence name during insert. (And drop the sequences when the owner is gone.) I'm not sure if creating a huge number of sequences is a good practice at all (to me 10k+ database objects is a huge number).
2. I also considered notorious max(visible_id) + 1 but we'll run into normal concurrency issues with that, so it's a no-go.
3. Don't save the owner specific id into the database at all but instead generate it in the select like suggested by Adam Musch. This is a wonderful idea but unfortunately the id needs to be the same during the "thing" instance lifetime.
4. Avoid the whole problem by letting the owners name the "thing". But they didn't like the idea at all - "Why I should bother, it's just so easy to say foo 16." !

Question

Is there some other way to address this issue or should I start creating sequences dynamically ? If the sequences are the answer please elaborate what pitfalls there might be (like implicit commits in DDL).

I'm interested in both Oracle 11gR2 and 12c solutions (if they are different).

Pseudo-code to illustrate the question

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

Since gaps are OK, you should implement a variation of "option 2". Allowing gaps means that your synchronization can be done quickly: with competing sessions merely checking and moving on rather than having to wait to see if the others commit or rollback.

If Oracle offered an INSERT INTO..NOWAIT option, this would be easy. As things are, I'd probably involve DBMS_LOCK. Here's my take on what your API would look like.

It makes some assumptions about the max visible ID you'd have because you made those assumptions in your original post.

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;