Order of execution for records on DML statements

Question

A list of objects should be inserted in the order it is populated, so lower indexes will be inserted first. This is important when dealing with a List<sObject> where you rely on the order within the list to prevent excessive chunking.

My question is whether the above statement holds true on upsert.

The code below assumes a Test__c object with one unique value text field named unique_field__c (obviously just an example).

// create a record with a unique value set and insert it (will get updated later)
Test__c test1 = new Test__c(unique_field__c = 'a');
insert test1;
// create a record with the same unique value, but don't save it (will get inserted later)
Test__c test2 = new Test__c(unique_field__c = 'a');

// edit the unique field on the persisted record
test1.unique_field__c = 'b';

// fill a list with the two records above, the existing one first
List<Test__c> testList = new List<Test__c>{test1, test2};

// try to upsert the list -> duplicate value error occurs
upsert testList;

The expected behaviour, based on how insert is processed, would be to update the first record, changing its unique value field, then inserting the second record, which at this moment can take the value because it's not used by the first one any more. Obviously, this doesn't happen. Either inserts are executed first or validation is done in advance.

Can someone confirm where the problem lies?

Btw, I know there are simple ways to work around this, but it intrigues me because my understanding of the order of execution is lacking.

Answer 1

The answer is no, it does not. Upsert is not operating within a degree of Snapshot Isolation sufficient to allow it to behave in a way that is completely cummutative with insert and update. In English, it means it is not a bullet-proof substitute for update and insert.

Your question includes reference to the importance of order of operation within an insert, relative to the order of a list being inserted in one call to insert. The order should not matter in a database with a high degree of isolation, because any conflicts that might occur during the call should be materialized (reported & acted upon) at the end of the call. Upsert is violating isolation by scheduling the inserts and their validation before the updates (and their eventual resolution of those conflicts), when a higher degree of isolation would defer the validation to the end, checking the final state of both internal operations.

Imagine the case of mutual key swaps between rows. Changing either one creates a state which is invalid until the other one is changed. Now there is no ordering that will help, as starting with either one leaves the state invalid temporarily. To succeed, you have to temporarily suspend the rules for the entire transaction, then apply them once at the end.

To execute a series of row changes where potentially any individual row change produces/resolves a conflict for another row change within the logical unit of work requires the database system to put the entire column (or its index, or even the entire table) into memory, run the entire series, validate in bulk, and write the changes back (or not), all in one transaction while the source column is locked to writes in the receiving table. This is very hard to do in practice.

Oracle can do this brilliantly in microseconds while consuming prodigious amounts of memory and CPU. Saleforce multi-tenancy performance throttling prevents this (I suppose) because the performance hits--to others--can be substantial.

After testing, it appears that upsert is internally implemented as:

savepoint
insert
validate
[ rollback | proceed ]
update
validate
[ rollback | commit ]

No external parameter fiddling seems to change this.

This raises a new question in my mind: is upsert fully ACID compliant? I'll raise this in a separate post.

Answer 2

This wouldn't be possible as:

Any DML operation on sObjects List run in a context. If any of the record causes exception then all operation done is rolled back (They are not physically committed).

For example: DML operation cannot be performed on list which contains 10001 records or more. If this is an ordered operation than it should throw exception on 10001st record and 10000 records would be inserted/upserted.

When DML operation executes it checks records for validation before actual commit. If there was a possibility of updating/inserting partially then we will always need to use Database.savepoint for DML with List<sObjects>. DML operation is a single execution process where all data is committed in single commit (No iteration when committing data into database). This is the reason DML through List count only one even it has several records.

Validation done between committed database with new virtual records and then among virtual records. If this all succeed then actual commit happens.