Data Engineering with Avishkar: Deadlocks in SQL Server

Deadlocks are a side effect of blocking and not a SQL Server Bug. Poorly written queries in SQL Server can trigger deadlock in the system. We all knows cyclic dependency causes dead lock.

A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource. For example, P1 holds a lock on table A and requests a lock on table B; P2 holds a lock on table B and requests a lock on table A.

The lock manager(Lock Monitor)/ thread checks for deadlocks. When a lock manager/ deadlock detection algorithm detects a deadlock, the lock manager chooses one of the SPIDs as a victim. The lock manager initiates a 1205 error message that is sent to the client, and the lock manager kills the SPID.

When SQL Server find a deadlock, it kill one process (deadlock victim) rolls back the transaction of the deadlock victim, and returns a 1205 error to the application. Database engine choose deadlock victim according to the least cost to rollback.

what can cause deadlocks?

• Locks

• Worker threads

• Memory

• Parallel query execution-related resources

• Multiple Active Result Sets (MARS) resources.

They can generally be resolved by changing application or stored procedure code to access tables in the same logic order, or by changing the actual database schema or indexing structures. The first step in Deadlock elimination is to identify what two statements are actually causing the deadlocks to occur.

Trace Flags can be used to output the details of deadlocks as well, and generally provide you the maximum amount of information to help facilitate identifying the root of the problem. In SQL 2000, flags 1204 (basic) and 1205 (detailed) provide information regarding deadlocks.

An Example statement to enable tracing to the ErrorLog for all connections:

DBCC TRACEON (1204, 3605, -1)

DBCC TRACEON (1205, 3605, -1)

DBCC TRACEON (1222, 3605, -1)

SQL Profiler

The SQL Profiler can also be used to help in identifying deadlocking. In order to identify the deadlocking statements in SQL Profiler, you need to be tracing the following events.

Locks - Lock:Deadlock

Locks - Lock:Deadlock Chain

Stored Procedure - SP:StmtStarting

TSQL - SQL:StmtStarting

How to create profiler trace to find deadlock?

1.Start SQL profiler

2.On the Trace Properties dialog box, on the General tab, check Save to file and specify a path to save the trace

3.Click “Show all events”

4.Click the Events tab, above specified events

(i) Add the Deadlock graph event to the trace from the Locks category. You will get an additional tab appears on the Trace Properties window, called Event Extraction Settings.

(ii) Click the Save Deadlock XML Events Separately check box. This causes the deadlock information to be written to a separate file.

When a deadlock is encountered, the Deadlock chain event will output the SPID's associated with the deadlock. From there, you can look at the StmtStarting event for the SPID's immediately following the Deadlock Event to isolate the problem statements.

This trace will record all deadlocks on this SQL Server instance, along with the ID of the source table of contention.

To translate the database id and object id into names, you can do

SELECT database_name(DatabaseID)

SELECT object_name(ObjectID)

You can also run following query to check which process is being blocked.

SELECT * FROM sys.sysprocesses WHERE blocked <> 0

Get the SPID from blocked column

DBCC inputbuffer (SPID)

sp_who2 'active'

sp_lock2

How to reproduce a deadlock:

-- ==================================

-- Create Tables

-- ==================================

CREATE TABLE [dbo].[Customers](

[CustomerID] [int] NOT NULL,

[CustName] [nvarchar](50) NOT NULL,

[PhoneNumber] [nvarchar](24) NOT NULL,

[Email] [nvarchar](100) NULL

)

CREATE TABLE [dbo].[Products](

[ProductID] [int] NOT NULL,

[ProductName] [nvarchar](40) NOT NULL,

[Price] [decimal](18, 2) NOT NULL

)

CREATE TABLE [dbo].[Orders](

[OrderID] [int] NOT NULL,

[CustomerID] [int] NULL,

[ProductID] [int] NOT NULL,

[OrderDate] [datetime] NOT NULL,

[DeliveryDate] [datetime] NOT NULL

)

-- ==================================

-- Add rows

-- ==================================

insert into [Customers] values ( 1,'Aaron Bryant','754-555-0137','aaron.bryant@gmail.com')

insert into [Customers] values ( 2,'Abigail Bailey','329-555-0153','abigail.bailey@gmail.com')

insert into [Customers] values ( 3,'Adrian Gray','379-555-0152','adrian.gray@gmail.com')

insert into [Products] values ( 1,'Bacon ice cream',300)

insert into [Products] values ( 2,'Chocolate ',200)

insert into [Products] values ( 3,'Coffee',150)

