Re: complex locking and sequence generation

"Maxim Demenko" <mdemenko_at_arcor.de> wrote in message news:447b1cba$0$4500$9b4e6d93_at_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

i'm wondering if the gapless requirement is an audit requirement or a 'prettiness' requirement

if it's an audit requirement, could it be met with some sort of an audit trail tracking inserts and deletes to prove that numeric 'gaps' do not represent missing records. there's also the possibility of generating the sequence via a function with an autonomouse transaction that writes an audit trail of sequence requests (not sure if that really would have much value, though).

++ mcs

++ mcs Received on Mon May 29 2006 - 11:45:34 CDT