We have a lot of that same logic (using updlock/rowlock) riddled throughout our 3rd party code (coreCARD) and it's there because of the way the transactions are being processed. As Gail has mentioned, it will handle the rowlock first, but SQL will escalate that to a table lock. The only way I know to prevent that escalation from ocurring is to disable lock escalation altogether for that table. It should then keep the locks at the level specified by the hint.
ALTER TABLE [dbo].[MyTable] SET (LOCK_ESCALATION = DISABLE)
Keep in mind that each lock requires server resources, and a single table lock is a lot less resources than potentially thousands of row/page locks.
Kalen Delaney has an article on lock escalation at: http://sqlblog.com/blogs/kalen_delaney/archive/2009/05/03/controlling-lock-granularity.aspx
Basically, if concurrency is a bigger deal to you than system resources, turning off lock escalation might be worth it. Otherwise, I’d look into things like snapshot isolation. That can have its own drawbacks, so make sure you understand all the options before deciding which to use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon