Re: complex locking and sequence generation

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
.

Relevant Pages

  • Re: Sean Pitman: definitions wanted
    ... but are claiming and asserting without evidence that this ... gaps) is the only possible mechanism for evolution. ... *regardless* of the size of the sequence or the number of amino acids ...
    (talk.origins)
  • Re: Please help with no-gap autoincrement field
    ... My boss wants to have an autoincrement index with no gaps, ... I am new to ORACLE and based on my limited knowledge, ... autoincrement field with no gaps? ... as the source of the key rather than a sequence. ...
    (comp.databases.oracle.misc)
  • Re: The Starting Point Problem - for Howard Hershey
    ... There is only a lack of viable mechanistic evidence to the contrary ... of the genome and for entire genomes. ... Sequence data comparisons do not explain the mechanism. ... mechanism can actually cross these gaps from one steppingstone to the ...
    (talk.origins)
  • Re: Please help with no-gap autoincrement field
    ... Since MySQL has this feature, some of the code depends on it. ... I am new to ORACLE and based on my limited knowledge, ... autoincrement field with no gaps? ... as the source of the key rather than a sequence. ...
    (comp.databases.oracle.misc)
  • Re: complex locking and sequence generation
    ... :> gaps in the sequence, caused by a user not committing a transaction" ... :> for any key value inserted as part of the transaction. ... Have the concurrent sessions insert data using a sequence. ... if it's an audit requirement, could it be met with some sort of an audit ...
    (comp.databases.oracle.misc)