Deadlock: insert vs. select from several tables. Snapshot isolation?

In my database I have two closely related tables. There is a frequently called SP that INSERTs some rows into both tables within a transaction, and several other places that do SELECTs from these tables joined.

INSER开发者_如何学运维Ts take X locks on both tables, SELECTs take S or IS locks on them. Since the order in which shared locks are taken varies from query to query, some of them occasionally get deadlocked with the INSERT transaction.

Is there any good way to avoid these deadlocks (NOLOCK probably doesn't qualify as 'good')?

So far the only general solution I can think of is using SNAPSHOT isolation level. However, it would add some performance overhead, and I haven't yet found any sound data on how large this overhead is.

---

I use snapshot in my system. It is not free, that's for sure, but the alternatives are not free either - blocking uses up resources too. Using rowlock does not always help. Also snapshot gives you a consistent point in time snapshot of your data; otherwise you are exposed to some subtle bugs.

One more thing: you can get deadlocks even if you have only one table, examples here: Reproducing deadlocks involving only one table

---

Does you SP select or update anything from these tables inside transaction? If not, you can try to use rowlock hints for your inserts and other selects (rowlocks usually do not escalate into page or table locks, unless you have too many rows in select results). If yes, then you can try updlock hint for your selects inside SP transaction.

---

I'm not sure if this will help you but I found this blog awhile ago and it helped me cleanup some deadlocks.