Re: Oracle sequence returning duplicate key for a given key name
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 10 Mar 2008 22:19:51 +0100
Message-ID: <47d5a604$0$14352$e4fe514c@news.xs4all.nl>
> 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
Date: Mon, 10 Mar 2008 22:19:51 +0100
Message-ID: <47d5a604$0$14352$e4fe514c@news.xs4all.nl>
"Robert Klemme" <shortcutter_at_googlemail.com> schreef in bericht
news:76ac522c-c9e2-4b7c-98a7-f0458204ad67_at_x41g2000hsb.googlegroups.com...
> 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
Just to understand this: Do you mean that even sequences can generate duplicate values in multithreaded situations?
Shakespeare Received on Mon Mar 10 2008 - 16:19:51 CDT