When acquiring locks in the same order is not possible or not feasible.http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/15/when-acquiring-locks-in-the-same-order-is-not-possible-or-not-feasible.aspxTo 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 whenenCommunityServer 2.1 SP2 (Build: 61129.1)re: When acquiring locks in the same order is not possible or not feasible.http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/15/when-acquiring-locks-in-the-same-order-is-not-possible-or-not-feasible.aspx#21099Fri, 15 Jan 2010 23:17:13 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21099merrillaldrich<p>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 &quot;reserve&quot; the parent row, then delete children from B, then the parent from A. A little ugly, but it works as a last resort...</p>
re: When acquiring locks in the same order is not possible or not feasible.http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/15/when-acquiring-locks-in-the-same-order-is-not-possible-or-not-feasible.aspx#21107Sat, 16 Jan 2010 14:26:28 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21107Alex K<p>Merril,</p>
<p>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.</p>
<p>This is the whole point of my post: instead of trying to maintain the same order, serialize on a single resource.</p>
re: When acquiring locks in the same order is not possible or not feasible.http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/15/when-acquiring-locks-in-the-same-order-is-not-possible-or-not-feasible.aspx#21217Tue, 19 Jan 2010 21:57:56 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21217Armando Prato<p>Alex, have you used the technique of capturing the 1205 message and re-trying the transaction? &nbsp;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.</p>
re: When acquiring locks in the same order is not possible or not feasible.http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/15/when-acquiring-locks-in-the-same-order-is-not-possible-or-not-feasible.aspx#21218Tue, 19 Jan 2010 22:20:14 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21218Alexander Kuznetsov<p>Armando,</p>
<p>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:</p>
<p><a rel="nofollow" target="_new" href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/08/retrying-after-deadlocks-leads-to-lost-updates.aspx">http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/08/retrying-after-deadlocks-leads-to-lost-updates.aspx</a></p>
re: When acquiring locks in the same order is not possible or not feasible.http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/15/when-acquiring-locks-in-the-same-order-is-not-possible-or-not-feasible.aspx#21278Wed, 20 Jan 2010 19:57:09 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21278Armando Prato<p>Right, I've read that one before. &nbsp;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. &nbsp; The way I look at it, if you fail on a 2nd attempt or subsequent attempts, you likely have a larger issue somewhere else.</p>
re: When acquiring locks in the same order is not possible or not feasible.http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/15/when-acquiring-locks-in-the-same-order-is-not-possible-or-not-feasible.aspx#21281Wed, 20 Jan 2010 20:16:21 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21281Alexander Kuznetsov<p>Armando,</p>
<p>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.</p>