Here is a demonstration that the locking hints work. You will need to create a permanent version of Jeff's table (dbo.Codes) as the procedure will be run from different sessions.
With this procedure it selects the row, then has a delay of a length that's passed into the stored procedure, then updates the table and returns the value.
CREATE PROCEDURE GetNextCode (@DelayLength as varchar(10)) AS
BEGIN
BEGIN TRANSACTION
DECLARE @EmpCode nvarchar(50)
SELECT TOP(1)@EmpCode =c.EmpCode
FROM dbo.Codes c WITH (XLOCK ROWLOCK READPAST)
WHERE c.AllotedStatus=0
ORDER BY c.IdCode
WAITFOR DELAY @DelayLength
UPDATE c
SET c.AllotedStatus=1
FROM dbo.Codes c
WHERE c.EmpCode = @EmpCode
SELECT @EmpCode EmpCode
COMMIT TRANSACTION
END
GO
You can then call the procedure from one SSMS screen like this:
EXEC dbo.GetNextCode '00:00:10'
This will put a 10-second delay between the SELECT and the UPDATE
Then while this is executing run the following from a new SSMS window.
EXEC dbo.GetNextCode '00:00:01'
This will run the procedure with a 1-second delay between the SELECT and the UPDATE.
You will find that the query you started first (with the 10-second delay) will have the first code, and the query started after that will have the second code. The first executing procedure won't block the second as the second query will just readpast the locked row, so the second query will complete before the first query.
November 25, 2019 at 2:38 pm
Hi Jeff,
thanks a lot for the code snippet and table structure. i learnt from this. you have mentioned that i don't need the transaction for this scenario. I would like to know how the concurrency will be maintained here. fr example, if 5 people tries to get the code, how the concurrency works here? one code should not be shared to other user. while they try to get the code simultaneously, code should not be shared. Please advice.
Hi Jonathan,
Thank you for the code snippet.
November 25, 2019 at 4:59 pm
one code should not be shared to other user. while they try to get the code simultaneously, code should not be shared.
Suppose you have 3 people and each has successfully requested an EmpCode. Jonathan and Jeff have both provided perfect code.
Person1: Code00123
Person2: Code00456
Person3: Code00789
In the db table the 3 codes have AllocationType =1 and AllocationDate is not null. Now what happens? Could Person1 give their code to Person3? Could Person2 make 5 copies of their code and share it with their friends?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 9, 2019 at 3:06 pm
sorry for being late in responding. i will mark it as answered this thread as i got answers from jeff and jonathan.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply