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 = @ItemCode',N'@ItemCode nvarchar(3)',@ItemCode=N'FX1'
490843 | SP:StmtStarting | 78 | SELECT AutoKey, OnHand FROM ONNM(XLOCK) CROSS JOIN OITM(XLOCK) WHERE ObjectCode IN ('20', '10000048') AND ItemCode = @ItemCode
490848 | Lock:Acquired | 78 | (ca73a6396124)
495391 | Lock:Acquired | 114 | (36fa3e654c8f)
495396 | Lock:Acquired | 114 | (36fa3e654c8f)
542738 | Lock:Deadlock Chain | 6 | Deadlock Chain SPID = 78 (36fa3e654c8f)
545746 | Lock:Deadlock Chain | 6 | Deadlock Chain SPID = 154 (36fa3e654c8f)
545769 | Lock:Deadlock Chain | 6 | Deadlock Chain SPID = 114 (ca73a6396124)
545982 | Lock:Deadlock | 78 | (36fa3e654c8f)
545985 | RPC:Completed | 78 | exec sp_executesql N'SELECT AutoKey, OnHand FROM ONNM(XLOCK) CROSS JOIN OITM(XLOCK) WHERE ObjectCode IN (''20'', ''10000048'') AND ItemCode = @ItemCode',N'@ItemCode nvarchar(3)',@ItemCode=N'FC3'
545990 | Lock:Released | 78 | (ca73a6396124)
545998 | Lock:Acquired | 114 | (ca73a6396124)
546130 | Deadlock graph | 17 | (See below)
The deadlock graph is as follows:
<deadlock-list>
<deadlock victim="process5e3ae08">
<process-list>
<process id="process5e3ae08" taskpriority="0" logused="0" waitresource="KEY: 6:72057598620205056 (36fa3e654c8f)" waittime="9507" ownerId="513854688" transactionname="user_transaction" lasttranstarted="2012-06-28T13:38:54.463" XDES="0x1bbf9b950" lockMode="RangeX-X" schedulerid="6" kpid="13248" status="suspended" spid="78" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-06-28T13:38:54.483" lastbatchcompleted="2012-06-28T13:38:54.463" clientapp=".Net SqlClient Data Provider" hostname="PMIUSRSTL00013" hostpid="3180" loginname="RICFSE01SAPB1" isolationlevel="serializable (4)" xactid="513854688" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="46" sqlhandle="0x02000000151c651e3c8b4437ee414cefdbc46b70aceca960"> SELECT AutoKey, OnHand FROM ONNM(XLOCK) CROSS JOIN OITM(XLOCK) WHERE ObjectCode IN ('20', '10000048') AND ItemCode = @ItemCode </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame>
</executionStack>
<inputbuf> (@ItemCode nvarchar(3))SELECT AutoKey, OnHand FROM ONNM(XLOCK) CROSS JOIN OITM(XLOCK) WHERE ObjectCode IN ('20', '10000048') AND ItemCode = @ItemCode </inputbuf>
</process>
<process id="process5e45048" taskpriority="0" logused="0" waitresource="KEY: 6:72057598620205056 (36fa3e654c8f)" waittime="12866" ownerId="513845303" transactionname="SELECT" lasttranstarted="2012-06-28T13:38:28.463" XDES="0x80048e50" lockMode="S" schedulerid="7" kpid="12772" status="suspended" spid="154" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2012-06-28T13:38:28.390" lastbatchcompleted="2012-06-28T13:38:28.380" lastattention="2012-06-28T13:38:27.927" clientapp="TagClerkService" hostname="PMIUSRSTW00008" hostpid="2772" loginname="RICFSE01SAPB1" isolationlevel="read committed (2)" xactid="513845303" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x020000000d86e30171762b464b35e0923de532c497188b81"> SELECT T0.* FROM [dbo].[ONNM] T0 </frame>
</executionStack>
<inputbuf> SELECT T0.* FROM [dbo].[ONNM] T0 </inputbuf>
</process>
<process id="process5e31708" taskpriority="0" logused="25240" waitresource="KEY: 6:72057597517103104 (ca73a6396124)" waittime="2031" ownerId="513831818" transactionguid="0xdefde99023405d4a96d101faac79ef96" transactionname="user_transaction" lasttranstarted="2012-06-28T13:38:19.237" XDES="0xd68df950" lockMode="RangeS-S" schedulerid="5" kpid="11564" status="suspended" spid="114" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-06-28T13:38:58.560" lastbatchcompleted="2012-06-28T13:38:58.490" lastattention="2012-06-28T12:48:33.060" clientapp="TagClerkService" hostname="PMIUSRSTL00011" hostpid="2908" loginname="RICFSE01SAPB1" isolationlevel="serializable (4)" xactid="513831818" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="24" sqlhandle="0x02000000a329a30eb1319f2df684296f889a1613a585b3a0"> SELECT ISNULL(a.U_FSE_ApplicationUsr,a.UserSign) ,a.DocNum ,b.LineNum ,f.IsLotTraced ,h.LotNumber ,b.WhsCode ,n.Bin ,g.Quantity ,b.LineTotal ,i.OnTimeDeliveryDaysEarly ,i.OnTimeDeliveryDaysLate ,i.ReceiptVarianceUnderAllowed ,i.ReceiptVarianceOverAllowed ,k.ItemCode ,d.Revision ,a.Comments ,e.RolledMaterialCost ,e.RolledLaborCost ,e.RolledVariableOverheadCost ,e.RolledFixedOverheadCost ,e.RolledOutsideCost ,ISNULL(c.Quantity, c2.Quantity) ,c.DocumentLineDetailKey /* TODO: Handle serial numbers for blanket deliveries */ ,ISNULL(c.InventoryCode, c2.InventoryCode) ,k.InvntryUom ,r.Resource ,p.ItemName ,CASE WHEN m.MOKey IS NULL THEN NULL ELSE b.U_FSE_STypeItemQty END ,a.DocDate FROM OPDN a LEFT JOIN PDN1 b ON a.DocEntry=b.DocEntry LEFT JOIN FSE_PurchaseGoodsReceiptLineDetail c ON (b.DocEntry=c.DocumentKey and b.LineNum=c.DocumentLineKey) LEFT JOIN FSE_PurchaseGoodsReceiptDeliveryLineDetail c2 ON (b.DocEntry=c2.DocumentKey and b.LineNum=c2.DocumentLineKey) LEFT JOIN FSE_MO m ON m.DocumentNumber = b.U_FSE_MONumbe </frame>
<frame procname="unknown" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame>
<frame procname="FSDB00.dbo.SBO_SP_TransactionNotification" line="25" stmtstart="1588" stmtend="2032" sqlhandle="0x030006000a73d75dfe95d60059a000000100000000000000"> exec usp_FSE_HandleSBONotification @object_type, @transaction_type, @num_of_cols_in_key, @list_of_key_cols_tab_del, @list_of_cols_val_tab_del, @error out, @error_message out -- keep this on one line --PMI DataTransfer </frame>
<frame procname="adhoc" line="1" sqlhandle="0x01000600fd116804e00371a5000000000000000000000000"> EXECUTE SBO_SP_TransactionNotification N'20',N'A',1,N'DocEntry',N'134138' </frame>
</executionStack>
<inputbuf> EXECUTE SBO_SP_TransactionNotification N'20',N'A',1,N'DocEntry',N'134138' </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057598620205056" dbid="6" objectname="FSDB00.dbo.ONNM" indexname="ONNM_PRIMARY" id="lock1aa7d9880" mode="RangeX-X" associatedObjectId="72057598620205056">
<owner-list/>
<waiter-list>
<waiter id="process5e3ae08" mode="RangeX-X" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057598620205056" dbid="6" objectname="FSDB00.dbo.ONNM" indexname="ONNM_PRIMARY" id="lock1aa7d9880" mode="RangeX-X" associatedObjectId="72057598620205056">
<owner-list>
<owner id="process5e31708" mode="RangeX-X"/>
</owner-list>
<waiter-list>
<waiter id="process5e45048" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057597517103104" dbid="6" objectname="FSDB00.dbo.OITM" indexname="OITM_PRIMARY" id="lock215ce6980" mode="X" associatedObjectId="72057597517103104">
<owner-list>
<owner id="process5e3ae08" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process5e31708" mode="RangeS-S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
My question is, why was it possible (is it normal) for SPID 78 to acquire a lock on OITM (ca73a6396124) (which happens to represent ItemCode FC1 if I recall correctly) even though it could not acquire a lock on ONNM (36fa3e654c8f). Is my assumption that SQL statements get all-or-none locks incorrect? Does it not hold if the holder of the ONNM lock was a shared lock on a different connection within a transaction that contains multiple connections in the other process? (I think SPID 99 is another connection in the same process as SPID 114.)
This seems to be a really complicated problem to decipher; I don't know if there's a better way of going about deadlock avoidance or finding out what happened here.
This question is being copied/migrated from https://stackoverflow.com/questions/11265864/all-or-none-exclusive-lock-on-2-sql-tables
Best Answer
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.