Re: Oracle sequence returning duplicate key for a given key name
Date: Mon, 10 Mar 2008 22:40:12 +0100
Message-ID: <63lo5vF27t7voU1@mid.individual.net>
On 10.03.2008 22:19, Shakespeare wrote:
> "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?
No, Oracle sequences are always safe. But they are used in an unsafe way here through the mix of DDL and DML. Note also that the return value is never written in case of exception - which will lead to all sorts of weird behavior - another problem with the code posted.
However, I should have added that the code I presented does not solve the same problem the OP seems to try to solve: apparently in his scenario the key is just a namespace for sequence values. Why he does not preallocate sequences is unclear to me - probably because he chose to post no more information about the environment. So I took the liberty to change the problem to another one that I found more interesting. :-)
Note also that you can rework my solution to get a solution for his problem. I leave that as an exercise for the interested reader because I think that solution would not be idiomatic (basically reimplementing sequences with a table). The only advantage I see is that you can avoid gaps in sequences.
Kind regards
robert Received on Mon Mar 10 2008 - 16:40:12 CDT