At 09:21 AM 10/11/2004 +0100, you wrote:

>Hello.
>
>I'm porting an application written in Delphi and DB2 to Delphi and
>Firebird. The application needs to call an stored procedure that locks
>some records in a table and other stored procedure that unlocks them
>(weird design, I know, but it's not in my hands to change this). Is
>there anyway to do this?

To "lock" records in Firebird, you have to either

a) update them in an explicit transaction that is in concurrency isolation, or
b) select them FOR UPDATE WITH LOCK, in concurrency isolation

The only way then to "unlock" them is to either commit or roll back the
transaction.

For a) Delphi programmers usually just perform a dummy update in the
AfterEdit event, viz.
update mytable set ThePrimaryKey = ThePrimaryKey
where .....

SANS AutoCommit, of course!! And with any update triggers conditioned
somehow not to fire when the dummy update is performed.

For b) the pessimistic lock doesn't kick in until the application actually
fetches the row. Using the Delphi dataset model, only those rows that
actually arrive in the buffer will be locked; the waiting rows on the
server will be subject to getting locked for update or delete by another
transaction. You won't know that until the application actually calls for
the row to be fetched. This means you must strictly limit the rows
requested, preferably to one single row.

In both cases, the "lock request" will fail if *any* of the requested rows
is already locked by another transaction.

Note also that any rows that are dependent on a "locked" row (through a
foreign key) will be locked also, until Commit or Rollback of the
transaction occurs.

./hb