Deadlock on multiple single row updates with the where using only the primary key

TomLynch · May 15, 2015, 4:45pm

Hi,

I am getting deadlocks in a scenario that seems very simple, but am guessing I am missing something obvious. I have a table called tblGame and this table is being updated simultaneously by many different users. The update statement is updating all columns in the table except the primary key column (single column primary key with a clustered index). The primary key column is the only part of the Where clause and is updating a single record in the table. The sql statement is an ad hoc statement and isn't part of a transaction (except the implicit transaction that sql server creates I assume). Below is the deadlock graph (NOTE I had to change the @ symbol to the text 'at' due to the system thinking I was referencing other users):

Deadlock graph	230959	26	1	sa	0X01		2015-05-15 11:29:47.977	<deadlock-list>
 <deadlock victim="process1dff40188">
  <process-list>
   <process id="process1dff40188" taskpriority="0" logused="0" waitresource="PAGE: 5:1:288119 " waittime="613" ownerId="1699685328" transactionname="UPDATE" lasttranstarted="2015-05-15T11:29:47.360" XDES="0x22ee2dd20" lockMode="U" schedulerid="8" kpid="5132" status="suspended" spid="54" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2015-05-15T11:29:47.360" lastbatchcompleted="2015-05-15T11:29:47.360" lastattention="1900-01-01T00:00:00.360" clientapp=".Net SqlClient Data Provider" hostname="SERVER2" hostpid="4480" isolationlevel="read committed (2)" xactid="1699685328" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="2268" sqlhandle="0x02000000f665801638835269e8725a355feb405506834b3c0000000000000000000000000000000000000000">
