blocking LCK_M_SCH_S, LCK_M_SCH_M

  • 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.

  • 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
  • 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

  • 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
  • yes users understand these type of dirty read might happen.

  • 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

  • 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
  • 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

  • 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
  • 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

  • So How does it work, its the isolation level i need to change.

  • You would enable READ_COMMITTED_SNAPSHOT on the database and remove all NOLOCK hints from your queries. See the article I linked to in my initial post on how to enable the option.

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

  • So i need to change all my SP's and Views to avoid nolock hint.

    so in this snapshot isolation level no locking happens at all for reporting scripts ( for read only scripts ) how about SP's which runs under transaction.

  • rocks (1/9/2013)


    So i need to change all my SP's and Views to avoid nolock hint.

    By all means, absolutely. If the use of NOLOCK is that pervasive then consider this your opportunity to start living right 🙂

    so in this snapshot isolation level no locking happens at all for reporting scripts ( for read only scripts ) how about SP's which runs under transaction.

    Locking still occurs such that writers block writers, but in this mode readers are not blocked by writers which is the problem the developers were likely trying to solve by adding all the NOLOCK hints.

    Have a read through that article and if you have more questions post back.

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

  • so In Snapshot , even i SP runs under transactions to read write, reporting SP's which are select script only will not get affected. how about Indexes, if i rebuild indexes online will it block as well or not.

Viewing 15 posts - 1 through 15 (of 22 total)

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