Update Statement on one table takes locks on other tables Given the following statement on the AdventureWorks database:

UPDATE Sales.SalesOrderDetail SET OrderQty = 100 WHERE ModifiedDate &gt;= '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:

   &lt;Object name="Person" schema_name="Person"&gt;
      &lt;Locks&gt;
        &lt;Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" /&gt;
      &lt;/Locks&gt;
    &lt;/Object&gt;
    &lt;Object name="SalesOrderDetail" schema_name="Sales"&gt;
      &lt;Locks&gt;
        &lt;Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" /&gt;
      &lt;/Locks&gt;
    &lt;/Object&gt;
    &lt;Object name="SalesOrderHeader" schema_name="Sales"&gt;
      &lt;Locks&gt;
        &lt;Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" /&gt;
      &lt;/Locks&gt;
    &lt;/Object&gt;


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