bugg (1/22/2013)

---

Any insight into dealing with deadlocks would be greatly appreciated.

anthony.green (1/22/2013)

---

Do you have traceflag 1222 enabled?

If so can you post the deadlock graph from the error log

If not run

DBCC TRACEON (1222, -1)

and then post the graphs once a deadlock has occured.

GilaMonster (1/22/2013)

---

In general:
Optimise the queries. If that doesn't fix the problem use one of the row versioning-based isolation levels

bugg (1/22/2013)

---

Started profiler now. Will update once ive captured a deadlock

bugg (1/22/2013)

---

Thanks Gila, I'm going to try using Rowlock with the update commands and see if that clears the locks.

bugg (1/22/2013)

---

...
After a bit of research I don't really want to use SNAPSHOT ISOLATION or READ_COMMITTED_SNAPSHOT as this approach would require downtime to alter the database and possibly loads of testing and might be overkill for my needs and unnecessary overload on my temp db.
...

GilaMonster (1/22/2013)

---

Profiler? That traceflag writes the deadlock graph into the SQL error log, not a trace event.

GilaMonster (1/22/2013)

---

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?