December 12, 2024 at 3:37 am
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.
December 12, 2024 at 8:26 am
I'm getting frequent blockage.
What makes you think this?
Will using SET XACT_ABORT ON can help?
Nothing to do with locking.
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
January 17, 2025 at 7:04 am
Sometimes, re-designing your processes is the best approach.
DBASupport
January 17, 2025 at 1:02 pm
Depending on the update, you may add an index on the field being updated in order that sql server doesn't have to lock the whole table.
update ... where mycol = Y
you may try an index on mycol
usually sql server uses that index to quickly lock on the relevant primary keys
As mentioned by Ken https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/understand-resolve-blocking
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply