Getting blockage on Update Statement

• anshu.shikha

  SSC Veteran

  Points: 206

  More actions

  December 12, 2024 at 3:37 am

  #4496974

  Hi,

  Need your help. I've a SP that is using Update statement on a table.

  2-3 processes are updating this table at the same time. I'm getting frequent blockage. How can we handle to prevent blockage?

  Will using SET XACT_ABORT ON can help?

  Or setting to isolation level can help?

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  Kindly suggest.

   

   

   

   

   

• Ken McKelvey

  SSCoach

  Points: 18849

  More actions

  December 12, 2024 at 8:26 am

  #4497073

  anshu.shikha wrote:

  I'm getting frequent blockage. 

  What makes you think this?

  anshu.shikha wrote:

  Will using SET XACT_ABORT ON can help? 

  Nothing to do with locking.

  anshu.shikha wrote:

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  Will not affect UPDATE locks and potentially dangerous elsewhere.

  If you think you have excesive blocking there are plenty of resources. eg

  Understand and resolve blocking problems - SQL Server | Microsoft Learn

   

  • This reply was modified 11 hours, 23 minutes ago by  Ken McKelvey.