November 6, 2015 at 1:47 am
Can anyone help in diagnosing the following deadlock.
This is happening on multiple servers all within the replication/cache topology of a client I am working on, and cant figureout how to resolve it.
<TextData>
<deadlock-list>
<deadlock victim="process5d9814cf8">
<process-list>
<process id="process5d9814cf8" taskpriority="0" logused="0" waitresource="KEY: 9:72057594039042048 (f46d0e0185fd)" waittime="2807" ownerId="103965194957" transactionname="UPDATE" lasttranstarted="2015-11-05T19:20:12.500" XDES="0x4ada38d28" lockMode="U" schedulerid="13" kpid="3712" status="suspended" spid="85" sbid="1" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-11-05T19:20:12.500" lastbatchcompleted="2015-11-05T19:20:12.500" lastattention="1900-01-01T00:00:00.500" clientapp="Microsoft SQL Server" hostname="HOST1" hostpid="1908" loginname="BuildCacheApp" isolationlevel="read committed (2)" xactid="103965194957" currentdb="9" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="DB.dbo.spCacheRemove" line="10" stmtstart="310" stmtend="808" sqlhandle="0x03000900b3de6e274ff38e009ca3000001000000000000000000000000000000000000000000000000000000">
UPDATEdbo.Cache
SETAvailability = Availability - 1,
LastUpdated = GETUTCDATE()
WHEREUnitId = @UnitId
ANDStartDate < DATEADD(DAY, @Duration, @StartDate)
AND@StartDate < DATEADD(DAY, Duration, StartDate)
ANDAvailability > 0; </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 9 Object Id = 661577395] </inputbuf>
</process>
<process id="process2b9b56558" taskpriority="0" logused="2688" waitresource="KEY: 9:72057594039173120 (65b8229a3f59)" waittime="2796" ownerId="103963696502" transactionname="user_transaction" lasttranstarted="2015-11-05T19:20:07.470" XDES="0x4aa3ea040" lockMode="U" schedulerid="9" kpid="6992" status="suspended" spid="80" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-11-05T19:20:12.483" lastbatchcompleted="2015-11-05T19:20:07.470" lastattention="1900-01-01T00:00:00.470" clientapp="HOST2_CacheSearch_CacheSearch" hostname="HOST2" hostpid="9748" loginname="ReplicationUser" isolationlevel="read committed (2)" xactid="103963696502" currentdb="9" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128056">
<executionStack>
<frame procname="DB.dbo.spCacheRemove" line="10" stmtstart="310" stmtend="808" sqlhandle="0x03000900b3de6e274ff38e009ca3000001000000000000000000000000000000000000000000000000000000">
UPDATEdbo.Cache
SETAvailability = Availability - 1,
LastUpdated = GETUTCDATE()
WHEREUnitId = @UnitId
ANDStartDate < DATEADD(DAY, @Duration, @StartDate)
AND@StartDate < DATEADD(DAY, Duration, StartDate)
ANDAvailability > 0; </frame>
<frame procname="adhoc" line="1" stmtstart="88" sqlhandle="0x010007008ebcba2fa0834c870500000000000000000000000000000000000000000000000000000000000000">
EXEC DB.dbo.spCacheRemove @UnitId, @Start, @Nights </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql </frame>
<frame procname="DB2.dbo.CacheItineraryBooking" line="27" stmtstart="1050" stmtend="1254" sqlhandle="0x03000700d0417248c26027003aa5000000000000000000000000000000000000000000000000000000000000">
EXEC sp_executesql @Command, N'@UnitId int, @Start date, @Nights smallint', @UnitId, @Start, @Nights; </frame>
<frame procname="DB2.dbo.sp_MSins_dboItinerary" line="109" stmtstart="4392" stmtend="9232" sqlhandle="0x030007002ed98c2aa0e422003aa5000001000000000000000000000000000000000000000000000000000000">
insert into [dbo].[Itinerary](
[ID],
[QuoteID],
[ServiceID],
[SupplierID],
[ContractID],
[BookingID],
[UnitID],
[TID],
[SID],
[ObjectID],
[Name],
[Code],
[Notes],
[Start],
[Finish],
[Expiry],
[Day],
[Nights],
[Cap],
[Child],
[Infant],
[Qty],
[Inst],
[SrcID],
[DstID],
[Dep],
[Arr],
[Days],
[Opt],
[Com],
[CRS],
[PID],
[Grp],
[TermsID],
[Free],
[FPer],
[FMax],
[Inc],
[FS],
[Hide],
[Out],
[O1],
[O2],
[O3],
[O4],
[O5],
[O6],
[O7],
[O8],
[P1],
[P2],
[P3],
[P4],
[P5],
[P6],
[P7],
[P8],
[B1],
[B2],
[B3],
[B4],
[B5],
[B6],
[B7],
[B8],
[S1],
[S2],
[S3],
[S4],
[S5],
[S6],
[S7],
[S8],
[Budget],
[Actual],
[BuyNA],
[SellNA],
[NearMatch],
[Attr],
[ACnt],
[Staff],
[Text],
[TExt2],
[AgentID],
[Est],
[NoReCost],
[Weekly],
[ExcQuestion],
[GFix],
[GRate],
[GChi </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 713873710] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594039042048" dbid="9" objectname="DB.dbo.Cache" indexname="PK_Cache" id="lock56b3aa880" mode="X" associatedObjectId="72057594039042048">
<owner-list>
<owner id="process2b9b56558" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process5d9814cf8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594039173120" dbid="9" objectname="DB.dbo.Cache" indexname="IX_Cache" id="lock582decd80" mode="U" associatedObjectId="72057594039173120">
<owner-list>
<owner id="process5d9814cf8" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process2b9b56558" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
</TextData>
November 6, 2015 at 9:30 am
Can you give us the DDL for dbo.Cache and/or the query plan for:
UPDATEdbo.Cache
SETAvailability = Availability - 1,
LastUpdated = GETUTCDATE()
WHEREUnitId = @UnitId
ANDStartDate < DATEADD(DAY, @Duration, @StartDate)
AND@StartDate < DATEADD(DAY, Duration, StartDate)
ANDAvailability > 0; </frame>
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 9, 2015 at 2:01 am
No problem Scott, here you go
CREATE TABLE [dbo].[Cache](
[Availability] [tinyint] NOT NULL,
[BasePrice] [numeric](9, 2) NOT NULL,
[Bookable] [tinyint] NOT NULL,
[BookingFee] [numeric](9, 2) NOT NULL,
[BookingFeeIncluded] [bit] NOT NULL,
[BrandId] [int] NOT NULL,
[CancellationScheme] [numeric](9, 2) NOT NULL,
[CancellationSchemeIncluded] [bit] NOT NULL,
[DateAdded] [datetime2](7) NOT NULL,
[Discount] [numeric](9, 2) NOT NULL,
[DiscountType] [tinyint] NOT NULL,
[Duration] [tinyint] NOT NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[LastUpdated] [datetime2](7) NOT NULL,
[PartitionId] [int] NOT NULL,
[ServiceId] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[UnitId] [int] NOT NULL,
[WasPrice] [numeric](9, 2) NOT NULL,
[Deleted] [bit] NOT NULL,
CONSTRAINT [PK_Cache] PRIMARY KEY CLUSTERED ([PartitionId] ASC,[ServiceId] ASC,[UnitId] ASC,[StartDate] ASC,[Duration] ASC)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Cache] ADD DEFAULT (getutcdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[Cache] ADD DEFAULT (getutcdate()) FOR [LastUpdated]
GO
ALTER TABLE [dbo].[Cache] ADD DEFAULT ((0)) FOR [Deleted]
GO
CREATE NONCLUSTERED INDEX [IX_Cache] ON [dbo].[Cache]([UnitId] ASC,[StartDate] ASC,[Duration] ASC)
GO
November 11, 2015 at 8:49 am
How many unique UnitIds are there? It looks as if the clustering key on the table should be ( UnitId, StartDate, Id ) [Id is optional, just to make it a unique key; remove Id if you don't need/want it to be unique).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 11, 2015 at 8:59 am
There are 38920 unique units, with 16117910 rows in total in the cache table.
The schema is a 3rd party schema so changing it is going to be months of work while they update their code and push it out via testing etc
November 11, 2015 at 9:20 am
Hmm. Typically one of the main things to address deadlocking is adjusting indexes. And the first priority in tuning is general is always to get the best clustered index on the table first.
Now, with that many unique unitids, you'd have to review potential fragmentation carefully before making it the clustering key. But just changing the clustering key from one keyset to another should not require testing, it should be transparent to the app. You can still have the same PK, just nonclustered. I'm only talking about changing the clustering key. Yeah, that's a pain, since everything has to be rebuilt, but there's no way around that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 12, 2015 at 4:24 am
While I agree with you that it should be seamless to the apps, as we are unfortunately not the custodians of the product we can't we just go and change the schema as it breaks the support agreement the client has with the 3rd party
I have suggested the change to the 3rd party, hopefully they will just say yes its ok to change, otherwise they will want to fully test and regression test it and then we would have to do the same
November 12, 2015 at 12:35 pm
anthony.green (11/6/2015)
UPDATEdbo.CacheSETAvailability = Availability - 1,
LastUpdated = GETUTCDATE()
WHEREUnitId = @UnitId
ANDStartDate < DATEADD(DAY, @Duration, @StartDate)
AND@StartDate < DATEADD(DAY, Duration, StartDate)
ANDAvailability > 0;
Any chance of getting them to recode this query to swap the predicate that uses table data in the function call?
AND@StartDate < DATEADD(DAY, Duration, StartDate)
Since both "StartDate" and "Duration" are columns in the table, this could be causing excessive locking. Per your stats, there is an average of 414 records per unique UnitID value, which means that around that many records need to be locked every time, and the query can't necessarily exclude any of them on that predicate.
One suggestion - have you tried using "snapshot isolation" on the database? If the deadlocking is due to non-intersecting records on shared pages (i.e., it isn't due to two processes trying to both update records with the same UnitId), that *might* help.
Clustering an index on (UnitID, StartDate, Duration, Availability) would provide the best chance to avoid index page collisions without any code changes.
If you have the opportunity to do some code changes, a couple of possibilities:
1. Create a computed column as "EndDate = StartDate + Duration + 1", so that you don't need to compute that value in the query. Then, include that column in the clustered index and recode the date part as:
AND StartDate BETWEEN EndDate AND DATEADD(DAY, @Duration-1, @StartDate)
This would avoid executing a function on the table data in the query.
2. If you can't add a computed column, try recoding the two date predicates as:
AND StartDate BETWEEN DATEADD(DAY, -(Duration+1), @StartDate) AND DATEADD(DAY, @Duration-1, @StartDate)
(Sorry - forgot to put the +/-1 adjustments for Duration to allow for the difference between a "BETWEEN" expression and ">" or "<". I updated the expressions with the adjustments.)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply