0

I execute several SQL Transactions from my ASP.NET application. One transaction executes several stored procedures one after another. Transactions are quite long running ones.

When two instances of my application i.e. two different processes are connecting to one database, I sometimes run into SQL deadlock issue. In that case SQL server automatically rolls back a transaction from any one process.

When this happens, I want to execute all commands (stored procedures) in the SqlTransaction which was rolled back.

How can I do this in C#?

Is there easy way to re-execute all the commands in a SQL transaction which were executed before the transaction was rolled back by the SQL server?

Or do I have to write my own logic to "remember" (and execute them once again whenever required) all the stored procedures I had executed?

Learner
  • 4,661
  • 9
  • 56
  • 102

1 Answers1

1

Actually its a bad practice to do so since as a dev you need to find what was the issue for rollback and fix that even so i would say declare a flag of int and increment it after each sp executions when something goes wrong in catch section where you have wrote the rollback just call the sps according to the flag. Again thats a wrong practice to do so

RAHUL S R
  • 1,569
  • 1
  • 11
  • 20
  • 1
    In principle, you are correct. But sometimes you have to write some defensive code like this, especially when you run into the SQL deadlock like issues and you cannot do necessary database level changes anytime soon due to various technical & non-technical reasons. – Learner Mar 22 '17 at 04:58
  • yeah thats true but to avoid deadlocks you need to configure your database w r t your needs setting isolation levels setting locks accordingly may avoid situations like these – RAHUL S R Mar 22 '17 at 05:14