UPDATE tblGame SET
IDDivision = atIDDivision
, IDBracketTemplate = atIDBracketTemplate
, GameType = atGameType
, RoundNumber = atRoundNumber
, GameNumber = atGameNumber
, Team1Source = atTeam1Source
, Team2Source = atTeam2Source
, IDTeam1 = atIDTeam1
, IDTeam2 = atIDTeam2
, Score1 = atScore1
, Score2 = atScore2
, Team1ExhibitionFlag = atTeam1ExhibitionFlag
, Team2ExhibitionFlag = atTeam2ExhibitionFlag
, FinalFlag = atFinalFlag
, IDFacility = atIDFacility
, ScheduledDateTime = atScheduledDateTime
, Team1SourcePoolPlace = atTeam1SourcePoolPlace
, Team2SourcePoolPlace = atTeam2SourcePoolPlace
, Team1SourceGameNumber = atTeam1SourceGameNumber
, Team2SourceGameNumber = atTeam2SourceGameNumber
, Team1SourceSeed = atTeam1SourceSeed
, Team2SourceSeed = atTeam2SourceSeed
, BorderRight = atBorderRight
, BorderLeft = atBorderLeft
, Width = atWidth
, Height = atHeight
, LeftPosition = atLeftPosition
, TopPosition = atTopPosition
, Note = atNote
, ChampionshipFlag = atChampionshipFlag
, ExternalGameID = atExternalGameID     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(atIDGame nvarchar(32),atIDDivision nvarchar(32),atIDBracketTemplate nvarchar(4000),atGameType nvarchar(7),atRoundNumber int,atGameNumber int,atTeam1Source nvarchar(14),atTeam2Source nvarchar(14),atIDTeam1 nvarchar(4000),atIDTeam2 nvarchar(4000),atScore1 int,atScore2 int,atTeam1ExhibitionFlag bit,atTeam2ExhibitionFlag bit,atFinalFlag bit,atIDFacility nvarchar(32),atScheduledDateTime datetime,atTeam1SourcePoolPlace int,atTeam2SourcePoolPlace int,atTeam1SourceGameNumber int,atTeam2SourceGameNumber int,atTeam1SourceSeed int,atTeam2SourceSeed int,atBorderRight bit,atBorderLeft bit,atWidth int,atHeight int,atLeftPosition int,atTopPosition int,atNote nvarchar(4000),atChampionshipFlag bit,atExternalGameID nvarchar(4000),atRawStatData nvarchar(4000),atUpdateDateTime datetime,atDeviceID nvarchar(4000),atScheduleUpdateDateTime datetime,atScoreUpdateDateTime datetime,atScheduledDateTimePublished nvarchar(4000),atIDFacilityPublished nvarchar(4000),atPointsWinner nvarchar(4000),atPointsLoser nvarchar(4000),atPointsTeam1 nvarchar(4000),atPointsTeam2 nvarchar(4000)    </inputbuf>
   </process>
   <process id="process251742928" taskpriority="0" logused="0" waitresource="PAGE: 5:1:288279 " waittime="613" ownerId="1699685328" transactionname="UPDATE" lasttranstarted="2015-05-15T11:29:47.360" XDES="0x24ed57d20" lockMode="U" schedulerid="1" kpid="3408" status="suspended" spid="54" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2015-05-15T11:29:47.360" lastbatchcompleted="2015-05-15T11:29:47.360" lastattention="1900-01-01T00:00:00.360" clientapp=".Net SqlClient Data Provider" hostname="SERVER2" hostpid="4480" isolationlevel="read committed (2)" xactid="1699685328" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="2268" sqlhandle="0x02000000f665801638835269e8725a355feb405506834b3c0000000000000000000000000000000000000000">
UPDATE tblGame SET
IDDivision = atIDDivision
, IDBracketTemplate = atIDBracketTemplate
, GameType = atGameType
, RoundNumber = atRoundNumber
, GameNumber = atGameNumber
, Team1Source = atTeam1Source
, Team2Source = atTeam2Source
, IDTeam1 = atIDTeam1
, IDTeam2 = atIDTeam2
, Score1 = atScore1
, Score2 = atScore2
, Team1ExhibitionFlag = atTeam1ExhibitionFlag
, Team2ExhibitionFlag = atTeam2ExhibitionFlag
, FinalFlag = atFinalFlag
, IDFacility = atIDFacility
, ScheduledDateTime = atScheduledDateTime
, Team1SourcePoolPlace = atTeam1SourcePoolPlace
, Team2SourcePoolPlace = atTeam2SourcePoolPlace
, Team1SourceGameNumber = atTeam1SourceGameNumber
, Team2SourceGameNumber = atTeam2SourceGameNumber
, Team1SourceSeed = atTeam1SourceSeed
, Team2SourceSeed = atTeam2SourceSeed
, BorderRight = atBorderRight
, BorderLeft = atBorderLeft
, Width = atWidth
, Height = atHeight
, LeftPosition = atLeftPosition
, TopPosition = atTopPosition
, Note = atNote
, ChampionshipFlag = atChampionshipFlag
, ExternalGameID = atExternalGameID     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(atIDGame nvarchar(32),atIDDivision nvarchar(32),atIDBracketTemplate nvarchar(4000),atGameType nvarchar(7),atRoundNumber int,atGameNumber int,atTeam1Source nvarchar(14),atTeam2Source nvarchar(14),atIDTeam1 nvarchar(4000),atIDTeam2 nvarchar(4000),atScore1 int,atScore2 int,atTeam1ExhibitionFlag bit,atTeam2ExhibitionFlag bit,atFinalFlag bit,atIDFacility nvarchar(32),atScheduledDateTime datetime,atTeam1SourcePoolPlace int,atTeam2SourcePoolPlace int,atTeam1SourceGameNumber int,atTeam2SourceGameNumber int,atTeam1SourceSeed int,atTeam2SourceSeed int,atBorderRight bit,atBorderLeft bit,atWidth int,atHeight int,atLeftPosition int,atTopPosition int,atNote nvarchar(4000),atChampionshipFlag bit,atExternalGameID nvarchar(4000),atRawStatData nvarchar(4000),atUpdateDateTime datetime,atDeviceID nvarchar(4000),atScheduleUpdateDateTime datetime,atScoreUpdateDateTime datetime,atScheduledDateTimePublished nvarchar(4000),atIDFacilityPublished nvarchar(4000),atPointsWinner nvarchar(4000),atPointsLoser nvarchar(4000),atPointsTeam1 nvarchar(4000),atPointsTeam2 nvarchar(4000)    </inputbuf>
   </process>
   <process id="process226ae0188" taskpriority="0" logused="608" waitresource="PAGE: 5:1:288279 " waittime="290" ownerId="1699679212" transactionname="UPDATE" lasttranstarted="2015-05-15T11:29:45.777" XDES="0x1b9c7a040" lockMode="U" schedulerid="8" kpid="356" status="suspended" spid="53" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2015-05-15T11:29:45.777" lastbatchcompleted="2015-05-15T11:29:45.777" lastattention="1900-01-01T00:00:00.777" clientapp=".Net SqlClient Data Provider" hostname="SERVER2" hostpid="4480" isolationlevel="read committed (2)" xactid="1699679212" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="2264" sqlhandle="0x02000000a39c7b025400c39271bb5a7bb26fd84aa96ff59a0000000000000000000000000000000000000000">
UPDATE tblGame SET
IDDivision = atIDDivision
, IDBracketTemplate = atIDBracketTemplate
, GameType = atGameType
, RoundNumber = atRoundNumber
, GameNumber = atGameNumber
, Team1Source = atTeam1Source
, Team2Source = atTeam2Source
, IDTeam1 = atIDTeam1
, IDTeam2 = atIDTeam2
, Score1 = atScore1
, Score2 = atScore2
, Team1ExhibitionFlag = atTeam1ExhibitionFlag
, Team2ExhibitionFlag = atTeam2ExhibitionFlag
, FinalFlag = atFinalFlag
, IDFacility = atIDFacility
, ScheduledDateTime = atScheduledDateTime
, Team1SourcePoolPlace = atTeam1SourcePoolPlace
, Team2SourcePoolPlace = atTeam2SourcePoolPlace
, Team1SourceGameNumber = atTeam1SourceGameNumber
, Team2SourceGameNumber = atTeam2SourceGameNumber
, Team1SourceSeed = atTeam1SourceSeed
, Team2SourceSeed = atTeam2SourceSeed
, BorderRight = atBorderRight
, BorderLeft = atBorderLeft
, Width = atWidth
, Height = atHeight
, LeftPosition = atLeftPosition
, TopPosition = atTopPosition
, Note = atNote
, ChampionshipFlag = atChampionshipFlag
, ExternalGameID = atExternalGameID     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(atIDGame nvarchar(32),atIDDivision nvarchar(32),atIDBracketTemplate nvarchar(4000),atGameType nvarchar(4),atRoundNumber int,atGameNumber int,atTeam1Source nvarchar(4000),atTeam2Source nvarchar(4000),atIDTeam1 nvarchar(32),atIDTeam2 nvarchar(32),atScore1 int,atScore2 int,atTeam1ExhibitionFlag bit,atTeam2ExhibitionFlag bit,atFinalFlag bit,atIDFacility nvarchar(32),atScheduledDateTime datetime,atTeam1SourcePoolPlace int,atTeam2SourcePoolPlace int,atTeam1SourceGameNumber int,atTeam2SourceGameNumber int,atTeam1SourceSeed int,atTeam2SourceSeed int,atBorderRight bit,atBorderLeft bit,atWidth int,atHeight int,atLeftPosition int,atTopPosition int,atNote nvarchar(4000),atChampionshipFlag bit,atExternalGameID nvarchar(4000),atRawStatData nvarchar(4000),atUpdateDateTime datetime,atDeviceID nvarchar(4000),atScheduleUpdateDateTime datetime,atScoreUpdateDateTime nvarchar(4000),atScheduledDateTimePublished datetime,atIDFacilityPublished nvarchar(32),atPointsWinner nvarchar(4000),atPointsLoser nvarchar(4000),atPointsTeam1 nvarchar(4000),atPointsTeam2 nvarchar(4000),at    </inputbuf>
   </process>
   <process id="process226ab1868" taskpriority="0" logused="608" waitresource="PAGE: 5:1:288119 " waittime="292" ownerId="1699679212" transactionname="UPDATE" lasttranstarted="2015-05-15T11:29:45.777" XDES="0x2338edd20" lockMode="U" schedulerid="1" kpid="3384" status="suspended" spid="53" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2015-05-15T11:29:45.777" lastbatchcompleted="2015-05-15T11:29:45.777" lastattention="1900-01-01T00:00:00.777" clientapp=".Net SqlClient Data Provider" hostname="SERVER2" hostpid="4480" isolationlevel="read committed (2)" xactid="1699679212" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="2264" sqlhandle="0x02000000a39c7b025400c39271bb5a7bb26fd84aa96ff59a0000000000000000000000000000000000000000">
UPDATE tblGame SET
IDDivision = atIDDivision
, IDBracketTemplate = atIDBracketTemplate
, GameType = atGameType
, RoundNumber = atRoundNumber
, GameNumber = atGameNumber
, Team1Source = atTeam1Source
, Team2Source = atTeam2Source
, IDTeam1 = atIDTeam1
, IDTeam2 = atIDTeam2
, Score1 = atScore1
, Score2 = atScore2
, Team1ExhibitionFlag = atTeam1ExhibitionFlag
, Team2ExhibitionFlag = atTeam2ExhibitionFlag
, FinalFlag = atFinalFlag
, IDFacility = atIDFacility
, ScheduledDateTime = atScheduledDateTime
, Team1SourcePoolPlace = atTeam1SourcePoolPlace
, Team2SourcePoolPlace = atTeam2SourcePoolPlace
, Team1SourceGameNumber = atTeam1SourceGameNumber
, Team2SourceGameNumber = atTeam2SourceGameNumber
, Team1SourceSeed = atTeam1SourceSeed
, Team2SourceSeed = atTeam2SourceSeed
, BorderRight = atBorderRight
, BorderLeft = atBorderLeft
, Width = atWidth
, Height = atHeight
, LeftPosition = atLeftPosition
, TopPosition = atTopPosition
, Note = atNote
, ChampionshipFlag = atChampionshipFlag
, ExternalGameID = atExternalGameID     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(atIDGame nvarchar(32),atIDDivision nvarchar(32),atIDBracketTemplate nvarchar(4000),atGameType nvarchar(4),atRoundNumber int,atGameNumber int,atTeam1Source nvarchar(4000),atTeam2Source nvarchar(4000),atIDTeam1 nvarchar(32),atIDTeam2 nvarchar(32),atScore1 int,atScore2 int,atTeam1ExhibitionFlag bit,atTeam2ExhibitionFlag bit,atFinalFlag bit,atIDFacility nvarchar(32),atScheduledDateTime datetime,atTeam1SourcePoolPlace int,atTeam2SourcePoolPlace int,atTeam1SourceGameNumber int,atTeam2SourceGameNumber int,atTeam1SourceSeed int,atTeam2SourceSeed int,atBorderRight bit,atBorderLeft bit,atWidth int,atHeight int,atLeftPosition int,atTopPosition int,atNote nvarchar(4000),atChampionshipFlag bit,atExternalGameID nvarchar(4000),atRawStatData nvarchar(4000),atUpdateDateTime datetime,atDeviceID nvarchar(4000),atScheduleUpdateDateTime datetime,atScoreUpdateDateTime nvarchar(4000),atScheduledDateTimePublished datetime,atIDFacilityPublished nvarchar(32),atPointsWinner nvarchar(4000),atPointsLoser nvarchar(4000),atPointsTeam1 nvarchar(4000),atPointsTeam2 nvarchar(4000),at    </inputbuf>
   </process>
   <process id="process195156188" taskpriority="0" logused="10000" waittime="196" schedulerid="4" kpid="3224" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-05-15T11:29:45.777" lastbatchcompleted="2015-05-15T11:29:45.777" lastattention="1900-01-01T00:00:00.777" clientapp=".Net SqlClient Data Provider" hostname="SERVER2" hostpid="4480" loginname="TMWeb" isolationlevel="read committed (2)" xactid="1699679212" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="2264" sqlhandle="0x02000000a39c7b025400c39271bb5a7bb26fd84aa96ff59a0000000000000000000000000000000000000000">
UPDATE tblGame SET
IDDivision = atIDDivision
, IDBracketTemplate = atIDBracketTemplate
, GameType = atGameType
, RoundNumber = atRoundNumber
, GameNumber = atGameNumber
, Team1Source = atTeam1Source
, Team2Source = atTeam2Source
, IDTeam1 = atIDTeam1
, IDTeam2 = atIDTeam2
, Score1 = atScore1
, Score2 = atScore2
, Team1ExhibitionFlag = atTeam1ExhibitionFlag
, Team2ExhibitionFlag = atTeam2ExhibitionFlag
, FinalFlag = atFinalFlag
, IDFacility = atIDFacility
, ScheduledDateTime = atScheduledDateTime
, Team1SourcePoolPlace = atTeam1SourcePoolPlace
, Team2SourcePoolPlace = atTeam2SourcePoolPlace
, Team1SourceGameNumber = atTeam1SourceGameNumber
, Team2SourceGameNumber = atTeam2SourceGameNumber
, Team1SourceSeed = atTeam1SourceSeed
, Team2SourceSeed = atTeam2SourceSeed
, BorderRight = atBorderRight
, BorderLeft = atBorderLeft
, Width = atWidth
, Height = atHeight
, LeftPosition = atLeftPosition
, TopPosition = atTopPosition
, Note = atNote
, ChampionshipFlag = atChampionshipFlag
, ExternalGameID = atExternalGameID     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(atIDGame nvarchar(32),atIDDivision nvarchar(32),atIDBracketTemplate nvarchar(4000),atGameType nvarchar(4),atRoundNumber int,atGameNumber int,atTeam1Source nvarchar(4000),atTeam2Source nvarchar(4000),atIDTeam1 nvarchar(32),atIDTeam2 nvarchar(32),atScore1 int,atScore2 int,atTeam1ExhibitionFlag bit,atTeam2ExhibitionFlag bit,atFinalFlag bit,atIDFacility nvarchar(32),atScheduledDateTime datetime,atTeam1SourcePoolPlace int,atTeam2SourcePoolPlace int,atTeam1SourceGameNumber int,atTeam2SourceGameNumber int,atTeam1SourceSeed int,atTeam2SourceSeed int,atBorderRight bit,atBorderLeft bit,atWidth int,atHeight int,atLeftPosition int,atTopPosition int,atNote nvarchar(4000),atChampionshipFlag bit,atExternalGameID nvarchar(4000),atRawStatData nvarchar(4000),atUpdateDateTime datetime,atDeviceID nvarchar(4000),atScheduleUpdateDateTime datetime,atScoreUpdateDateTime nvarchar(4000),atScheduledDateTimePublished datetime,atIDFacilityPublished nvarchar(32),atPointsWinner nvarchar(4000),atPointsLoser nvarchar(4000),atPointsTeam1 nvarchar(4000),atPointsTeam2 nvarchar(4000),at    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="288119" dbid="5" subresource="FULL" objectname="TournamentManager.dbo.tblGame" id="lock23ed58900" mode="U" associatedObjectId="72057594118668288">
    <owner-list>
     <owner id="process195156188" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process1dff40188" mode="U" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <pagelock fileid="1" pageid="288279" dbid="5" subresource="FULL" objectname="TournamentManager.dbo.tblGame" id="lock227152480" mode="U" associatedObjectId="72057594118668288">
    <owner-list>
     <owner id="process195156188" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process251742928" mode="U" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <pagelock fileid="1" pageid="288279" dbid="5" subresource="FULL" objectname="TournamentManager.dbo.tblGame" id="lock227152480" mode="U" associatedObjectId="72057594118668288">
    <owner-list>
     <owner id="process251742928" mode="U" requestType="wait"/>
    </owner-list>
    <waiter-list>
     <waiter id="process226ae0188" mode="U" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <pagelock fileid="1" pageid="288119" dbid="5" subresource="FULL" objectname="TournamentManager.dbo.tblGame" id="lock23ed58900" mode="U" associatedObjectId="72057594118668288">
    <owner-list>
     <owner id="process1dff40188" mode="U" requestType="wait"/>
    </owner-list>
    <waiter-list>
     <waiter id="process226ab1868" mode="U" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <exchangeEvent id="Pipe1b9b02a80" WaitType="e_waitPipeGetRow" nodeId="3">
    <owner-list>
     <owner id="process226ab1868"/>
     <owner id="process226ae0188"/>
    </owner-list>
    <waiter-list>
     <waiter id="process195156188"/>
    </waiter-list>
   </exchangeEvent>
  </resource-list>
 </deadlock>
</deadlock-list>

Here is the table definition:

USE [TournamentManager]
GO
/****** Object: Table [dbo].[tblGame] Script Date: 05/15/2015 11:37:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblGame](
[IDGame] varchar NOT NULL,
[IDDivision] varchar NOT NULL,
[IDBracketTemplate] varchar NOT NULL,
[GameType] varchar NOT NULL,
[RoundNumber] [int] NOT NULL,
[GameNumber] [int] NOT NULL,
[Team1Source] varchar NOT NULL,
[Team2Source] varchar NOT NULL,
[IDTeam1] varchar NOT NULL,
[IDTeam2] varchar NOT NULL,
[Score1] [int] NOT NULL,
[Score2] [int] NOT NULL,
[Team1ExhibitionFlag] [bit] NOT NULL,
[Team2ExhibitionFlag] [bit] NOT NULL,
[FinalFlag] [bit] NOT NULL,
[IDFacility] varchar NOT NULL,
[ScheduledDateTime] [datetime] NULL,
[Team1SourcePoolPlace] [int] NOT NULL,
[Team2SourcePoolPlace] [int] NOT NULL,
[Team1SourceGameNumber] [int] NOT NULL,
[Team2SourceGameNumber] [int] NOT NULL,
[Team1SourceSeed] [int] NOT NULL,
[Team2SourceSeed] [int] NOT NULL,
[BorderRight] [bit] NOT NULL,
[BorderLeft] [bit] NOT NULL,
[Width] [int] NOT NULL,
[Height] [int] NOT NULL,
[LeftPosition] [int] NOT NULL,
[TopPosition] [int] NOT NULL,
[Note] varchar NOT NULL,
[ChampionshipFlag] [bit] NOT NULL,
[ExternalGameID] varchar NOT NULL,
[RawStatData] [text] NOT NULL,
[InsertDateTime] [datetime] NOT NULL,
[UpdateDateTime] [datetime] NOT NULL,
[DeviceID] varchar NOT NULL,
[ScheduleUpdateDateTime] [datetime] NULL,
[ScoreUpdateDateTime] [datetime] NULL,
[ScheduledDateTimePublished] [datetime] NULL,
[IDFacilityPublished] varchar NOT NULL,
[PointsWinner] [int] NULL,
[PointsLoser] [int] NULL,
[PointsTeam1] [int] NULL,
[PointsTeam2] [int] NULL,
[IDTeam3] varchar NOT NULL,
[TheCubeID] varchar NOT NULL,
[Team1BonusStandingPoints] [int] NOT NULL,
[Team2BonusStandingPoints] [int] NOT NULL,
CONSTRAINT [PK_tblGame] PRIMARY KEY CLUSTERED
(
[IDGame] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

There is an INSTEAD OF DELETE trigger on the table, but the deadlocking statement are on Update statements not Delete statements

If there is any other information I can provide let me know. Also, I did just turn on READ_COMMITTED_SNAPSHOT to see if that could help, but this appears to have had no affect.

Thanks for any help you can provide.

Tom Lynch

graz · May 15, 2015, 6:07pm

What object does this map to? Is it the table that is being updated?

TomLynch · May 15, 2015, 6:36pm

Yes, that maps to tblGame

TomLynch · May 15, 2015, 7:01pm

Any chance this is related to the text column RawStatData. My understanding is that text fields are handled differently by sql server than the other data types, so thought maybe it could be related. Kind of grasping at straws at this point.

graz · May 15, 2015, 7:53pm

Hmmm. That's a possibility.

I'm assuming PAGE: 5:1:288279 also maps to the same table? Does either one map on LOB storage?

TomLynch · May 15, 2015, 7:55pm

Yes both of those are pointing to tblGame

JamesK · May 15, 2015, 8:49pm

If I am reading the deadlock graph correctly, these are the processes involved.

PAGE LOCKS
PageID	Owner			Waiter
288119	process195156188	process1dff40188
288279	process195156188	process251742928
288279	process251742928	process226ae0188
288119	process1dff40188	process226ab1868

EXCHANGEEVENT
Id		Owner			Waiter
Pipe1b9b02a80	process226ab1868	process195156188
Pipe1b9b02a80	process226ae0188	process195156188

So it is not just two guys fighting it out with each other, it is a multi-participant deadloc. The presence of EXCHANGEEVENT is an indication of locks due to parallelism. But if the only activity involves updating a single row based on a clustered key, this shouldn't be an issue, except, as you suggested, it might be related to the presence of the text column (which, unlike varchar(max) is always stored off-row)

Anyway, I am not offering any solutions; if you are able to reproduce the issue in a dev environment, two things might be worth experimenting with:

Setting MAXDOP=1 and testing to see if the problem goes away.
Changing the text column to VARCHAR(MAX) to see if that has any impact.

In any case, I would be very interested in learning how you resolved the issue when you eventually do.

TomLynch · May 15, 2015, 9:07pm

Unfortunately I have been unable to reproduce the problem in a test or dev environment. The text column in this instance is not really important to the application (more for trouble shooting issues with raw xml data pulled from another source). I am considering moving that data into a separate table, but it may be a bit before we can get that done. In the short run I am considering changing some of the updates in the code to only update the subset of columns that they are changing, rather than updating all columns in the table (this would leave the text column out of the update). Right now my only way to see if a fix worked is to put it in production and see if the problem goes away (not ideal), but both of the changes I am considering are worth doing on their own merits, so I will give them a try. Will let you know if it works.

JeffModen · May 17, 2015, 2:17am

Are you sure that the code is identical in all those environments? I ask because (and it might just be getting chopped off in the deadlock graph) I don't see a WHERE clause in any of the code.

graz · May 17, 2015, 2:59pm

I'm still thinking about this one.

Have you tried using a ROWLOCK hint?

Have you considered adding a SELECT before the UPDATE that uses a HOLDLOCK to lock the row you're updating?

And I really like the idea of MAXDOP 1.

I would also like to know what the issue is.

JeffModen · May 17, 2015, 3:55pm

Heh.... I'm still looking for the WHERE clause.