Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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>
share|improve this question
    
Are these inserts part of a larger transaction? Does the table have triggers? –  Aaron Bertrand Mar 5 '13 at 18:19
    
No, they are in their own, new, transaction. There are no triggers. –  Yishai Mar 5 '13 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? –  Aaron Bertrand Mar 5 '13 at 18:23
    
Yes, there is a wrapping transaction by the application server. But no other database activity happens in it. –  Yishai Mar 5 '13 at 18:24
2  
Then what is the purpose of the wrapping transaction? If there's no other database activity, stop doing that... –  Aaron Bertrand Mar 5 '13 at 18:25
show 2 more comments

1 Answer

I took a quick look at the deadlock in SQL Sentry Plan Explorer PRO. 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

share|improve this answer
    
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 Mar 5 '13 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. –  Aaron Bertrand Mar 5 '13 at 19:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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