At 02:21 PM 12/22/2000 +1100, Helen Borrie wrote:

> >I think the record IS locked when you edit it. You need to set
> >WAIT in transaction params (See <--- below) to avoid a deadlock error.
>
>This is NOT good advice. Never use WAIT for a transaction whose life is
>under user control.

Why not? Wait doesn't affect records that the transaction holds,
just those that it wants that are held by others. The advantage
of waiting is that you don't consume any cycles until the record
becomes available.

>Make certain that Autocommit is OFF.
>
>Test the transaction and if it is InTransaction then
> Shut down the transaction (i.e. commit and on exception roll it back).
>
>Inside a retry loop do
> Start the transaction.
> Set up the lock statement (update ATable set pkfield=pkfield where
>pkfield = :pkfield).
> Prepare the lock statement.
> Pass parameter to lock statement.
> Execute the lock statement.
> If lock conflict exception is returned
> Roll back the transaction.
> Loop back.

Using a WAIT transaction for the loop above can save lots of
unnecessary iterations.

>If transaction is still InTransaction then you have the lock.
>Proceed to:
> Get the dataset for edit if necessary.
>When ready to perform the update:
> If the update statement is prepared then unprepare it (set Prepared to
>False).
> Prepare the update.
> Assign values to parameters as required.
> Execute the update.
> Commit and on exception roll it back. (At this stage an exception won't
>be from lock conflict but from other causes and it will be necessary to
>begin again).

Perhaps you're concerned about your user becoming bored waiting
for a lock. In that case a timer in another thread could abort
the request. (Having just read ahead in the list, I realize that
this is the concern. Rather than using a no-wait and wasting the
server's time, why not post a periodic message to the user saying
that you're still alive? That would keep the wasted effort on the
client side.


Regards,

Ann
www.ibphoenix.com
We have answers.