Specific entity sequence

Question

Background

I have many different “things” (item / item / domain item) that are visible to the owners of the “thing” (people). Owners are going to identify their “things” with a number. Instead of showing a large "random" number, I want to show them a small number (preferably a sequence starting with 1), which is easier for people. Owners are very comfortable talking about "my foo 37" and "her bar 128". There the "sequence" may have spaces, but the attached number should remain unchanged during the life of the "thing". So I need a way to generate the “thing” + the owner’s own identifier (currently called the “visible identifier”).

The number of combinations "thing" + owner in a scale of 10k +. Currently, new “things” cannot be dynamically generated, but owners can be.

The number of copies of “pieces” per owner is relatively small, about a dozen per owner, but there is no hard cover that can be obtained from business rules. New instances of "things" are created and deleted frequently.

Considered Parameters

I found a good discussion on the SO Oracle Partitioned Sequence question, which addresses almost the same issue as mine.

So far I have considered the following options:

• I think the standard database sequence will be perfectly fine, but it will require me to dynamically create a large number of “real” + user-specific sequences, as well as resolve the sequence name during insertion. (And discard the sequences when the owner has left.) I'm not sure that creating a huge number of sequences is good practice (I have 10,000 + database objects - a huge amount).
• I also considered the notorious max(visible_id) + 1 , but we will run into the usual concurrency problems with this, so this is not-go.
• Do not save a separate owner identifier in the database, but instead generate it in the Adam Mush selection. This is a great idea, but unfortunately the identifier must be the same during the lifetime of the "thing" instance.
• Avoid the whole problem by letting owners call a “thing.” But they didn't like the idea at all: "Why should I worry, it's so easy to say foo 16."!

Question

Is there any other way to solve this problem or should I start creating sequences dynamically? If the sequences are the answer, please specify what pitfalls may be (for example, implicit commits in DDL).

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

Pseudocode 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 the spaces are in order, you should implement option 2. Gap resolution means your synchronization can be done quickly: competing sessions just check and move, rather than waiting for others to commit or roll back.

If Oracle offered the INSERT INTO..NOWAIT option, that would be easy. Be that as it may, I would DBMS_LOCK . Here I understand how your API will look.

He makes some assumptions about the maximum visible identifier that you have, because you made these 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;