Hi.

Sorry for the inconveniences.

I was testing the SP with isql. The problem was the default SNAPSHOT
isolation mode. Changing it to READ COMMITTED everything works as
expected.

Thank you.

Juan Pedro

> 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
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>