I will add that, in any RDBMS, whether locking or MGA, it is faster and
more reliable to use a unique constraint, and just insert and handle an
exception than to explicitly check first then insert. Since the insert
must perform the constraint check, when you explicitly check in your
application you double your I/O to no benefit.

On Thu, 2005-10-13 at 02:38 +0000, Adam wrote:
> --- 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
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>