GraphQL Transaction for entire mutation

Hi all,

I had brought up on the Slack channel a discussion regarding transactions for the GraphQL API and I thought it best to move it here after discussing with our team.

Essentially, we face an issue at the moment whereby each mutation that runs within a single, overall ‘mutation’ call (see below example) runs in a separate transaction; because of this, we face an issue regarding the creation of a set of nodes in phase 1 of the mutation which would end up being orphaned if phase 2 of the mutation failed.

We understand that once a given mutation fails, no subsequent mutations will run and the rest will return errors, which will be satisfactory in the majority of situations, however, there is a situation which does not currently appear to be handled and will lead to orphaned nodes (which would require an extra trip to the DB subsequent to the failed overall mutation to ‘clean up’ the now orphaned nodes).

See the following schema:

type SubJob implements IsSubJobOrSubJobLeg {
    id: ID!
    insertionGuid: String! @search(by: [term]) @id
    name: String! @search(by: [term])

    has_sub_job_legs: [SubJobLeg!]! @hasInverse(field: is_leg_of)
    has_booking_contacts: [BookingContact!] @hasInverse(field: is_booking_contact_of)
}

type SubJobLeg implements IsSubJobOrSubJobLeg {
    id: ID!
    insertionGuid: String! @search(by: [term]) @id
    name: String! @search(by: [term])

    is_leg_of: SubJob! @hasInverse(field: has_sub_job_legs)
}

type BookingContact {
    id: ID!
    insertionGuid: String! @search(by: [term]) @id
    phoneNumber: String! @search(by: [term])

    has_booking_contact_settings: [BookingContactSetting!] @hasInverse(field: belongs_to_booking_contact)
    is_booking_contact_of: SubJob @hasInverse(field: has_booking_contacts)
}

type BookingContactSetting {
    id: ID!
    insertionGuid: String! @search(by: [term]) @id
    getsCall: Boolean! 

    is_booking_contact_setting_of: IsSubJobOrSubJobLeg @hasInverse(field: has_booking_contact_setting)
    belongs_to_booking_contact: BookingContact @hasInverse(field: has_booking_contact_settings)
}

interface IsSubJobOrSubJobLeg {
    has_booking_contact_setting: BookingContactSetting @hasInverse(field: is_booking_contact_setting_of)
}

and the following mutation (ignore the fact that I don’t use actual GUIDs… this is for reference only, but pay attention to their unique values):

mutation{
  addBookingContact(input: [
    {
        insertionGuid: "0002"
        phoneNumber: "0400111111"
        has_booking_contact_settings: [
        {
          insertionGuid: "0003"
          getsCall: false
        },{
          insertionGuid: "0004"
          getsCall: true
        },
        ,{
          insertionGuid: "0007"
          getsCall: true
        }]
      }
  ]){
    bookingcontact{
      id
      insertionGuid
    }
  }
  
  addSubJob(input: [
    {
      name: "SubJob" 
      insertionGuid: "0001"
      has_booking_contacts: [
        {
          insertionGuid: "0002"
        }
      ],
      has_booking_contact_setting: {insertionGuid: "0007"}
      has_sub_job_legs: [
        {name: "LEG1" insertionGuid: "0005"
        	has_booking_contact_setting: {insertionGuid: "0003"}
        },
        {name: "LEG2" insertionGuid: "0006"
        	has_booking_contact_setting: {insertionGuid: "0004"}
        },
      ]
    }
  ]){
    subjob {
      id
      insertionGuid
      name
      
      has_booking_contacts{
        id
        insertionGuid
        phoneNumber
        
        has_booking_contact_settings {
          id
          insertionGuid
          getsCall
        }
      }
      
      has_booking_contact_setting {
        id
        insertionGuid
        getsCall
      }
      
      has_sub_job_legs {
        id
        insertionGuid
        name
        
        has_booking_contact_setting {
          id
          insertionGuid
          getsCall
        }
      }
    }
  }
}

As you can see, because the BookingContact node I created contains BookingContactSetting nodes which are used in different parts of the ‘tree’ of node creation here, I had to run the addBookingContact mutation prior to running the addSubJob mutation so that I could reference the insertionGuid of the created BookingContactSettings and this could not be achieved in the single addSubJob mutation.

The issue we face here is that if the addSubJob mutation fails, the BookingContact and BookingContactSetting nodes will be orphaned in the DB which is not ideal.

It is equally a problem for us if we create the SubJob first and then create the BookingContact and BookingContactSetting nodes as it’s important that both of these succeed, or all of them fail.

In my opinion, there are two options to avoid this issue:

1. (less than ideal)
We change the way the GraphQL API works such that I can both create an entity and refer to it in the same mutation, which would allow the following (insertionGuid is used as a lookup whilst having been created in the same mutation):

mutation{ 
  addSubJob(input: [
    {
      name: "SubJob" 
      insertionGuid: "0001"
      has_booking_contacts: [
        	{
            insertionGuid: "0002"
            phoneNumber: "0400111111"
            has_booking_contact_settings: [
            {
              insertionGuid: "0003"
              getsCall: false
            },{
              insertionGuid: "0004"
              getsCall: true
            },
            ,{
              insertionGuid: "0007"
              getsCall: true
            }]
        }
      ],
      has_booking_contact_setting: {insertionGuid: "0007"}
      has_sub_job_legs: [
        {name: "LEG1" insertionGuid: "0005"
        	has_booking_contact_setting: {insertionGuid: "0003"}
        },
        {name: "LEG2" insertionGuid: "0006"
        	has_booking_contact_setting: {insertionGuid: "0004"}
        },
      ]
    }
  ]){
    subjob {
      id
      insertionGuid
      name
      
      has_booking_contacts{
        id
        insertionGuid
        phoneNumber
        
        has_booking_contact_settings {
          id
          insertionGuid
          getsCall
        }
      }
      
      has_booking_contact_setting {
        id
        insertionGuid
        getsCall
      }
      
      has_sub_job_legs {
        id
        insertionGuid
        name
        
        has_booking_contact_setting {
          id
          insertionGuid
          getsCall
        }
      }
    }
  }
}

…OR…

2. (ideal)
We simply change the way that the GraphQL API works, or allow for an option to be passed as a header, that would ensure that all of the mutations in a single overall mutation request (everything inside mutation{...} runs as a single transaction, such that if any of the ‘sub mutations’ fails, they all fail. I liken this to running a DB transaction with multiple INSERT or UPDATE statements and only ‘committing’ if all parts of that overall transaction succeed, or then ‘rolling back’ if any of them fail.

Very keen to hear your opinions. I suspect number 2 would be easier to implement and is likely more versatile, but I’d certainly be happy with either of them.

2 Likes

Thanks for bringing this up. We are already considering adding native support for transactions in GraphQL queries. It is similar to what you have mentioned in the 2nd solution. Currently, the design and timeline are being discussed. We would probably finalize it by starting next week. I will keep you updated.

3 Likes

Hi @machship-mm, We have finalized the transaction RFC. We would love to hear from you if you have any comments.

1 Like