4

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 (&apos;20&apos;, &apos;10000048&apos;) 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 (&apos;20&apos;, &apos;10000048&apos;) 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&apos;20&apos;,N&apos;A&apos;,1,N&apos;DocEntry&apos;,N&apos;134138&apos;     </frame>
            </executionStack>
            <inputbuf>  EXECUTE SBO_SP_TransactionNotification N&apos;20&apos;,N&apos;A&apos;,1,N&apos;DocEntry&apos;,N&apos;134138&apos;    </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

Improve this question
0

1 Answer 1

Reset to default
3

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.

Improve this answer
8
  • We have a retry system that re-attempts the transaction when the transaction is chosen as the deadlock victim, but are wary of using that approach because re-trying transactions increases the resource load. Am I correct in assuming that if I focus on retrying this specific statement when it deadlocks, it is much better than retrying the transaction wherever it may have deadlocked down the line without having established this lock first?
    – BlueMonkMN
    Jul 1, 2012 at 13:02
  • Also, I'm concerned about the time it takes for a deadlock to be detected. How quickly is a deadlock detected once it occurs?
    – BlueMonkMN
    Jul 1, 2012 at 13:03
  • Be careful with retrying stuff. A deadlock can be semantically meaningful in the sense that underlying data was changed and you need to adapt what you write (think of a counter being incremented concurrently. You would loose an update when you just retry the write). Deadlocks are detected periodically on a background timer. This is documented somewhere on MSDN.
    – usr
    Jul 1, 2012 at 13:09
  • Right, I mean we would retry the whole transaction and not just one statement in the middle, but I said just this specific statement because this is the first statement in the transaction, so I assume retrying from that point is much more performant than having to retry later.
    – BlueMonkMN
    Jul 1, 2012 at 13:18
  • I guess so. Deadlocks should be rare anyway. I'd try some common countermeasures first like enabling snapshot isolation and running under the right isolation level. I think retrying rare deadlocks is entirely ok. Rare events don't matter for total performance.
    – usr
    Jul 1, 2012 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.