Error Slow function gave error

[bigwill]

Hi all

We tried to change a function yesterday which resulted in loop and serious problem for our database. Thought i share the function with you and see if you can spot any problems with it.

The scenario is very easy. Create a new record and assign a counter (which is our primary key for this table).

Here is the code that gave us a problem:

******* Contents of file: gen/KeyFunctions.i:
 
function getNextStockparteventSeqnr returns dec ().
  def buffer bStockpartevent for stockpartevent.
  def var deSeqNr as dec no-undo.
 
  find last bStockpartevent no-lock no-error.  /*stpe-seqnr is set as primary key*/
  assign deSeqNr = (if available bStockpartevent then bStockpartevent.stpe-seqnr + 1 else 1).
 
  /* Check if Ordernr already taken by another User */
  repeat transaction:
    find bStockpartevent
        where bStockpartevent.stpe-seqnr = deSeqNr
        exclusive-lock no-wait no-error.
    if locked bStockpartevent then
    assign deSeqNr = deSeqNr + 1.
    else
    leave.
  end.
 
  if available bStockpartevent then find current bStockpartevent no-lock.
 
  return deSeqNr.
end function.
 
****** End contents of file gen/KeyFunctions.i
 
 
{gen/KeyFunctions.i}.
MAIN:
do transaction:
 
  create StockPartEvent.
  repeat:
    assign StockPartEvent.stpe-seqnr = getNextStockparteventSeqnr().
    if not error-status:error then
      leave.
  end.
 
  /*Find buffer again due to commit problem when calling other procedures later on*/ 
  find current StockPartEvent exclusive-lock no-wait no-error.
 
  /* Do things, assign other values, run other procedures etc */ 
  ...
end.

Does anyone have a better way of getting last number used on a table with high traffic ?

 

[oli]

Consider a database sequence. It's easy, fast & safe.

 

[Cringer]

Absolutely correct oli. This should be a database sequence. That's what they are there for.

 

[bigwill]

I agree. Sequence are better, but should't it work in the example ?

 

[Cringer]

I guess it should work, although it's not the way I would do it for that sort of solution anyway. But the point is, sequences have almost 0 overhead on performance and it's what they're there for, so why would you do it another way that is always going to be inferior?