Sql-server – How to understand this deadlock

deadlocksql-server-2005

I have two threads doing inserts into a table at the same time, causing a deadlock. The interaction with the table is in a new transaction, so I'm pretty confident there is nothing else going on here.

What is the issue?

The following is table (the primary key is the only index)

CREATE TABLE [dbo].[ImageCache](
    [ImageStoreKey] [nvarchar](255) NOT NULL,
    [ImageData] [varbinary](max) NULL,
    [LastModified] [datetime] NULL,
    [StoredInRemote] [bit] NOT NULL,
 CONSTRAINT [PK_ImageCache] PRIMARY KEY CLUSTERED 
(
    [ImageStoreKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = OFF, 
    ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I have a deadlock that looks like two statements doing this at the same time on the same table, and then deadlocking each other (I'm taking a sample here of a different execution because the deadlock XML doesn't have the full declaration of the prepared statement):

declare @p1 int
set @p1=2218
exec sp_prepexec @p1 output,N'@P0 varbinary(max),@P1 datetime2,@P2 bit,
    @P3 nvarchar(4000)',N'insert into ImageCache (imageData, lastModified, 
    storedInRemote, imageStoreKey) 
    values (@P0, @P1, @P2, @P3)                                ',
    [binary data],'2013-03-05 10:44:53.6050000',0,N'257-27c440c1980070224a79'
select @p1

Multiple threads inserting into this table in the same way cause the following deadlock:

<deadlock-list>
 <deadlock victim="processf1d828">
  <process-list>
   <process id="processc3eb08" taskpriority="0" logused="0" 
    waitresource="OBJECT: 7:1282220464:0 " waittime="218" ownerId="5521931466" 
    transactionname="implicit_transaction" lasttranstarted="2013-03-04T15:54:48.543" 
    XDES="0x7498c0700" lockMode="X" schedulerid="1" kpid="7288" status="suspended" 
    spid="145" sbid="0" ecid="0" priority="0" transcount="2" 
    lastbatchstarted="2013-03-04T15:54:48.543" 
    lastbatchcompleted="2013-03-04T15:54:48.497" 
    clientapp="Microsoft JDBC Driver for SQL Server" hostname="newappserver" 
    hostpid="0" loginname="User" isolationlevel="read committed (2)" 
    xactid="5521931466" currentdb="7" lockTimeout="4294967295" 
    clientoption1="671088672" clientoption2="128058">
    <executionStack>
    <frame procname="adhoc" line="1" stmtstart="120"
         sqlhandle="0x02000000732b8e307aef74c20d8606c2b827936fe195eee9">
    insert into ImageCache (imageData, lastModified, storedInRemote, imageStoreKey) 
    values (@P0, @P1, @P2, @P3)     
    </frame>
    <frame procname="unknown" line="1" 
        sqlhandle="0x000000000000000000000000000000000000000000000000">
           unknown     
    </frame>
    </executionStack>
    <inputbuf>
        Select 1    
    </inputbuf>
   </process>
   <process id="processf1d828" taskpriority="0" logused="0" 
        waitresource="OBJECT: 7:1282220464:0 " waittime="218" 
        ownerId="5522008674" transactionname="implicit_transaction" 
        lasttranstarted="2013-03-04T15:54:54.843" XDES="0x66e46ca90" 
        lockMode="X" schedulerid="6" kpid="11456" status="suspended" 
        spid="316" sbid="0" ecid="0" priority="0" transcount="2" 
        lastbatchstarted="2013-03-04T15:54:54.843" 
        lastbatchcompleted="2013-03-04T15:54:54.843" 
        clientapp="Microsoft JDBC Driver for SQL Server" 
        hostname="newappserver" hostpid="0" loginname="User" 
        isolationlevel="read committed (2)" xactid="5522008674" 
        currentdb="7" lockTimeout="4294967295" clientoption1="671088672" 
        clientoption2="128058">
    <executionStack>
    <frame procname="adhoc" line="1" stmtstart="120" 
        sqlhandle="0x02000000732b8e307aef74c20d8606c2b827936fe195eee9">
        insert into ImageCache (imageData, lastModified, storedInRemote, imageStoreKey) 
        values (@P0, @P1, @P2, @P3)     
    </frame>
    <frame procname="unknown" line="1" 
        sqlhandle="0x000000000000000000000000000000000000000000000000">
        unknown     
    </frame>
    </executionStack>
    <inputbuf>
        Select 1    
    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="1282220464" subresource="FULL" 
        dbid="7" objectname="Database.dbo.ImageCache" id="locke394cf80" 
        mode="IX" associatedObjectId="1282220464">
    <owner-list>
     <owner id="processc3eb08" mode="IX"/>
     <owner id="processf1d828" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="processf1d828" mode="X" requestType="convert"/>
     <waiter id="processc3eb08" mode="X" requestType="convert"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

Best Answer

I took a quick look at the deadlock in SentryOne Plan Explorer. Two things stand out to me:

(1) the transaction count is 2, not 1. Regardless of whether this is how "Java technology works," I am strongly suspicious that there is more going on in that wrapping transaction than you think, or at least SQL Server thinks that there might be more going on. It's not picking a victim because it doesn't like Java. Also note that wrapping transactions in SQL Server makes little sense, because there is no such thing as autonomous transactions.

(2) each INSERT statement is accompanied by an additional statement simply labeled unknown - I have no idea what this is doing, but I suspect it must be related.

Sorry this isn't a direct answer but hope it helps shed some light on where to look (how JDBC is structuring your transaction and whether you should have two transactions in the first place).

Full size graphic here

enter image description here

Related Question