bugg (1/22/2013)
GilaMonster (1/22/2013)
bugg (1/22/2013)
GilaMonster (1/22/2013)
bugg (1/22/2013)
Any insight into dealing with deadlocks would be greatly appreciated.In general:
Optimise the queries. If that doesn't fix the problem use one of the row versioning-based isolation levels
Thanks Gila, I'm going to try using Rowlock with the update commands and see if that clears the locks.
Optimise your queries, don't mess with locking hints unless you really understand what you're doing and why and have optimised as much as possible.
You do realise that a row lock hint means more chance of lock escalation (to table), not less?
Nope I did not realize that! I thought it was specific to the row being updated :unsure:
Depends how many rows the update effects. If it's one row (and the stats estimate that), SQL would probably go for a row lock anyway. If it's several thousand rows, SQL would probably go for page locks, if you put a row lock hint on, SQL will start with row locks, if the number of locks held pass a threshold, it escalates to table locks.
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