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.

Testing the SP, if there is no exception at all, everything works
perfectly. 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.

I guess the SP code is wrong at any point but I can't undestand why.

Could you plese tell me something about it?

Thank you very much.

Juan Pedro Lopez