Some typical Case recently

Analysis and suggestion of China Merchants Bank

In view of the recent problems in my work, I selected some typical cases and added my own analysis.

1. Ftpdbn1-deadlock question:

By opening the data fetched by event monitor, it is found that the deadlock is caused by a large number of delete statements of the same table executed by a stored procedure. The stored procedure is defined as follows:

CREATEPROCEDURE CIFDBO.SP_FRM_E_FMEVRFCA_LOG_ADD_V1 (

IN pMDL_INS_COD CIFDBO.MDL_INS_COD_ARRAY

.

IN pREC_CNT CIFDBO.INT_ARRAY

OUT pRtCode INTEGER

)

SPECIFIC SP_FRM_E_FMEVRFCA_LOG_ADD_V1

P1: BEGIN

DECLARE vEVT_ID CHAR (20)

DECLARE i, n INTEGER

DECLARE vTimestamp TIMESTAMP

DECLARE vDateDelete TIMESTAMP

DECLARE SQLCODE INTEGER

DECLAREEXIT HANDLER FOR SQLEXCEPTION

BEGIN

SET pRtCode = SQLCODE

END

SET pRtCode =-1

SET vTimestamp = CURRENT TIMESTAMP

SET vDateDelete = vTimestamp-3 MONTHS

DELETEFROM FRM.FMEVRFCA_LOG WHERE CRT_TIM