Queries for SERIALIZABLE Table Hint

1. Usage of SERIALIZABLE table hint (to be used in transaction)

SQL Server Query 1

            
 -- Window 1: Comprehensive Report Generation with SERIALIZABLE Consistency  

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Base level, hint specific tables  

 BEGIN TRANSACTION;  

   

 -- Step 1: Get initial product list and category names  

 SELECT  

     P.ProductName,  

     C.CategoryName  

 INTO #InitialProductCategorySnapshot  

 FROM Products AS P WITH (SERIALIZABLE) -- Holds SERIALIZABLE locks on Products  

 JOIN Categories AS C WITH (SERIALIZABLE) -- Holds SERIALIZABLE locks on Categories  

     ON P.CategoryID = C.CategoryID;  

   

 -- Step 2: Aggregate sales by category, guaranteeing no new products/categories appeared  

 SELECT  

     C.CategoryName,  

     SUM(OD.Quantity * OD.UnitPrice) AS TotalSales  

 FROM Categories AS C WITH (SERIALIZABLE) -- Locks still held/re-acquired for this read  

 JOIN Products AS P WITH (SERIALIZABLE) ON C.CategoryID = P.CategoryID  

 JOIN [Order Details] AS OD ON P.ProductID = OD.ProductID  

 GROUP BY C.CategoryName  

 ORDER BY TotalSales DESC;  

   

 DROP TABLE #InitialProductCategorySnapshot;  

 COMMIT TRANSACTION;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("P.ProductName","C.CategoryName")  

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

     .From("Products", "P", new List<IHint>() { new SERIALIZABLE() })  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("Categories","C", new List<IHint>() { new SERIALIZABLE() })  

             .On(new Column("P.CategoryID").Equale(new Column("C.CategoryID")))  

     })  

     .Select()  

     .Column("C.CategoryName")  

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

     .From("Categories", "C", new List<IHint>() { new SERIALIZABLE() })  

     .Join(new List<IJoin>()   

     {  

         new INNERJOIN().TableName("Products", "P", new List<IHint>() { new SERIALIZABLE() })  

             .On(new Column("C.CategoryID").Equale(new Column("P.CategoryID"))),  

         new INNERJOIN().TableName("[Order Details]","OD")  

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

     })  

     .GroupBy(new GroupBy("C.CategoryName"))  

     .OrderBy(new OrderBy().SetColumnAscending("TotalSales"))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT P.ProductName,
       C.CategoryName
INTO #InitialProductCategorySnapshot
FROM Products AS P WITH (SERIALIZABLE)
     INNER JOIN
     Categories AS C WITH (SERIALIZABLE)
     ON P.CategoryID = C.CategoryID;

SELECT C.CategoryName,
       SUM(OD.Quantity * OD.UnitPrice) AS TotalSales
FROM Categories AS C WITH (SERIALIZABLE)
     INNER JOIN
     Products AS P WITH (SERIALIZABLE)
     ON C.CategoryID = P.CategoryID
     INNER JOIN
     [Order Details] AS OD
     ON P.ProductID = OD.ProductID
GROUP BY C.CategoryName
ORDER BY TotalSales ASC;

DROP TABLE #InitialProductCategorySnapshot;


            
        

Parameters (If used)

Query Results 1: