All-or-none exclusive lock on 2 SQL tables

My simplistic testing suggested that I can acquire an exclusive lock on 2 tables or postpone acquiring the lock on either until both can be acquired at once by using a query like this:

SELECT AutoKey, OnHand FROM ONNM(XLOCK) CROSS JOIN OITM(XLOCK)
WHERE ObjectCode IN ('20', '10000048') AND ItemCode = @ItemCode

the key feature being the CROSS JOIN used to include multiple tables in a single query. I confirmed that if an exclusive lock already exists on the OITM row for @ItemCode in another transaction, then this statement doesn't introduce any lock on ONNM until it can acquire both locks. However, in the more complex full test run, SQL profiler returns some conflicting information. I have imported the SQL profiler results into a table and executed the following query:

select rownum, e.name, EventClass, TextData, SPID, ClientProcessID from #pmit t
join sys.trace_events e on t.EventClass = e.trace_event_id
where rownum between 275799 and 546130
and (TextData LIKE '(ca73a6396124)' or TextData LIKE '(36fa3e654c8f)'
     or TextData LIKE '%(XLOCK)%' or TextData LIKE '%DeadLock%')
order by rownum

And these are the results I got back:

```
Row | Event | SPID | Text
275799 | SP:StmtStarting | 99 | SELECT AutoKey, OnHand FROM ONNM(XLOCK) CROSS JOIN OITM(XLOCK) WHERE ObjectCode IN ('20', '10000048') AND ItemCode = @ItemCode
304781 | Lock:Acquired | 139 | (36fa3e654c8f)
305365 | Lock:Acquired | 139 | (36fa3e654c8f)
351093 | Lock:Acquired | 139 | (ca73a6396124)
468768 | Lock:Released | 13 | (ca73a6396124)
470912 | Lock:Released | 13 | (36fa3e654c8f)
470922 | Lock:Acquired | 164 | (36fa3e654c8f)
470928 | Lock:Released | 164 | (36fa3e654c8f)
470971 | Lock:Acquired | 99 | (36fa3e654c8f)
471013 | RPC:Completed | 99 | exec sp_executesql N'SELECT AutoKey, OnHand FROM ONNM(XLOCK) CROSS JOIN OITM(XLOCK) WHERE ObjectCode IN (''20'', ''10000048'') AND ItemCode = @ItemCo

No, locks are acquired one-after-the-other because the number of locks is generally unpredictable. It might change with row count. This could work differently, but it doesn't.

Your locking technique basically doesn't work. The best workaround I can think of is to write a retry-loop which tries to acquire both locks and retries on deadlock.

StackExchange Database Administrators Q#20172, answer score: 3