Phil5780
asked on
SQL Server Deadlock Free!
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?
tigin44
use of managed transactions will prevent the deadlocks. But this method sometimes causes performance degradation.
also using the correct transaction isolation level settings can prevent the the deadlocks.
ASKER
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.
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.
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...
ASKER
When should I use NOLOCK? I've had DBA's tell me opposing thoughts, just confused me more. As a master, what say you?
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 )
- 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 )
use stored procedures for all the operations
ASKER
"use NOLOCK Hints in all your reports"
Is this referring to SQL SELECT?
Is this referring to SQL SELECT?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great info, thanks for the help!