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 |
|
Non-conformer
Starting Member
14 Posts |
 Posted - 2006-02-03 : 10:57:48
|
| 1) How does the locking hint UPDLOCK compare to HOLDLOCK? i.e. which one has better consistency and/or better concurency?2) Based on available literature, UPDLOCK sounds like a "perfect" solution with regard to concurency issues. That being the case, when would you NOT use UPDLOCK (assuming you're gonna update something)? What is the default behavior with regard to UPDLOCK? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-03 : 11:15:46
|
Sounds like you've been reading Books Online so I assume you've read this note:quote:
The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary. Disallowing a locking level can affect concurrency adversely.
Do you believe you needs are such that you require non-default concurrency management?However, the way I understand it, holdlock is the highest level of isolation so what is "better" depends on your objective. I think the differences between the locking hints is most apparent when combined with explicit transactions that include both reads and updates.Be One with the OptimizerTG |
 |
|
|
Non-conformer
Starting Member
14 Posts |
Posted - 2006-02-03 : 11:59:32
|
| >>Do you believe you needs are such that you require non-default concurrency management?The answer to that question is: I don't know. Sql server has all these locking hints and stuff, and I'm just not sure if I'm wasting my time trying to understand it all.A nice way to condense it all would be a document that would say, for example: "If you use this locking hint, it will speed up your query, but the data output might not be totally accurate." (or vice versa)Keep it simple.NC |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-03 : 12:12:07
|
| Good for you for wanting to understand it! My suggestion is that if you are not trying to solve a specific problem, don't implement any locking hints or non-default isolation levels. However, experimenting with them in a safe environment to observe the affects on currency is a great idea.The only (very general and common) thing you might want to consider is this:if you have relatively long running queries for reading data (like reports and searches) against the same table that are being inserted/updated/deleted, you may consider using "with (nolock)" hints on your reads. That is the same as Read Uncommitted isolation level. That will not block the tables for writing for the duration of the reads. You should be aware though that will result in "dirty reads" so if your business is a reservation system then that may not be an option for you.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Update locks