Another way that is can be done.

There are strict rules in Firebird regarding which transactions are
allowed to modify a record, based on whether the last modification to
that record was committed at the time your record started (snapshot
transactions) or whether it is committed at the time you try an update
(read committed).

This means that if you run an update query on a record and it doesn't
throw a lock conflict exception, no-one else has. Now in theory you
could just leave that transaction uncommitted until you finish the
document, but that is a bad design. If the user goes home without
closing the document properly, you end up with a long running
transaction that inhibits garbage collection.

So you need to allow for four possibilities
* Another user has the record locked in a committed transaction.
* Another user has the record locked in an uncommitted transaction.
* You already have the record locked.
* No-one has the record locked

You could then write a stored procedure to 'lock if possible' or to
return you the name of the user who it thinks does.

SET TERM ^ ;
CREATE OR ALTER PROCEDURE LOCKDOCUMENT
(
USERID INTEGER,
DOCUMENTID INTEGER
)
RETURNS
(
LOCKAQUIRED CHAR(1),
CONFLICTSWITHUSERID INTEGER
)
AS
BEGIN
CONFLICTSWITHUSERID = NULL;
LOCKAQUIRED = 'F';

BEGIN
UPDATE DOCUMENTS SET
CURRENTUSERID = :USERID
WHERE ID = :DOCUMENTID
AND (CURRENTUSERID IS NULL OR CURRENTUSERID = :USERID);

IF (ROWCOUNT > 0) THEN
BEGIN
-- A record was 'modified' by the update
LOCKAQUIRED = 'T';
END
ELSE
BEGIN
-- Find out who modified it
SELECT CURRENTUSERID
FROM DOCUMENTS
WHERE ID = :DOCUMENTID
INTO :CONFLICTSWITHUSERID;
END

WHEN ANY DO
BEGIN
-- Don't use ANY, lookup the code (I am just lazy).
END
END

-- By this stage, we know if we have aquired the lock or not.
-- In some situations, we know who has the lock if we can't
-- aquire it, but there are exceptions that can occur if you
-- have multiple users attempting to lock the same document at
-- the same time.

END
^

Start a transaction, run this procedure and commit immediately. If you
always follow that procedure, even when it can't return you the user,
retrying a few moments later will succeed (in telling you who has the
lock aquired anyway).

You also need an unlock document procedure that sets the CURRENTUSERID
to null if it = your USERID.

As Lester points out, it is possible that a user may have a power
failure or something that causes the lock to not be removed (this is a
consequence of your locking design). With the above procedure, this
can be fixed by having the user who the database thinks has the lock
then take out the lock again and unlock it properly. (The stored
procedure accommodates the fact you may already have the lock).

Adam