Post reply

Intent Exclusive Lock (IX)

  • July 28, 2016 at 8:32 am

    #315607

    Can anyone describe at what situation session can acquire intent exclusive lock without any exclusive lock on the object?

    spiddbidObjId IndIdType Resource ModeStatus

    227170 0DB SGRANT

    22717180991050TAB IXGRANT

    This session is blocking the regular database maintenance session.

    Thanks in advance.

  • July 28, 2016 at 8:42 am

    #1892491

    A web search for sql server intent lock gets this as the top result. It is very clear and helpful:

    https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 28, 2016 at 9:04 am

    #1892499

    Thank you.

    Intent exclusive lock is placed on higher level (page, object) if exclusive lock (X) is placed on lower level of object (rid,key,page). But In my case i do not see any exclusive lock on the object that's why i am trying to find out what could be the reason for this.

  • July 28, 2016 at 9:19 am

    #1892503

    sudhakara (7/28/2016)

    Thank you.

    Intent exclusive lock is placed on higher level (page, object) if exclusive lock (X) is placed on lower level of object (rid,key,page). But In my case i do not see any exclusive lock on the object that's why i am trying to find out what could be the reason for this.

    Intent doesn't mean it will have happened yet. It may in fact never be placed.

    What you need to do is examine the actual query statement stack on the spid of concern to see what calls are making this happen. There could be something you could do about those to make this stop happening.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 28, 2016 at 10:43 am

    #1892528

    Select statement is being executed through the session which holds the IX lock on the table. Not sure why the select statement holding this IX.

  • July 28, 2016 at 12:24 pm

    #1892538

    sudhakara (7/28/2016)

    Select statement is being executed through the session which holds the IX lock on the table. Not sure why the select statement holding this IX.

    The current SELECT statement currently being executed could be part of a 20-query transaction. That is why I mentioned the entire query stack should be examined. My guess is that something else was done before the SELECT you see that took the IX.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 6 posts - 1 through 5 (of 5 total)

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