Post reply

Database blocking

  • September 3, 2012 at 6:45 am

    #280684

    Hi there,

    I need some advice....I have a select statement that gets blocked by an update statement. When analyzing the stats I see that the update has an Intent Exclusive lock on the select statement. See example of queries below:

    SELECT * FROM <table1> WITH (UPDLOCK, INDEX( table1PK ))

    WHERE (column1 = <variable>)

    ORDER BY column1 ASC

    update <table2>

    set column1 = C.column2,

    column2 = C.column2

    from <table2> A

    join table1 B on A.column3 = B.column3

    join table3_VIEW C on C.column4 = B.column4

    where B.column5=<value>

    and A.column6 < (<variable> - 30)

    The update runs once but the select runs continuous against the database. The update will block the select from happening and therefore cause timeouts to occur. Can anyone please advise on how I can prevent the Intent Exclusive lock from occurring?

    Regards

    IC

  • September 3, 2012 at 7:42 am

    #1532965

    You can't prevent intent exclusives, they're taken at higher levels in the lock hierarchy to protect the lower level resources and to make it easier for SQL to identify whether lower-level locks are taken.

    The reason it's blocking is the UPDLock hint on the select. If that wasn't there, the select wouldn't be blocked by the IX lock. Why is that UPD lock hint there? Also, why is there an index hint?

    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

  • September 3, 2012 at 7:46 am

    #1532967

    Hi Gila,

    Thanks for the reply. I am not sure why it is there....this code was written by a vendor...it is a bit difficult to change the code.

    Regards

    IC

  • September 3, 2012 at 7:50 am

    #1532969

    Without changing the code you won't be able to fix the blocking. Can you speak to the vendor about it?

    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

  • September 3, 2012 at 8:05 am

    #1532975

    I will convey to the developers...thanks

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

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