>
>
> thanx for all the reply..
> im able to lock the record im still facing some major problem.
> i use select * from i_agent where agent_id ='123' with lock; in my vb
> application and when this query is till in open transaction i try to
> run another vb application that will read from the same record and my
> 2nd vb application will hang! until the 1st vb application end the
> transaction! this is no good for clients and server based application.
>

Of course it is no good, but it is not Firebird's fault that you have
set the Transaction2 to be a 'WAIT' transaction. When you define the
transaction as 'WAIT' when lock conflicts occur, the second
transaction will wait until the first transaction commits or rollback.
If it commits, transaction2 will get the exception. If rolledback,
transaction2 succeeds. So this optimistic wait is a poor choice to use
when you have a user waiting for the output (but quite sensible when
you have short automated transactions).

Change your transaction to 'NOWAIT'. You will have to work out how
that is set in VB.

> is there a way for me to prevent this error by happening? or is there
> a way to check if any particular record is being lock? pls help me out

Of course the way you have decided to handle the problem is a recipe
for ensuring your database fills with garbage. The garbage collection
mechanism relies on the gap between the oldest and oldest active
transaction. If you have a transaction left open for too long, and
lots of transactions start in the meanwhile, then you keep old record
versions from being garbage collected.

If you do persist with the select with lock to enforce this rule, then
you need to make sure you have some timeout mechanism on the edit to
make sure they don't click edit then leave to go on leave before
hitting save.

There are other ways of handling this. You could use events to alert
the user that someone else has just deleted the record (and then offer
them the choice of re-inserting the record or cancelling the edit).
Another mechanism is to use an updatenumber field which you fill with
a trigger and generator. You only update fields where the updatenumber
is the same as the update number you selected, otherwise you need to
have a mechanism (automated or manual) for merging changes by two
users to the same record.

Adam