Deadlocking
Deadlocks occur when two tasks permanently block each other because each task has a lock on a resource needed by the other task.
1. SET DEADLOCK_PRIORITY Low /Normal/High or -8,-9 8,9,0
2. While adding NOLOCK can prevent readers and writers from blocking each other
3. where two or more threads are each blocked by the other so that no one can proceed.
4.
To help identify which tables or stored procedures are causing deadlock problems, turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data).
DBCC TRACEON (3605,1204,-1)
Example Deadlock :
Transaction (Process ID 52) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
Some tips for reducing the deadlock:
Ensure the database design is properly normalized.
Have the application access server objects in the same order each time.
During transactions, don't allow any user input. Collect it before the transaction begins.
Avoid cursors.
Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch.
Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.
Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
If appropriate, use as low of an isolation level as possible for the user connection running the transaction. Consider using bound connections.
Deadlocks occur when two tasks permanently block each other because each task has a lock on a resource needed by the other task.
1. SET DEADLOCK_PRIORITY Low /Normal/High or -8,-9 8,9,0
2. While adding NOLOCK can prevent readers and writers from blocking each other
3. where two or more threads are each blocked by the other so that no one can proceed.
4.
To help identify which tables or stored procedures are causing deadlock problems, turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data).
DBCC TRACEON (3605,1204,-1)
Example Deadlock :
Transaction (Process ID 52) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
Some tips for reducing the deadlock:
Ensure the database design is properly normalized.
Have the application access server objects in the same order each time.
During transactions, don't allow any user input. Collect it before the transaction begins.
Avoid cursors.
Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch.
Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.
Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
If appropriate, use as low of an isolation level as possible for the user connection running the transaction. Consider using bound connections.
Comments
Post a Comment