Share via

Row Lock in sql server

BHVS 61 Reputation points
2021-07-17T17:23:18.123+00:00

Hi All,

I have requirement, i want to apply row lock in sql server side in sp's.

I have a table like below, When ever same record update by multiple users from (Front end side) , i want to show a message, this record is used by username(ABC). is this achievable in Sql Server Stored Procedure?

CREATE TABLE [dbo].[Employee](
[EmployeeId] [int] NOT NULL,
[LastName] nvarchar NOT NULL,
[FirstName] nvarchar NOT NULL,
[Title] nvarchar NULL,
[ReportsTo] [int] NULL,
[BirthDate] [datetime] NULL,
[HireDate] [datetime] NULL,
[Address] nvarchar NULL,
[City] nvarchar NULL,
[State] nvarchar NULL,
[Country] nvarchar NULL,
[PostalCode] nvarchar NULL,
[Phone] nvarchar NULL,
[Fax] nvarchar NULL,
[Email] nvarchar NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeId] 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
INSERT [dbo].[Employee] ([EmployeeId], [LastName], [FirstName], [Title], [ReportsTo], [BirthDate], [HireDate], [Address], [City], [State], [Country], [PostalCode], [Phone], [Fax], [Email]) VALUES (1, N'Adams', N'Andrew', N'General Manager', NULL, CAST(N'1962-02-18 00:00:00.000' AS DateTime), CAST(N'2002-08-14 00:00:00.000' AS DateTime), N'11120 Jasper Ave NW', N'Edmonton', N'AB', N'Canada', N'T5K 2N1', N'+1 (780) 428-9482', N'+1 (780) 428-3457', N'andrew@TESTcorp.com')
GO
INSERT [dbo].[Employee] ([EmployeeId], [LastName], [FirstName], [Title], [ReportsTo], [BirthDate], [HireDate], [Address], [City], [State], [Country], [PostalCode], [Phone], [Fax], [Email]) VALUES (2, N'Edwards', N'Nancy', N'Sales Manager', 1, CAST(N'1958-12-08 00:00:00.000' AS DateTime), CAST(N'2002-05-01 00:00:00.000' AS DateTime), N'825 8 Ave SW', N'Calgary', N'AB', N'Canada', N'T2P 2T3', N'+1 (403) 262-3443', N'+1 (403) 262-3322', N'nancy@TESTcorp.com')
GO
INSERT [dbo].[Employee] ([EmployeeId], [LastName], [FirstName], [Title], [ReportsTo], [BirthDate], [HireDate], [Address], [City], [State], [Country], [PostalCode], [Phone], [Fax], [Email]) VALUES (3, N'Peacock', N'Jane', N'Sales Support Agent', 2, CAST(N'1973-08-29 00:00:00.000' AS DateTime), CAST(N'2002-04-01 00:00:00.000' AS DateTime), N'1111 6 Ave SW', N'Calgary', N'AB', N'Canada', N'T2P 5M5', N'+1 (403) 262-3443', N'+1 (403) 262-6712', N'jane@TESTcorp.com')
GO
INSERT [dbo].[Employee] ([EmployeeId], [LastName], [FirstName], [Title], [ReportsTo], [BirthDate], [HireDate], [Address], [City], [State], [Country], [PostalCode], [Phone], [Fax], [Email]) VALUES (4, N'Park', N'Margaret', N'Sales Support Agent', 2, CAST(N'1947-09-19 00:00:00.000' AS DateTime), CAST(N'2003-05-03 00:00:00.000' AS DateTime), N'683 10 Street SW', N'Calgary', N'AB', N'Canada', N'T2P 5G3', N'+1 (403) 263-4423', N'+1 (403) 263-4289', N'margaret@TESTcorp.com')
GO
INSERT [dbo].[Employee] ([EmployeeId], [LastName], [FirstName], [Title], [ReportsTo], [BirthDate], [HireDate], [Address], [City], [State], [Country], [PostalCode], [Phone], [Fax], [Email]) VALUES (5, N'Johnson', N'Steve', N'Sales Support Agent', 2, CAST(N'1965-03-03 00:00:00.000' AS DateTime), CAST(N'2003-10-17 00:00:00.000' AS DateTime), N'7727B 41 Ave', N'Calgary', N'AB', N'Canada', N'T3B 1Y7', N'1 (780) 836-9987', N'1 (780) 836-9543', N'steve@TESTcorp.com')
GO
INSERT [dbo].[Employee] ([EmployeeId], [LastName], [FirstName], [Title], [ReportsTo], [BirthDate], [HireDate], [Address], [City], [State], [Country], [PostalCode], [Phone], [Fax], [Email]) VALUES (6, N'Mitchell', N'Michael', N'IT Manager', 1, CAST(N'1973-07-01 00:00:00.000' AS DateTime), CAST(N'2003-10-17 00:00:00.000' AS DateTime), N'5827 Bowness Road NW', N'Calgary', N'AB', N'Canada', N'T3B 0C5', N'+1 (403) 246-9887', N'+1 (403) 246-9899', N'michael@TESTcorp.com')
GO
INSERT [dbo].[Employee] ([EmployeeId], [LastName], [FirstName], [Title], [ReportsTo], [BirthDate], [HireDate], [Address], [City], [State], [Country], [PostalCode], [Phone], [Fax], [Email]) VALUES (7, N'King', N'Robert', N'IT Staff', 6, CAST(N'1970-05-29 00:00:00.000' AS DateTime), CAST(N'2004-01-02 00:00:00.000' AS DateTime), N'590 Columbia Boulevard West', N'Lethbridge', N'AB', N'Canada', N'T1K 5N8', N'+1 (403) 456-9986', N'+1 (403) 456-8485', N'robert@TESTcorp.com')
GO
INSERT [dbo].[Employee] ([EmployeeId], [LastName], [FirstName], [Title], [ReportsTo], [BirthDate], [HireDate], [Address], [City], [State], [Country], [PostalCode], [Phone], [Fax], [Email]) VALUES (8, N'Callahan', N'Laura', N'IT Staff', 6, CAST(N'1968-01-09 00:00:00.000' AS DateTime), CAST(N'2004-03-04 00:00:00.000' AS DateTime), N'923 7 ST NW', N'Lethbridge', N'AB', N'Canada', N'T1H 1Y8', N'+1 (403) 467-3351', N'+1 (403) 467-8772', N'laura@TESTcorp.com')
GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_EmployeeReportsTo] FOREIGN KEY([ReportsTo])
REFERENCES [dbo].[Employee] ([EmployeeId])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_EmployeeReportsTo]
GO

Thanks in Advance....

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,826 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,639 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 111.6K Reputation points MVP
    2021-07-17T21:55:38.843+00:00

    Yes, it is possible, but it is not exactly trivial, and it requires good understanding of what you are doing.

    You may think that you would use the locking system in SQL Server for this, but, no, it is not designed for this purpose. The way to do this, is that you add a LockedBy column to the table, and when a user reads a row, you set LockedBy to the that user's name. If LockedBy already has a value, you give the user an error message.

    But then you need to figure out when to "release" a lot, that is set LockedBy to NULL. Particularly, you need to decide when a lock has to be considered stale. Maybe the user reads the row, looks at it and then goes for lunch. Or starts working with something else. Do you see where this is leading?

    The most common means to protect against simultaneous updates is optimistic concurrency. That is, you don't lock any rows when you read, but when you update you check that the row is the same, and if it is not, you give the user an error "The record has been updated by another user, try again". One way to check if a row has been modified is to add a column of type rowversion to the table. Such a column is automatically updated when a row is updated, which easily permits you detect concurrent changes.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.