Community user self-registration process – How to overcome the MIXED_DML_OPERATION error in a single transaction ?


Your company use Salesforce Community to allow your customer or partner to interact with your Salesforce CRM. You will provide a self-registration process to your external identities to self register and immediately having access to you community portal. However, you might end-up with the following error MIXED_DML_OPERATION if you create from scratch an account with a contact and the corresponding user to connect to the community. This error means that for example you cannot in the same transaction mixed standard object creation (account + contact + user) or you cannot insert an account and then insert a user or a group member in a single transaction.

Mixed DML Operation

This link sObjects That Cannot Be Used Together in DML Operations shows all sObjects impacted by this restriction and the proposed solution is to go through @Future annotation (A future method runs in the background asynchronously), allowing then another transaction to be started by the platform. This solution works but in my opinion:

  • is not a scalable solution for a large Salesforce implementation
  • is not under the control of the developer as it is not guarantee to succeed. You must implement a configurable retry mechanism to replay your code, and handle errors in a proper way.
  • the @Future is also limited as:
    • nested calls are not allowed
    • parameters for a @future method must be primitive data types, arrays of primitive data types, or collections of primitive data types.

The challenge

So the challenge is double:

  • How can we replace the @Future call and provide a more elegant & scalable solution ?
  • How can I avoid the mixed DML operations without sequentially calling:
    1. create an Account & commit
    2. create a Contact & commit
    3. create an User & commit

I would expect to have a better code implementation than these sequential calls (e.g having only one insert call) !

Proposed Solution

Hopefully, Queueable Apex was introduced in Winter ’15 and enables you to easily start and manage asynchronous processes. Salesforce provide a way to monitor this asynchronous process using the AsyncApexJob’s table queryable through SOQL, REST/SOAP API. The benefit is huge: we can now have  an history  of all our asynchronous processes and the time it take to completed this process !

To avoid the mixed DML operations, it took me some time to find an acceptable implementation. What’s put me on the right track is you can use insert statement with a list of sObject in a single call (e.g insert new List<Sobject> {sobj1, sobj2, sobj3, … }).

But still, this end me up with theses errors:

  • Error1: System.DmlException: Insert failed. First exception on row 0; first error: INVALID_FIELD, More than 1 field provided in an external foreign key reference in entity: Account: [].
  • Error2: EXCEPTION: System.DmlException: Insert failed. First exception on row 2; first error: INVALID_CROSS_REFERENCE_KEY, invalid cross reference id: []

Googling for the last error refers me to this article: How do I use an External ID to import related records?

Salesforce allows custom fields on standard objects and custom objects to be identified by the attribute of External ID (Salesforce will create an index for the field). Custom fields of type text, number or Email can have the External ID attribute enabled.
The External ID feature is extremely useful when performing integration and data migration with Salesforce. When inserting new Contacts, we need the Salesforce id of the related Account object, so that the Contact gets created under the related Account. In the integration setup, this would require the external system to know of the Salesforce id of the Account. If the Account object has an External ID field, it can be used instead of the Account Salesforce id with the Salesforce Upsert operation to load Contacts.

I will take the advantage of the External ID to achieve what I need. You just need to create:

  • a unique external ID on you account object : account_ExtID__c
  • a unique external ID on you contact object : contact_ExtID__c

As we are migrating users/contact/account from an external system to Salesforce, this perfectly fits my needs and used them for my registration process to support at scale.

Proposed Implementation

Below an sample implementation using Apex:

public class AsyncExecutionExample  implements Queueable {
    public void execute(QueueableContext context) {

    	if (isNewAccount & isNewContact & isNewUser) {
		Account a = new Account(Name='Account001', account_ExtId__c='Account001');
		Contact c = new Contact(FirstName='Chenda', LastName='Mok', Account=new Account(account_ExtId__c='Account001'), contact_ExtId__c='Contact001');
		User u    = new User( FirstName = c.FirstName, LastName = c.LastName, Email = '', Username = '',
							  Alias ='	cmok001', CommunityNickname = 'mokchend',ProfileId = '00e58000000Ht5a',
		                  	  Contact = new Contact(contact_ExtId__c='Contact001'), UserPermissionsMobileUser = false, 
		                  	  TimeZoneSidKey='America/Chicago', LocaleSidKey= 'en_US', EmailEncodingKey= 'UTF-8', LanguageLocaleKey= 'en_US');
		insert new List<Sobject>{a, c,u};

You can use anonymous call to test this class:
ID jobID = System.enqueueJob(new AsyncExecutionExample());
System.debug('jobID=' + jobID);

Thanks for reading.

About Chenda Mok

19 years of hands on experience in software design and development with emphasis on Enterprise Application Integration (EAI), Services Oriented Architecture (SOA) and Identity Management (IDM) solutions. I’m a software engineer, member of the professional service delivery team working for Salesforce. Prior to this, I worked for Oracle as Solution Architect, through SeeBeyond(06/2005), then SUN’s acquisition (04/2009). After my master’s degree in computer science in 1997; I always delivered consulting on architecture, design, implementation on integration’s field. I’m interested in architecture using EAI/SOA/IDM/BPM/Cloud technologies, software development and Java’s related technologies. I may blog about my work/activities at Salesforce, but I do not speak for my employer, past, present or future.
This entry was posted in Salesforce and tagged , , , , , , , , . Bookmark the permalink.