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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply