Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

blocking LCK_M_SCH_S, LCK_M_SCH_M Expand / Collapse
Author
Message
Posted Tuesday, January 8, 2013 9:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:57 AM
Points: 23, Visits: 426
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.


  Post Attachments 
Blocking.PNG (33 views, 79.97 KB)
Post #1404340
Posted Tuesday, January 8, 2013 9:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 39,866, Visits: 36,208
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 2008, MVP
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

Post #1404355
Posted Tuesday, January 8, 2013 10:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:57 AM
Points: 23, Visits: 426
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
Post #1404365
Posted Tuesday, January 8, 2013 10:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 39,866, Visits: 36,208
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 2008, MVP
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

Post #1404386
Posted Tuesday, January 8, 2013 11:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:57 AM
Points: 23, Visits: 426
yes users understand these type of dirty read might happen.
Post #1404400
Posted Wednesday, January 9, 2013 1:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1404972
Posted Wednesday, January 9, 2013 1:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 39,866, Visits: 36,208
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 2008, MVP
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

Post #1404993
Posted Wednesday, January 9, 2013 1:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1404997
Posted Wednesday, January 9, 2013 2:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 39,866, Visits: 36,208
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 2008, MVP
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

Post #1404999
Posted Wednesday, January 9, 2013 2:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1405002
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse