Deadlocking Issue in stored proc

  • I have a stored procedure that is causing exceptions to be thrown regularly in my ASP.NET app.

    The stored proc is basically inserting into 2 tables, with the inserts wrapped in a transaction. This is the only stored proc that is making inserts into this database, so I am assuming that this stored proc is the problem, and maybe the stored proc design is flawed.

    Is there a better way to make these inserts without getting deadlocks? Here is my stored proc code:

    ALTER PROCEDURE [dbo].[s_StatsInsert]

    (

    @StatsXML nvarchar(4000),

    @Result int output

    )

    AS

    BEGIN

    DECLARE @DocHandle int, @PageId int, @Error int

    EXEC sp_xml_preparedocument @DocHandle output, @StatsXML

    BEGIN TRAN

    INSERT INTO Page([Date], SiteName, PageName, RawUrl, ClientIP, SessionID, Username, UserAgent, Referer)

    SELECT P.[Date], P.SiteName,

    CASE P.PageName

    WHEN 'handlers/glossary.ashx' THEN 'document.aspx'

    WHEN 'handlers/sectionlog.ashx' THEN 'document.aspx'

    ELSE P.PageName

    END,

    P.RawUrl, P.ClientIP, P.SessionID, P.Username, P.UserAgent, P.Referer

    FROM OPENXML(@DocHandle, '/Page', 2)

    WITH (

    [Date] datetime,

    SiteName varchar(50),

    PageName varchar(50),

    RawUrl varchar(256),

    ClientIP varchar(64),

    SessionID varchar(40),

    Username varchar(50),

    UserAgent varchar(256),

    Referer varchar(256)

    ) P

    SET @Error = @@ERROR

    SET @PageId = SCOPE_IDENTITY()

    IF @Error = 0 BEGIN

    INSERT INTO PageData(PageId, [Name], Value)

    SELECT @PageId, D.[Name], D.Value

    FROM OPENXML(@DocHandle, '/Page/Data', 2)

    WITH (

    [Name] nvarchar(256),

    Value nvarchar(256)

    ) D

    END

    SET @Error = @@ERROR

    IF @Error = 0 BEGIN

    COMMIT TRAN

    SET @Result = 0

    END

    ELSE BEGIN

    ROLLBACK TRAN

    SET @RESULT = -1

    END

    EXEC sp_xml_removedocument @DocHandle

    END

    Thanks for looking.

    Joel

  • How big is the XML you are passing in? I've seen issues where if this is large, then it will take a while to parse through it. I've found better performance putting it in a table variable first and then using that in the inserts. Also, you should use a try catch block instead of what your using. Lastly, in the catch part of the try/catch, you can check for deadlocks and then retry. See this article on MSDN about how to do this

    http://msdn.microsoft.com/en-us/library/aa175791(SQL.80).aspx

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • My XML is aroung 600 chars long which results in 1 record being inserted into the first table and 4 or 5 records being inserted into the second table. I will try using a temp table so that I am not inserting directly from the XML, as you have suggested.

    ... but a question on locking. The first insert statement causes a lock on the first table, when would this lock get released? I assumed that because I put both inserts into transaction, then the lock on the first table would not be released until the transaction is committed. Is this how locking works? maybe my understanding is wrong.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply