Fighting and Fixing the “MIXED_DML_OPERATION” Error!

“MIXED_DML_OPERATION, DML operation on setup object is not permitted after you have updated a non-setup object (or vice versa): User, original object: Account”

You can easily run into this error if you are trying to perform DML on setup and non-setup objects in the same transaction. Non-Setup objects can be any one of the standard objects, like Account or any custom object.

Here are a few examples of the Setup Objects:

• Group1

• GroupMember

• QueueSObject

• User2

• UserRole

• UserTerritory

• Territory

This error typically comes in two different scenarios, i.e.

1. Non-Test code

2. Test Code

We will cover both of these scenarios in detail below. But for the sake of example, here is a scenario:

1. AFTER INSERT Trigger on Account.

2. This trigger creates a GROUP from all newly created accounts.

3. The same trigger also adds the current user as a member to those newly created groups.

1. Non-Test Code

Non-Test code means any Apex code that is not written for test cases, for example, triggers.

Typically, the trigger code for the same would be something like this:

trigger Account on Account (after insert) {
    //AccountHandler.afterInsert(Trigger.newMap);
    
  List newGroups = new List();
  for (Account acc : Trigger.new) {
     newGroups.add(new Group(name=acc.Name, type='Regular', DoesIncludeBosses=false));
  }
  insert newGroups;
  
  List newGroupMembers = new List();
  for (Group grp : newGroups) {
      newGroupMembers.add(new GroupMember(GroupId = grp.Id, UserOrGroupId=UserInfo.getUserId()));
  }
    insert newGroupMembers;
}

On creating an Account, this trigger will fail with this error:

“Apex trigger abhinav.Account caused an unexpected exception, contact your administrator: abhinav.Account: execution of AfterInsert caused by: System.DmlException: Insert failed. First exception on row 0; first error: MIXED_DML_OPERATION, DML operation on setup object is not permitted after you have updated a non-setup object (or vice versa): GroupMember, original object: Account: []: Trigger.abhinav.Account: line 14, column 1”

The solution is simple: you split the DML into future and non-future contexts, i.e.

1. Perform DML on Non-Setup object type.

2. Perform DML on Setup object type in @future methods.

Vice versa will also work. Here is the fixed code; we created a new Apex class for the trigger code

Account.trigger

trigger Account on Account (after insert) {      
  List newGroups = new List();
  for (Account acc : Trigger.new) {
     newGroups.add(new Group(name=acc.Name, type='Regular', DoesIncludeBosses=false));
  }
  insert newGroups;
  
  Set groupIds = new Map (newGroups).keySet();
  // call in future context to avoid MIXED DML conflicts
  AccountHandler.createGroupMembers(groupIds); 
}

AccountHandler.cls

This is the class with the future method to do setup and non-setup DML in different context:

public class AccountHandler {
  @future
  public static void createGroupMembers(Set groupIds) {
    List newGroupMembers = new List();
    
    for (Id grpId : groupIds) {
        newGroupMembers.add(new GroupMember(GroupId=grpId, UserOrGroupId=UserInfo.getUserId()));
    }
      insert newGroupMembers;
  }
}

For more details on this, please check this Salesforce document.

2. Test Code

Let’s take an example scenario where you are having a trigger created in the above fashion, i.e. Setup and Non-Setup objects are updated in different transactions via @future methods. But in the Test case, we tend to serialize all async operations like future calls via Test.startTest() and Test.stopTest(), so we are again on the same condition within the same context.

Here is the test code that will start failing again for the same error:

public static testmethod void test() {
 // use this to make future methods execute synchronously 
    Test.startTest();
      insert new Account(Name = 'Some Account Name');
    Test.stopTest();
}

Here is the error that comes on executing this test:

System.DmlException: Insert failed. First exception on row 0; first error: MIXED_DML_OPERATION, DML operation on setup object is not permitted after you have updated a non-setup object (or vice versa): GroupMember, original object: Account: []

Now, how to fix this?

As this problem is specific to test code only, this can be fixed by starting a new context in the future method or the conflicting DML point. Here is the fixed future method that works well from both UI and Test code.

public class AccountHandler {
  @future
  public static void createGroupMembers(Set groupIds) {
    List newGroupMembers = new List();
    
    for (Id grpId : groupIds) {
        newGroupMembers.add(new GroupMember(GroupId=grpId, UserOrGroupId=UserInfo.getUserId()));
    }
       
   if (Test.isRunningTest()) {
     // start new context via system.runAs() for the same user for test code only
      System.runAs(new User(Id = Userinfo.getUserId())) {
       insert newGroupMembers;
      }
    } else {
      // in non-test code insert normally
      insert newGroupMembers;
    }
  
  }
}

But this is not clean; imagine a big complex force.com development project that has so many future methods and triggers. Doing this patch everywhere will not look neat. So, I tried coming up with some utility class that will handle this patching in one place only; this class is named “MixedDMLOps.cls” and here is the source for the same:

MixedDMLOps.cls

/**
Handles mixed dml situations in code. It runs the DML operation in different context for Test code only, so that conflict between DML on setup and non-setup object is gone.

PLEASE NOTE:
============
methods are not named as delete, insert because they are reserved words by Apex
*/
public without sharing class MixedDMLOps {
  // DML UPDATE operation 
  public static Database.SaveResult[] up (Sobject[] objs) {
    Database.Saveresult[] updateRes;
     if (Test.isRunningTest()) {
          System.runAs(new User(Id = Userinfo.getUserId())) {
          updateRes = database.update(objs);
          }
        } else {
        updateRes = database.update(objs);
        }  
    return updateRes;
  }
  
  // DML DELETE
  public static Database.DeleteResult[] del (Sobject[] objs) {
    Database.DeleteResult[] delRes;
     if (Test.isRunningTest()) {
          System.runAs(new User(Id = Userinfo.getUserId())) {
          delRes = database.delete(objs);
          }
        } else {
        delRes = database.delete(objs);
        }  
    return delRes;
  }
  
  
  // DML INSERT
  public static Database.Saveresult[] ins (Sobject[] objs) {
    Database.Saveresult[] res;
     if (Test.isRunningTest()) {
          System.runAs(new User(Id = Userinfo.getUserId())) {
          res = database.insert(objs);
          }
        } else {
        res = database.insert(objs);
        }  
    return res;
  }
  
  
}

The future method code can be simplified a lot now; here is the one that uses MixedDMLOps:

public class AccountHandler {
  @future
  public static void createGroupMembers(Set groupIds) {
    List newGroupMembers = new List();
    
    for (Id grpId : groupIds) {
        newGroupMembers.add(new GroupMember(GroupId=grpId, UserOrGroupId=UserInfo.getUserId()));
    }
    // the change   
    MixedDMLOps.ins(newGroupMembers);
  }
}

The best fix for this problem?

Ideally, we shouldn’t be doing such hacks to fix MIXED DML in tests; the Apex test harness should handle it correctly.

Reference

• Mixed DML Operations in Test Methods

• Our Salesforce Tool Suite— Chrome Extension (for debugging errors and logging)

Drop a note with your queries to move forward with the conversation 👇🏻