I'm trying to understand outsystems transaction error handling so a test was performed to see what would happen when an error occurs.

In this scenario the email is mis-configured : the developer has forgotten or incorrectly configured the email server settings under Service Center and has attempted to submit a form 6 times to create a USER_MASTER record along with other sequential transactions.

After the email settings was properly configured under Service Center, the form was submitted again and on this 7th attempt everything worked perfectly. However, strangely enough the new USER_MASTER record created has an ID of 8. 

USER_MASTER ID 1 belongs to "Administrator" but why did outsystems create the new USER_MASTER record with an ID of 8? not ID 2? or ID 7? (1+6)  it seems that outsystems actually created the record at first but on the first sign it detects an error, outsystem stops the transaction (does not proceed any further) and then automatically deletes all transactions right before the error occurred, removing any transaction records created on error. 

On the 7th transaction attempt outsystems detected that the transaction was successful so it left that new created record alone!

Does it always do this? (delete records on the first sign of error)

(It was actually strange to see outsystems do this on a misconfiguration email setting error, but personally i think this feature is good.)

Hi Robert,

That is the way transactions are handled in SQL Server.

"If you think about it, the auto-increment number should not be transactional. If other transactions had to wait to see if the auto-number was going to be used or "rolled back", they would be blocked by the existing transaction using the auto-number."
in http://stackoverflow.com/questions/282451/sql-identity-autonumber-is-incremented-even-with-a-transaction-rollback


Cheers,
Tiago Simões

Hi Robert,

What is important to understand is that what is happening in you application is the expected transactional behavior in the database:

1. Database transaction begins;
2. You applications inserts a USER_MASTER record in the database
   • The auto-number ID is increment - as Tiago referred below, not in your transaction scope; 
3. An error is thrown by your application
4. If you do not have an error handler, or your error handler has Abort Transaction set to Yes, your transaction is Aborted (all changes done to the database in the scope of your transaction are rolled-back, i.e. your USER_MASTER will not be in the database)

Conclusion, you will have the ID incremented, but the User record will not be inserted. Please be aware that the fact the trasaction was aborted is not a consequence of the fact that an exception was raised because of the misconfiguration, it is a consequence of the fact that you Aborted your database transaction in the error handler (or did not have any error handler - in this case, the default behavior is aborting the transaction). See below how you set an error handler to Abort Transactions.



I would also like to point-out that there is an excellent post Transactions in Hub Edition written by Rodrigo Coutinho on how transactions are managed by the OutSystems platform.

Best Regards,

Daniel Lourenço
OutSystems

Thanks Tiago and Daniel

Daniel: "Conclusion, you will have the ID incremented, but the User record will not be inserted. Please be aware that the fact the trasaction was aborted is not a consequence of the fact that an exception was raised because of the misconfiguration, it is a consequence of the fact that you Aborted your database transaction in the error handler"

After reading your comment and viewing your diagram, it makes sense! In the screen action "All Exceptions: Abort transaction" properties has been set to yes, so a misconfiguration of email setting rises an exception error, then the transaction is aborted and SQL Server rolled back the transaction.

Also thanks for the link reference to Rodrigo thread....

----------
(Rodrigo Sousa Coutinho)  "If everything goes well the transaction is committed, if there is an unhandled exception the transaction is rolled back." 

As confirmed above. 

(Rodrigo Sousa Coutinho) "You can also avoid rollbacks when exceptions occur by creating an error handler for the exception and setting the "Abort Transaction" property to false (the default value)." 

Interesting....