How to proceed with deleting information on two databases

Question

I need to delete items from two databases - one internal managed by my team, and another managed by some other team (they hold different, but related data). The constraint is that if one of these deletes from database fail, then the entire operation should be cancelled and rolled back.

Now, I can control and access my own database easily, but not the database managed by the other team. My line of thought is as follows:

1. delete from my database first (if it fails, abort everything straightaway)
2. assuming step 1 succeeds, now I call the API from the other team to delete the data on their side as well
3. if step 2 succeeds, all is good... if it fails, I'll roll back the delete on my database in step 1

In order to achieve step 3, I think I will have to save the data in step 1 in some variables within the function. Roughly speaking...

public void deleteData (String id) {
    Optional<var> entityToBeDeleted = getEntity(id);
    try{
        deleteFromMyDB(id);
    } catch (Exception e){
        throw e;
    }
    try{
        deleteFromOtherDB(id);
    } catch (Exception e){
        persistInMyDB(entityToBeDeleted);
        throw e;
    }
}

Now I am aware that the above code looks horrible. Any guru can give me some advice on how to do this better?

Answer 1

What does it mean if the remote deletion fails? That the deletion should not happen at all?

Can the local deletion fail for a non-transient reason?

A possible solution is:

Create a "pending deletions" table in your database which will contain the keys of records you want to delete.

When you need to delete record, insert a row in this table.

Then delete the record from the remote system.

If this succeeds, delete the "pending deletion" record and the local record, preferably in a single transaction.

Whenever you start your system, check the "pending deletion" table, and delete any records mention from the local and remote systems (I assume that both these operations are idempotent). Then delete the "pending deletion" record.