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.
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:
max(visible_id) + 1
create table foo (
id number primary key -- the key for computers
,visible_id number -- the key for humans
create constraint foo_u1 unique foo(owner_id, visible_id);
-- primary key sequence
create sequence foo_id_seq;
insert into foo values(
,1 -- what to put here ?
insert into foo values(
,1 -- what to put here ?
,'dolor sit amet'
select visible_id, data_ from foo where owner = 2 order by visible_id;
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;