Deadlock issue

Question

Post reply

Deadlock issue

muratos2007

SSC Rookie

Points: 32
More actions
June 12, 2015 at 6:33 am

#304886

Hi friends,
I wish a good weekend for all 🙂
I have a problem with a deadlock.
Deadlock graph
The first query is :
Update Booking_Basket set
Cancelled=N'N',Departure=N'AYT',Arrival=N'a235c451900c428a8fc92f310be59364',CancelDate=N'19000101 00:00:00',CheckInDate=N'20150618 00:00:00',CheckOutDate=N'20150618 23:59:59',Class=N'',MealType=N'',ProductName=N'Group Transfer',CommissionRatio=0.1,CommissionBase=1985.46756,CommissionAmount=198.54,RemoteSysRef=N'',SupplierSalePrice=25,SupplierReducedPrice=0,SupplierSaleCurr=N'USD',DmcSalePrice=25,DmcSaleCurr=N'USD',OprSalePrice=30,OprSaleCurr=N'USD',OprSaleCrossRate=56.8088,OprSaleSurcharge=0
Where
ID=669125
There is a trigger on this table which updates Booking_Main table.
ALTER TRIGGER [dbo].[trgInsUpdBookingBasket]
ON [dbo].[Booking_Basket]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @BkgNumber varchar(16);
declare cs cursor
LOCAL STATIC READ_ONLY FORWARD_ONLY
for
select Distinct BkgNumber from inserted;
open cs;
fetch cs into @BkgNumber;
while(@@FETCH_STATUS=0)
begin
update Booking_Main
Set
DateBegin= (select min(CheckInDate) from Booking_Basket where BkgNumber=@BkgNumber and Cancelled='N'),
DateEnd= (select max(CheckOutDate) from Booking_Basket where BkgNumber=@BkgNumber and Cancelled='N')
where ID=@BkgNumber;
fetch cs into @BkgNumber;
end;
close cs
deallocate cs
END
The deadlock victim process
Update Booking_Main set
ServicesTotal=34132.928941419,TourPrice=39765,TotalCommission=3977,FuelSurcharges=6600,AgencyShouldPay=42388,Balance=-27388,BkgGuid=N'3e3a51ed403344d28f4834c59680564b',Status=N'WOC'
Where
ID=N'104324976'
So there are two updates which deadlock.
I have all the nonclustered indexes for where clauses for Booking_Main and everything works with Index Seek.
I have suggested not using the cursor for the programmer. But, surely he will continue use it 😀
I don't know what to do more.
Here is the execution plan.
Thanks for your help
Murat

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

There's no need to use a cursor in that trigger:

ALTER TRIGGER [dbo].[trgInsUpdBookingBasket]

ON [dbo].[Booking_Basket]

AFTER INSERT,UPDATE

AS

BEGIN

SET NOCOUNT ON;

UPDATE BM

SET

DateBegin = MinCheckInDate,

DateEnd = MaxCheckOutDate

FROM Booking_Main AS BM

CROSS APPLY (

SELECT MIN(CheckInDate) AS MinCheckInDate,

MAX(CheckOutDate) AS MaxCheckOutDate

FROM Booking_Basket

WHERE BkgNumber = BM.ID

AND Cancelled='N'

) AS aggr

WHERE BM.ID IN (

SELECT BkgNumber

FROM inserted

);

END

The shorter the transaction, the lesser it is likely to deadlock.

-- Gianluca Sartori

muratos2007 SSC Rookie Points: 32 More actions · Answer 2

Thanks for the quick reply 🙂

I will look at your code and try with that.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

muratos2007 (6/12/2015)
....I have suggested not using the cursor for the programmer. But, surely he will continue use it 😀
I don't know what to do more....

Your programmer is learning his trade at your expense, and he's a slow learner 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden