>
> I have a similar instance of the same problem and overcame it with
> simply using an additional transaction, creating an UPDATE statement
> on the fly when the INSERT fails with a duplicate entry, and running
> the UPDATE via isc_dsql_execute_immediate().

This reduces the chance of the problem, but the other person could
still have the insert uncommitted when you start the update
transaction. It is simply a fact of life for transaction isolation
when you have global constraints and an isolated view.

>
> Don't forget to roll back the original INSERT.

Why? If an insert statement raises an exception, it has no effect.
Firebird automatically undoes anything it did within the operation if
there is an unhandled exception.

You only need to rollback if prior operations within your transactions
should be considered atomic to your insert.

Start Transaction T1
Insert -> fails with Unique constraint violation
Rollback T1

has exactly the same effect as:

Start Transaction T1
Insert -> fails with Unique constraint violation
Commit T1

Adam