Re: A Typical Concurrent Access Problem

From: Lewis C <lewisc_at_excite.com>
Date: Thu, 09 Jun 2005 12:09:03 GMT
Message-ID: <mubga1depvn6hsjk8ag9oaqijjudo1jnhi@4ax.com>

On Thu, 9 Jun 2005 17:49:35 +0800, "jack" <jackIt_at_it.com> wrote:

>
>"Lewis C" <lewisc_at_excite.com> ¦b¶l¥ó
>news:6kvfa1lp2lr5o796jf2rcb6ak5l2q5pc5h_at_4ax.com ¤¤¼¶¼g...
>>
>> 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
>>
>>
>Thx, but...the id is representing some real life id identifying storage
>spaces in the warehouse. So we must reuse the same id value if the storage
>space is available (i.e. row get deleted) again. This requirement is
>genuine and cannot be changed to accomodate implementation diffculty.
>

And the storage spaces in a warehouse dynamically change? Ok, give each space a key, i.e. sequence. Each space is dynamically driven, i.e. location, size and inventory. Don't create new records unless you are adding space. Don't delete spaces; resize the storage space or change inventory or whatever but don't delete and insert. Use update. Still sounds like a design problem to me.

And I still don't see why, even if you delete and insert, why you must reuse an ID. Give each storage space a name, i.e. "Storage closet A1A",or "big flat space in the back of the warehouse by column c", whatever. Hide the PK.

Is this a port from MSSQL or something along those lines?

Lewis

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 - 07:09:03 CDT