On 24-9-2018 16:46, blackfalconsoftware@... [firebird-support]
wrote:

> I would think that there are two ways that this issue could be easily
> handled...
>
> 1...
> Ifthe SQL code in question is within a stored procedure then error
> trapping could be implemented to  throw an error back to the calling
> procedure.  Thus, if a user attempted to update a record that no longer
> existed, the stored procedure could throw an error.
>
> The following stored procedure throws an error if by accident a
> zero-based key is provided as the parameter value...
>
> >>>
> CREATE PROCEDURE SP_GET_CAT_CHILD_NODES_BYKEY(
>   PI_KEY_IN BIGINT NOT NULL)
> RETURNS(
>   PI_KEY_OUT BIGINT,
>   PI_PARENT_KEY_OUT BIGINT)
> AS
> BEGIN
>   FOR
>     WITH RECURSIVE HIERARCHY_TABLE AS
>     (
>         SELECT RCN1.CN_KEY,
>                RCN1.CN_PARENT_KEY
>             FROM RI_CATEGORY_NODES RCN1
>             WHERE RCN1.CN_KEY = :PI_KEY_IN
>             UNION ALL
>                 SELECT RCN2.CN_KEY,
>                        RCN2.CN_PARENT_KEY
>                     FROM RI_CATEGORY_NODES RCN2
>                  &n bsp;  JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY =
> HIERARCHY_TABLE.CN_KEY
>     )
>     SELECT CN_KEY,
>            CN_PARENT_KEY
>         FROM HIERARCHY_TABLE
>         INTO :PI_KEY_OUT,
>              :PI_PARENT_KEY_OUT
>   DO
>     BEGIN
> IF (PI_KEY_IN = 0) THEN
>         EXCEPTION ROOT_CAT_NODE_DELETE;
>
>       SUSPEND;
>     END
> END;
> <<<

That looks a bit complicated and I'm not sure how you'd apply this for
the given question.

One could check row_count after the update, or in a client, retrieve the
update count.

> The above code does not require any form of transactional processing
> since it is a retrieval routine.  However, by using transactional
> processing, the existence of a record to be deleted can be tested for
> within the transaction and if not found can simply then ignore the
> process to delete it, returning a success result since nothing was done
> to incur an error.

That depends, if the second transaction is started before the first is
committed, you get an update conflict error.

> Here is a link for the Firebird documentation on handling
> transactions.with the options to force a transaction to act in a
> single-threaded manner (WAIT option)
>
> https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-transacs-statements.html

That is not an entirely correct interpretation of what WAIT does, or at
least a bit too simplified (and besides, the OP was already using WAIT).
WAIT transactions work in parallel unless they want to update (or
delete) the same record. In that case, the second will wait until the
first transaction completes. If that transaction completes with a
rollback, the second transaction can continue, if committed, it will
fail with an update conflict.

The only real way to avoid this situation, is to use a SNAPSHOT TABLE
STABILITY transaction with table reservation, but that can reduce
concurrency by a great deal.

Mark
--
Mark Rotteveel