RedditPP Mascot

RedditPP

r/MSSQL • … • u/Gnaskefar

Over thinking update with rowlock

I have some Azure functions running, that will gather some data from some APIs, and update an audit table on my sqlserver. Depending on the data a date will be set. As Azure functions have a limited lifetime, and I expect to have quite a lot of API calls to make, they will run in parallel, each call in it own function.

My concern is, that I may get a couple of hundred functions running, more or less in parallel. And that locks can prevent updates from some other functions, without me knowing. 1 function will update 1 row, and no 2 functions will update the same row.

So, my understanding is, that using WITH (rowlock) I can suggest the engine to only put a lock on a row, and I will be safe, as each update is on 1 unique row. Is that correct? Also, it is a suggestion and the engine doesn't have to obey it.

Is it a waste or risk using WITH (rowlock)? Is there a better way to make sure, I get to know, if a query does not make it to the table?

— 0