Skip to Content (Press Enter)
UserImage.jpg
Ezra
274Views
5Comments
Solved
Rollback all previous records when the n-th record raise an exceptions.
Question
Reactive
Database
Oracle
OutSystems 11
Development
Application Type
Reactive
Service Studio Version
11.14.16 (Build 60354)

Hi,

We have a batch process for example 1 process need to iterate 300 hundreds records to Insert / Update a few entities into DB. But there is a problem, we want to Rollback the previous insertion / update that had been done.

For my scenario, I have 3 modules.

  • Modules A which have 300 records.
  • Modules B where the new records created.
  • Modules C to generate new Bill number.

Module A consume Module B

Module B consume Module C


Here the step for the batch process.

  1. First we fetch the 300 records.
  2. Next, using for-each loop, we iterate each record one by one and pass through the input parameter for server action provided by Module B.
  3. Server action Module B will process current iteration receive through the input parameter. 
  4. Before server action Module B writing the record into the server, Module C will generate a Bill number. 
  5. Module C will save the generated Bill number as reference by creating a new record in Module C table.  
  6. Then server action Module B will insert a new record and save a the Bill Number generated by Module C into Module B table. 
  7. Then Module A will update an indicator for current iteration for example 'PROCESS_INDICATOR = Yes' in Module A table.
  8. The process will continue until it reach the 300th record.
  9. Let say, if an error occurred or an exception is raised in any server action (Module A / Module B / Module C) during the 250th record, all previous iteration / changes made from 1 till 249th record must be rollback.
  10. For your information, we already implemented exception handling and set the Abort Transaction = Yes.
  11. We also didn't implement an explicit CommitTransaction in all our process.
  12. We didnt use any service action during the process.
  13. We already assign AllException Handler and AbortTransaction Handler to catch any uncaught exceptions.
  14. Currently, the process only rollback for the current record iteration, rollback didn't happen for previous iterations.

Can someone give some idea or perspective on how to tackle this scenario. How is the exception handling rollback works? How to rollback previous iterations?


Many Thanks

Ezra

0
0
12 Sep 2022
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
Solution

Hi Ezra,

Assuming you use Server actions, not Service actions, then all database actions run in the same database transaction. This means that if you perform an explicit rollback (calling AbortTransaction) or an exception is generated that has its "Abort Transaction" property set to "Yes", all database actions in the current transaction are rolled back.

If you somehow find that less than the expected number of transactions are rolled back, this means that there is an explicit commit (calling CommitTransaction) somewhere. This can be in your own code, or in some action you are calling (direct or indirect). If you really have no idea where it is, you could generate explicit rollbacks, and see to which point it rolls back. If there's no rollback at all, you know the previous action has a commit somewhere.

Again note that Service actions or REST method calls do not run in the same database transaction, and will not be rolled back. This is especially useful for error reporting. But it seems you are talking about regular Server actions only, so that shouldn't be the problem.

See solution in context
1
0
12 Sep 2022
Copy Link
UserImage.jpg
Ezra

Hi Kilian,

Thank you for your reply. This give us some idea on how to check back our business logic. We will try to check back which point in our action that having an explicit commit.

Regarding the service actions or REST method class, these action when triggered are running in different transaction. What happen if there is an explicit commit used in service actions or REST method class. Will it effect the main transaction from the server action?

For example,

Let say I'm using a service action in Module C with an explicit commit.

After the 200th iteration in Module A, if an exception is raised in Module C, will it rollback all records from previous iteration or all the records from previous iteration were committed due to an explicit commit triggered by the service action in Module C?

Regards,

Ezra

0
0
13 Sep 2022
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP

Replying to Ezra's comment on 13 Sep 2022 14:57:57

Hi Ezra,

Being in different transactions means that a commit or rollback has no effect on other transactions. So if a REST method does a commit, that's just for that method's transaction. The code calling that method has still its own, uncomitted, transaction.

However, if a REST method produces an unhandled exception, or it explicitly returns an HTTP status code other than in the 200-range, the platform generates an exception in the calling module, which may cause an abort transaction if not handled properly.

1
0
13 Sep 2022
Copy Link
UserImage.jpg
Saurabh Shivananda Prabhu Chimulkar

Hi Ezra,

Can you please provide a screenshot of the processing done. As you mentioned its a batch process, i believe all the processing is done in a server flow, and from a client action you are not calling each of the said server actions.

Regards,

Saurabh

1
0
12 Sep 2022
Copy Link
UserImage.jpg
Ezra

Hi Saurabh,

Thank you for your reply, unfortunately i cant share the screenshot on how the processing is done since the original process are extracted into multiple sub-level server action to meet our business logic requirement. I try to simplify the process into scenario as stated above.

Regards,

Ezra

0
0
13 Sep 2022
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
Solution

Hi Ezra,

Assuming you use Server actions, not Service actions, then all database actions run in the same database transaction. This means that if you perform an explicit rollback (calling AbortTransaction) or an exception is generated that has its "Abort Transaction" property set to "Yes", all database actions in the current transaction are rolled back.

If you somehow find that less than the expected number of transactions are rolled back, this means that there is an explicit commit (calling CommitTransaction) somewhere. This can be in your own code, or in some action you are calling (direct or indirect). If you really have no idea where it is, you could generate explicit rollbacks, and see to which point it rolls back. If there's no rollback at all, you know the previous action has a commit somewhere.

Again note that Service actions or REST method calls do not run in the same database transaction, and will not be rolled back. This is especially useful for error reporting. But it seems you are talking about regular Server actions only, so that shouldn't be the problem.

1
0
12 Sep 2022
Copy Link
UserImage.jpg
Ezra

Hi Kilian,

Thank you for your reply. This give us some idea on how to check back our business logic. We will try to check back which point in our action that having an explicit commit.

Regarding the service actions or REST method class, these action when triggered are running in different transaction. What happen if there is an explicit commit used in service actions or REST method class. Will it effect the main transaction from the server action?

For example,

Let say I'm using a service action in Module C with an explicit commit.

After the 200th iteration in Module A, if an exception is raised in Module C, will it rollback all records from previous iteration or all the records from previous iteration were committed due to an explicit commit triggered by the service action in Module C?

Regards,

Ezra

0
0
13 Sep 2022
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP

Replying to Ezra's comment on 13 Sep 2022 14:57:57

Hi Ezra,

Being in different transactions means that a commit or rollback has no effect on other transactions. So if a REST method does a commit, that's just for that method's transaction. The code calling that method has still its own, uncomitted, transaction.

However, if a REST method produces an unhandled exception, or it explicitly returns an HTTP status code other than in the 200-range, the platform generates an exception in the calling module, which may cause an abort transaction if not handled properly.

1
0
13 Sep 2022
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.