do I need locking or what's the best way to do this ?

Question

do I need locking or what's the best way to do this ?

aykut canturk

SSC Eights!

Points: 870
More actions
May 7, 2013 at 12:20 am

#273701

dear friends,
I need to implement a code in my application that updates monthly revenues when any transaction occurs. meaning:
after this statement executes:
INSERT INTO Transactions (amount, transactiondate, companyID) VALUES (3.5, transactiondate, SomeCompanyID)
the following code should run:
IF EXISTS( SELECT * FROM Revenues WHERE companyID=SomeCompanyID AND month = DATEPART(mm,transactiondate) )
-- update revenue table
UPDATE Revenues SET Revenue = Revenue + amount WHERE companyID=SomeCompanyID AND month = DATEPART(mm,transactiondate)
else
INSERT INTO Revenues (Revenue, Month, CaompanyID) VALUES (amount, DATEPART(mm,transactiondate) , SomeCompanyID)
end
As far I I see I can do that in sp or in my aspnet application with code. my first question is which one should I choose and second one is if I do that in code side, should I need some kind of tablelock. Please note its very likely that there can be multiple transaction at exactly same time as there is heavy usage. What I'm afraid of is right after EXISTS statement runs, some other process inserts a new record just before UPDATE command is executed. is that possible ? can this worst case scenario happens if I use sp ? it can obviously happen if I use aspnet code. Am I right ?
what do you suggest ?
have a wonderful day:-)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Have you considered using MERGE rather than an insert/update pair?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aykut canturk SSC Eights! Points: 870 More actions · Answer 2

I have no knowledge about MERGE I'll google it. meantime, if you write down some sample 😀

aykut canturk SSC Eights! Points: 870 More actions · Answer 3

I think this is the code.

merge into revenues as R

using (

select companyid, amount, trndate from transactions where ID=2

) T

ON (R.companyid = T.companyid and r.trnmonth = DATEPART(mm,T.trndate) )

WHEN MATCHED THEN

UPDATE SET Revenue = r.Revenue + t.amount

WHEN NOT MATCHED THEN

INSERT (Revenue, trnmonth, companyid) VALUES (T.amount, DATEPART(mm,T.trndate) , T.companyid) ;

as I understand, in my aspnet code I'll insert transaction and execute the sql code above and the ID number in select statement in second line will be changed with the Id number of record I just inserted.

thanks...

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

1) install a copy of SQL 2012 Books Online. It is an INCREDIBLE, FREE resource. In this case it is really perfect for you because it has a TON of examples about MERGE and what you can do with it. It is a VERY powerful construct.

2) If you do not go with MERGE, you need VERY strict controls of locks and transactioning or you run the risk of getting bad data in your system through concurrent access. There is a finite, non-zero amount of time between an EXISTS check and the next action (or vice-versa if you code it the other way, which is possible) where another spid can grab "your" value/row if you don't have things "locked down". I have seen clients get into HUGE messes from this type of action due to the generation of duplicate key values such as an OrderID or InvoiceID, etc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service