Saturday, 18 January 2014

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 standard objects like Account or any custom object, here are 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 these scenarios in detail below. But for sake of example lets take an example scenario:
  1. AFTER INSERT Trigger on Account
  2. This trigger create a GROUP from all newly created accounts.
  3. The same trigger also adds current user as 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 trigger code for the same would be something like this
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
trigger Account on Account (after insert) {
    //AccountHandler.afterInsert(Trigger.newMap);
     
  List<Group> newGroups = new List<Group>();
  for (Account acc : Trigger.new) {
     newGroups.add(new Group(name=acc.Name, type='Regular', DoesIncludeBosses=false));
  }
  insert newGroups;
   
  List<GroupMember> newGroupMembers = new List<GroupMember>();
  for (Group grp : newGroups) {
      newGroupMembers.add(new GroupMember(GroupId = grp.Id, UserOrGroupId=UserInfo.getUserId()));
  }
    insert newGroupMembers;
}
On creating an Account, this trigger will fail for 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”
mixed_dml_operation error on salesforce ui, after trigger failure 
The solution is simple you split the DML into future and non future context 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 create new Apex class for the trigger code

Account.trigger

?
1
2
3
4
5
6
7
8
9
10
11
trigger Account on Account (after insert) {     
  List<group> newGroups = new List<group>();
  for (Account acc : Trigger.new) {
     newGroups.add(new Group(name=acc.Name, type='Regular', DoesIncludeBosses=false));
  }
  insert newGroups;
   
  Set<id> groupIds = new Map<id  , Group> (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
?
1
2
3
4
5
6
7
8
9
10
11
public class AccountHandler {
  @future
  public static void createGroupMembers(Set<Id> groupIds) {
    List<GroupMember> newGroupMembers = new List<GroupMember>();
     
    for (Id grpId : groupIds) {
        newGroupMembers.add(new GroupMember(GroupId=grpId, UserOrGroupId=UserInfo.getUserId()));
    }
      insert newGroupMembers;
  }
}
For more details on this, please check salesforce docs here .

2. Test Code

Take an example scenario, where you are having a trigger created in above fashion i.e. Setup and Non Setup objects are updated in different transactions via @future methods. But in Test case we tend to serialize all async operations like future calls via Test.startTest() and Test.stopTest(), so we are again on same condition that within same context. Here is the test code that will start failing again for the same error.
?
1
2
3
4
5
6
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: []   
mixed_dml_operation error on test case execution 

Now, how to fix this ?

As this problem is specific to test code only, this can be fixed by starting 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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public class AccountHandler {
  @future
  public static void createGroupMembers(Set<Id> groupIds) {
    List<GroupMember> newGroupMembers = new List<GroupMember>();
     
    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 is having 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 at one place only, this class is named “MixedDMLOps.cls” and here is the source for the same:

MixedDMLOps.cls

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/**
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
?
1
2
3
4
5
6
7
8
9
10
11
12
public class AccountHandler {
  @future
  public static void createGroupMembers(Set<Id> groupIds) {
    List<GroupMember> newGroupMembers = new List<GroupMember>();
     
    for (Id grpId : groupIds) {
        newGroupMembers.add(new GroupMember(GroupId=grpId, UserOrGroupId=UserInfo.getUserId()));
    }
    // the change  
    MixedDMLOps.ins(newGroupMembers);
  }
}

 

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)