0

I am seeing some strange behavior which has occurred on random basis. Here's what my store procedure basically does.

begin try
        begin tran
        insert into table1
        update table2
        insert into table3
        commit tran
end try

begin catch
        rollback tran
end catch

For most of the time above code works fine except once in a while(once per day or two) when some error occurs, the transaction does not rollback the changes from all 3 tables.

   begin try
            begin tran
            insert into table1----Rollback doesn't happen
            update table2--Rollback happens 
            insert into table3--Rollback happens 
            commit tran
    end try

    begin catch
            rollback tran
    end catch

Can anyone please can suggest something where I might be wrong or do I need to handle transaction in some another way?

Thanks in advance.

AdamL
  • 12,421
  • 5
  • 50
  • 74
Vinay
  • 3
  • 6
  • This isn't possible. Your analysis must be wrong. The only way a transaction would be partially rolled back would be if you were rolling back to a savepoint deliberately. – Martin Smith Jan 24 '14 at 13:29
  • How have you determined that the rollback is/isn't happening in the way you describe. What information/logs, etc do you have that you believe points to this being the problem? – Damien_The_Unbeliever Jan 24 '14 at 13:29
  • 1
    You said that for `update table2` and `insert into table3` rollback happens. It's not necessarily true. If the error occurs on `insert into table1`, execution jump right away to `catch` block. So it's not "rollback happens", rather "operation never performend". – AdamL Jan 24 '14 at 13:31
  • It happened to me that an error rolled back the transaction but the batch continued to execute, now without transaction. I do not fully understand the exact conditions under which this can happen. Might be the case here. – usr Jan 24 '14 at 13:49
  • @frikozoid in that case what I am not able to understand is why I still see the record inserted in table1 and not in other tables. Hence I assumed that rollback happened for table2 and table3 whereas not for table1. – Vinay Jan 25 '14 at 04:46
  • @Damien_The_Unbeliever : On checking the database, I can see that sometimes a record does exist in table1 but not in table2 and table3. Which could happen only if the rollback didnt work properly, right? – Vinay Jan 25 '14 at 04:48
  • @Martin Smith : I am not setting any savepoints – Vinay Jan 25 '14 at 04:49
  • Far more likely scenarios are a) that `WHERE` clauses attached to the second and third statements prevent them from adding rows in the first place, despite your expectations, b) that some other piece of code is affecting tables 2 and 3 afterwards, or c) that some other piece of code is just doing an insert into table 1. – Damien_The_Unbeliever Jan 25 '14 at 05:51

1 Answers1

0

Check your SET XACT_ABORT setting and what the error level inside the failing sql actually is

How to make SET XACT_ABORT ON rollback the transaction?

Community
  • 1
  • 1
Maslow
  • 18,464
  • 20
  • 106
  • 193