0

I have software that has to write "packets" of goods to a database. It must write either all of them, or none, in case of an error. To do this we use transactions. Begin transactions, N-hundred inserts, commit transaction. So far so good.

Now there are two different scenarios:

  1. there is a bug in a query for whatever reason or the database stops responding while inserting: the transaction is open, and requires a rollback.

  2. the entire software crashes during the insert, and the transaction remains open.

In these cases I need to be able to recover. I was thinking to simply kill all rows that are returned by this query:

SELECT spid FROM sys.sysprocesses WHERE open_tran > 0;

But this kills the connection if the situation is the first one above. And a Rollback does not work if it the situation 2 above.

what would be the proper way to recover from the above situations, and be sure that there are no open transactions?

Dale K
  • 25,246
  • 15
  • 42
  • 71
sharkyenergy
  • 3,842
  • 10
  • 46
  • 97
  • 1
    If your session uses `set xact_abort on` it will rollback on app crash. The topic is too big to answer in a single question, but there are a large number of articles available which will explain the intricacies of error handling and transactions in SQL Server. – Dale K May 25 '21 at 06:26
  • @DaleK thank you, should i simply add that to the query before the `begin transaction`? what is the criteria for the rollback? is there a timeout or something like that? – sharkyenergy May 25 '21 at 06:29
  • I'll point you to the official docs for that info, – Dale K May 25 '21 at 06:33
  • 3
    This is the bible of error handling in SQL Server: https://www.sommarskog.se/error-handling-I.html – Dale K May 25 '21 at 06:34
  • 3
    @DaleK I think that's the old one - the newer one https://www.sommarskog.se/error_handling/Part1.html#jumpgeneralpattern is more up-to-date and the link there goes straight to a template for TRY/CATCH and TRANSACTION blocks – seanb May 25 '21 at 06:54
  • THank you! that workded great. Dalek, could you please post it as answer so i can accept it? – sharkyenergy May 25 '21 at 07:51

0 Answers0