Lynn Pettis (4/29/2013)

---

sql-lover (4/29/2013)

---

Lynn Pettis (4/29/2013)

---

sql-lover (4/29/2013)

---

The best way to fix deadlocks is via T-SQL code, changing the order of your transactions.

Deadlocks occurred because two transactions pin each other, so the 1st one can't complete because is waiting for the 2nd one, but the 2nd one it is also waiting for the 1st one which is still running.

If you change the order on which those are executed, you will fix the deadlock. You can also add a hint, like NO LOCK as part of the select statement. That may alleviate the problem but again, changing the order it's the best way to fix it.

I do NOT recommend you to use SNAPSHOT isolation or anything that can use tempdb without proper testing. tempdb is shared among all databases. If your tempdb can't handle the load, you may create more issues than what you are actually solving.

And I would not recommend using NO LOCK due to all the potential issues it can introduce.

True! 🙂

But using NO LOCK is less dangerous, IMO, than changing to SNAPSHOT isolation level. As a matter of fact, NO LOCK is not the recommended approach either, and will help with select only, that's why I am suggesting changing the actual order of the transactions. But I saw one DBA changing the isolation level without proper testing, and the whole SQL instance started coughing because that, as tempdb was not properly set (right LUN or RAID, size, etc)

You call missing or duplicate data less dangerous? That alone makes me nervous.