none
Intent lock blocked (blocked process report) RRS feed

  • Question

  • I have those queries which lasts 2 or 3 minutes causing shared locks in read committed mode.

    The goal of intent lock is to indicate a lower level lock exists on a table or a page to prevent other locks from happening at the page or table level.

    What I don't get is why the opposite is also true, why does an intent exclusive lock waits on shared locks? The intent lock is just an indication to prevent other pages or table locks from happening.  It is not an actual lock, the database does not require to lock the whole table to update a single row...

    Why would the "blocked process report" report an intent exclusive lock blocked by shared locks?

    I have those blockings reported by the blocked process report using extended event sessions under SQL server 2012 in read committed isolation.

    Monday, November 7, 2016 10:26 PM

Answers

  • Intent locks are also actual locks. If you look at SQL Server lock compatibility matrix, if a resource hold a S lock then another process can't acquire IX lock. These two lock types are not compatible. Please refer the below link.

    https://technet.microsoft.com/en-us/library/ms186396%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    • Marked as answer by Antoine F Saturday, November 12, 2016 12:54 PM
    Saturday, November 12, 2016 12:27 AM
  • Antoine,

    IX lock is an actual lock, and what you are seeing is actually correct behavior, it can definitely go both ways... IX lock is an intent to get exclusive lock on some range of data pages or table. it will block other lock requests and could be blocked by S lock requests already in place on the data in question since the intent is for X lock mode.

    In case you didnt look at this already here is the link to MS page about lock modes, and lock granularity.

    https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    Hope this helps

    Thanks

    Kon

    Tuesday, November 8, 2016 12:14 AM

All replies

  • Antoine,

    IX lock is an actual lock, and what you are seeing is actually correct behavior, it can definitely go both ways... IX lock is an intent to get exclusive lock on some range of data pages or table. it will block other lock requests and could be blocked by S lock requests already in place on the data in question since the intent is for X lock mode.

    In case you didnt look at this already here is the link to MS page about lock modes, and lock granularity.

    https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    Hope this helps

    Thanks

    Kon

    Tuesday, November 8, 2016 12:14 AM
  • Thanks Kon,

    I read the article. 

    However, it says the goal of intent lock is to prevent locking at higher level (page, table).  it also says intent locks are set in place before the "regular" lock is set.

    So, if an intent lock can both block and be blocked, doesn't that make it the equivalent of a regular lock.  Then if the intent lock is systematically set on higher level (page and table), doesn't it imply that all row lock must obtain a table lock before being obtained.  Why have row and page lock in that case and why differentiate the intent from the regular locks?

    I am sure this is not the explanation or else Microsoft would not bother differencing locks at all...

    I am probably missing something, I do not believe I saw any mention of intent locks being blocked in the article. yet I see it in the blocked process report being reported as the waiting process.




    • Edited by Antoine F Friday, November 11, 2016 1:36 AM
    Friday, November 11, 2016 1:32 AM
  • Antoine,

    You are right in a way, intent lock is the same as a regular lock since it's an actual lock, but intent locks are not automatically put in place when you run a query, and not guaranteed to be issued at the table level, server may only need a page level intent lock. Just think of it in terms of query, you are updating 10mil records in 20 mil table, lock starts at a row level, then escalated to page, then potentially table, depending... so even though you are starting at row the engine knows its going to be more efficient with a higher level lock but it doesnt need it just yet so it places intent locks to help improve the process...and prevent potential deadlocks...

    Hope this helps

    Friday, November 11, 2016 11:11 PM
  • Intent locks are also actual locks. If you look at SQL Server lock compatibility matrix, if a resource hold a S lock then another process can't acquire IX lock. These two lock types are not compatible. Please refer the below link.

    https://technet.microsoft.com/en-us/library/ms186396%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    • Marked as answer by Antoine F Saturday, November 12, 2016 12:54 PM
    Saturday, November 12, 2016 12:27 AM
  • Thanks both of you,

    The compatibility matrix makes it quite clear.  Intent locks can be blocked.

    Kon, I'll do some additional reading but so far, my understanding is that intent locks are systematically established on higher level before the regular locks are established.  The part which does not necessarilly happen is the conversion of the intent lock to the regular lock which only happens during lock escalation.

    Monday, November 14, 2016 7:04 PM