Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-10-04 : 02:37:36
|
| 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,Hariarul |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-04 : 02:39:52
|
If you are in a multiuser environment, I would go for ROWLOCK hint instead.TABLOCK hint might give faster performance since it "locks" out all other queries during statement.Not using hints, defaults to PAGELOCK.Also read Books Online about locks.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2007-10-04 : 04:06:07
|
| TABLOCK is useful in an environment where performance is a critical issue, particuarly rapid inserts.Essentially , it decreases the lock contention on the table.Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2007-10-04 : 06:48:42
|
| I a full tablock will improve large inserts/updates but severely degrade selects unless you specify the nolock hint. Selecting "dirty" data is dangerous but used carefully it can improve performance quite a bit. Proper indexing is also crucial to prevent excessive locking/deadlocking...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
|
|
Using TABLOCK.