Re: Oracle sequence returning duplicate key for a given key name

On Mar 7, 7:13 am, vijay <vksingh..._at_gmail.com> wrote:
> Tool - VC2005, OleDb
> Database - Oracle 10g
> OS - Window 2003 Server
>
> Hi,
>
> We have developed oracle stored procedure to generate unique key for a
> given key name. Stored procedure uses sequences to return new key.
>
> create or replace PROCEDURE ABI_SP_GETNEWLOGKEY
> (strkeyname IN VARCHAR2, nkeyvalue OUT NUMBER )
> is
> begin
> declare
> begin
> EXECUTE IMMEDIATE 'SELECT abi_' ||strkeyname ||'.NEXTVAL FROM
> dual'
> INTO nkeyvalue;
> EXCEPTION
> WHEN OTHERS THEN
> EXECUTE IMMEDIATE 'create sequence abi_' || strkeyname || '
> INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE NOCACHE';
> end;
> end;
>
> Above stored procedure is being called from multi threaded vc++
> application. VC++ application uses OleDb to call sp. Some time stored
> procedure return duplicate key for a given key name.
>
> Can anyone suggest me what could be reason.

I am not sure whether this has become clear from the other postings yet: your design is inherently flawed since it does not use proper locking. If you want a concurrent application that is guaranteed to only ever return one specific value for a key you either need mutual exclusion or a deterministic algorithm which calculates the number based on the input. Since you are using a database I reckon you have chosen option 1.

Here's one option to achieve proper locking:

create sequence global_ids INCREMENT BY 1 START WITH 1 MINVALUE 0 NOCYCLE; create table ids (
  key varchar2(100) primary key,
  id number(20)
)

Then recode the SP to do this:

create or replace function get_id(key_val in varchar2) return number
is
  res number(20);
begin
  begin
    insert into ids ( key, id ) values ( key_val, global_ids.nextval )     returning id into res;

• dbms_output.put_line('inserted'); exception when DUP_VAL_ON_INDEX then select id into res from ids where key = key_val;
• dbms_output.put_line('selected'); end; return res; end; /

This will guarantee proper locking per key and avoid to reduce concurrency more than necessary. You can easily test this out by uncommenting the output lines above and using two concurrent SQL*Plus sessions doing this and experimenting with commit and rollback.

set serveroutput on
declare
  i number;
begin
  i := get_id('foo');
  dbms_output.put_line('id=' || i);
end;
/

Cheers

robert Received on Mon Mar 10 2008 - 04:13:37 CDT