Helen Borrie wrote:

> I think you are making some wrong assumptions, perhaps through not
> understanding transaction isolation. You really must get around to
> visualizing a transaction as a wrapper for a single task, that either
> fails
> completely or succeeds completely.
>
> By trying to run a single task in two separate transactions, you risk
> causing exactly the problems that transaction isolation is designed to
> prevent. If you need to check for an existing duplicate, then design the
> task so that it is done in the same context.
>
> The proper way to do this would be with a BEFORE INSERT trigger on the
> Invoice table and using a custom exception to block the operation and
> alert
> the user, something like:
>
> create exception duplicate_invoice 'This invoice has already been
> entered';
> commit;
>
> create trigger bi_check_for_duplicates for invoice
> active before insert position 0 as
> begin
> if (exists (
> select 1 from invoice
> where cust_id = new.cust_id
> and invoice_date = new.invoice_date
> and cust_ref = new.cust_ref) then
> exception duplicate_invoice;
> end
>
> Put the Insert action in the client inside a TRY...EXCEPT loop, have the
> app look for that exception message and you have your totally atomic
> solution. The exception stops the insert statement from being posted and
> the ball is back in the application's court to resolve - perhaps by
> cancelling the insert (which is now only in the client buffer, and not
> posted to the database) and rolling back the transaction, or perhaps by
> getting the user to fix the error and resubmit the request.
>
> However, remember that NO transaction can see work that has not been
> committed by another transaction. Even this transaction could fail if,
> between its start and commit, another transaction with a lower system
> transaction id committed the same combination of unique data items. This
> can happen even with ReadCommitted, depending on the NoWait and
> RecVersion
> settings on the transaction. In almost all conditions, the transaction
> with the lowest transaction id will win.
>
> This system transaction id has nothing to do with any ID that the client,
> i.e. IBO, gives to it. It is known in the database; in Fb 1.5 you can
> read
> the ID of your own current transaction (CURRENT_TRANSACTION). You can
> never find out whether you have the newest transaction id affecting this
> record, other than by attempting to post.
>
> It's a serious flaw in design to try to work around concurrency
> protection
> in the way you are trying to do it. It doesn't work; and you are
> risking
> unpredictable logical corruption. (Logical corruption means bad data
> that
> isn't detected by the engine).
>
> Helen
>

Thanks a lot Helen for those lecture. It trully helps and adds up to my
knowledge in making a much better application.

Cheers,
james