HI Group,

Thanks to Helen and Lester for responding to my
inquiry.

Helen responded with [snip]
"If possible, get your number from a generator
because generators (unlike absolutely positively
everything else) are independent of transactions. Once
a transaction gets that number, no other transaction
can get it; and there is no possibility of any locking
conflict.

You are getting this locking bottleneck because the
main task and the getting and setting of the serial
number are enclosed in the same transaction context.
You will get a deadlock (or, more descriptively, a
"livelock" which locks up all users of the number
table without resolution) as soon as any two
transactions both want to get and set the number.

If you *must* implement this serialization like this,
do it with a stored procedure that returns the new
number as an output parameter. Make a function in your
app that calls the procedure to get and update the
number table in a __separate__, dedicated,
pessimistically locked transaction that just "hits and
runs". Make both this transaction and your main
transaction tiReadCommitted. Set PessimisticLocking to
true on the get-and-set function.

Make the function deliver the procedure call statement
via a TIB_DSQL (Execute, not Open, natch!!). Set its
StoredProcHasDML property true. Read the Fields[0]
property of the statement in its AfterExecute event to
get the number. Have the function handle any locking
conflict by rolling back and resubmitting until you
get a result. "

In reviewing the responses and the situation, I have
come to the conclusion that the table is not required.
The suggestion of a generator used with a stored
procedure to get the next number is superior. I am
confused by the last paragraph. What do I have to set
for ib_dsql1 and will a try... except... end statement
within the function be enough to trap for a deadlock.
Do you have any code samples that illustrate what is
being discussed.

Thanks in advance,
rgds,
David Husarik


__________________________________________________
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone.
http://phone.yahoo.com