--- In firebird-support@yahoogroups.com, "Leonardo DIppolito"
<leodippolito@g...> wrote:

>
>
> Are they really necessary for simple CRUD operations? I am trying to
> understand when to use them, since for update's and delete's I can use
> an optimistic concurrency mechanism, like a timestamp column.
>
> If I need to check if a record exists before inserting, to avoid
> duplicates, the only way to do this is by using a lock?
>
> I appreciate any information.
>

Firebird is a MGA Database, and as such your simplified locking
approach may hit some brick walls. Firstly, unless you have a single
transaction at a time system, you will not be able to test for
duplicates in a before insert trigger etc.

You can however declare a unique constraint on a field, and any
attempt to insert a record will result in an exception which allows
you to take appropriate alternate action.

Generally speaking, you want to avoid using locks unnecessarily. They
inhibit performance, cause scalability nightmares and provide no real
benefit.

When someone updates a record, a new version of that record is
created. No other transaction will be allowed to update that same
record until the first transaction commits or rolls back. You can set
different behaviours, such as nowait, which will immediately generate
an exception in such a case, or wait, which will hang around until the
first transaction commits before raising an exception. Of course if
the first transaction rolled back, the second transaction would
succeed in the update.

There are occasions where you can benefit from using a record as a
gate, but these need to be carefully considered, because most of the
time they are not really necessary.

Adam