August 23, 2009 at 4:20 pm
Deadlock for typical scenario in SQL Server 2005:
Select -> Update -> Select
What is the best way to avoid deadlock for above (details below)scenario:
Use RowLock hint for First Select
Use RowLock hint for Second Select
Avoid putting Table locks during updates on myTable
Any other way / suggestion
Also do we have query to get readable/formated locked information on abobe case (table invloved, lock level, session, login user)
----------------------
begin tran
select * from mytable where c1 = 'abc'
update mytable set c2 = 100
where c3 = 200 -- ONE OF THE RECORDS RETURNED BY THE ABOVE SQL
----
before the transaction commits, another process does this:
select * from mytable where c1 = 'abc'
and is blocked by the first transaction having a table lock on the entire mytable.
August 23, 2009 at 8:20 pm
At the beginning you say "Deadlocks" but at the nd you say "blocking". Which is it, or which is the problem? Any solution to prevent deadlocks is likely to involve blocking of some sort.
Also, this would be easier if you could show us the DDL script for the tables including any keys, indexes and constraints.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 23, 2009 at 11:59 pm
The best way to avoid deadlocks are optimal code and effective indexes.
If you're getting deadlocks, please can you post table structure, the two queries involved and the deadlock graph.
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply