Maintenace

Deadlock Issue in SQL Serever

February 9, 2025

What is a Deadlock?

A deadlock in SQL Server occurs when two or more processes hold locks on resources and each process is waiting for the other to release its lock, causing a cycle where none can proceed.

Why Does It Occur?

Deadlocks typically hapen due to:

1. Concurrent Transactions: Multiple transactions access the same resources in a conflicting order.
2. Locking Order: Processes acquire locks in different sequences, leading to circular wait conditions.
3. Long-Running Transactions: Holding locks for an extended period increases the chance of conflicts.
4. Insufficient Indexing: Poor indexing leads to table scans, increasing lock contention.
5. Blocking Issues: Heavy blocking can escalate to deadlocks if multiple processes wait indefinitely.

How Poor Maintenance Can Lead to Deadlocks

1. Fragmented Indexes & Performance Degradation
   • If the database has grown significantly and indexes haven’t been maintained (i.e., no reindexing or rebuilding), queries will take longer to execute.
   • Longer query execution times mean locks are held for extended periods, increasing the chances of deadlocks.
2. Full Logging & Large Transaction Logs
   • If transaction logs are continuously growing without proper backups or truncation, SQL Server might struggle with log management, leading to slower transaction processing.
   • Slow transactions hold locks for longer, making deadlocks more likely.
3. Mass Deletes Without Reindexing
   • Deleting a large number of records without reindexing can leave fragmented pages and inefficient query plans.
   • The database engine might perform table scans instead of index seeks, leading to increased lock contention.
4. Query Plan Changes (Due to Increased Data Size)
   • As the database grows, SQL Server might generate different execution plans that were not optimized for the current data size.
   • This can lead to more locking and blocking, increasing the chance of deadlocks.

Demo - Create a test table

1. Create a Large Table & Insert Sample Data

USE master;
GO
CREATE DATABASE ConflictTestDB;
GO
USE ConflictTestDB;
GO

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME DEFAULT GETDATE(),
    OrderAmount DECIMAL(10,2),
    Status VARCHAR(20)
);
GO

-- Insert ~1 Million Rows
SET NOCOUNT ON;
DECLARE @i INT = 1;
BEGIN TRAN
WHILE @i <= 1000000
BEGIN
    INSERT INTO Orders (CustomerID, OrderAmount, Status)
    VALUES (ABS(CHECKSUM(NEWID())) % 1000, RAND() * 1000, 'Pending');
    SET @i = @i + 1;
END
COMMIT TRAN;
GO

2. Create Index Fragmentation (Without Reindexing)

-- Delete a large number of records randomly to cause index fragmentation
DELETE FROM Orders WHERE OrderID % 10 = 0;
GO

-- Fill gaps with new inserts (in random order)
SET NOCOUNT ON;
DECLARE @i INT = 1;
BEGIN TRAN
WHILE @i <= 100000
BEGIN
    INSERT INTO Orders (CustomerID, OrderAmount, Status)
    VALUES (ABS(CHECKSUM(NEWID())) % 1000, RAND() * 1000, 'Pending');
    SET @i = @i + 1;
END
COMMIT TRAN;
GO

3. Check Fragmentation for All Indexes on [Orders] Table

SELECT 
    index_id,
    index_type_desc,
    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent,
    page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Orders'), NULL, NULL, 'LIMITED');

avg_fragmentation_in_percent → Shows fragmentation level: