?
Solved

using OPTION (FORCE ORDER) in query

Posted on 2009-04-08
7
Medium Priority
?
1,041 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 42

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 42

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 42

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

800 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