Re: A Typical Concurrent Access Problem

On Thu, 9 Jun 2005 13:04:20 +0800, jack wrote:

> I know this is a typical problem and just want to know what are the possible
> solutions:
> 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.
>

I remember one solution was published on the Quest Pipelines. Try this link: http://www.quest-pipelines.com/pipelines/plsql/tips02.htm#NOVEMBER or if it doesnt work, go to http://www.quest-pipelines.com and find Pl/SQL tip of the month for November 2002 (Generating Sequence Numbers Without Gaps).
Didnt try it myself though. Hope it will work for you. Received on Thu Jun 09 2005 - 01:33:26 CDT