0

Given the following statement on the AdventureWorks database:

UPDATE Sales.SalesOrderDetail SET OrderQty = 100 WHERE ModifiedDate >= '2014-01-01'

If I open another query window and look at the locks the query is using by running

sp_WhoIsActive @get_locks =1

i can see locks as follows:

   <Object name="Person" schema_name="Person">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="SalesOrderDetail" schema_name="Sales">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="SalesOrderHeader" schema_name="Sales">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>

I was expecting the table lock on SalesOrderDetail however, I don't understand why the table lock has been taken on the Person Table and the SalesOrderHeader table

I can confirm the locks are present by trying to SELECT * from one of the tables which is then blocked.

Why are tables that are not being updated locked by the update statement?

2

The tables are being locked due to the trigger: [iduSalesOrderDetail] on [Sales].[SalesOrderDetail]

enter image description here

The trigger launches if any of these actions occur:

IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) OR UPDATE([UnitPriceDiscount])

And UPDATE([OrderQty]) is one of these actions.

This trigger will then update Person.Person two times.

One of these Update statements:

  UPDATE [Person].[Person] 
            SET [Demographics].modify('declare default element namespace 
                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
                replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 
                with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")') 
            FROM inserted 
                INNER JOIN [Sales].[SalesOrderHeader] AS SOH
                ON inserted.[SalesOrderID] = SOH.[SalesOrderID] 
                INNER JOIN [Sales].[Customer] AS C
                ON SOH.[CustomerID] = C.[CustomerID]
            WHERE C.[PersonID] = [Person].[Person].[BusinessEntityID];

And also Sales.SalesOrderHeader

 UPDATE [Sales].[SalesOrderHeader]
        SET [Sales].[SalesOrderHeader].[SubTotal] = 
            (SELECT SUM([Sales].[SalesOrderDetail].[LineTotal])
                FROM [Sales].[SalesOrderDetail]
                WHERE [Sales].[SalesOrderHeader].[SalesOrderID] = [Sales].[SalesOrderDetail].[SalesOrderID])
        WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted);

Explaining the Exclusive locks on these objects.

  • 1
    I looked in keys and constraints to try to find refererences to the other tables and couldn't find any.....I should have kept looking a bit further down the object explorer :) Thanks - makes perfect sense – SEarle1986 Mar 21 at 23:40
  • @SEarle1986 That would have also been a possibility! What helped me was remembering that AdventureWorks has some crazy triggers :). – Randi Vertongen Mar 21 at 23:42
  • 1
    Another good way to track these down is to turn on actual execution plan for the query. That innocent looking UPDATE turned out to be 12 different queries! – addp009 Mar 21 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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