How to Avoid Deadlocks in SQL Server

   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