--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

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

Adam.. the only way i know is select with lock..is there other way to
do it? can you help me to list some of the option? i know there is
select with lock ,select update with lock