Avoiding table locking while Update

Question

Avoiding table locking while Update

Salil Singh 60

Hi Team,

I am working on a SQL Server Stored Procedure, where I need to update a column of a table in a no. of rows.

This stored procedure will be called from a timer trigger Azure function, which will scale so that 2 function instances may call the stored procedure at same time and work on same same rows. Please let me know how should I write the update statement so that I do not face any table locking issue in the future.

Please let me know if any other detail is needed.

Thanks,

Salil

2 answers

Your answer

Answer 1

Bruce (SqlWork.com) 77,686 Volunteer Moderator

It’s depends on the update logic whether or how often a deadlock can occur. But your code should recover from a deadlock as it is an expected response.

Salil Singh 60 Reputation points

2025-05-21T20:15:54.4333333+00:00

Logic is simple where I need to update few rows of a table as per a condition. Along with update statement do I need to apply any other code to make sure table never gets locked.
Bruce (SqlWork.com) 77,686 Reputation points Volunteer Moderator

2025-05-22T00:03:54.5566667+00:00

Locking always takes place as SQL Server supports atomic updates. Locking can lead to a deadlock. when two or more queries are deadlock, victim(s) are selected, and those transactions are canceled and rolled back, while one of the transactions completes.

While your schema and transaction logic can reduce the occurrence of deadlocks, there is generally no way to fully prevent.

Your azure function can retry or return an error when it is the deadlock victim. typically a retry will use a random delay so two deadlock victims don't hit each other.

note: whether reads are blocked during updates depends on the isolation level of the read and the read consistency you need.

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

First of all, there should be an index that supports the WHERE clause in your UPDATE statement.

Next, the conditions in the WHERE clause should not entangle the indexed column in any expressions, as that is likely to render the index less useful.

Share via

Avoiding table locking while Update

2 answers

Your answer