insert into [Orders] values ( 1,2,3,'2007-07-04','2007-07-06')

insert into [Orders] values ( 2,2,2,'2007-07-14','2007-08-06')

insert into [Orders] values ( 3,3,3,'2007-09-04','2007-09-06')

insert into [Orders] values ( 4,1,3,'2007-11-14','2007-11-16')

-- ====================================

-- Execute this query using a query window for Deadlock Thread 1

-- ====================================

BEGIN TRANSACTION

UPDATE [Customers] SET [PhoneNumber] = '999-555-1212' WHERE [CustomerID] = 1

WAITFOR DELAY '00:00:10'

UPDATE [Products] SET [Price] = 120 WHERE [ProductID]= 2

ROLLBACK TRANSACTION

-- ====================================

-- Immediately execute this query using a query window for Deadlock Thread 2

-- ====================================

BEGIN TRANSACTION

UPDATE [Products] SET [ProductName] = 'Ice cream' WHERE [ProductID]=1

UPDATE [Customers] SET [CustName] = 'Mr Abigail Bailey' WHERE [CustomerID] = 2

WAITFOR DELAY '00:00:10'

ROLLBACK TRANSACTION

All transactions in a deadlock will wait indefinitely unless one of the participating transactions is rolled back, for example, because its session was terminated.

The application with the transaction chosen as the deadlock victim should retry the transaction, which usually completes after the other transaction or transactions involved in the deadlock have finished.

View and save a deadlock graph in XML

Viewing a deadlock graph in XML format allows you to copy the inputbuffer of Transact-SQL statements involved in the deadlock. You may also prefer to analyze deadlocks in a text-based format.

If you have used a Transact-SQL query to return deadlock graph information, to view the deadlock graph XML, select the value in the deadlock_xml column from any row to open the deadlock graph's XML in a new window in SSMS.

<victim-list>

</victim-list>

<process-list>

unknown </frame>

unknown </frame>

</executionStack>

-- ====================================

-- Immediately execute this query using a query window for Deadlock Thread 2

-- ====================================

BEGIN TRANSACTION

UPDATE [Products] SET [ProductName] = 'Ice cream' WHERE [ProductID]=1

UPDATE [Customers] SET [CustName] = 'Mr Abigail Bailey' WHERE [CustomerID] = 2

WAITFOR DELAY '00:00:10'

ROLLBACK TRANSACTION </inputbuf>

</process>

unknown </frame>

unknown </frame>

</executionStack>

-- ====================================

-- Execute this query using a query window for Deadlock Thread 1

-- ====================================

BEGIN TRANSACTION

UPDATE [Customers] SET [PhoneNumber] = '999-555-1212' WHERE [CustomerID] = 1

WAITFOR DELAY '00:00:10'

UPDATE [Products] SET [Price] = 120 WHERE [ProductID]= 2

ROLLBACK TRANSACTION </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

The XML for this example deadlock graph is:

A deadlock graph typically has three nodes:

Victim-list. The deadlock victim process identifier.

Process-list. Information on all the processes involved in the deadlock. Deadlock graphs use the term 'process' to represent a session running a transaction.

Resource-list. Information about the resources involved in the deadlock.

5 comments:

Sanjoy Singh said...: Well Written !!!; April 15, 2010 at 10:43 AM
dhiraj salui said...: dear avishkar,
good post.keep on writing.
would also like to see few
articles on troubleshooting replication and mirroring issues; January 17, 2012 at 5:03 PM
lp said...: Well written post, Avishkar. I like the section where you step through how to use SQL Server Profiler to run a trace. It was very concise and easy to read. I went through the steps and successfully ran a trace. The major question I had after I finished was how to interpret the trace file in such a way that I could identify what changes should be made to increase performance. I asked around and it seems like a lot of people use a visual tool to interpret traces, like SQL Trace Analyzer. Do you have any experience with such tools?; March 22, 2013 at 7:26 AM
Anonymous said...: Avishkar, this is a great post, you should really keep it up.

Also a nice description of deadlocks here:

http://www.programmerinterview.com/index.php/database-sql/database-deadlock/; July 12, 2013 at 12:56 PM
praveen said...: Wow i amazed with your blog,
Thanks to share with us,
data science training in chennai

data science training in porur

android training in chennai

android training in porur

devops training in chennai

devops training in porur

artificial intelligence training in chennai

artificial intelligence training in porur; August 23, 2020 at 4:26 PM

Data Engineering with Avishkar

April 12, 2010

Deadlocks in SQL Server

5 comments:

Secure a Microsoft Fabric data warehouse

Search This Blog