Archives of the TeradataForum

Message Posted: Fri, 06 Aug 2004 @ 13:10:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Friday, August 06, 2004 09:01 -->

Marek,

We battled the same issue for a long time as well. The blocking locks did not occur very often given the number of times the stored procedures were executed, and the blocking session was never doing anything other than holding a lock, so it was difficult to get a handle on. What we *finally* discovered was that the problem procedures had an EXIT HANDLER for NOT FOUND, and a NOT FOUND condition does not result in an implicit rollback, so it was just sitting there waiting for the transaction to end.

     DECLARE EXIT HANDLER
          FOR NOT FOUND
      BEGIN
         SET oReturn_It = 78;
         INSERT INTO
     PRODMD.sp_Error_Log('ODSDB','spD_Account',:SQLCODE,Current_Timestamp(0),
     'Delete Key ' || :iAccount_Row_Nb || ' NOT FOUND');
         END TRANSACTION;  =DF--originally not part of the sp
      END;

An EXIT HANDLER for SQLEXCEPTION does not have the same issue because a SQLEXCEPTION will cause a rollback.

	<Prev	Next>		<<First	<Prev	Next>	Last>>