On Wed, 10 Nov 2004 20:32:38 +1100, Helen Borrie <helebor@...> wrote:

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

Thanks. I've tried the FOR UPADTE WITH LOCK and it's perfect. But ¿can
I do that inside a stored procedure? The FOR UPDATE WITH LOCK gives me
an error.

--
Antonio Carrillo
antonio.a.carrillo@...