Reduce deadlocks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Reduce deadlocks

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-09-28 : 05:15:20

I have a proc that is causing deadlocking issues. I have changed the code slightly, with little affect. I have an idea of replacing the SELECT statement (which assigns the result to a parameter which is then used in the where clause) with a join:

Current Code:

SELECT TOP 1 @OrderID = OI.OrderID 
FROM oms.OrderIdentifier OI WITH (NOLOCK)
INNER JOIN oms.OrderHeader OH WITH (UPDLOCK) ON OI.OrderID = OH.OrderID
INNER JOIN oms.OrderStatus OS  WITH (NOLOCK) ON OS.StatusID = OH.StatusID
AND os.StatusID IN (1,3)
WHERE OI.OriginatorID = @OriginatorID
ORDER BY os.Precedence, OH.OrderID ASC 


UPDATE oms.OrderHeader SET 	
	DeliveryCharge = @DeliveryCharge,
	StaffDiscount = @StaffDiscount,
	OrderTotal = @OrderTotal,
	ClubcardPoints = @ClubcardPoints,
	DeliveryChargeTPNB = CASE WHEN @ReplaceDeliveryChargeTPNB = 1 THEN @DeliveryChargeTPNB ELSE DeliveryChargeTPNB END
WHERE OrderID = @OrderID

However, the first statement needs to select only the one OrderID, StatusID of 1 having precedence over 3. I don't know how to translate this requirement in the join, as the code below would update both Orders with a statusID of 1 or 3, which is incorrect:

UPDATE oms.OrderHeader SET 	
	DeliveryCharge = @DeliveryCharge,
	StaffDiscount = @StaffDiscount,
	OrderTotal = @OrderTotal,
	ClubcardPoints = @ClubcardPoints,
	DeliveryChargeTPNB = CASE WHEN @ReplaceDeliveryChargeTPNB = 1 THEN @DeliveryChargeTPNB ELSE DeliveryChargeTPNB END
FROM oms.OrderIdentifier OI WITH (NOLOCK)
INNER JOIN oms.OrderHeader OH WITH (UPDLOCK) ON OI.OrderID = OH.OrderID
INNER JOIN oms.OrderStatus OS  WITH (NOLOCK) ON OS.StatusID = OH.StatusID
AND os.StatusID IN (1,3)
WHERE OI.OriginatorID = @OriginatorID

If an individual had an order with a status of 1 or 3, then I would only want to update the order with the highest precedence. I could set @@rowcount to 1, so the update would apply to only the one order, however, this seems to be a bit of a hack, therefore, I would greatly appreciate anyones perspective on this.

Thanks in advance

Hearty head pats

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 05:20:45

How about using Set Rowocount to limit no. of rows to be updated to 1:

SET ROWCOUNT 1

UPDATE oms.OrderHeader SET 	
	DeliveryCharge = @DeliveryCharge,
	StaffDiscount = @StaffDiscount,
	OrderTotal = @OrderTotal,
	ClubcardPoints = @ClubcardPoints,
	DeliveryChargeTPNB = CASE WHEN @ReplaceDeliveryChargeTPNB = 1 THEN @DeliveryChargeTPNB ELSE DeliveryChargeTPNB END
FROM oms.OrderIdentifier OI WITH (NOLOCK)
INNER JOIN oms.OrderHeader OH WITH (UPDLOCK) ON OI.OrderID = OH.OrderID
INNER JOIN oms.OrderStatus OS  WITH (NOLOCK) ON OS.StatusID = OH.StatusID
AND os.StatusID IN (1,3)
WHERE OI.OriginatorID = @OriginatorID

Harsh Athalye
India.
"Nothing is Impossible"

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-09-28 : 05:54:01

Hiya

Thankyou for your reply. I did think of that, but felt that it was a bit of a workaround rather than solving the actual query. Any other suggestions?

Thanks

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-09-28 : 06:00:27

Oh, and we are using SQL Server 2005, so any approaches including new TSQL syntax would be handy.

Ta

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-09-28 : 08:10:46

Hmmmm, tried to do the query using ROWCOUNT, but alas, I cannot ORDER BY StatusID, which means that this method will not work (I have to order the results in order to obtain the correct Order in terms of precendence).

I have created a subselect instead, which works fine. However, I would still be interested in a method using a join.

Hearty head pats

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-28 : 13:26:00

Regarding the "1 row by precedence" will this work?


update	oh set
	DeliveryCharge = @DeliveryCharge,
	StaffDiscount = @StaffDiscount,
	OrderTotal = @OrderTotal,
	ClubcardPoints = @ClubcardPoints,
	DeliveryChargeTPNB = CASE WHEN @ReplaceDeliveryChargeTPNB = 1 THEN @DeliveryChargeTPNB ELSE DeliveryChargeTPNB END
from	oms.OrderHeader oh
where	orderid =
	(
	select	top 1 oh.orderid
	from	oms.OrderIdentifier oi
	join	oms.OrderHeader oh
			on oh.orderid = oi.orderid
	where	oi.originatorid = @originatorid
	and	oh.StatusID in (1,3)
	order by oh.statusid
	)

The deadlocks could be a stickier situation. Is this the only statement in your stored procedure? Is there explicit transaction control in this procedure? Are different transactional statements being issued concurrently?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources