locked
Concurrent read and update RRS feed

  • Question

  • I am facing a scenario which may be common to others as well, in which I want simultaneous requests read a sequence value and update it by incrementing by 1, one after another. But it is possible that two requests read the same value of sequence and finally both will update it to the same value. I thought using serializable the whole process will run one after another for multiple requests. But unfortunately, even in serializable, two requests read the same value simultaneously and though they update it one after another, they update it to the same value.

    How can I overcome this situation?

    Monday, January 27, 2020 3:57 PM

Answers

  • The simplest solution may be to use a sequence object:

    CREATE SEQUENCE MySeq AS int
    START WITH 1

    SELECT NEXT VALUE FOR MySeq
    SELECT NEXT VALUE FOR MySeq

    Note however, that if a value is not used, there wll be gaps, which is also the case if SQL Server shuts down unexpectly.

    If you don't want gaps, you can try:

    UPDATE tbl
    SET    @value = col = col + 1
    WHERE  ...

    If you want to make it two statements, it is important to keep it inside a transaction, since else locks are relased after the SELECT:

    BEGIN TRANSACTION

    SELECT @val = col FROM tbl WITH (UPDLOCK) WHERE ...

    UPDATE tbl SET   col = @val + 1
    FROM  ...

    COMMIT TRANSACTION

    The UPDLOCK hint is needed to retain the lock until the update in READ COMMITTED mode. The UPDLOCK also prevents a concurrent value from taking the UPDLOCK until the transaction has completed; only one process at a time can have a UPDLOCK on a resource.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Thursday, January 30, 2020 11:50 AM
    Monday, January 27, 2020 10:12 PM
  • Both sessions need to use the UPDLOCK hint.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Thursday, January 30, 2020 8:29 AM
    Thursday, January 30, 2020 8:20 AM
  • Yes, updlock on both sessions works fine.

    By using tablockx (in only one session), reading same value can be avoided. what is the difference between the two and which one would be better?

    I'm not recommend UPDLOCK because it is pretty.

    TABLOCKX means that the entire table is locked, and no other process can read a single row from it.

    UPDLOCK means that the just the single row is locked. Furthermore, it is not locked for shared reads, so a process that is only reading with SELECT without hints will not be blocks. But attempts to write to the row will be blocked. And furthermore, only a single process can hold an UPDLOCK on a resources, and this is why it works in this example.

    There is one more thing, though. UPDLOCK implies the isolation level REPEATABLE READ.  But if you would say SET ISOLATION LEVEL REPEATABLE READ for both sessions above, but don't use the UPDLOCK hint, it would not work - they would block each other when they arrive to the UPDATE and deadlock. This is why UPDLOCK is important. It is a lock that means "I'm locking this row with the intention to update it".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Thursday, January 30, 2020 10:31 AM
    Thursday, January 30, 2020 9:03 AM

