Solved

using OPTION (FORCE ORDER) in query

Posted on 2009-04-08
7
961 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

815 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

9 Experts available now in Live!

Get 1:1 Help Now