-
Jun 9th, 2022, 09:33 AM
#1
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
-
Jun 9th, 2022, 09:53 AM
#2
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>
Last edited by wqweto; Jun 9th, 2022 at 09:58 AM.
-
Jun 9th, 2022, 10:37 AM
#3
Re: MSSQL Locks
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.
-
Jun 9th, 2022, 12:13 PM
#4
Re: MSSQL Locks
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>
-
Jun 10th, 2022, 10:00 AM
#5
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
-
Jun 10th, 2022, 11:22 AM
#6
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...
-
Jun 10th, 2022, 12:38 PM
#7
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
-
Jun 11th, 2022, 03:04 AM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|