Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
psfaro
Starting Member
49 Posts |
 Posted - 2011-11-21 : 18:02:12
|
| Hi,I have a table Intensivily update by many users.I have two methods to update the table with a Stored proc.1-Update the table with just one Update command that will update several rows joined with other table.2- Various updates, but the update command only updates a row.The "Update" command is using a transaction.What is the best method , to avoid a DeadLock . 1) or 2).Regards Pedro |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-22 : 01:43:45
|
| i think 1 should be faster and efficient as its set based. dunno if you want successful updates to be retained incase of intermediate failure in batch------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
johntech
Yak Posting Veteran
51 Posts |
Posted - 2011-11-23 : 11:56:50
|
| 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 your application does 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, not from SQL Server. 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. http://www.sql-server-performance.com/2006/deadlocks/ |
|
|
|
|
|
|
Question about Deadlock