CODEWITHSUNDEEP
c#sql-servert-sql
user3007447
user3007447

Reputation: 400

What happens when you commit a transaction after rollback?

Let’s say I have a C# class that creates a transaction for the database and then calls a stored procedure. The stored procedure returns an int = 1 if there’s an error. And let’s say that we update a table but then return 1 because an error happened somewhere in the SP.

This then throws an exception in c#, which calls a method to rollback the transaction — but in the FINALLY block of the method, we commit the transaction.

Since the transaction was rolled back, what happens when we then call commit?

Upvotes: 2

Views: 5673

Answers (2)

Prakash Mhasavekar
Prakash Mhasavekar

Reputation: 592

I would like to suggest you to use transactions in your stored procedure too along with try catch block.

I have confirmed that after rollback we cannot commit the same transaction.

Make sure another transaction is not in waiting, else it will be committed.

Also don't do anything with transaction in finally block. Always use finally block to close connection , data reader etc.

Always commit in try block and do rollback in catch block.

Upvotes: 3

Michał Turczyn
Michał Turczyn

Reputation: 37337

If you rollback the transaction, all changes made in that transactions are just... rolled back, cancelled. So your commit in finally block won't do anything, at least when you have no other transactions waiting.

I wouldn't place commit method in finally block, I'd commit the transaction at the end of try block and rollback in catch block.

Upvotes: 8

Related Questions