Personally I came across a situation where I solved the problem but I would like to know more about how UPDLOCK works. I had a situation that was basically like this within a procedure:
SET @MAX = SELECT MAX(NRDOC) FROM TABLE1 WITH(NOLOCK)
SET @MAX2 = SELECT MAX(NRDOC) FROM TABLE2 WITH(NOLOCK)
IF @MAX2 > @MAX
SET MAX = MAX2
INSERT INTO TABLE1 (NRDOC) VALUES (@MAX+1)
In reality there were more fields in the insert and it was an insert into x select ..., but the problem was in the document number because it was PK in table 1. The problem occurred because the procedure was executed more than 1 time at the same time with other parameters, due to being asynchronous processing of C # and 15 files processed a 4 or 5 problem occurred because in MAX were returned the same values, I solved the problem by changing the WITH (NOLOCK) WITH (UPDLOCK ) on account of a research I did on the internet, however I did not quite understand the processing of what it does, and if there was another way to solve it with another access to the table. This and other procedures were within a transaction