1
People I came across a situation where I was able to solve the problem but I would like to know more in depth how the UPDLOCK works. I had a situation that was basically like this inside a trial:
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 Sert 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, on account of being inside an asynchronous processing of C# and 15 files processed some 4 or 5 occurred problem because in MAX were returned the same values, I solved the problem by replacing WITH(NOLOCK) by WITH(UPDLOCK) on account of a search I did on the internet, but I didn’t understand very well behind the processing what it does, and if there would also be another way to solve this with another access to table. This and other procedures were within a transaction