Update Statement on one table takes locks on other tables

Question

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?

Answer 1

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

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.