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
EmoticonEmoticon