SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


blocking LCK_M_SCH_S, LCK_M_SCH_M


blocking LCK_M_SCH_S, LCK_M_SCH_M

Author
Message
rocks
rocks
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 461
Hi Guru's,
I am trying to under stand about no lock hint with a select statement.
today when i was checking for locking / blocking history.
when i check alter index with online = on, which was type LCK_M_SCH_M, was getting blocked by a select statement with no lock hint.
and alter index was blocking select statement, which has type LCK_M_SCH_S.
how can i avoid this type of blocking / locking. attaching the file which show blocking hierarchy.

in attached screen shot process id 234 is blocking 85, 53, 55, 68 etc and 85 is blocking 51 and 92 etc.

How can i avoid blocking in this situation.


Any help will be appreciated.
Attachments
Blocking.PNG (103 views, 79.00 KB)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226621 Visits: 46330
You can't. Sch-M is a schema modification lock. Blocks everything and is blocked by absolutely anything, including the Sch-S locks that all queries have to take. Online index rebuilds only require short-lived Sch-M locks, so shouldn't be a major issue.

Oh, and you might want to ditch those nolock hints.... See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

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


rocks
rocks
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 461
HI,
Thanks for quick reply, I have gone through the post. so what is the best way to avoid this locks. we are using nolock hint as we can have dirty read.

as this was on going problem in our production server. if i use offline indexes it will block even more.

GilaMonster (1/8/2013)
You can't. Sch-M is a schema modification lock. Blocks everything and is blocked by absolutely anything, including the Sch-S locks that all queries have to take. Online index rebuilds only require short-lived Sch-M locks, so shouldn't be a major issue.

Oh, and you might want to ditch those nolock hints.... See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226621 Visits: 46330
rocks (1/8/2013)
HI,
Thanks for quick reply, I have gone through the post. so what is the best way to avoid this locks. we are using nolock hint as we can have dirty read.


You cannot avoid schema locks. All queries absolutely must in every case take schema stability locks. An index rebuild, even an online one, needs a schema mod lock (very short lived for an online rebuild) and a schema modification lock is blocked by any other lock that exists and will in turn block any other lock request.

Dirty reads are allowed, so the users are absolutely fine with their reports potentially missing chunks of data, potentially reading sets of rows twice or more? They fully understand and accept that any results they see are potentially incorrect?

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


rocks
rocks
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 461
yes users understand these type of dirty read might happen.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40076 Visits: 14413
rocks (1/8/2013)
yes users understand these type of dirty read might happen.

I doubt they fully understand what dirty read really means otherwise they would not be alright with it. It means their reports are generally useless. If you care about the data you would be best to look into enabling READ_COMMITTED_SNAPSHOT in the database. The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads. The downside is that it can add some workload onto tempdb, but typically it is an easy tradeoff and unless your system is tempdb-constrained at the moment one that usually has no overall adverse effects.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226621 Visits: 46330
opc.three (1/9/2013)
The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads.


Queries with nolock in them run under read uncommitted (for the hinted table anyway), regardless of whether the default is read committed or read committed snapshot. Hints will override the default or requested isolation level.

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


Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40076 Visits: 14413
GilaMonster (1/9/2013)
opc.three (1/9/2013)
The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads.


Queries with nolock in them run under read uncommitted (for the hinted table anyway), regardless of whether the default is read committed or read committed snapshot. Hints will override the default or requested isolation level.

That's right. I was thinking of trying to explicitly set READ COMMITTED, which is not honored when READ_COMMITTED_SNAPSHOT is ON (i.e. DBCC USEROPTIONS still shows read committed snapshot). Setting READ UNCOMMITTED (and NOLOCK) would still be honored.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226621 Visits: 46330
opc.three (1/9/2013)
GilaMonster (1/9/2013)
opc.three (1/9/2013)
The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads.


Queries with nolock in them run under read uncommitted (for the hinted table anyway), regardless of whether the default is read committed or read committed snapshot. Hints will override the default or requested isolation level.

That's right. I was thinking of trying to explicitly set READ COMMITTED, which is not honored when READ_COMMITTED_SNAPSHOT is ON (i.e. DBCC USEROPTIONS still shows read committed snapshot). Setting READ UNCOMMITTED (and NOLOCK) would still be honored.


Well, it's kinda honoured. Technically read committed and read committed snapshot aren't two different isolation levels, they're one isolation level with two possible methods of being enforced, and that method is chosen with an ALTER DATABASE statement

If you have read committed snapshot on and really want the old locking behaviour (and there's some good reasons to do so), then you can only do that with a hint: WITH (READCOMMITTEDLOCK)

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


Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40076 Visits: 14413
GilaMonster (1/9/2013)
opc.three (1/9/2013)
GilaMonster (1/9/2013)
opc.three (1/9/2013)
The nice thing is that no queries need to change, not even the ones with the NOLOCK hint applied, and you'll automatically get transactionally consistent reads.


Queries with nolock in them run under read uncommitted (for the hinted table anyway), regardless of whether the default is read committed or read committed snapshot. Hints will override the default or requested isolation level.

That's right. I was thinking of trying to explicitly set READ COMMITTED, which is not honored when READ_COMMITTED_SNAPSHOT is ON (i.e. DBCC USEROPTIONS still shows read committed snapshot). Setting READ UNCOMMITTED (and NOLOCK) would still be honored.


Well, it's kinda honoured. Technically read committed and read committed snapshot aren't two different isolation levels, they're one isolation level with two possible methods of being enforced, and that method is chosen with an ALTER DATABASE statement

Yep. Two "modes" of one isolation level is how I think about them. And SNAPSHOT is another animal completely, its own isolation level unto itself.

If you have read committed snapshot on and really want the old locking behaviour (and there's some good reasons to do so), then you can only do that with a hint: WITH (READCOMMITTEDLOCK)

That's a new one to me. I will have to look into it. Thanks.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search