The system need to auto generate the Control Code column for the items. The
business rule is that the system should have an option to assign the lowest
next available Control Code for the new item created.
"Mark D Powell" <Mark.Powell_at_eds.com> ¦b¶l¥ó
news:1106322262.334215.59830_at_c13g2000cwb.googlegroups.com ¤¤¼¶¼g...
> My first question is why you want to do this? If you need to identify
> the data in numeric order with no missing values I would look to
> populate the this value at the time of use by ordering the data on a
> sequence generated value and then assigning a consecutive numeric
> label (rownum).
>
> I am not sure what you want can even be done with 100% certainty. You
> have to consider the Oracle read consistency model where writers do not
> block readers. I though of trying to find the max value then
> performing a select for update on it with nowait specified, inserting
> the new row, replacing the selected row, and commiting. Other sessions
> would error off due to the nowait condition, burn some centi-seconds,
> refind the max value, retry the select for update on the new max value,
> etc.... If testing with several concurrent update attempts shows that
> two sessions can still find the same maxvalue then single threading
> access to the table via dbms_lock would be required.
> This is not efficient nor scalable.
>
> HTH -- Mark D Powell --
>
Received on Fri Jan 21 2005 - 10:41:47 CST
