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.

DDL Changes breaks cubes

Hi,

Lately I had to increase the size of a table varchar column.

This column is used in a cube dimension attribute.

After the change, cube processing ended-up in error.

I had to first refresh the dataview and then go manually change the KeyColumns Datasize property to increase it as well.

Is there a better way? I find this very dangerous.
As a minimum, a dependency check should include cubes using that metadata.

I notticed the same type of problems on the SSIS sides, metadata becomes out of synch. This is not a show stopper but these are missing link in the dependency food chain.

Sub-systems are not integrated, they are in a vacuum. A little less than with 2000 but still in a vacuum.

Philippe

Very valuable observation.

This is something we are going to take a look at for the next version :)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.