PetaPoco transaction not rolling back

Question

Maybe this is a really noob question since I'm fairly new to handling transactions with PetaPoco. Problem I'm facing using PetaPoco as a microORM to handle my db transaction is that if I throw an exception just before the .Complete() method of the transaction, everything is rolled back correctly but if I'm catching exceptions inside the

Using scope As PetaPoco.Transaction = db.GetTransaction()
    ' try/catch here and if the db command fails transaction won't roll back

    scope.Complete()
End Using

the transaction won't roll back if one of the db operations fails. How can I solve this?

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Dec 22 '13 at 19:09
I'm not familiar with PetaPoco transaction. However, looking quickly at PetaPoco's code, transaction management seems funny and - frankly - useless in the Microsoft environment. Why don't you just surround your transactional code with a standard TransactionScope using statement (like this: http://msdn.microsoft.com/library/ee818746.aspx) it should work just fine with most standard databases. — Simon Mourier, Dec 26 '13 at 10:02
Thank you Simon, I think what petapoco uses under the hood is this object infact even with petapoco you cannot roll back the transaction "manually" but it works like "If you want to rollback a transaction, you should not call the Complete method within the transaction scope. For example, you can throw an exception within the scope. The transaction in which it participates in will be rolled back.". But for some reason I tried to not call the .Compete() but the first db query succeded the same. I will give a shot to the plain .NET method by the way and report back here. — Manight, Dec 26 '13 at 14:10
See following question (http://stackoverflow.com/questions/12157341/sqlclient-sqltransaction-vs-system-transactions) and article it points to. Using System.Transactions in single db environments has some problems therefore many orm, db helpers tools implement their own transaction strategies. — Atilla Ozgur, Dec 31 '13 at 00:18
That's interesting... I'll stick to PetaPoco system for now. Thanks Atilla — Manight, Dec 31 '13 at 11:45

score 2 · Accepted Answer · answered Dec 26 '13 at 16:22

The issue was me not handling correctly the "call/not call" the scope.Complete() based on Exceptions intercepted along the path. In particulart I had a boolean flag "rollBackTransaction" starting to false and then updating to true if any of the try/catch block inside the transaction raised and exception. At the end I just checked it:

                    If Not rollBackTransaction Then
                        scope.Complete()
                    End If

This can be used as well for the TransactionScope suggested by Simon wich will eventually roll back a transaction if .Complete() is not called before closing the Using block.

Now what was causing a false flag and thus calling the scope.Complete() method each time, was that inside the transaction I called a sub wich had it's own exception handling and thus would never raise an exception in the main transaction block to correctly update the "rollBackTransaction" flag.

What I learned is that if you are using try/catch inside the transaction, be very sure that the external methods you call raise an exception if they fall, and update a flag all along based on wich you will call the scope.Complete().

Anyway Simon, thanks for pointing out that .NET feature I didn't know wich seems to be extremely useful! Wonder what else it will include in the transaction... file system changes?

PetaPoco transaction not rolling back

1 Answers1