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: Mark D Powell <Mark.Powell_at_eds.com>
Date: 29 May 2006 08:47:06 -0700
Message-ID: <1148917626.719849.187180@j33g2000cwa.googlegroups.com>


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 -- Received on Mon May 29 2006 - 10:47:06 CDT

Original text of this message

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