Good morning,

I am trying to get my head around locking (row, table) and Isolation Levels. We have written a large .NET/SQL application and one day last week we had about two dozen people in our company do some semi "stress/load" testing of the app.

On quite a few occassions, a few of the users would receive the following error:

"Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

We are handling this on two fronts, the app and the database. The error handling in the app is being modified to capture this specific error and to retry the transaction.

However, from the database side, I am trying to find the most affective and efficient change to make regarding locking. I have been doing a lot of reading online and in BOL to get a better grasp of locking, but what I would really like is feedback from the community (forum) and get there thoughts on what changes I should make, if any, on the db side.

Thanks...

Scott

__________________
========================
Scott Klein
Author of:
Professional SQL Server 2005 XML
Professional WCF Programming: .NET Development with the Windows Communication Foundation
Professional LINQ
========================

Deadlock means that two transaction each lock a resource say a row in a Table.
Perhaps in the usp_UpdateProductPrice proc (the name sounds good anyway) you do the following
begin tran
    update Product
    Update QuoteHeader -- to update all of the Quotes (that are not yet sales) with the new price
Commit tran

Then in another proc perhaps named usp_UpdateQuoteHeader (to update some other info)
begin tran
    SELECT FROM QuoteHeader
    select FROM Product
    Update QuoteHeader
Commit Tran

Well when usp_UpdateProductPrice and usp_UpdateQuoteHeader happen to run at the same time and are affecting the same rows
Here is what happens
usp_UpdateProductPrice usp_UpdateQuoteHeader
Exclusive Lock on Product Shared Lock on QuoteHeader
Intent Exclusive on QuoteHeader Intent Shared on Product
(can't get the exclusive (can't get the shared because of the exclusive)
because of the shared lock)

We get a deadlock, because usp_UpdateProductPrice won't let go of Product until it gets QuoteHeader
and usp_UpdateQuoteHeader won't let go of QuoteHeader until it gets Product.

So SQL Server will randomly pick on transaction and call it the deadlock victim and kill it.
However, this takes 30 seconds.

The solution is to change on of the procs so that they both access things in the same order

Then in another proc perhaps named usp_UpdateQuoteHeader (to update some other info)
SELECT FROM QuoteHeader -- get the product info
begin tran
    select FROM Product -- put this first in the transaction
    SELECT FROM QuoteHeader
    Update QuoteHeader
Commit Tran

Now we get the following:
usp_UpdateProductPrice usp_UpdateQuoteHeader
Exclusive Lock on Product Intent Shared on Product
Exclusive Lock on QuoteHeader Blocking waiting for Shared Lock on Product
Commits and releases locks
                Shared Lock on Product
                Shared Lock on QuoteHeader
                Exclusive Lock on QuoteHeader
                Commits and releases locks

OR we get

usp_UpdateProductPrice usp_UpdateQuoteHeader (if this is slightly ahead)
                Shared Lock on Product
Intent Exclusive on Product Shared Lock on QuoteHeader
                Exclusive Lock on QuoteHeader
                Commits and releases locks
Exclusive Lock on Product
Exclusive Lock on QuoteHeader
Commits and releases locks

Use Profiler and your error reporting from the client app to sniff out which
SQL Transactions are causing the deadlocks

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Hi David, I appreciate the response.

I forgot an important piece of information in my original post. The error the users recieved were on INSERTING data, meaning, user1 added a record within seconds of user2 into the same table. We also do not use procs for our updates and inserts (we use data binding via a 3rd party framework for that...but yes, we use procs for our selects for reports, etc).

This is where I am having the difficulty.

It may be that the users received the error while inserting data but it could be something else that locked the resources until you received a deadlock error.

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

sad to say there is no 100% solution. This issue deals with how MS-SQL does updates. Inspite of what MS claims they still do page locking. So when you see high level volumes if you get three transactions where the data from one transactions overlaps the page of another and that data overlaps the page of yet another. The SQL engine gets deadlocking. As of SQL 2000 they added a feature (when they claimed to have "FIXED" the issue). That auto selects one of the processes and rejects it. The error message your are getting is identifying your client that they were the one selected to die to allow the others to complete. Unfortunately MS-SQL does not have a retry insert functionality.

If you don't like this about the only solution is to go to Oracle. If you want to minimize it there are a number of directions you can head to reduce it but you can't stop it 100% until MS eventually does true row level locking like Oracle does (the only 24/7 high load high volume database). You can pool your connections. If you have 100 users setup something where they connect to some sort of middle tier and only the middle tier communicates to the SQL server. This reduces the chance of three inserts hitting simultaneously, sort of queue's them up a bit. Also have your reads to dirty reads if the logic of your app allows it (no lock). and there is one more technique. Keep your inserts as short and quick as possible, avoid long transactions before commiting them.

Thats about all you can do unfortunately. SQL servers that do good row level locking are much slower than those that do page level locking. If you want to play with Mysql it gives you the options of different engines one that handle row locking and one that does page locking similar to MS-SQL. The speed is about 4x slower and space requirements for data storage is about 4x larger for the row level locking engine. I doubt Oracle did as inefficient a job but I am sure it makes things slower and more cumbersome.

Oooops almost forgot try to federate your data as much as possible making smaller tables or dividing your data into different databases if your application logic allows it.

Later and good luck,
Rob

Thank you everyone for your responses and feedback. Each one is greatly appreciated. I know I can always trust this forum to provide excellent feedback.

I think this issue will need to be addressed from both sides. I was talking with one of the developers of our .NET app today and he admitted that there are probably some spots where the code can probably be looked at in better detail because the deadlocking was happening in a specific part of the app. So I think a lot of this can be cleaned up by looking at the .NET code.

I will also do some more looking into what can be done on the db side based on all of your comments. Unfortunately, going to Oracle isn't an option right now, but I appreciate the idea, Rob.

If we do isolate this and are able to bring the deadlocking down to a minimum, I will certainly let you all know.

Thanks again everyone.

Scott

Depending on how deeply you want to test you may want to increase the number of users in your testing or setup some sort of automated testing application on multiple machines. I generally only start to see this with 50+ heavy use users in typical client server or web client server type apps. You need a high volume of inserts and selects on the same database table preferably with a lot of rows to see the problem consistently. The longer the transactions the more likely you see the issue.

My point about switching to Oracle was intended as only if it is absolutely needed. If you follow the news Hitachi built a part of the Japanese stock exchange using MS-SQL. It is the same system that has crashed recently multiple times becaue of deadlocking in high volume situations (guy in charged quit over it). My point was more along the lines of if your doing something like that you should consider Oracle. For most apps MS-SQL is good enough because few require a true 24-7 workload or need to process the volume you seen in a stock exchange. MS-SQL is easier to code in, costs less, has more install locations and generally has a larger skilled labor pool than you see with Oracle. With cost pressures what they are you see more companies moving away from Oracle to MS-SQL because its good enough, and to Mysql or Postgress because its even cheaper and good enough. I like to use various RDBMS to better understand thier differences. I look forward to finding out what you did to reduce the locking issues.