Deadlock graph showing unknown Index name

Question

I get a deadlock situation in SQL Server 2012.

After running SQL Server Profiler, I got a deadlock graph as below:

When moved mouse over the processes(oval), both processes showed identical PrepareStatement queries (I'm using JDBC).

The query I'm using is as follow:

MERGE INTO MA4TB_MT_LOG_MSG  USING (VALUES (1)) AS S(Num) ON ( MSG_ID = ? )
            WHEN MATCHED THEN
                UPDATE SET 
                    DIST_DATE   = ?,
                    DIST_CODE   = ?
            WHEN NOT MATCHED THEN
                INSERT (
                    MSG_ID, DIST_DATE, DIST_CODE
                ) VALUES (
                    ?,?,?
                );

The thing that bothers me is the Index name under the Key lock resource box.

I don't have an index called '1' under the MA4TB_MT_LOG_MSG table.

MSG_ID is the primary key of MA4TB_MT_LOG_MSG and there are no indexes on DIST_DATE, DIST_CODE.

Any forms of advice on this deadlock situation would be appreciated.

Thanks in advance,

Answer 1

SQL Profiler generates more information than is displayed in that chart/mouseover, and that info may or may not shed more light on your problem. Try this:

• Select the rows in Profiler that causes the graph to be displayed
• Do a "copy" (ctrL+C, or menu option)
• Paste this into a text editor (such as an SSMS query window)
• What you want are the XML contents of the Profiler "Text" column -- delete everything else
• Open this XML in an XML editor (whatever you have that makes it legible, I use MIcrosoft's XML Notepad)
• Drill down. It takes a bit, but once you get the hang of the layout (hierarchy, tag names, etc.) things should be clear

Answer 2

Lets start off answering your first question. I do not have a index id = 1.

Yes you do!!

Let's take a look at Adventure Works 2012 database on SQL Server 2014 CTP2. This is my laptop specification.

There is a table name [AWBuildVersion]. It has a clustered index just like your table. Under indexes, we can see that the PK is showing up. If we get the object id of the table (sys.objects) and lookup the entry for the index (sys.indexes), we can see that the index is at position 1.

In short, a primary key by default is a clustered index.

http://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

Okay, so what does a table without an index have? Those tables are called heaps. An they do have their own index at position zero that points to the first IAM page.

http://technet.microsoft.com/en-us/library/ms188270(v=sql.105).aspx

The code below creates a schema called [crafty] and copies the [AWBuildVersion] table to a new schema using SELECT INTO. The nice thing about SELECT INTO is that no indexes are carried over.

use AdventureWorks2012
go

create schema [crafty] authorization [dbo];
go

select * into crafty.awbuildversion from dbo.awbuildversion
go

In short, we can see the heap defined with a index at position zero.

So what is a deadlock and what does request mode U mean?

A deadlock is when two processes grab resources at the same time but not in the same order. In short, both processes can not proceed. The engine picks the session with the least amount of rollback time and kills the process.

http://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

A picture is worth a thousand words! Transaction 1 grabs resource 1. Transaction 2 grabs resource 2. When they try to grab each others resources, a deadlock is created.

So what does a key lock and user mode U mean?

To update your table, you need to update the data/index pages. But the data pages are really index pages (clustered index) in your table. The SQL Engine takes out a (U)PDATE lock. This lock will be escalated to a exclusive lock (X) during the actual update.

When two processes request a Exclusive lock, there is a potential of a deadlock.

To complete this topic, shared locks (SELECT) can be executed without blocking (at the same time) Usually a process starts off blocking then turns into a deadlock when the Engines deadlock process thread detects the cyclic graph.

The default isolation level is Read Uncommitted.

http://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx

At this point, you have a deadlock.

Where do you go from here?

1 - There is probably more than one session (SPID) running the same code. Why? Can you change this so that only one process runs the code at a time?

2 - Grab the actual TSQL that is being generated by JDBC. This can be done with SQL profiler and/or looking through your DMV's.

The merge statement does both an UPDATE and/or INSERT. Thus a compound operation.

3 - Can you change the Isolation level to serializable?
http://technet.microsoft.com/en-us/library/ms173763.aspx

This will add more locks and probably will change your deadlock issue into a timeout issue. See Kalen Daleny article on how LOCK_TIMEOUT can be set. You will have to adjust your code to retry the operation again with some delay in between.

http://sqlmag.com/sql-server/inside-sql-server-controlling-locking

I hope this information helps you.

Please post your TSQL if you need more help.