PK error during process asynchronous process

1

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

    
asked by anonymous 30.03.2018 / 14:31

1 answer

0
UPDLOCK

Specifies that update locks will be used and maintained until the transaction completes. UPDLOCK uses update locks only for line-level or page-level read operations. If UPDLOCK is combined with TABLOCK , or if a table-level lock is used for another reason, a unique (X) lock will be used.

When UPDLOCK is specified, the insulation-level tips READCOMMITTED and READCOMMITTEDLOCK are ignored. For example, if the session isolation level is set to SERIALIZABLE and a query specifies ( UPDLOCK , READCOMMITTED ), the READCOMMITTED hint is ignored and the transaction is executed using the SERIALIZABLE .

source: msdn.microsoft.com

    
03.04.2018 / 22:20