Re: A Typical Concurrent Access Problem

From: Lewis C <lewisc_at_excite.com>
Date: Thu, 09 Jun 2005 08:37:19 GMT
Message-ID: <6kvfa1lp2lr5o796jf2rcb6ak5l2q5pc5h@4ax.com>

On Thu, 9 Jun 2005 13:04:20 +0800, "jack" <jackIt_at_it.com> wrote:

>I know this is a typical problem and just want to know what are the possible
>solutions:
>
>DECLARE
> v_id number;
>BEGIN
> SELECT MAX(id) INTO v_id FROM table;
>...
> INSERT INTO TABLE (id) VALUES (v_id+1);
>....
>END;
>
>This approach of finding new id value would cause 2 duplicated id values if
>2 txns execute the procedure simultaneously - both get the same value for
>MAX(id) at the beginning and insert the same value +1 to table for id.
>
>How do we overcome this problem? The requirement dictate that we need to
>try reusing the lowest available id if some rows get deleted. That means we
>cannot use sequence to generate the id - which keeps on increasing.
>
>Even if we set the transaction to serializable, 2 simultaneous SELECT
>MAX(id) still get the same value which still cause duplicated values.
>

As you said, the way to do this is to use a sequence. If, due to requirements, you can't use a sequence, fix the requirements.

I have had requirements forbid the use of sequences twice in my career. Once sounded a lot like your requirement. After doing some investigation, that particular case was afraid they would run out of numbers if the sequence got too big. That is a silly reason and a little math will prove that the requirement is not needed.

The other time was an audit issue and we resolved that with a display field and PK audit record. Bascially the human viewed ID field and the actual PK were inserted into an audit table on creation. The PK was a sequence.

Revisit your requirement.

Thanks,

LewisC

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Thu Jun 09 2005 - 03:37:19 CDT