Post reply

Rolling back already commited data..?

  • August 10, 2011 at 9:38 am

    #245953

    Got this email from one of the devs...

    Not sure if you can help with this? I’m writing some Unit Tests to verify the Workflow process. I initially have my Deposit Application wrapped in a Transaction. The problem is that at some points in the code, the Workflow will create a new Connection and Transaction and cannot read to update the uncommitted records in the initial Transaction.

    What I will most likely have to do is commit the “Test” Deposit Application so I can pass it to the Workflow process. The problem is that now I have to clean up the Workflow database to remove the “Test” Deposit Application. The only way I can think of is to simply delete the records for the “Test” Deposit Application from the database. Can you suggest any other ways that you might be aware of?

  • August 10, 2011 at 9:44 am

    #1366678

    restore the database to a point prior to when the developer entered the data? or delete the data as he surmised?

    that's the onyl two things i can think of.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 10, 2011 at 9:49 am

    #1366684

    Yeah I kinda figured

    thanks man

  • August 10, 2011 at 9:56 am

    #1366695

    If it's Enterprise or Developer edition, create a database snapshot before, revert to it after.

    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

  • August 10, 2011 at 10:00 am

    #1366698

    The "best" solution depends on how complicated the transaction is. If it's just writing records to a table, then his idea of deleting might be good enough. Or make a copy of the table before, then truncate & repopulate the table. If multiple tables are involved, with parent-child and dependencies, then maybe a restore would be better.

  • August 10, 2011 at 12:27 pm

    #1366812

    Thanks everyone..

  • August 10, 2011 at 2:30 pm

    #1366916

    We bounced this arround in the office and one option would be to allow the second process to READ UNCOMMITED transactions. We are assuming this is a controlled, test environment, and he is looking for particular records.

    Then the he can just roll back the uncommited trans at the end.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • August 10, 2011 at 2:45 pm

    #1366933

    intersting Leo....

    can you elaborate?

  • August 10, 2011 at 2:49 pm

    #1366940

    Leo.Miller (8/10/2011)

    We bounced this arround in the office and one option would be to allow the second process to READ UNCOMMITED transactions. We are assuming this is a controlled, test environment, and he is looking for particular records.

    Then the he can just roll back the uncommited trans at the end.

    Cheers

    Leo

    I was wondering about that too .... Never tried it so not sure how/if it would work.

  • August 10, 2011 at 3:07 pm

    #1366957

    krypto69 (8/10/2011)

    intersting Leo....

    can you elaborate?

    Yes.....

    But I assume you want more than this..:-)

    On one connection do the update - This was my test

    begin tran

    update Test1 set First_Name = 'Test1' where ID_Col = 10

    waitfor delay '00:00:20'

    rollback

    On another connection run the query but first set the Transaction Isolation Level.

    SET TRANSACTION ISOLATION LEVEL

    READ UNCOMMITTED

    select * from Test1 where ID_Col = 10

    waitfor delay '00:00:20'

    select * from Test1 where ID_Col = 10

    The first select result was as expected, 'Test1'. but after the 20 seconds the value was rolled back.

    As long as he can setup the second set of queries to set the TIL this should work.

    Actually it's quite a sweet solution. I wish I'd thought of it myself.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • August 10, 2011 at 3:12 pm

    #1366967

    Another solution would be to set all the queries to use WITH (NOLOCK)

    select * from Test1 with (nolock) where ID_Col = 10

    But I would consider this a bit of a dirty solution, and if there are a lot of queries it may take a while to update them all.

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • August 10, 2011 at 3:16 pm

    #1366968

    Leo.Miller (8/10/2011)

    Another solution would be to set all the queries to use WITH (NOLOCK)

    select * from Test1 with (nolock) where ID_Col = 10

    But I would consider this a bit of a dirty solution, and if there are a lot of queries it may take a while to update them all.

    Leo

    How is READ UNCOMMITED any less dirty than NOLOCK? Aside from the less code to change point.

  • August 10, 2011 at 3:26 pm

    #1366975

    Ninja's_RGR'us (8/10/2011)

    Leo.Miller (8/10/2011)

    Another solution would be to set all the queries to use WITH (NOLOCK)

    select * from Test1 with (nolock) where ID_Col = 10

    But I would consider this a bit of a dirty solution, and if there are a lot of queries it may take a while to update them all.

    Leo

    How is READ UNCOMMITED any less dirty than NOLOCK? Aside from the less code to change point.

    Personal opinion only, hence my wording.

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • August 10, 2011 at 3:40 pm

    #1366982

    Ah! Ok, tx.

  • August 11, 2011 at 7:19 am

    #1367271

    Awesome!

    thanks Leo and please say thank you to everyone that helped...

    good karma to you...

Viewing 15 posts - 1 through 14 (of 14 total)

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