Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

using OPTION (FORCE ORDER) in query

Posted on 2009-04-08
7
Medium Priority
?
1,072 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 43

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 43

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 43

Accepted Solution

by:
pcelba earned 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline

610 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