SQL Server Performance

Lock request time out period exceeded

Discussion in 'T-SQL Performance Tuning for Developers' started by anilsaritha, Aug 9, 2006.

  1. anilsaritha New Member

    Friends<br /><br />Below is the exception information from my production server's event log file<br />1) Exception Information<br />*********************************************<br />Exception Type: System.Data.SqlClient.SqlException<br />Errors: System.Data.SqlClient.SqlErrorCollection<br />Class: 16<br />LineNumber: 49<br />Message: Lock request time out period exceeded.<br />Lock request time out period exceeded.<br />Number: 1222<br />Procedure: IRC_fHasONTXN<br />Server: CBSDATA<br />State: 54<br />Source: .Net SqlClient Data Provider<br />TargetSite: System.Data.SqlClient.SqlDataReader ExecuteReader(System.Data.CommandBehavior, System.Data.SqlClient.RunBehavior, Boolean)<br />HelpLink: NULL<br /><br /><br />Based on this info I analyzed the function IRC_fHasONTXN<br />I am placing the code for this function.<br />Can Somebody please tell me whether this function can be tuned to avoid Lock Request timeout period exceeded error?.Whether this function adheres to the querying standards?<br />Kindly guide<br /><br /><br /><br />CREATE FUNCTION dbo.IRC_fHasONTXN <br />( <br />@p_BrID UDVC16, <br />@p_PrdID UDVC16, <br />@p_AcctID UDVC16, <br />@p_OprType UDVC25, <br />@p_BusnDt UDVC25, --&lt;&lt;ADDED BY EDA ON 13.10.2005(TO AVOID NEXT DEMAND COLLECTION) <br />@p_TxnAmt UDLDGRAMT <br />) <br />RETURNS UDVC25 <br />AS <br />BEGIN <br /> /************************************************************************** <br /> 'SP NAME :IRC_fHasONTXNUPD <br /> 'Parameter <img src='/community/emoticons/emotion-3.gif' alt=':O' />prtntype,BrID,PRdID,AcctID <br /> 'Output Parameter : <br /> 'Created By :MSS <br /> 'Created Date :01-09-2005 <br /> 'Description : <br /> 'Modified By :EDA(EDA0704200601) <br /> 'Modified Date :07-04-2006 <br /> '**************************************************************************/ <br /> DECLARE @m_HasIntParams UDBIT, <br /> @m_RaiseDem UDCH1, <br /> @m_HasOnTxn UDCH1, <br /> @m_IntCalcFreq UDVC25, <br /> @m_PrdType UDVC25, <br /> @m_Purpose UDVC25, <br /> @m_NxtDmdDt UDDT, <br /> @m_AcctOpnDt UDDT, <br /> @m_DmdFreq UDVC5, <br /> @m_FreqType UDVC5, <br /> @m_BusnDt UDDT, <br /> @m_DmdRoutExec UDCH1, <br /> @m_CustOSClear UDLDGRAMT, <br /> @m_LstDmdDt UDDT, <br /> @m_IntRePayFreq UDVC10, <br /> @m_MatDt UDDT, <br /> @m_IntRepayType UDVC10 <br /> <br /> --&lt;&lt; ADDED BY EDA ON 13.10.2005(FOR HAS ONTXN FUNCTION) <br />SET @m_BusnDt = dbo.fSetBusnDate(@p_BusnDt) <br /> <br />SELECT@m_PrdType = PrdType <br />FROM SAM_tProductMst <br />WHERE PrdID = @p_PrdID <br /> <br /> IF @m_PrdType = 'LON' BEGIN <br /> --&lt;&lt;MODIFIED BY EDA ON 12.10.2005(TO AVOID NXT COLLECTION) <br /> SELECT @m_HasIntParams = E.HasIntParams, <br /> @m_RaiseDem=isnull(B.IsRaiseCurrDem,'N'), <br /> @m_NxtDmdDt = NxtDmdDt, @m_AcctOpnDt = OpeningDt, <br /> @m_CustOSClear = CustOSClear <br /> FROM LON_tInstEnt E,Lon_tAcctBaln B <br /> WHERE E.BrID = @p_BrID <br /> AND E.PrdID = @p_PrdID <br /> AND E.AcctiD= @p_AcctID <br /> AND B.BrID = E.BrID <br /> AND B.PrdID = E.PrdID <br /> AND B.AcctiD= E.AcctiD <br /> <br /> IF @m_HasIntParams = 0 <br /> BEGIN <br /> SELECT @m_IntCalcFreq=IntCalcFreq, @m_DmdFreq = IntDemandFreq, <br /> @m_FreqType = DemandFreqType <br /> FROM SAM_tPrdIntParams <br /> WHERE PrdID = @p_PrdID <br /> END <br /> ELSE <br /> BEGIN <br /> SELECT @m_IntCalcFreq=IntCalcFreq, @m_DmdFreq = IntDemandFreq, <br /> @m_FreqType = DemandFreqType <br /> FROM LON_tAcctIntParams <br /> WHERE BrID = @p_BrID <br /> AND PrdID = @p_PrdID <br /> AND AcctiD = @p_AcctID <br /> END <br /> <br /> IF Upper(@p_OprType) ='COLLECTION' or Upper(@p_OprType) ='RECEIPTS' <br /> BEGIN <br /> IF Upper(@m_IntCalcFreq) ='ONTXN' <br /> BEGIN <br /> SET @m_Purpose ='ONTXN' <br /> END <br /> --&lt;&lt;MODIFIED BY EDA ON 12.10.2005(TO AVOID NXT COLLECTION) <br />-- ELSE IF Upper(@m_IntCalcFreq) ='ONFREQ' BEGIN-----BY MSS 24-12-2005 <br /> ELSE IF (Upper(@m_IntCalcFreq) ='ONFREQ' AND Upper(@m_DmdFreq)&lt;&gt;'NA')BEGIN <br /> IF @m_RaiseDem='Y' AND @m_NxtDmdDt &lt;= dbo.IRC_fChkOnTxnDt(@m_BusnDt, @m_AcctOpnDt, @m_DmdFreq, @m_FreqType) BEGIN <br /> IF @p_TxnAmt &lt;&gt; 0 AND (@m_CustOSClear &gt; @p_TxnAmt) BEGIN <br /> SET @m_Purpose ='PENALCALC' -- Modified By RMA on 20-03-2006 -&gt; Changed to Calculate Penalty for TxmAmt &lt; CustOsClear <br /> END <br /> ELSE BEGIN <br /> SET @m_Purpose ='ONTXN' <br /> END <br /> END <br /> IF @m_RaiseDem='N' AND @m_NxtDmdDt &lt;= dbo.IRC_fChkOnTxnDt(@m_BusnDt, @m_AcctOpnDt, @m_DmdFreq, @m_FreqType) BEGIN <br /> SET @m_Purpose ='PENALCALC' <br /> END <br /> END <br />-- ELSE <br />-- IF @m_RaiseDem='N' <br />-- BEGIN <br />-- SET @m_Purpose ='PENALCALC' <br />-- END <br />-- ELSE <br />-- BEGIN <br />-- SET @m_Purpose ='ONTXN' <br />-- END <br /> END <br /> ELSE if Upper(@p_OprType) ='PRECLOSURE' <br /> BEGIN <br /> SET @m_Purpose='PRECLS' <br /> END <br /> ELSE if Upper(@p_OprType) ='CLOSURE' <br /> BEGIN <br /> --&lt;&lt;MODIFIED BY EDA ON 03.10.2005 (FOR CLOSURE) <br /> SET @m_Purpose='ONCLOSURE' <br /> END <br /> <br /> END <br /> ELSE IF @m_PrdType = 'DEP' BEGIN <br /> SELECT @m_HasIntParams = HasIntParams, @m_LstDmdDt = LstDmdDt, @m_MatDt = MatDt <br /> FROM DEP_tDepAcctMst IE, DEP_tAcctBaln AB <br /> WHERE IE.BrID = @p_BrID <br /> AND IE.PrdID = @p_PrdID <br /> AND IE.AcctID = @p_AcctID <br /> AND IE.BrID = AB.BrID <br /> AND IE.PrdID = AB.PrdID <br /> AND IE.AcctID = AB.AcctID <br /> <br /> IF @m_HasIntParams = 1 BEGIN <br /> SELECT @m_IntCalcFreq= IntCalcFreq, @m_IntRePayFreq = IntRepayFreq, <br /> @m_IntRepayType = IntRepayType, @m_DmdFreq = IntDemandFreq <br /> FROM DEP_tAcctIntParams <br /> WHERE BrID = @p_BrID <br /> AND PrdID = @p_PrdID <br /> AND AcctiD = @p_AcctID <br /> END <br /> ELSE BEGIN <br /> SELECT @m_IntCalcFreq= IntCalcFreq, @m_IntRePayFreq = IntRepayFreq, <br /> @m_IntRepayType = IntRepayType, @m_DmdFreq = IntDemandFreq <br /> FROM SAM_tPrdIntParams <br /> WHERE PrdID = @p_PrdID <br /> END <br /> --&lt; --- return purpose based on oprtn type----&gt; <br /> IF Upper(@p_OprType) ='COLLECTION' Or Upper(@p_OprType)='RECEIPTS' <br /> BEGIN <br /> IF Upper(@m_IntCalcFreq) ='ONTXN' <br /> BEGIN <br /> SET @m_Purpose ='ONTXN' <br /> END <br /> END <br /> ELSE if Upper(@p_OprType) ='PRECLOSURE' <br /> BEGIN <br /> SET @m_Purpose='PRECLS' <br /> END <br /> ELSE if Upper(@p_OprType) ='CLOSURE' <br /> BEGIN <br />-- SET @m_Purpose='ONCLOSURE' <br /> --&lt;&lt;MODIFIED BY EDA ON 21.10.2005 (FOR CLOSURE) --&lt;&lt;EDA2103200601 <br /> IF (@m_IntRepayType ='MD' Or @m_IntRepayType ='RDTYPE') AND DATEADD(Day,1,IsNull(@m_LstDmdDt,@m_MatDt)) &gt;= @m_MatDt BEGIN <br /> SET @m_Purpose='' <br /> END <br /> ELSE BEGIN <br /> SET @m_Purpose='ONCLOSURE' <br /> END <br /> END <br /> ELSE IF (Upper(@p_OprType) ='PAYMENTS' AND @m_IntRepayType ='MD')--&lt;&lt;EDA0704200601 <br /> BEGIN <br />-- IF DATEADD(Day,1,@m_LstDmdDt) &gt;= @m_MatDt BEGIN <br /> IF DATEADD(Day,1,IsNull(@m_LstDmdDt,@m_MatDt)) &gt;= @m_MatDt AND @m_DmdFreq &lt;&gt; 'NA' BEGIN <br /> SET @m_Purpose='' <br /> END <br /> ELSE BEGIN <br /> IF @m_BusnDt &gt;= @m_MatDt BEGIN <br /> SET @m_Purpose='ONCLOSURE' <br /> END <br /> ELSE BEGIN <br /> SET @m_Purpose='' <br /> END <br /> END <br /> END <br /> END <br /> <br />RETURN @m_Purpose <br /> <br />END <br /> <br /><br />Deep Regards<br />Anil<br /><br />Never Give Up
    anilsaritha, Aug 9, 2006
    #2

  2. anilsaritha New Member

    Friends


    Introduction of NOLOCK hint in the Select statements in the Function mentioned in the previous post can avoid Locks?

    Regards
    Anil




    Never Give Up
    anilsaritha, Aug 9, 2006
    #2

Share This Page