3

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>
7
  • Are these inserts part of a larger transaction? Does the table have triggers? Commented Mar 5, 2013 at 18:19
  • No, they are in their own, new, transaction. There are no triggers.
    – Yishai
    Commented Mar 5, 2013 at 18:22
  • 1
    Right, but are you sure there isn't a wrapping transaction? Transactions aren't autonomous in SQL Server. In other words, can you reproduce this in two SSMS windows, with just the inserts? Otherwise how do we know what else is being done by whatever application is preparing these statements and sending them to SQL Server? Commented Mar 5, 2013 at 18:23
  • Yes, there is a wrapping transaction by the application server. But no other database activity happens in it.
    – Yishai
    Commented Mar 5, 2013 at 18:24
  • 2
    Then what is the purpose of the wrapping transaction? If there's no other database activity, stop doing that... Commented Mar 5, 2013 at 18:25

1 Answer 1

3

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

2
  • Thanks for the insight. The unknown is the preperation of the statement. That is why I put my separate capture of the activity as part of the question. All there is is start transaction and commit transaction around the statement I quoted. I could write chapter and verse about the underlying JPA/Hibernate stuff going on here, but either way reading the code I'm confident there is no other database activity, and running a trace in testing demonstrates that. In SSMS the second insert just waits for the first transaction to complete.
    – Yishai
    Commented Mar 5, 2013 at 18:57
  • 1
    @Yishai well I don't know what else to tell you other than to stop using whatever component in Java is forcing you to create this additional transaction. Commented Mar 5, 2013 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.