SQL

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: Concurrent Transactions: Multiple transactions access the same resources in a conflicting order. Locking Order: Processes acquire locks in different sequences, leading to circular wait conditions. Long-Running Transactions: Holding locks for an extended period increases the chance of conflicts. Insufficient Indexing: Poor indexing leads to table scans, increasing lock contention. Blocking Issues: Heavy blocking can escalate to deadlocks if multiple processes wait indefinitely. How Poor Maintenance Can Lead to Deadlocks 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. 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. 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. 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: ...