Key points from Microsoft re: Deadlocks:
A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.
The SQL Server Database Engine automatically detects deadlock cycles within SQL Server. After a deadlock is detected, the Database Engine ends a deadlock by choosing one of the threads as a deadlock victim.
The Database Engine terminates the current batch being executed for the thread, rolls back the transaction of the deadlock victim, and returns a 1205 error to the application. Rolling back the transaction for the deadlock victim releases all locks held by the transaction. This allows the transactions of the other threads to become unblocked and continue. The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.
By default, the Database Engine chooses as the deadlock victim the session running the transaction that is least expensive to roll back. Alternatively, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. DEADLOCK_PRIORITY can be set to LOW, NORMAL, or HIGH, or alternatively can be set to any integer value in the range (-10 to 10). The deadlock priority defaults to NORMAL. If two sessions have different deadlock priorities, the session with the lower priority is chosen as the deadlock victim. If both sessions have the same deadlock priority, the session with the transaction that is least expensive to roll back is chosen. If sessions involved in the deadlock cycle have the same deadlock priority and the same cost, a victim is chosen randomly.
Solution:
Create a Log table to store deadlock occurrences:
CREATE TABLE [dbo].[LOG_DEADLOCKS](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[Object_Name] [varchar](100) NULL,
[Counter_Name] [varchar](150) NULL,
[Instance_Name] [varchar](150) NULL,
[Cntr_Value] [int] NULL,
[Cntr_Type] [int] NULL,
[InsertedDate] [datetime] NULL,
CONSTRAINT [PK_LOG_DEADLOCKS] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Execute the below T-SQL in a SQL Job (ex. every 15 minutes 24/7):
Insert into [dbo].[LOG_DEADLOCKS] ([Object_Name],[Counter_Name],[Instance_Name],[Cntr_Value],[Cntr_Type])
select * from sys.dm_os_performance_counters where counter_name = 'Number of Deadlocks/sec'
At this point you will be aware of all deadlocks occurring in the system. This will be deadlocks occurring since the last
SQL Service (MSSQLSERVER) re-start. Logging every 15 minutes for example will show the rate of deadlocks occurring over time.
Next, get detailed information about each Deadlock. This is where Trace Flags come in.
SQL Server Configuration Manager
Navigate to the SQL Server (MSSQLSERVER) service properties and select StartUp Parameters tab.
Add -T1204 and click add, click apply. You will be required to re-start SQL Server service next.
At this point we have enabled Trace Flag 1204 & thus detailed deadlock information will be logged to SQL Server Logs.
It will show the SPID's involved in the deadlocks, which you can then use to trouble-shoot why the deadlock occurred.
Note: Trace Flag 1222 also shows Deadlock information.
SQL Server Log:
In this case the SPID 55 was chosen as the Deadlock Victim. SPID 55 was executing a Stored Procedure with ObjectID = 1405882254.
As indicated in the image - Line 3740 - was the location in the stored procedure of where the deadlock casue occurred. We added
a with (lock) and that specific deadlock never occurred again.
The other SPID(66) was executing an UPDATE which was from a Transactional Replication stored procedure - based on the ObjectID check within sysobjects table.
That's it !
Resources:
Trace Flags (Transact-SQL)
|
|
|
|
|
|