Post reply

Avoid locks

  • June 19, 2012 at 3:12 am

    #258195

    Can any one let me know How to avoid locks on Insert/Delete statement in SQL server?

  • June 19, 2012 at 3:22 am

    #1502645

    You can't. All data modifications lock.

    Keep the transactions as short as possible, make sure the indexes support the modifications (deletes and updates) and consider one of the snapshot isolation levels if the locks have too-severe an impact on the reads.

    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

  • June 19, 2012 at 3:48 am

    #1502663

    Thank you for the reply Gila..

    I have an SSIS package. I am running the SSIS package parallely by passing two different variable values to it. Lets assume that the package is running with two values 100(instance1) and 200(instance2) respectively.This package has so many deletes, inserts and selects. When one of the package instance comes to delete task and the other comes to select, its blocking the way from executing even if i use nolock. Please suggest how to overcome this problem.

  • June 19, 2012 at 4:00 am

    #1502674

    GilaMonster (6/19/2012)

    Keep the transactions as short as possible, make sure the indexes support the modifications (deletes and updates) and consider one of the snapshot isolation levels if the locks have too-severe an impact on the reads.

    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

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

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