Tutorial :Updating Aggregate Column In Isolation Question



Question:

I have a deal and transaction table, where the deal table has an aggregated column, 'TotalSales', which is simply a count of transactions per deal.

With the following command, is there ever a chance the transaction row count and total sales count would not be in sync (not including deleting rows)? Does the default isolation level work for this? We have many users making purchases at potentially the same time, so I'm trying to figure out the best way to handle this.

BEGIN TRANSACTION  INSERT INTO [transaction] ...     UPDATE deal  SET TotalSales = (select count(*) from [transaction] where dealid = @dealId)  WHERE dealId = @dealId   COMMIT TRANSACTION  


Solution:1

My perference for things like these is to use a view:

CREATE VIEW vw_sales AS  AS          SELECT t.dealid,                      COUNT(*) AS total_sales            FROM TRANSACTION t    GROUP BY t.dealid  

Less hassle involved with keeping the value in sync, redundant data is not stored...

It's just as susceptible to isolation level issues - the default is to read committed data. Reading uncommitted data for total sales would be Bad(tm).


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »