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

Answer 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).