Saturday, November 27, 2010

We are using MSSQL for our site which a large number of users visit at the same time. Our problem is that we encounter the "deadlock" problem so often when the number of these visitors increase. Is there anything you could recommend us to get rid of this problem?

There are many solutions to this problem. But you have to see the business requirement and I can suggest you some standard solutions.

1. Access objects in the same order.
2. Avoid user interaction in transactions.
3. Keep transactions short and in one batch.
4. Use a low isolation level.
5. Use locking hints like NOLOCK (Check Business Requirement)
6. Use FAST_Forward / Read Only cursors
7. Try to get as minimum rows as possible.
8. Try to minimize the use of triggers
9. Make sure that all heavy-processing logic is in SP only.
10. Check normalization

No comments:

Post a Comment