Post reply

Table lock during 1 row update

  • September 1, 2015 at 8:15 am

    #398977

    Hello

    I'm working on sql server 2012 enterprise edition - MS Dynanic CRM 2015 DB

    The DB is :

    read_committed_snapshot ON

    allow_snapshot_isolation ON

    While update 1 row only - there is a Table Lock.

    How can I avoid Table lock on this case?

    Thank you in advance.

  • September 1, 2015 at 8:23 am

    #1823641

    There's probably either no suitable index, or the update is written in such a way that SQL can't use the index and has to scan the table, hence the table lock.

    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 2, 2015 at 3:10 am

    #1823899

    Thank You for you help

    I've added a new Index - now it's work fine

    but the sp_lock still show lock IX on TAB .

    Yossi

  • September 2, 2015 at 3:14 am

    #1823901

    That's normal. You'll always have intent locks at the table level. Nothing to worry about, it's how SQL's locking process works.

    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 2, 2015 at 3:40 am

    #1823906

    Thank you for your help.

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

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