Solved

using OPTION (FORCE ORDER) in query

Posted on 2009-04-08
7
927 Views
Last Modified: 2012-05-06
I am considering using OPTION (FORCE ORDER) in a query to eliminate a deadlock condition.

Are there any known issues with this option? (did not find any using google)  
Are there better choices?  
My initial tests show that that FORCE ORDER actually improves query performance.  Is that surprising?  

Details:

The (over) simplified version of the 1st query (candidate for FORCE ORDER) is essentially the classic:

SELECT C.CustomerID, O.OrderDate, O.Amount
      FROM Customer C
      INNER JOIN Order O ON C.CustomerId = O.CustomerId

Query Optimizer, in it's infinite wisdom,  Index Scans 'Order', THEN Index Scans 'Customer'..  

In my environment 'Customer' has 200,000 rows and 'Order' has 3 million rows.  This 1st query is typically the deadlock victim.  

The 2nd query (simplified) is an INSERT transaction:

BEGIN TRANSACTION
INSERT INTO Customer (CustomerName) VALUES( 'joe bob')
SET @CustomerId = @@IDENTITY
INSERT INTO Order (@CustomerId, Date, Amount) VALUES ('2009-04-31', 200)
INSERT INTO Order (@CustomerId, Date, Amount) VALUES ('2009-05-31', 200)
COMMIT TRANSACTION

These 2 queries create the textbook SQL Server deadlock condition.  1st query accesses the tables in sequence:  'Order' then 'Customer'.  2nd query accesses 'Customer' then 'Order'.  Both are holding locks on the table needed by the other query:  deadlock.  I have already taken an optimization pass at both queries and that improved the deadlocking, but did not eliminate it.

My plan is to add FORCE ORDER to the 1st query as:

SELECT C.CustomerID, O.OrderDate, O.Amount
      FROM Customer C
      INNER JOIN Order O ON C.CustomerId = O.CustomerId
      OPTION (FORCE ORDER)

This will cause both queries to access the tables in the same order and avoid deadlock.  

All comments appreciated. Thank you.
0
Comment
Question by:fesnyng
  • 4
  • 3
7 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 24097663
You should study TRANSACTION ISOLATION LEVELs, as well. The proper isolation level should allow data reading during the transaction.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24097717
Is it necessary to have both Customer and Order insertion in one transaction? If you split it into two transactions then the deadlock cannot happen.
0
 
LVL 5

Author Comment

by:fesnyng
ID: 24097778
Good observation.  I left that part out.  It took a very long time to figure out the deadlock because it certainly seemeed that the 1st query should be able to read.

The 1st transaction is running at READ COMMITTED -- it is an accounting function and cannot run at READ UNCOMMITTED (nolock)..  This causes a shared lock at the table level (there is no where clause).  The INSERTs also attempt to obtain locks and the deadlocak occurs.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 5

Author Comment

by:fesnyng
ID: 24097793
>>Is it necessary to have both Customer and Order insertion in one transaction

Yes.  It is necessary for this function.  Another great observation.  

I have been looking at this for months and failed to include some of my assumptions -- which, of course, may be in error.  So, I do appreciate the questions.
0
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 24097849
Then it seems you have to use the FORCE ORDER option. It should not cause any problems, it just slightly changes optimization schema calculated by SQL which is not important in this case.
0
 
LVL 5

Author Comment

by:fesnyng
ID: 24097938
>>just slightly changes optimization schema calculated by SQL

I like the change as long as there are no side effects.

Results in milliseconds:
without FORCE ORDER      903
with FORCE ORDER      720

THANK YOU.
0
 
LVL 5

Author Closing Comment

by:fesnyng
ID: 31568038
Thank you for your help.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now