December 27, 2011 at 8:49 pm
I have (Updlock,Rowlock) in a select statment(Transactional).The Select statment has a structure as follow:
Select FirstName,LastName from Employee With (Updlock,Rowlock) where AccountID = 123 order by ID
In Employee table, ID and AccountID are Non-Clustered Index's, and we have a Clustered Index base on some other columns(composite key).This Select statment is being called(by different AccountID) from different sessions(Thread) and sometimes is causing Deadlock.Deadlock is happening on Primary Key and Non-Clustered-Index on ID.
When we use (Updlock,Rowlock) hint in our select statment,Does that create an updlock on the whole table or just for the row tht we do the select operation?
Thanks
December 28, 2011 at 1:58 am
SQL will start with a row lock, as the hint requests, if necessary it will escalate that to a table lock.
Why the hints in the first place?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 28, 2011 at 9:32 am
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.
ex:ALTER TABLE [dbo].[MyTable] SET (LOCK_ESCALATION = DISABLE)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 28, 2011 at 9:42 am
MyDoggieJessie (12/28/2011)
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.ex:
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
December 28, 2011 at 9:47 am
MyDoggieJessie (12/28/2011)
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.ex:
ALTER TABLE [dbo].[MyTable] SET (LOCK_ESCALATION = DISABLE)
I strongly, strongly recommend that you do not do that, unless you're happy with intermittent query failures with this:
Server: Msg 1204, Level 19, State 1, Line 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
or even a complete server out of memory error
Lock escalation is there for a reason. Disabling it (and there are more ways than just the ALTER statement) can have serious side effects.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2012 at 9:20 pm
Can you elaborate on the "serious side effects"?
The way I see it, if the server has sufficient memory, and one has some rough idea on how many row/page locks there will be at one time from bulk insert, there is really no harm to disable lock escalation.
From http://msdn.microsoft.com/en-us/library/ms184286.aspx, each lock takes about 100 bytes. On a dedicated machine with 16G memory, with lock escalation threshold at 24% of the total memory, the server can hold about 40 millions locks at one time, which is a lot.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply