Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
deadlocks
- Thread starter logi2000
- Start date
SemperFiDownUnda
Instructor
Apply simple rules.
Lock resources as late as you can.
Unlock resources as quick as you can.
Use resources in the same order.
The 3rd one is key. If you follow it your statements won't deadlock. They will serialise but and might be slow but deadlocks will not occur.
Lock resources as late as you can.
Unlock resources as quick as you can.
Use resources in the same order.
The 3rd one is key. If you follow it your statements won't deadlock. They will serialise but and might be slow but deadlocks will not occur.
SleepingSand
Programmer
The following is just CUT and PASTE from
Here are some tips on how to avoid deadlocking on your SQL Server:
· 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.
Please check Books Online and search "Minimizing Deadlocks".
Here are some tips on how to avoid deadlocking on your SQL Server:
· 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.
Please check Books Online and search "Minimizing Deadlocks".
- Status
Similar threads
- Locked
- Question