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 |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2005-03-19 : 13:01:21
|
| Hi,In books online, it says that in order to avoid deadlocks, one should 'Access Objects in the Same Order' and then it shows a couple of diagrams of two different transactions updating the same set of tables in the same order.However, what if all the rows you want to update are in the same table?For example, if I have two concurrent transactions running this query..UPDATE Books SET Price = 0..how can I avoid one transaction starting from one end of the of the table and another from the other end?Cheers, Ian. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-19 : 13:09:50
|
| If concurrent users are both trying to update every row in the table you may want to consider a different approach. How many records are in the table? Why do they need to update the entire table? If you want to update a large number of rows without locking out other users, your only real option is to perform the update in "chunks" of rows at a time. That would result in more, smaller transactions rather than 1 big one.Be One with the OptimizerTG |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-03-19 : 13:24:13
|
| Well its not necessarily every row. You could get a deadlock in a similar way with each transaction updating just 2 records each.I think it would be handy if you could sepecify an ORDER BY clause with an UPDATE. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-19 : 13:35:42
|
| You're right, you can't specify an order by in an update. I'm surprised you get a deadlock if you're just updating 2 records. That should be fast enough for one process to successfully wait on the other.Edit:Unless of course, the locks are being held from the client while the user is interacting with the data. That wouldn't be good.Be One with the OptimizerTG |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-03-19 : 15:46:20
|
| Thanks for your reply.I'm not actually getting deadlocks, I'm was just speaking theoretically. |
|
|
|
|
|
|
Avoiding Deadlocks