Locking and blocking lifetime on UNION queries on MS SQL Server



  • Can anyone shed any light on how locking works on UNION queries? Say I have the below query and the Archive tables take 1 min to run while the non-archive tables take 5s. Are any locks taken on the non-archive tables held until the whole query executes?

    SELECT
        COH.OrderNumber,
        COH.OrderType,
        COH.OrderDate,
        COL.Product,
        COH.Customer,
        SUM(COL.Quantity) Quantity,
        SUM(COL.Quantity* COL.Price) Ext
    FROM
    
    CustomerOrderHeaders COH
    
    JOIN CustomerOrderLines COL ON COH.OrderNumber = COL.OrderNumber
    

    GROUP BY

    COH.OrderNumber,
    COH.OrderType,
    COH.OrderDate,
    COL.Product,
    COH.Customer
    

    UNION
    ALL
    SELECT

    COH.OrderNumber,
    COH.OrderType,
    COH.OrderDate,
    COL.Product,
    COH.Customer,
    SUM(COL.Quantity) Quantity,
    SUM(COL.Quantity * COL.Price) Ext
    

    FROM

    CustomerOrderHeadersArchive COH
    
    JOIN CustomerOrderLinesArchive COL ON COH.OrderNumber = COL.OrderNumber
    

    GROUP BY

    COH.OrderNumber,
    COH.OrderType,
    COH.OrderDate,
    COL.Product,
    COH.Customer
    



  • Locks are taken to preserve transaction isolation as described on the https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#Basics doc. If the release of the lock doesn't hurt the ACID properties, there's no reason for SQL Server to keep a resource locked and, in fact, the sooner the lock is released the better for good performance.

    If you see the https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#lock_modes , you'll find that the query from your question is most likely taking https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#shared :

    Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction. [emphasis added]

    So no, the fact that your second query takes longer to run doesn't mean it causes the tables on the first one to be held util the end of the whole UNION. Microsoft has a doc on https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb677357(v=sql.105)?redirectedfrom=MSDN that uses Extended Events to exhibit the locks (acquired and released). I used the proposed session on a AdventureWorks2016 database to check the following query:

    SELECT ShoppingCartID, ModifiedDate
    FROM Sales.ShoppingCartItem
    

    UNION

    SELECT SalesOrderDetailID, ModifiedDate
    FROM Sales.SalesOrderDetail

    Live data

    That query is similar to yours and you'll be able to see locks being acquired and released interchangeably, instead of only acquired at the beginning and released at the end.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2