Post reply

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

  • 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:-)

  • May 7, 2013 at 12:24 am

    #1612862

    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

  • May 7, 2013 at 12:26 am

    #1612863

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

  • May 7, 2013 at 12:57 am

    #1612868

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

  • May 7, 2013 at 6:59 am

    #1612959

    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

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

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