THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

When acquiring locks in the same order is not possible or not feasible.

To avoid deadlocks, one of the most common recommendations is "to acquire locks in the same order" or "access objects in the same order". Clearly this makes perfect sense, but is it always feasible? Is it always possible? I keep encountering cases when I cannot follow this advice.

If I store an object in one parent table and one or more child ones, I cannot follow this advice at all. When inserting, I need to insert my parent row first. When deleting, I have to do it in the opposite order.

If I use commands that touch multiple tables or multiple rows in one table, then usually I have no control in which order locks are acquired, (assuming that I am not using hints).

So, in many cases trying to acquire locks in the same order does not prevent all deadlocks. So, we need some kind of handling deadlocks anyway - we cannot assume that we can eliminate them all. Unless, of course, we serialize all access using Service Broker or sp_getapplock.

 

 

Published Friday, January 15, 2010 4:18 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

merrillaldrich said:

I've been able to work around this in some tough cases by adding a select statement with a locking hint upstream, to force the order. (this violates your rule about no hints, though :-) For example, if it's important to lock a row in table A and then delete from table B, and then delete from A, it's possible to select from A with locking hints to "reserve" the parent row, then delete children from B, then the parent from A. A little ugly, but it works as a last resort...

January 15, 2010 6:17 PM
 

Alex K said:

Merril,

In the scenario which you are describing, you do not need to acquire locks on all objects - locks on just one, on the parent row, is enough. If I understood you correctly, that is exactly what you are doing: getting an exclusive lock on one resource. As long as you are acquiring that one exclusive lock in the same way in all your modules, you are fine, and your approach is equivalent to sp_getapplock. If, however, you are using different commands to get it, you still may get deadlocks.

This is the whole point of my post: instead of trying to maintain the same order, serialize on a single resource.

January 16, 2010 9:26 AM
 

Armando Prato said:

Alex, have you used the technique of capturing the 1205 message and re-trying the transaction?  Admittedly, I have never had the need for it but was curious if you have and if you've found the results less than ideal.

January 19, 2010 4:57 PM
 

Alexander Kuznetsov said:

Armando,

If I catch 1205 and retry, it definitely works. If I do it in C#, I can do it just once for all modifications, so there is very little effort involved. However, automatically retrying after deadlocks may lead to lost updates, and that might be not good:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/08/retrying-after-deadlocks-leads-to-lost-updates.aspx

January 19, 2010 5:20 PM
 

Armando Prato said:

Right, I've read that one before.  I guess my fundamental question is whether a mutex lock really makes sense or should you just make an attempt at re-trying at least once before giving up.   The way I look at it, if you fail on a 2nd attempt or subsequent attempts, you likely have a larger issue somewhere else.

January 20, 2010 2:57 PM
 

Alexander Kuznetsov said:

Armando,

In my benchmarks, custom tailored for my environment, it is much faster to avoid deadlocks than to have them and retry. I cannot make blanket statements other than that we need to benchmark if performance is important.

January 20, 2010 3:16 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement