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 |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
 Posted - 2012-10-01 : 06:54:21
|
| I have three processes that access the same table for updates to different date ranges on columns and occasionally two of them clash with one of them failing.If I partition the table would the table partitions be able to be updated independantly on these dates or would a lock be on the whole table whilst one of the processes is updating it? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-01 : 08:36:37
|
| I don't really know the answer to whether partitioning will help, but these are my thoughts/questionsWhen you said two updates clash and one of them fails, did you mean that one of the updates times out, or there is a deadlock, or something else?Assuming it is time-out, partitioning may or may not help. Unless you are explicitly locking the tables, I would infer that it is lock escalation that results in table-level locks. If that is the case, partitioning may help. But that may also bring with it higher likelyhood of deadlocks where there may have been none before partitioning. I am saying this because it is possible that two different updates need to lock the hobts for the same set of partitions, but might go about it in different order.So, like I said earlier, I don't know if partitioning will help or hurt. But, before you do the partitioning, it would be best to get a detailed understanding of what the updates do and why/how they are failing - whether timeout is the issue, whether the timeout interval can be increased, whether the updates can be cut up into smaller pieces so a single update wouldn't keep the tables locked etc. |
 |
|
|
|
|
|
|
|
Table Partition question!