At 06:16 PM 21-12-00 -0500, you wrote:

>Hi:
>
> 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. If you are not using middleware that encapsulates the
pessimistic lock, then you will have to effect it yourself, viz.

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.

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).

This strategy holds the row locked until this transaction is finished with it.

Recognise that, in a high concurrency environment, pessimistic locking will
be a costly performance overhead. If your application lets users go to
lunch whilst leaving open transactions, they will soon want your head on a
plate. If I had a requirement for pessimistic locking in this kind of
environment, I'd want to review my design.

H.

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________