Using (Updlock,Rowlock) in Select statment

  • Aspet Golestanian Namagerdi

    SSCarpal Tunnel

    Points: 4444

    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

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • MyDoggieJessie

    SSC-Forever

    Points: 44276

    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

  • GSquared

    SSC Guru

    Points: 260824

    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

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sayap

    SSC Rookie

    Points: 35

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply