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>