SQL Server Locking – Update Statement on One Table Takes Locks on Other Tables

concurrencylockingsql serversql server 2014

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?

Best Answer

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.

Related Question