SQL Server Performance

Dead lock issue

Discussion in 'SQL Server 2005 General DBA Questions' started by poratips, Aug 6, 2010.

  1. poratips New Member

    Hi,
    I am running Store Procedure in sql server 2005 but sometimes i see deadlock error.
    Could you please advice me what could be the problem and where i need to identifiy?
    I am doing insert/update inside the procedure.
    This is written by third party.

    Please see the Store Procedure
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE procedure [dbo].[TrkOrd]
    (
    @CallType INT = 1
    ,@App VARCHAR(50)
    ,@OrdNum INT = 0
    ,@OrdDt DATETIME = NULL
    ,@OrigReq XML = ''
    ,@OrdReq XML = ''
    ,@Response XML = ''
    ,@SubmitFlag BIT = 0
    ,@Exceptions VARCHAR(MAX) = ''
    )
    AS
    BEGIN
    ---BEGIN TRAN T1
    DECLARE @Ver AS SMALLINT
    IF @CallType = 1
    BEGIN
    SELECT TOP 1 @Ver = ISNULL(Ver,0) + 1 FROM OrdProcStatus
    WHERE App = @App AND OrdNum = @OrdNum
    ORDER BY Ver DESC
    IF @Ver is NULL
    Set @Ver = 0
    Insert INTO OrdProcStatus(App,OrdNum,OrdDt,OrigReqMsg,Ver)
    VALUES(@App,@OrdNum,@OrdDt,@OrigReq,@Ver)
    --IF @@ERROR <> 0 GOTO X
    END
    ELSE IF @CallType = 2
    BEGIN
    SELECT TOP 1 @Ver = Ver FROM OrdProcStatus
    WHERE App = @App AND OrdNum = @OrdNum
    ORDER BY Ver DESC

    UPDATE OrdProcStatus
    SET
    OrdReqMessage = @OrdReq

    WHERE App = @App AND OrdNum = @OrdNum AND Ver = @Ver
    --IF @@ERROR <> 0 GOTO X
    END
    ELSE IF @CallType = 3
    BEGIN
    SELECT TOP 1 @Ver = Ver FROM OrdProcStatus
    WHERE App = @App AND OrdNum = @OrdNum
    ORDER BY Ver DESC

    UPDATE OrdProcStatus
    SET
    ResponseMessage = @Response
    ,SubmitFlag = @SubmitFlag
    WHERE App = @App AND OrdNum = @OrdNum AND Ver = @Ver
    --IF @@ERROR <> 0 GOTO X
    END
    ELSE IF @CallType = 4
    BEGIN
    SELECT TOP 1 @Ver = Ver FROM OrdProcStatus
    WHERE App = @App AND OrdNum = @OrdNum
    ORDER BY Ver DESC

    UPDATE OrdProcStatus
    SET Exceptions = @Exceptions
    WHERE App = @App AND OrdNum = @OrdNum AND Ver = @Ver
    --IF @@ERROR <> 0 GOTO X
    END
    --COMMIT TRAN T1
    SELECT 1 AS Success FOR XML RAW--, XMLDATA
    --RETURN
    --X:
    --ROLLBACK TRAN T1
    --SELECT 0 AS Success FOR XML RAW--, XMLDATA

    END

    Appreciate your kind help!
    thanks
  2. MichaelB Member

    How are you sure this proc is the cause of the issue? Are you running any trace flags? also , is this deadlocking against another SPID running this same code? Please more information. Thanks!
  3. poratips New Member

    Michael,
    Thanks for your response.
    we are running same stored proc in another few application which has same set of tables but its never having locking issue, only particular one applicaiton having dead lock issue.
    Thanks,
  4. ashish287 New Member

    Trace it using profiler.
    In profiler select the deadlock and deadlock graph within lock columns.
    Run your profiler for specific database and then run your query on another window.
    Stop the trace and you will see the deadlock victim in deadlock graph.
  5. poratips New Member

    Thanks Ashish.
    If i turn Profiler ON then it won't put much overhead on DB?
  6. MichaelB Member

    The code looks OK. I would check if indexes are not being used (table scans) by examining the estimated query plan. Most deaqdlocks can be solved by proper indexing and speed. I am guessing that the query is slow or fired very often. If is slow, speed it up.. If it is fired often, then make it fast[:D]
  7. poratips New Member

    Thanks Michael.
    If code is ok then what causes to dead lock?
    Otherthing, its not dead locking in other application but only one application.
  8. rohit2900 Member

  9. satya Moderator

Share This Page