Solved

using OPTION (FORCE ORDER) in query

Posted on 2009-04-08
7
986 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 42
Error message when scheduling a job using a linked Server 12 67
Unable to save view in SSMS 21 73
Help  needed 3 35
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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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