Hello Helen.

Thank you for your response.

Just a few minutes after posting my first message I thought I had
discovered what the problem was, so I posted a second message with the
proposed solution: "I was testing the SP with isql. The problem was the
default SNAPSHOT isolation mode. Changing it to READ COMMITTED
everything works as expected".

Now your answer makes me doubt about the correctness of my solution.

> You have two problems.
> --- First is that the handler should *follow* the block where the

exception

> is expected to occur. So your execution flow does not handle any
> exception. I suppose that it simply freezes because the transaction

is set

> to WAIT.
>
> However, fixing that will not correct your execution logic. When your
> error code is set, the WHILE loop causes execution to restart the
> FOR...SELECT loop from the beginning. The whole set is simply

re-executed

> until it again encounters the SAME deadlock error ....and so on until
> eternity (or until memory is exhausted). The "good" records beyond the

bad

> one are never processed.

I think the subsequent FOR...SELECT execution won't find the same
deadlock condition because it was resolved after the concurrent
transaction commited/rolled back its work and the SP catched the
deadlock exception (its transaction is set to WAIT). Every new
FOR...SELECT execution will purge the conflicting records found in the
previous loop.

> Abandon the WHILE loop. Once the exception is handled, execution will

loop

> back and read the next record in the FOR...SELECT loop

If I remove the WHILE loop, conflicting records won't be purged. I want
a SP that purges every record meeting a condition, not beeing affected
for the infrecuent concurrent transactions.

> I would want to add use WHEN ANY so that you catch lock conflicts

other

> than deadlock.

>

The only expected exceptions I want to catch are:

SQLCODE -913: deadlock
SQLCODE -901: cannot update ereased record.

By the way, the latter one shares its SQLCODE with many other exceptions
and I couldn't find a simple way to differentiate it from the others.
AFAIK there isn't a GSDCODE available for this exception (at least in
Firebird 1.0.3).

Other exceptions should be propagated to the caller.

> I would also initialise the value of the subpromotion
> variable and check and reinitialise it at each turn of the loop.

Why should the subpromotion variable be initialized? I guess the
FOR...SELECT BEGIN/END block will be executed once for each row
retrieved in the SELECT statement. So there is no risk at all, is it?

> I hope you also realise that the handler will not undo any of the
> successful purges that have already occurred inside this iteration.

If the

> exception occurs in SP, then the two history purges will not happen (=
> OK). However, if it occurs in TP or SH, then the successful deletes
> preceding it will not be cancelled (is this still OK?).

There is no problem at all because the last table purged is SH. If there
is a cancelled iteration, the next FOR...SELECT execution (because of
the WHILE loop) will retrieve the previously cancelled subpromotion
value from SH and will complete the job.

> I mention the possibility of using the exception handler to write out

to an

> error log, since you currently have no way to know which records were
> skipped because of conflicts.
>
> CREATE PROCEDURE PURGE_TABLES (
> DAYS INTEGER)
> AS
> declare variable subpromotion INTEGER = -99;
>
> BEGIN
>
> FOR SELECT K_SUBPROMOTION
> FROM SUBPROMO_HISTORY SH
> WHERE SH.D_OUTPUT < CURRENT_TIMESTAMP - :DAYS
> AND SH.N_STATE = 2
> INTO :subpromotion
> DO
> BEGIN
> if (subpromotion > 0) then
> begin
> DELETE FROM SETUP_PUSH_PROMOTION SP
> WHERE SP.K_SUBPROMOTION = :subpromotion;
> DELETE FROM TARGET_PUSH_PROMO_HISTORY TP
> WHERE TP.K_SUBPROMOTION = :subpromotion;
> DELETE FROM SUBPROMO_HISTORY SH
> WHERE SH.K_SUBPROMOTION = :subpromotion;
> subpromotion = -99;
> END
> WHEN ANY DO
> BEGIN
> /* DUMMY HANDLER or log the blocked subpromotion value */
> END
> /* Exception has been handled and execution moves to
> the next record in the FOR...SELECT loop */

Shouldn't the WHEN...DO statement be the last statement in a BEGIN...END
block. This is information provided in the docs.
I think the proposed handler placement won't let the next record in the
FOR...SELECT loop be executed.

> DELETE FROM USERS_OPERATIONS UO
> WHERE UO.D_DATETIME < CURRENT_TIMESTAMP - :DAYS;
>
> DELETE FROM DOMAINS_OPERATIONS DOP
> WHERE DOP.D_DATETIME < CURRENT_TIMESTAMP - :DAYS;
>
> /* EXIT; not wanted here; use EXIT when you have a branching

condition

> where the entire procedure is to end without executing any subsequent

code. */

> END
>
> >Testing the SP, if there is no exception at all, everything works
> >perfectly.
>
> Take care with variables. Firebird does not reset them for you.
>
> >But if I force a deadlock exception by means of a prior
> >concurrent update/delete in an independent transaction, the SP seems

to

> >hang and never ends.
>
> With transactions in WAIT, a lock conflict will be unresolved if the
> contending transaction is not committed or rolled back and your SP

does not

> know what to do (because the handler is in the wrong place). That's a
> "hang"....

How could a transaction neither be commited nor rolled back? Maybe a
limbo transaction could hang my waiting SP? Every contending transaction
will be a well behaved one so will finish its job in a fixed amount of
time.


Thank you very much.

Juan Pedro

> ./hb
>
>
>
>
>