Nikunj Satasiya
What are the efficient ways to prevent deadlock in SQL Server?
By Nikunj Satasiya in SQL Server on Jan 11 2021
  • Shweta Lodha
    Jan, 2021 17

    • Ensure the database design is properly normalized.
    • Develop applications to access server objects in the same order each time.
    • Do not allow any user input during transactions.
    • Avoid cursors.
    • Keep transactions as short as possible.
    • Reduce the number of round trips between your application and SQL Server by using stored procedures or by keeping transactions within a single batch.
    • Reduce the number of reads. If you do need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there.
    • Reduce lock time. Develop applications that obtain locks at the latest possible time, and release them at the earliest possible time.
    • If appropriate, reduce lock escalation by using ROWLOCK or PAGLOCK.

    • If the data being locked is not modified very frequently, consider using NOLOCK to prevent locking.

    • If appropriate, use the lowest possible isolation level for the user connection running the transaction.
    • Consider using bound connections.

    • 4
  • Mahesh Ugopal
    Feb, 2021 19

    Using NOLOCK for every table join eg: ............... From TableName with(nolock) INNER JOIN OtherTableName with(nolock) ON TableName.Feild=OtherTableName.Feild INNER JOIN OtherTableName1 with(nolock) ON ..............

    • 0

Most Popular Job Functions