At 06:27 AM 4/04/2006, you wrote:

>I have a situation where I end up locking and unlocking a lot of records one
>at a time throughout the day and occaisonally one of the computers will
>crash after locking a record which leaves the record locked untill all
>computers disconnect from the database. Is there a way to force an unlock on
>said record?

No. Whether you use an "application lock" or the FOR UPDATE WITH
LOCK syntax, a row, once locked, stays locked until the transaction
ends. So long-running and dead transactions are going to hold the
lock as long as the transaction lasts.

In the case of "dead" transactions - those that had locks at the time
of a crash - even disconnecting all users will not guarantee that the
lock is released, since the database is not in a physically shut-down
state. Your application code (hopefully) will have taken care to
commit or roll back pending transactions when users exit from their
programs. But the "dead" transactions will survive until the server
eventually detects that the crashed connection is gone (at least 2
hours after the crash, with default Linux and Windows keepalive settings).

The lesson here is *not* to use pessimistic locking in applications
where there is any likelihood of transactions not completing
(committing or rolling back) immediately.

I guess you have already heard the sermon about it being both
unnecessary and undesirable to use pessimistic locking in an
optimistic locking environment unless there is a real reason (read
"business requirement") to do so. Porting an application from a DBMS
that achieves its concurrency through pessimistic locking is not a
reason to use pessimistic locking in Firebird.

./heLen