1

We can undo an action using Command or Memento pattern.

If we are using kafka then we can replay the stream in reverse order to go back to the previous state.

For example, Google docs/sheet etc. also has version history.

in case of pcpartpicker, it looks like the following:

enter image description here

For being safe, I want to commit everything but want to go back to the previous state if needed.

I know we can disable auto-commit and use Transaction Control Language (COMMIT, ROLLBACK, SAVEPOINT). But I am talking about undoing even after I have commited the change.

How can I do That?

Ahmad Ismail
  • 11,636
  • 6
  • 52
  • 87
  • 1
    You have to save the Part List in a history file. To undo, you read the history file and reverse the database changes. For a Part List, that would mean adding the items back into inventory and closing the list as unfulfilled. – Gilbert Le Blanc Mar 22 '21 at 06:40

1 Answers1

2

There isn't a real generic answer to this question. It all depends on the structure of your database, span of the transactions across entities, distributed transactions, how much time/transactions are allowed to pass before your can revert the change, etc.

Memento like pattern

Memento Pattern is one of the possible approaches, however it needs to be modified due to the nature of the relational databases as follows:

  1. You need to have transaction log table/list that will hold the information of the entities and attributes (tables and columns) that ware affected by the transaction with their primary key, the old and new values (values before the transaction had occurred, and values after the transaction) as well as datetimestamp. This is same with the command (memento) pattern.
  2. Next you need a mechanism to identify the non-explicit updates that ware triggered by the stored procedures in the database as a consequence of the transaction. This is important, since a change in a table can trigger changes in other tables which ware not explicitly captured by the command.
  3. Mechanism for rollback will need to determine if the transaction is eligible for roll-back by building a list of subsequent transactions on the same entities and determine if this transaction is eligible for roll-back, or some subsequent transaction would need to be rolled-back as well before this transaction can be rolled-back.
  4. In case of a roll-back is allowed after longer period of time, or a near-realtime consumption of the data, there should also be a list of transaction observers, processes that need to be informed that the transaction is no longer valid since they already read the new data and took a decision based on it. Example would be a process generating a cumulative report. When transaction is rolled-back, the rollback will invalidate the report, so the report needs to be generated again.

For a short term roll-back, mainly used for distributed transactions, you can check the Microservices Saga Pattern, and use it as a starting point to build your solution.

History tables

Another approach is to keep incremental updates or also known as history tables. Where each update of the row will be an insert in the history table with new version. Similar to previous case, you need to decide how far back you can go in the history when you try to rollback the committed transaction.

Regulation issues

Finally, when you work with business data such as invoice, inventory, etc. you also need to check what are the regulations related with the cancelation of committed transactions. As example, in the accounting systems, it's not allowed to delete data, rather a new row with the compensation is added (ex. removing product from shipment list will not delete the product, but add a row with -quantity to cancel the effect of the original row and keep audit track of the change at the same time.

jordanvrtanoski
  • 5,104
  • 1
  • 20
  • 29