All replies

  • The simplest solution may be to use a sequence object:

    CREATE SEQUENCE MySeq AS int
    START WITH 1

    SELECT NEXT VALUE FOR MySeq
    SELECT NEXT VALUE FOR MySeq

    Note however, that if a value is not used, there wll be gaps, which is also the case if SQL Server shuts down unexpectly.

    If you don't want gaps, you can try:

    UPDATE tbl
    SET    @value = col = col + 1
    WHERE  ...

    If you want to make it two statements, it is important to keep it inside a transaction, since else locks are relased after the SELECT:

    BEGIN TRANSACTION

    SELECT @val = col FROM tbl WITH (UPDLOCK) WHERE ...

    UPDATE tbl SET   col = @val + 1
    FROM  ...

    COMMIT TRANSACTION

    The UPDLOCK hint is needed to retain the lock until the update in READ COMMITTED mode. The UPDLOCK also prevents a concurrent value from taking the UPDLOCK until the transaction has completed; only one process at a time can have a UPDLOCK on a resource.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Thursday, January 30, 2020 11:50 AM
    Monday, January 27, 2020 10:12 PM
  • Hi Erland,

    I tried the query you suggested.

    I executed the queries in two sessions simultaneously by using updlock and waitfor delay of few secs. before update statement in the first session. But both sessions read same value and eventually updated to the same value. 

    Wednesday, January 29, 2020 2:08 PM
  • Did you use the BEGIN/COMMIT statements?

    You do not need the WAITFOR but it should not cause any problems, either.

    Josh

    Wednesday, January 29, 2020 3:31 PM
  • I executed the queries in two sessions simultaneously by using updlock and waitfor delay of few secs. before update statement in the first session. But both sessions read same value and eventually updated to the same value. 

    What did your actual code look like?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, January 29, 2020 10:12 PM
  • using Adventurework database:

    First Session:

    BEGIN TRANSACTION
    DECLARE @v INT
    SELECT @v = E.VacationHours
    FROM HumanResources.Employee E(UPDLOCK)
    WHERE E.BusinessEntityID=2
    PRINT @v
    WAITFOR DELAY '00:00:10'

    UPDATE E SET E.VacationHours=@v+1
    FROM HumanResources.Employee E
    WHERE E.BusinessEntityID=2
    COMMIT

    Second Session:

    BEGIN TRANSACTION
    DECLARE @v INT
    SELECT @v = E.VacationHours
    FROM HumanResources.Employee E
    WHERE E.BusinessEntityID=2

    PRINT @v
    UPDATE E SET E.VacationHours=@v+1
    FROM HumanResources.Employee E
    WHERE E.BusinessEntityID=2
    COMMIT

    Thursday, January 30, 2020 4:26 AM
  • Did you use the BEGIN/COMMIT statements?

    You do not need the WAITFOR but it should not cause any problems, either.

    I used waitfor to see whether the queries in two sessions read the same value.
    Thursday, January 30, 2020 4:28 AM
  • Both sessions need to use the UPDLOCK hint.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Thursday, January 30, 2020 8:29 AM
    Thursday, January 30, 2020 8:20 AM
  • Yes, updlock on both sessions works fine.

    By using tablockx (in only one session), reading same value can be avoided. what is the difference between the two and which one would be better?

    Thursday, January 30, 2020 8:29 AM
  • Yes, updlock on both sessions works fine.

    By using tablockx (in only one session), reading same value can be avoided. what is the difference between the two and which one would be better?

    Tablelock would lock out others from even reading the table while this is going on - and it would lock out this while anybody else is reading, too.

    Probably better with updlocks.

    Josh

    ps - but you were asking about IDs, while your sample code uses vacation hours!  This shouldn't be necessary for vacation hours, that would be a different discussion.
    • Edited by JRStern Thursday, January 30, 2020 9:00 AM
    Thursday, January 30, 2020 8:58 AM
  • Yes, updlock on both sessions works fine.

    By using tablockx (in only one session), reading same value can be avoided. what is the difference between the two and which one would be better?

    I'm not recommend UPDLOCK because it is pretty.

    TABLOCKX means that the entire table is locked, and no other process can read a single row from it.

    UPDLOCK means that the just the single row is locked. Furthermore, it is not locked for shared reads, so a process that is only reading with SELECT without hints will not be blocks. But attempts to write to the row will be blocked. And furthermore, only a single process can hold an UPDLOCK on a resources, and this is why it works in this example.

    There is one more thing, though. UPDLOCK implies the isolation level REPEATABLE READ.  But if you would say SET ISOLATION LEVEL REPEATABLE READ for both sessions above, but don't use the UPDLOCK hint, it would not work - they would block each other when they arrive to the UPDATE and deadlock. This is why UPDLOCK is important. It is a lock that means "I'm locking this row with the intention to update it".


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Thursday, January 30, 2020 10:31 AM
    Thursday, January 30, 2020 9:03 AM

  • I'm not recommend UPDLOCK because it is pretty.


    Thanks for clear explanation but which one do you recommend? I didn't get what you said in the quoted statement.

    As you have said the updlock locks the row with the intention of updating it, I think this will work for me. But I don't know if there is any risk with it.

    • Edited by Curendra Thursday, January 30, 2020 10:36 AM
    Thursday, January 30, 2020 10:33 AM
  • Thanks for clear explanation but which one do you recommend? I didn't get what you said in the quoted statement.

    As you have said the updlock locks the row with the intention of updating it, I think this will work for me. But I don't know if there is any risk with it.

    The statement was ungrammatical. It should be "recommending". Sorry about that.

    UPDLOCK is what you should use in a situation like this. When I say "because it's pretty" I intended to imply ", but because there is a real reason", which I also elaborated in my post.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 30, 2020 11:45 AM