At 09:27 AM 21/04/2005 +0200, Juan Pedro Lopez wrote:

>Hello everybody.
>
>I've a problem with a SP execution workflow.
>
>This is the SP code:
>
>CREATE PROCEDURE PURGE_TABLES (
> DAYS INTEGER)
>AS
>declare variable subpromotion INTEGER;
>declare variable err INTEGER;
>
>BEGIN
> err = 1;
> WHILE (err > 0) DO
> BEGIN
> err = 0;
> FOR SELECT K_SUBPROMOTION
> FROM SUBPROMO_HISTORY SH
> WHERE SH.D_OUTPUT < CURRENT_TIMESTAMP - :DAYS
> AND SH.N_STATE = 2
> INTO :subpromotion
> DO
> 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;
>
> WHEN SQLCODE -913 DO
> err = 1;
> END
> END
>
> 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;
>END
>
>
>What I want it to do is to purge some tables when a time condition is
>met.
>
>Inside the second begin/end block, three tables are purged. In case a
>concurrent update/delete happens, I catch the deadlock exception and set
>an error condition. The loop should keep executing until the last
>for/select result is retrieved.
>
>The outer while loop just ensures the inner for/select loop is repeated
>in case the error condition is set. In that case the subsequents
>executions should purge the remaining records that couldn't be deleted
>in the previous executions. The whole workflow is an interative loop.

The whole workflow is TWO iterative loops.

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.

Abandon the WHILE loop. Once the exception is handled, execution will loop
back and read the next record in the FOR...SELECT loop

I would want to add use WHEN ANY so that you catch lock conflicts other
than deadlock. I would also initialise the value of the subpromotion
variable and check and reinitialise it at each turn of the loop.

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

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 */

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"....

./hb