Deadlock

In a high traffic environment, deadlocks eventually occur as number of data processes increase. How can deadlocks be avoided, minimized and resolved. Please kindly provide scenario examples and samples of T-SQL code. Thanks much.

Check out this previous thread on deadlocks:http://forums.asp.net/862097/ShowPost.aspx. You might find something in there to help you, in particular this link:Tips for Reducing SQL Server Deadlocks.|||In your previous post, you said:
These 3 suggestions have all but eliminated deadlocking for me:
-- Keep transactions as short as possible
-- Reduce lock time
-- Consider using the NOLOCK hint
Did a full combination of the above eliminate deadlocking or not?
Please advise how to implement the 2nd and third suggestions with sample T-SQL code.
Thanks much.
|||

thuhue wrote:

In your previous post, you said:
These 3 suggestions have all but eliminated deadlocking for me:
-- Keep transactions as short as possible
-- Reduce lock time
-- Consider using the NOLOCK hint
Did a full combination of the above eliminate deadlocking or not?
Please advise how to implement the 2nd and third suggestions with sample T-SQL code.
Thanks much.

For my particular situation, yes, the above eliminated the deadlockproblems we were experiencing. #1 and #2 were for me the samething -- using transactions as late as possible and finishing them asearly as possible reduced the lock time and went a long way to reducingcontention. For the NOLOCK hint, this is how you use it:

SELECT someColumns FROM myTable WITH (NOLOCK) WHERE someCondition

See also:SQL Server Lock Contention Tamed: The Joys Of NOLOCK and ROWLOCK.