1. Create TABLE, set index with ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCKS = ON for PRIMARY KEY
Example:
CREATE TABLE
[dbo].[Supplier](
[SupplierID] [int] IDENTITY(1,-1) NOT NULL,
[SupplierName] [varchar](50) NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_Supplier_SupplierID] PRIMARY KEY CLUSTERED
(
[SupplierID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2. WITH (NOLOCK) must be applied for SELECT statements.
Example:
SELECT * FROM dbo.Supplier WITH (NOLOCK)
WHERE SupplierName LIKE '%Govindaraj%'
3. Appropriate INDEX needs to be created for WHERE clause specified
column order with ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCKS = ON
Example:
CREATE UNIQUE
NONCLUSTERED INDEX
[UQ_Supplier_SupplierName] ON [dbo].[Supplier]
(
[SupplierName] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, FILLFACTOR = 90) ON [PRIMARY]
4. WITH (ROWLOCK) must be applied for UPDATE statements when update
Example:
UPDATE dbo.Supplier
WITH (ROWLOCK)
SET SupplierName =
'Govindaraj Kanniappan'
WHERE SupplierID = 1
Enable Disable the Page/Row Level Locks
Enable
Page Level Locks
ALTER INDEX
[UQ_Supplier_SupplierName] ON [dbo].[Supplier] REBUILD
WITH (ALLOW_PAGE_LOCKS = ON)
GO
Disable
Page Level Locks
ALTER INDEX
[UQ_Supplier_SupplierName] ON [dbo].[Supplier] REBUILD
WITH (ALLOW_PAGE_LOCKS = OFF)
GO
Enable Row
Level Locks
ALTER INDEX
[UQ_Supplier_SupplierName] ON [dbo].[Supplier] REBUILD
WITH (ALLOW_ROW_LOCKS = ON)
GO
Disable
Row Level Locks
ALTER INDEX
[UQ_Supplier_SupplierName] ON [dbo].[Supplier] REBUILD
WITH (ALLOW_ROW_LOCKS = OFF)
GO
