Row Lock Question

  • I have a requirement to read a value from table and increment it by 1. There can be multi-threads doing the same operation and would need a ROW LOCK so that read and write both are atomic. How can i put an exclusive lock on the row before I read the value from the table.

    CREATE TABLE [dbo].[tblOnboardingSequence](

    [OnboardingSequenceID] [uniqueidentifier] NOT NULL,

    [Name] [nvarchar](255) NOT NULL,

    [NextNumber] [bigint] NOT NULL,

    CONSTRAINT [PK_OnboardingSequence] PRIMARY KEY CLUSTERED

    (

    [OnboardingSequenceID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    My Stored Procedure has below logic

    DECLARE @NextNumber BIGINT

    ----------- Acquire row lock here

    SELECT @NextNumber = NextNumber FROM tblOnboardingSequence WHERE Name = 'TPO'

    UPDATE tblOnboardingSequence SET NextNumber = @NextNumber + 1 WHERE Name = 'TPO'

    ----------- Release row lock here

    I would like to have a row lock for the row having Name "TPO" before SELECT query and release after UPDATE query.

    What si the best way to deal with this?

  • Is this solution efficient?

    DECLARE @NextNumber BIGINT

    BEGIN TRAN

    SELECT @NextNumber = NextNumber FROM tblOnboardingSequence WITH (HOLDLOCK, ROWLOCK) WHERE Name = 'TPO'

    UPDATE tblOnboardingSequence SET NextNumber = @NextNumber + 1 WHERE Name = 'TPO'

    COMMIT TRAN

  • You can just use a single UPDATE statement unless you need to other actions between the SELECT and the UPDATE:

    UPDATE tblOnboardingSequence

    SET NextNumber = NextNumber + 1

    WHERE Name = 'TPO'

    Btw, if you access this table by Name, you should cluster it on name, not on the guid. You can still use the guid as a PK, must make it a nonclustered PK.

    DECLARE @NextNumber bigint

    BEGIN TRANSACTION

    ----------- Acquire row lock here

    SELECT @NextNumber = NextNumber

    FROM tblOnboardingSequence WITH (ROWLOCK, UPDLOCK)

    WHERE Name = 'TPO'

    --...other logic??...

    UPDATE tblOnboardingSequence

    SET NextNumber = @NextNumber + 1

    WHERE Name = 'TPO'

    ----------- Release row lock here

    COMMIT TRANSACTION

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 3 posts - 1 through 2 (of 2 total)

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