.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

Using TABLOCK

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :Sql Server
 

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




View Complete Post


More Related Resource Links

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend