Results 1 to 8 of 8

Thread: MSSQL Locks

  1. #1

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    MSSQL Locks

    Conceptually we have a table with a set of identifiers that are a four digit base number and two digit sequence number. Every year this table is cleared. Currently to get a new base number or a new sequence number a read of the table is made followed by an insert. e.g. Read getting the max base number, increment base by 1, insert it. The same sort of thing happens for sequence number for a given base number. This is a low volume, low creation table. Last year there were 4,392 rows in the table.

    But... we managed to have conflict, two people getting a new sequence got the same one, which can obviously happen.

    I created a test table and came up with this stored procedure to see if we can alleviate the possibility of conflict. In the table there is only one base number. I am a DB rookie and pieced this together from a day spent with Dr. Google.

    Thoughts please. Thanks.

    Code:
    USE [LRTEST]
    GO
    /****** Object:  StoredProcedure [revision].[NewSeq]    Script Date: 6/8/2022 11:24:12 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [revision].[NewSeq]
           -- Add the parameters for the stored procedure here
    AS
    BEGIN TRANSACTION
    
           SET NOCOUNT ON;
    
      INSERT INTO[revision].[Test] WITH (ROWLOCK) 
               ([Base]
               ,[Seq]
               ,[Comment])
                  OUTPUT Inserted.[ID], Inserted.[Base], Inserted.[Seq]
                  SELECT TOP 1 [Base]
                           ,[Seq]+1
                           ,[Comment]
                    FROM [revision].[Test]
                    WITH (TABLOCKX, HOLDLOCK)
                    WHERE Base=200
                    ORDER BY Seq DESC
                   
    
    COMMIT TRANSACTION
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  2. #2
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,121

    Re: MSSQL Locks

    This is extremely inefficient to hold *all* the generated sequences for each base instead of only the last generated number (for each base).

    For SELECT-s if you want to lock the rows it touches you can use WITH(UPDLOCK, ROWLOCK, SERIALIZABLE) hint. These hints effectively raises isolation level to SERIALIZABLE so data in base tables stays stable for the duration of the transaction. It's "locked for update" so cannot be modified and cannot be "locked for update" by other connections. This means that if the same stored procedure is executed by another connection it will have to wait for the first one to complete its transaction before second one can "lock any overlapping rows on its own for update".

    In your case the SELECT TOP 1 ... ORDER BY Seq DESC will effectively lock the whole table (by lock escalation) so a TABLOCK instead of ROWLOCK will be more appropriate for the "curious" design you've come up with and HOLDLOCK is a synonym (legacy keyword) for SERIALIZABLE so it looks good to me. Inefficient but will get the job done.

    Btw, using WITH(UPDLOCK, ROWLOCK, SERIALIZABLE) on the SELECT will (theoretically) allow concurrently generating next sequences for different bases i.e. Base=200 vs Base=201

    cheers,
    </wqw>

  3. #3

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: MSSQL Locks

    Quote Originally Posted by wqweto View Post
    This is extremely inefficient to hold *all* the generated sequences for each base instead of only the last generated number (for each base).

    For SELECT-s if you want to lock the rows it touches you can use WITH(UPDLOCK, ROWLOCK, SERIALIZABLE) hint. These hints effectively raises isolation level to SERIALIZABLE so data in base tables stays stable for the duration of the transaction. It's "locked for update" so cannot be modified and cannot be "locked for update" by other connections. This means that if the same stored procedure is executed by another connection it will have to wait for the first one to complete its transaction before second one can "lock any overlapping rows on its own for update".

    In your case the SELECT TOP 1 ... ORDER BY Seq DESC will effectively lock the whole table (by lock escalation) so a TABLOCK instead of ROWLOCK will be more appropriate for the "curious" design you've come up with and HOLDLOCK is a synonym (legacy keyword) for SERIALIZABLE so it looks good to me. Inefficient but will get the job done.

    Btw, using WITH(UPDLOCK, ROWLOCK, SERIALIZABLE) on the SELECT will (theoretically) allow concurrently generating next sequences for different bases i.e. Base=200 vs Base=201

    cheers,
    </wqw>
    Based on what I said how would you modify the Insert? Appreciate your input. Thanks.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,121

    Re: MSSQL Locks

    Quote Originally Posted by dbasnett View Post
    Based on what I said how would you modify the Insert? Appreciate your input. Thanks.
    LGTM as it would prevent concurrency issues as is.

    I wouldn't want to further the "curious" design you got here so just format it a bit -- just get rid of unnecessary [] quotes and use online SQL formatter perhaps.

    The ROWLOCK hint on the insert is redundant as you already TABLOCKX the entire table in the SELECT -- so can be safely removed.

    cheers,
    </wqw>

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: MSSQL Locks

    You only need those [] around a field if that field is either a reserved word or has something bad in it like a space. SQL likes to toss them in there just in case, and Access is REALLY enthusiastic about sticking [] in wherever it can, but you don't need them unless you do. In your case, none of your field names have spaces in them, or anything else that would be bad, but they are also single words, for the most part. Being single words, there's always a chance that they are reserved words. I don't know all the reserved words in MSSQL, and wouldn't care to memorize them anyways. Therefore, I always prefer to use compound field names. That's always possible, too. For example, you have a field 'Base'. From your description of the problem, it is clear what that is, but there are alternative, two word names, that could be used, such as BaseNumber, BaseSeq, and so on. There is NO chance that you will accidentally use a reserved word if you name things like that.

    Of course, you probably don't have any flexibility with the names, by this point, but it's something to consider. Meanwhile, I don't believe that any of the field names you are using are reserved words (though Base is the one that I would be a bit hesitant about), so you can get rid of the [].
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: MSSQL Locks

    The real table has better names. What I was really looking for was someone to tell me the best way to overcome our issue.

    I think wqweto said what I posted would work but isn't optimal.

    Reading MSSQL documentation makes my head hurt...
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: MSSQL Locks

    You have describe a race condition, and locking would be the way to solve such a thing. I'm not familiar with the locks that are available in SQL Server, so I can't say whether you are using the right or best ones, but the approach looks correct in general.
    My usual boring signature: Nothing

  8. #8
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,121

    Re: MSSQL Locks

    In MSSQL can't remember another use-case where one would need to manually use lock hints besides UPDLOCK on a SELECTs to serialize access to certain rows so that multiple connections do not see "data in transit" while it's being processed and persisted by the (single) writer.

    This is esp. needed with RCSI (read commited snapshot isolation) level where each connection literally has its own "copy of the world" and can see stale (old) data very easily from its snapshot of the world.

    Using UPDLOCK hint is very useful for deadlock prevention too because it allows manually setting up the order base tables are locked for modification in a transaction. Out of order updates by multiple transactions is usually the root cause for deadlocks in any RDBMS.

    cheers,
    </wqw>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width