?
Solved

SQL Server Deadlock Free!

Posted on 2009-12-30
10
Medium Priority
?
206 Views
Last Modified: 2012-05-08
I'm a seasoned enterprise app developer and have done a fair amount in the SQL Server realm.  I'm tasked to rebuild our existing SQL schema and eliminating deadlocks is a high priority.  The new schema will use numerous concise SQL procedures and the update volume will be high.  What's the best way to ensure that deadlocks don't ocurr, and if they must, resolve themselves?
0
Comment
Question by:Phil5780
  • 4
  • 3
  • 3
10 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26149703
use of managed transactions will prevent the deadlocks. But this method sometimes causes performance degradation.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26149708
also using the correct transaction isolation level settings can prevent the the deadlocks.
0
 

Author Comment

by:Phil5780
ID: 26149735
Tigin44,
How many percent of performance loss?  Efficiency is my #1 priority:)  I'm introducing an architecture that can scale-out into outer space if necessary.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 26

Expert Comment

by:tigin44
ID: 26149775
its hard to say anything about performance degradation. Its highly bound on the nature of the transactions.. Without monitoring the processes takes place on the server cannot comment about it...
0
 

Author Comment

by:Phil5780
ID: 26149864
When should I use NOLOCK?  I've had DBA's tell me opposing thoughts, just confused me more.  As a master, what say you?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26150309
here are few tips

- use NOLOCK Hints in all your reports
- if you have to perform, the same operations on few tables in multiple places, make sure that you do that in the same order everywhere (for example, say you are updating the tables a and B within a transaction in 2 places, in such cases , make sure that you perform the updates in the same order say first A then B  )
-- make your transaction as less as possible (try avoiding the select statements inside the transaction )
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26150405
use stored procedures for all the operations
0
 

Author Comment

by:Phil5780
ID: 26150779
"use NOLOCK Hints in all your reports"
Is this referring to SQL SELECT?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26150926
yes..
0
 

Author Closing Comment

by:Phil5780
ID: 31671436
Great info, thanks for the help!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question