What is the usage of UPDLOCK in Select and Update statement

The function of using UPDLOCK is to allow users to read data first (and not to block other users from reading data), and to ensure that when the data is updated later, the data has not been modified by other users during this period of time. When this option is selected, SQL Server uses a modification lock instead of a shared lock when reading data, and keeps this lock until the end of the entire transaction or command. Using this option can ensure that multiple processes can read data at the same time but only this process can modify the data.

UPDLOCK affects the type of lock. It means for a SELECT statement that U locks will be taken rather than an S lock. At default read committed level they will be released as soon as the data is read.

UPDLOCK uses an update lock when reading a table instead of a shared lock, and keeps the lock until the end of the statement or transaction. The advantage of UPDLOCK is that it allows you to read data (without blocking other transactions) and update the data later, while ensuring that the data has not been changed since the last time the data was read. When we use UPDLOCK to read the record, we can add an update lock to the fetched record, so that the locked record cannot be changed in other threads and can only be changed after the end of the transaction of this thread. The following example:

BEGIN TRANSACTION -- start a transaction
SELECT Qty
FROM myTable WITH (UPDLOCK)
WHERE Id in (1,2,3)
UPDATE myTable SET Qty = Qty-A.Qty
FROM myTable AS A
INNER JOIN @_Table AS B ON A.ID = B.ID
COMMIT TRANSACTION -- Commit the transaction

In this way, during the update, other threads or transactions cannot change the records with IDs 1, 2, 3 before these statements are executed. Others can be modified and read. 1, 2, 3 can only be read. If you want to modify it, you can only wait for the completion of these statements before you can operate. So as to ensure that the data is modified correctly.

UPDLOCK

Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock is taken instead.

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