how to lock the record

Question

how to lock the record

Jonathan AC Roberts

SSCoach

Points: 18263
More actions
November 25, 2019 at 11:53 am
Answer
#3700532
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.

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 1

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.

Steve Collins SSCrazy Points: 2069 More actions · Answer 2

KGJ-Dev wrote:

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

KGJ-Dev SSCertifiable Points: 5780 More actions · Answer 3

sorry for being late in responding. i will mark it as answered this thread as i got answers from jeff and jonathan.