Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: complex locking and sequence generation

Re: complex locking and sequence generation

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Mon, 29 May 2006 18:03:59 +0200
Message-ID: <447b1cba$0$4500$9b4e6d93@newsread2.arcor-online.net>


Mark D Powell schrieb:
> Galen, I would say "obviously not".
>
> Steve, there is no way to meet the requirement ", I cannot allow any
> gaps in the sequence , caused by a user not committing a transaction"
> for any key value inserted as part of the transaction. If the
> transaction fails to complete then the value is gone. Also remember
> the Oracle read consistency model. Writers do not block readers so two
> sessions can produce the same max(col) value and hence attempt to
> update to the same key value unless you serialize access.
>
> Serializing access will kill concurrent DML performance.
>
> Idea. Have the concurrent sessions insert data using a sequence.
> Periodically read the table in sequence order where your desired key
> column is null. Update the desired key value with sequential values.
> Since this is a batch process all keys will be issued without gaps in
> order. You will not be able to use your key as a PK but this method
> will meet your other stated requirements for labeling the data.
>
> However, I note that your vlaue actually appears to be 3 separate
> columns. A constant, character representation of the date formatted a
> specific way, and the revision number. This value would be rather easy
> to construct on data retrieval. Do you really need this as a key to
> the table or only when the data is processed/transmitted? It seems
> that a Function Based Index, FBI, could be part of the solution if you
> just need to ensure no duplicates are entered.
>
> HTH -- Mark D Powell --
>

Another idea - if only gapless representation of data is required - one can use inserts in a table with reqular sequences ( with all the benefits of concurrent processing ) and create a view on this table with row_number() over (order by sequence_generated_values) analytical function. That is an easy way to eliminate the gaps.

Best regards

Maxim Received on Mon May 29 2006 - 11:03:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US