how to lock the record

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

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

  • 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

  • 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