Queries for HOLDLOCK Table Hint

1. Ensuring Consistent Financial Aggregation for an Audit Report (Ensure that transaction is used and committed succesfully)

SQL Server Query 1

            
 BEGIN TRANSACTION;  

   

 -- Step 1: Read all order details for a specific period, holding locks.  

 -- This ensures no new order details are inserted for these orders,  

 -- and no existing ones are modified or deleted, for the duration of this transaction.  

 SELECT  

     OD.ProductID,  

     SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS PeriodSales  

 INTO #TempProductPeriodSales -- Store in temp table for further processing  

 FROM [Order Details] AS OD WITH (HOLDLOCK) -- Hold locks on order details  

 JOIN Orders AS O WITH (HOLDLOCK)           -- Hold locks on orders  

     ON OD.OrderID = O.OrderID  

 WHERE O.OrderDate BETWEEN '1997-01-01' AND '1997-01-31'  

 GROUP BY OD.ProductID;  

   

 -- Step 2: Now, use the consistent data from the temp table for final reporting  

 -- The data in #TempProductPeriodSales is guaranteed to be consistent with the initial read.  

 SELECT  

     P.ProductName,  

     TPS.PeriodSales  

 FROM Products AS P  

 JOIN #TempProductPeriodSales AS TPS ON P.ProductID = TPS.ProductID  

 WHERE TPS.PeriodSales > 1000  

 ORDER BY TPS.PeriodSales DESC;  

   

 -- Clean up temp table  

 DROP TABLE #TempProductPeriodSales;  

   

 COMMIT TRANSACTION;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Column("OD.ProductID")  

     .Column(new SUM(new ColumnArithmatic("OD.Quantity").MULTIPLY("OD.UnitPrice").MULTIPLY()  

         .StartBracket(1).SUBTRACT("OD.Discount").EndBracket()), "PeriodSales")  

     .INTO(new Table("#TempProductPeriodSales"))  

     .From("[Order Details]", "OD", new List<IHint>() { new HOLDLOCK() })  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName("Orders", "O", new List<IHint>() { new HOLDLOCK() })  

             .On(new Column("OD.OrderID").Equale(new Column("O.OrderID")))  

     })  

     .Where(new Where(new BETWEEN(new Column("O.OrderDate"), "1997-01-01", "1997-01-31")))  

     .GroupBy(new GroupBy("OD.ProductID"))  

     .Select()  

     .Columns("P.ProductName", "TPS.PeriodSales")  

     .From("Products","P")  

     .Join(new List<IJoin>()   

     {   

         new INNERJOIN().TableName("#TempProductPeriodSales", "TPS")  

             .On(new Column("P.ProductID").Equale(new Column("TPS.ProductID")))  

     })  

     .Where(new Where(new Column("TPS.PeriodSales").GreaterThan(1000)))  

     .OrderBy(new OrderBy().SetColumnDescending("TPS.PeriodSales"))  

     .DropTable(new Table("#TempProductPeriodSales"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT OD.ProductID,
       SUM(OD.Quantity * OD.UnitPrice * (@pMAIN_2507200147312975520 - OD.Discount)) AS PeriodSales
INTO #TempProductPeriodSales
FROM [Order Details] AS OD WITH (HOLDLOCK)
     INNER JOIN
     Orders AS O WITH (HOLDLOCK)
     ON OD.OrderID = O.OrderID
WHERE O.OrderDate BETWEEN @pMAIN_2507200147312975521 AND @pMAIN_2507200147312975522
GROUP BY OD.ProductID;

SELECT P.ProductName,
       TPS.PeriodSales
FROM Products AS P
     INNER JOIN
     #TempProductPeriodSales AS TPS
     ON P.ProductID = TPS.ProductID
WHERE TPS.PeriodSales > @pMAIN_2507200147312975523
ORDER BY TPS.PeriodSales DESC;

DROP TABLE #TempProductPeriodSales;


            
        

Parameters (If used)

Query Results 1:

2. Ensuring Data Integrity Before a Complex Update Operation (Ensure that transaction is used and committed succesfully)

SQL Server Query 2

            
 BEGIN TRANSACTION;  

 -- Step 1: Identify employees with high freight orders, holding locks to ensure consistency.  

 -- This prevents concurrent updates to Order Freight or new orders for these employees  

 -- until this transaction completes.  

 SELECT DISTINCT  

     E.EmployeeID,  

     E.FirstName,  

     E.LastName  

 INTO #TempHighFreightEmployees  

 FROM Employees AS E WITH (HOLDLOCK) -- Hold locks on employees (if relevant for your EmployeeID filtering)  

 JOIN Orders AS O WITH (HOLDLOCK)     -- Hold locks on orders  

     ON E.EmployeeID = O.EmployeeID  

 GROUP BY E.EmployeeID, E.FirstName, E.LastName  

 HAVING SUM(O.Freight) > 10000;  

 -- Step 2: Now, use the consistent set of EmployeeIDs for a subsequent operation.  

 -- This update will only use the EmployeeIDs identified in Step 1,  

 -- and you are guaranteed their high-freight status was accurate at the time of Step 1.  

 -- (Assuming a hypothetical EmployeePerformance table)  

 -- INSERT INTO EmployeePerformance (EmployeeID, Metric, Value)  

 -- SELECT EmployeeID, 'HighFreightOrders', 1 FROM #TempHighFreightEmployees;  

 -- For demonstration, just select them again  

 SELECT * FROM #TempHighFreightEmployees;  

 DROP TABLE #TempHighFreightEmployees;  

 COMMIT TRANSACTION;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select().Distinct()  

     .Columns("E.EmployeeID","E.FirstName","E.LastName")  

     .INTO(new Table("#TempHighFreightEmployees"))  

     .From("Employees","E", new List<IHint>() { new HOLDLOCK() })  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("Orders","O", new List<IHint>() { new HOLDLOCK() })  

             .On(new Column("E.EmployeeID").Equale(new Column("O.EmployeeID")))  

     })                  

     .GroupBy(new GroupBy("E.EmployeeID","E.FirstName","E.LastName"))  

     .Having(new Having(new SUM(new Column("O.Freight")).GreaterThan(10000)))  

     .Select().Star().From(new Table("#TempHighFreightEmployees"))  

     .DropTable(new Table("#TempHighFreightEmployees"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT DISTINCT E.EmployeeID,
                E.FirstName,
                E.LastName
INTO #TempHighFreightEmployees
FROM Employees AS E WITH (HOLDLOCK)
     INNER JOIN
     Orders AS O WITH (HOLDLOCK)
     ON E.EmployeeID = O.EmployeeID
GROUP BY E.EmployeeID, E.FirstName, E.LastName
HAVING SUM(O.Freight) > @pMAIN_2507200147313138230;

SELECT *
FROM #TempHighFreightEmployees;

DROP TABLE #TempHighFreightEmployees;


            
        

Parameters (If used)

Query Results 2: