none
Using TABLOCK

    Question

  • Hi all,

    During a load test , we found a procedure that was causing a deadlock. In the profiler deadlock graph both the process that blocked as well as the victim where having the same object id of the procedure.

    I used WITH (TABLOCK) hint and now I'm not finding the deadlocks.

    Is this a right solution ?
    Will this affect the functionality ?

    Code piece inside the procedure when it caused deadlocks

    INSERT INTO Table1
    SELECT @Col1, @Col2, @Col3, @Col4

    UPDATE Table1
    SET Col5 = @Col5
    WHERE @Col1 = @Var

    UPDATE Table2
    SET Col = @ColVal


    Code piece changed now with WITH (TABLOCK)

    INSERT INTO Table1
    SELECT @Col1, @Col2, @Col3, @Col4

    UPDATE Table1 WITH (TABLOCK)
    SET Col5 = @Col5
    WHERE @Col1 = @Var

    UPDATE Table2 WITH (TABLOCK)
    SET Col = @ColVal


    Any suggestions would be appreciated.

    Thanks,

    DBLearner

    • Moved by Tom PhillipsModerator Thursday, October 14, 2010 5:15 PM TSQL Question (From:SQL Server Database Engine)
    Thursday, October 04, 2007 8:23 AM

Answers

All replies

  • Yes this is 1 of the best ways of doing it you could also put 1 of the following 2 queries into your production environment.

     

    1) 

    INSERT INTO Table1 WITH (TABLOCK)
    SELECT @Col1, @Col2, @Col3, @Col4 OPTION (FAST 1)

    UPDATE Table1 WITH (TABLOCK) 
    SET Col5 = @Col5
    WHERE @Col1 = @Var

    UPDATE Table2 WITH (TABLOCK) 
    SET Col = @ColVal 

     

    2)

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    INSERT INTO Table1 

    SELECT @Col1, @Col2, @Col3, @Col4

    UPDATE Table1  
    SET Col5 = @Col5
    WHERE @Col1 = @Var

    UPDATE Table2  
    SET Col = @ColVal 

     

    Thursday, October 14, 2010 8:10 AM
  • http://www.sqlmag.com/article/sql-server/deadlocks-with-custom-sequence.aspx
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 14, 2010 9:19 AM