Does Encryption Affect Seeing Statements in Deadlock Graphs?

Good question. I don’t have a clue. So let’s set up a test. I’ll create this stored procedure:

CREATE PROCEDURE DL2e
    WITH ENCRYPTION
AS 
    BEGIN TRANSACTION
    UPDATE  Purchasing.PurchaseOrderDetail
    SET     OrderQty = 2
    WHERE   ProductID = 448
            AND PurchaseOrderID = 1255;

Then I’ll execute things in the following order. From one connection this query:

UPDATE  Purchasing.PurchaseOrderHeader
SET     Freight = Freight * 0.9 --9% discount on shipping
WHERE   PurchaseOrderID = 1255;

From a second connection, my stored procedure:

EXEC dbo.dl2e;

Then, back on the first connection, this query:

UPDATE Purchasing.PurchaseOrderDetail
SET OrderQty = 4
WHERE ProductID = 448
AND PurchaseOrderID = 1255;

That will generate a deadlock. It’s a straight-forward classic deadlock. I’m using extended events to capture the deadlock graph and the output looks like this:

<deadlock>
 <victim-list>
  <victimProcess id="process472310928" />
 </victim-list>
 <process-list>
  <process id="process472310928" taskpriority="0" logused="400" waitresource="KEY: 9:72057594046840832 (4ab5f0d47ad5)" waittime="4261" ownerId="2823352" transactionname="user_transaction" lasttranstarted="2012-06-19T10:51:50.420" XDES="0x47b3c96a8" lockMode="U" schedulerid="2" kpid="3532" status="suspended" spid="56" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2012-06-19T10:55:17.900" lastbatchcompleted="2012-06-19T10:55:17.900" lastattention="2012-06-19T10:52:28.403" clientapp="Microsoft SQL Server Management Studio - Query" hostname="RED1" hostpid="1704" loginname="NEVERNEVER\grant" isolationlevel="read committed (2)" xactid="2823352" currentdb="9" lockTimeout="4294967295" clientoption1="671098976" clientoption2="390200">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="64" sqlhandle="0x02000000d0c7f31a30fb1ad425c34357fe8ef6326793e7aa0000000000000000000000000000000000000000">
UPDATE [Purchasing].[PurchaseOrderDetail] set [OrderQty] = @1  WHERE [ProductID]=@2 AND [PurchaseOrderID]=@3    </frame>
    <frame procname="adhoc" line="1" sqlhandle="0x02000000b3eb2f2775cd4b2ff5256510f0ca8f71762f6d740000000000000000000000000000000000000000">
UPDATE Purchasing.PurchaseOrderDetail
SET OrderQty = 4
WHERE ProductID = 448
AND PurchaseOrderID = 1255;    </frame>
   </executionStack>
   <inputbuf>
UPDATE Purchasing.PurchaseOrderDetail
SET OrderQty = 4
WHERE ProductID = 448
AND PurchaseOrderID = 1255;   </inputbuf>
  </process>
  <process id="process47b01dc38" taskpriority="0" logused="9484" waitresource="KEY: 9:72057594046906368 (4bc08edebc6b)" waittime="11015" ownerId="2844375" transactionname="user_transaction" lasttranstarted="2012-06-19T10:55:11.143" XDES="0x46f5d16a8" lockMode="U" schedulerid="2" kpid="3816" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-06-19T10:55:11.140" lastbatchcompleted="2012-06-19T10:55:11.140" lastattention="2012-06-19T10:43:34.333" clientapp="Microsoft SQL Server Management Studio - Query" hostname="RED1" hostpid="1704" loginname="NEVERNEVER\grant" isolationlevel="read committed (2)" xactid="2844375" currentdb="9" lockTimeout="4294967295" clientoption1="673327200" clientoption2="390200">
   <executionStack>
    <frame procname="AdventureWorks2012.Purchasing.uPurchaseOrderDetail" line="39" stmtstart="2732" stmtend="3864" sqlhandle="0x0300090004cc856a0d55da0014a0000000000000000000000000000000000000000000000000000000000000">
UPDATE [Purchasing].[PurchaseOrderHeader]
            SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = 
                (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])
                    FROM [Purchasing].[PurchaseOrderDetail]
                    WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] 
                        = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])
            WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] 
                IN (SELECT inserted.[PurchaseOrderID] FROM inserted);    </frame>
    <frame procname="AdventureWorks2012.dbo.DL2e" line="5" stmtstart="126" sqlhandle="0x03000900d7ef9050a0f3af0075a0000001000000000000000000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="adhoc" line="1" sqlhandle="0x01000900fe00e208603d10350400000000000000000000000000000000000000000000000000000000000000">
EXEC dl2e    </frame>
   </executionStack>
   <inputbuf>
EXEC dl2e   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594046840832" dbid="9" objectname="AdventureWorks2012.Purchasing.PurchaseOrderDetail" indexname="1" id="lock46b6dab00" mode="X" associatedObjectId="72057594046840832">
   <owner-list>
    <owner id="process47b01dc38" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process472310928" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594046906368" dbid="9" objectname="AdventureWorks2012.Purchasing.PurchaseOrderHeader" indexname="1" id="lock46746c300" mode="X" associatedObjectId="72057594046906368">
   <owner-list>
    <owner id="process472310928" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process47b01dc38" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

Note, the statement offset is crazy, but that’s because it’s coming from an encrypted stored procedure. But the short answer is, yes you can (at least in this test).