Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

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).

Comments

Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...