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

---

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>

Quote:

---

Originally Posted by wqweto

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.

Quote:

---

Originally Posted by dbasnett

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>

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 [].

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...

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.

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>