Category Archives: limits

Batchables-Limits Exceptions

2 Replies

A bad thing happened the other day. Here was the sequence:

  1. Batch job started. start() method returned 5000 + Opportunity rows.
  2. Database.stateful used to record an internal log of activity for subsequent posting in the finish() method.
  3. Each batch of 200 was passed to execute() method.
  4. Execute method added bits to the stateful log (a string variable).
  5. Batch 21 (out 28) blew up on a Limits Heap Size exception. Blow up continued on batches 22-28.
  6. finish() method started and took value from Database.Stateful variable and persisted to Sobject Log__c (s).
  7. AND HERE IS WHERE BAD THINGS HAPPENED… finish() method started a “finalize” batch job, passing a list of sobject IDs that had exceptions in any previous batch execute. The finalize batch job (i.e. the chained batch job), made updates to all Opportunities that weren’t selected from the previous batches start() method and weren’t already marked as exceptions. In my case, these Opportunities were marked as closed lost.

So .. because the Opportunities in batches 21-28 were never processed and never marked with an exception (because of the uncatchable Limits exception), the chained (second) batch job blithely assumed that the Opportunities in batches 21-28 had never been fetched in the previous batch job’s start() method. Hence, perfectly good Opportunities got marked as closed lost.

Uh-oh.


So, what should I have done differently?

First, I wrongly assumed that a Limits exception would terminate the entire batch job, not just the currently running execute() batch.

And, since of this misconception, the finish() method unconditionally executes without knowing if all of the batches passed without uncaught exceptions. And, any work the finish() method performs that involves DML-type work, including scheduling a subsequent chained job may lead to incorrect behavior.

  1. The finish() method has access to the BatchableContext and can get, via getJobId(), the AsyncApexJob that represents the batch job.
  2. AsyncApexJob has a field NumberOfErrors that identifies how many batches are in error. If greater than zero, appropriate business logic should be applied.

Of course, the Limits Exception needs to be avoided in the first place by taking a different approach to stateful logging. I’ll investigate this in a subsequent post (but don’t stay up waiting for it!)

REST SOQL OFFSET and LIMIT – 2000 records

Leave a reply

I recently ran into an issue with an Apex batchable class that executed a REST SOQL query performed from SFDC org A against SFDC org B

The batch Apex code in org A was written to handle REST responses that looked like this:

{"totalSize":12,
"done":true | false,
"nextRecordsUrl" : "/services/data/v30.0/query/01g8000001J2eXvAAJ-2000",

"records":[
{"attributes":{
"type" : "the sobject",
"url" : "/services/data/v30.0/sobjects/the sobject/the id"
},
"field0 in query" : "value of field 0",
"field1 in query" : "value of field1",
...},
next record ...
]
}

If "done" : false was present, then, once the iterator reached the end of the retrieved SObjects, it would do an HTTP Request against the value in nextRecordsUrl .

Hence, the batch program could retrieve a large number of records.

And your problem was what?

Turns out my query string used in the original REST GET was of this form:

select id,name from Opportunity where closeDate = THIS_QUARTER Limit 10000 OFFSET 0

Even though there were more than 2000 Opportunities in Org B and even though the coding for the batchable class’s iterator could deal with the queryMore logic, the total number of records retrieved was exactly 2000.

Analysis

Turns out, if you code OFFSET 0, the maximum value returned in the totalSize JSON property of the REST response body is 2000 – regardless of there being more than 2000 rows in the queried org.

Solution
As there was already logic to deal with queryMore(), I simply removed the OFFSET clause. I verified there was no logic to increment OFFSET and repeat the query.

Why was there queryMore() logic in the code in the first place – why not implement LIMIT and OFFSET per SFDC spec?

Turns out, if the retrieved payload from a given query exceeds the maximum transfer size per REST response, SFDC will break up the query results into segments, indicated by the presence of "done": false. These segments will be a maximum of 2000 records but could be (and in my case were – do to payload mass) fewer. The queryMore logic had to be added anyway. Once this was present, the OFFSET portion of the SOQL became superfluous and should have been removed everywhere. Unfortunately, a vestigial OFFSET 0 was present causing ther REST query to only retrieve 2000 rows before saying – I’m done.