How Update Lock works

Sudip Bhatt 2,276 Reputation points
2020-09-10T18:53:02.647+00:00

I read this article but still has bit confusion https://www.mssqltips.com/sqlservertip/6290/sql-server-update-lock-and-updlock-table-hints/

default sql server isolation is read committed but in case of update lock do i need to change isolation to work it properly? in this link no isolation has been changed. so i assumed it is using default isolation read committed.

tell me update lock works fine with read committed isolation ?

thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,625 questions
Accepted answer
  1. Erland Sommarskog 110.4K Reputation points MVP
    2020-09-10T21:32:49.36+00:00

    You don't need to change the isolation level. An UPDATE lock is a read lock which means "I intend to update this row/page/table". Readers are not blocked by this lock, but only one process can hold an UPDATE lock on a resource.

    Normally, SQL Server takes out an UPDATE lock without you have to ask for it, for instance when it is in the process of locating rows for updating.

    However, you can yourself add the hint UPDLOCK to require an UPDATE lock. Say that you have some piece of code where you do:

    SELECT @val1 = col1, @val2 = col2, ... FROM tbl WHERE keycol = @keyval
    -- Some complicated computation here.
    UPDATE tbl SET resultcol = @result WHERE keycol = @keyval

    You may be anxious that another process may update the row while for @keyval while you are making the computation, which could lead to that you update the row with an incorrect value.

    You can prevent this from happening this way:

    BEGIN TRANSACTION
    SELECT @val1 = col1, @val2 = col2, ... FROM tbl WITH (UPDLOCK) WHERE keycol = @keyval
    -- Some complicated computation here.
    UPDATE tbl SET resultcol = @result WHERE keycol = @keyval
    COMMIT TRANSACTION

    You need to wrap the operation in a transaction, or else the lock will be released. But you don't need to change the isolation level explicitly. The UPDLOCK hint, however, implicitly sets the isolation level to REPEATABLE READ for tbl. (But not any other tables you may read in the computation.)

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,196 Reputation points
    2020-09-11T05:52:31.937+00:00

    Hi @Sudip Bhatt ,

    Update Lock (U): Occurs in update statement. Update Lock is used to find data. When the data is not to be updated, it is converted to S lock. When the data is to be updated, it is converted to X lock.

    In SQL Server, under all isolation levels (Read Uncommitted, Read Committed (default), Repeatable Reads, Serializable) Exclusive Locks(X) are acquired for Write operations.

    It is emphasized that determining the Isolation Level will not affect the locks obtained by the data modification commands. When a transaction changes information, regardless of the Transaction Isolation Level assigned to it, it will always keep the exclusive lock (X) obtained until the end of the transaction.

    You could also refer more details about UPDLOCK hint from below:
    Confused about UPDLOCK, HOLDLOCK

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  2. Erland Sommarskog 110.4K Reputation points MVP
    2020-09-19T19:27:52.637+00:00

    This is answer to the question asked in the comment to my accepted answers. It was too long to post as a comment.

    A common pattern to roll-your-own-id is this: 

    BEGIN TRANSACTION

SELECT @id = insull(MAX(id), 0) FROM tbl WITH (xxxx)

INSERT tbl (id, col1, col2, ....)
    VALUES(@id, @val2, @val2, ...)

... 

COMMIT TRANSACTION

    Let's now consider what will happen when two processes run this piece of code in parallel. More exactly, the two processes runs the first SELECT statement before the other have executed the INSERT. Let's study this for different values of xxx:

    -- Nothing at all -> The process that comes second will get a primary key violation, Not good.

    -- REPEATABLEREAD -> Again the second process will a PK violation, because what is locked is the old MAX id.

    -- HOLDLOCK or SERIALIZABLE (these two hints means the same thing): This leads to a deadlock. The serializable isolation level means that the MAX query should produce the same result. Thus, both processes takes locks that prevents the other from inserting a row.

    -- UPDLOCK. Now the second process gets blocked at the SELECT already, so it will not read the MAX(id) until the first process has committed. There is still one case where things can go wrong and that is the special case when the table is empty - since there is no row lock in that case.

    -- UPDLOCK, SERIALIZABLE. In theory this addresses the problem with the empty table, but SERIALIZABLE is prone to produce deadlock for other reasons, so it is not worth it.

    -- UPDLOCK, HOLDLOCK. This is the same as UPDLOCK, SERIALIZEABLE.

    This is the probably the most common scenario where you use UPDLOCK, but you could also have a situation where you read a value from a row, perform some computations on it and the updates the row. And you don't want the row to change while your doing the computation.

    Then you can take the situation where you read a suite of rows from id = 10 to 20 to update later, and if you get five rows, you don't want a sixth row to be added while you're working. In this case you would use UPDLOCK, SERIALIZABLE. But I don't think I have encountered this situation ever.

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.