Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Rollback timeout issue with large data transactions?

I am using simple .net transaction (C# code using ado.net data reader and stored procedure) to delete some records I provided 30 seconds’ transaction timeout period.

But my stored procedure takes more than 30 seconds in large records case, I am using try – catch block to handle transaction.

In large data case when transaction rollbacks, rollback process again facing timeout because rollback process taking more than 30 seconds due to complex data table relations (multiple foreign keys constraints)

Doubts on situation,

1.  How to handle this new rollback timeout issue without increasing transaction main timeout (30 seconds), we need to wait till full rollback completes.
2.  In rollback timeout situation database is in complete blocked situation I am not able to execute SP_Who2 also.

These are two main issues, other minor issues are,

1.  In above situation, what happens to executed SP after timeout is it still in running state or paused.
2.  How we can minimize transaction rollback time?

 

Please provide your valuable solution with .net and SQL server