Post reply

Avoid blocking and deadlock

  • June 4, 2015 at 6:25 pm

    #318048

    How we can avoid blocking and deadlock?

  • June 5, 2015 at 12:56 am

    #1803231

    Here are some basic guidelines

    1)Use clustered indexes on high-usage tables.

    2)Avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time.

    3)Break long transactions up into many shorter transactions.

    4)Make sure that UPDATE and DELETE statements use an existing index. Add missing indexes.

    5)If you use nested transactions, be sure there are no commit or rollback conflicts.

    6)You can use NOLOCK or READPAST, if possible

  • June 5, 2015 at 9:48 am

    #1803369

    You can't avoid blocking, and you don't want to. It is an integral part of SQL's engine to maintain data integrity.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • June 5, 2015 at 9:53 am

    #1803375

    Write queries efficiently, make sure that there are supporting indexes. That's mostly what it comes down to.

    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