May 12, 2015 at 9:07 am
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?
May 12, 2015 at 9:54 am
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
May 12, 2015 at 10:09 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy