On March 31, 2003 11:40 am, Antonio Sala wrote:

> Hi!
>
> I work with: Firebird 1.02, FIBPlus, Delphi 6.
>
> The transactions are: write, nowait, rec_version, read_committed
>
> I'm developping a multi-user application.
> Because of it I prohibit that two users can modify the same record of the
> same table at the same time. To implement this restriction I lock the
> record that is desired to modify: If anywhere EDIT the record, is sent
> UPDATE to record in the database. Thus, until itself not the modification
> be accepted (CommitRetainning) the record remains blocked for the other
> users. The EDIT, the UPDATE and the COMMITRETAINING are carried out inside
> a same transaction.

A couple of things, first intentionally locking a record usually isn't
required, the transaction mechanism will take care of dealing with two users
attempting to update the same record at the same time. Second, you may be
trying to do too much within a transaction, whenever you need to use
COMMITRETAINING, then you need to look at whether you are trying to do too
much with a transaction, often this is done to give a CD player type
mechanism to record editing. You need to look at whether you should be using
one transaction to read records, and another one for the updates, replacing
the COMMITRETAINING with a simple COMMIT. The problem is that repeatedly
calling COMMITRETAINING can stall the OAT/OIT pointers, this can hurt
performance. Transactions that update should be as short as possible, often
the cycle read-update-commit can be well under 1 second in length, and that
is the way it should be.