Leonardo DIppolito wrote:

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

If what you mean is "select ... for update with lock", you would use
that syntax if you wanted to reserve a record for your transaction's
use. That syntax is preferable to the older "update in place" method
because it doesn't cause update triggers to fire. The need for this is
relatively rare, and the feature should be used only as a last resort
because it can cause cascading system wide waits.

The most reliable way of dealing with duplicates on insert is uniqueness
constraints (or primary key) and error handling on the insert. Because
the changes made by one transaction are invisible to concurrent
transactions, you can't guarantee uniqueness by selecting the value.

Regards,


Ann