Getting blockage on Update Statement

Question

Post reply

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.

Viewing 7 posts - 1 through 7 (of 7 total)

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

Ken McKelvey SSCoach Points: 18910 More actions · Answer 1

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 6 months ago by Ken McKelvey.

cyrusbaratt SSC-Addicted Points: 430 More actions · Answer 2

Sometimes, re-designing your processes is the best approach.

DBASupport

Jo Pattyn SSC-Dedicated Points: 32430 More actions · Answer 3

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

VastSQL SSC-Dedicated Points: 36229 More actions · Answer 4

anshu.shikha wrote:

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.

Can you share the blocking xml details?

VastSQL SSC-Dedicated Points: 36229 More actions · Answer 5

anshu.shikha wrote:

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.

Can you share the blocking xml details?

Jeff Moden SSC Guru Points: 1004596 More actions · Answer 6

I don't have the links for them but I've recently read a few articles that say the turning off page locking for the table did the trick nicely for them. Keep in mind that this is NOT a good idea if the updates affect (for example) tens of thousands of rows in one update. This is for lots and lots of "fast jabs" of just a row or two.

Understand that disabling page locking on a table/index will make it so REORGANIZE cannot be used. Personally, since it frequently perpetuates fragmentation and doesn't work the way most people think it does, I wouldn't use it in any "general purpose" Index Maintenance routines, most of which are also incorrect.

And, I agree with others... the best way to make the UPDATE less resource intensive and faster. Remember that "set based" does NOT mean "all in one query". If you have lots of joins in the UPDATE, you may want to do a little "Divide'n'Conquer". Also remember that your joined updates MUST include the table being updated in the FROM clause and it should have an alias that would be the target of the update. If you don't, then you have an "illegal UPDATE" and you'll never find such an example in the Microsoft documentation. Illegal updates can sporadically loose their minds and take a whole lot longer to do an update of even a single row than you ever thought possible.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Getting blockage on Update Statement

Cookies on SQLServerCentral