This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(275.2, 61%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESS%3C/text%3E%3C/svg%3E)
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
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 61%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
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.
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.
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.