Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

Another way to ask the question: Are SOQL queries transactional/atomic on the second level?

I am writing a data integration with salesforce using the REST API. I want to retrieve a list of updated objects since the last time I pulled data from the API. This could be accomplished by a query like this: SELECT Id, LastModifiedDate FROM Task WHERE LastModifiedDate > 2013-12-18T21:50:43.000+0000

The value "2013-12-18T21:50:43.000+0000" would come from the greatest LastModifiedDate available from the copy of the objects I already have.

BUT, how do I know that I retrieved all records for the 43rd second when I last queried the database? Are SOQL queries transactional/atomic on the second level? If they are transactional then the above query will retrieve the exact records I am interested in. If it is not transactional then I would need a query like this: SELECT Id, LastModifiedDate FROM Task WHERE LastModifiedDate >= 2013-12-18T21:50:43.000+0000

BUT, now I will have duplicates for all records modified for the 43rd second. I know this wouldn't be a huge deal, but I would like to pull back as little data as possible.

I am aware of the updated endpoint in the REST API. Documentation for that endpoint can be found here: http://www.salesforce.com/us/developer/docs/api_rest/Content/resources_getupdated.htm I do not want to use that endpoint for two reasons. 1) only Ids are retrieved. (I can't retrieve a list of columns that I am interested in, I would need additional api calls to get those). 2) It ignores anything less than minutes in the start and end values. If I am going to hit the API, I don't want to miss out on the last 1-59 seconds of activity.

share|improve this question

2 Answers 2

The problem with LastModifiedDate is that it is updated sometime after the record has completed updating, which means you can miss records that were "in flight" at the time of the last query, since they were not committed (there's a similar question that addresses this problem on this site). In order to be 100% accurate, you should use the replication API (getUpdated/getDeleted SOAP API or updated/deleted REST API). This will always include records that fall within the requested date range to the current date and give you the ability to accurately retrieve all records without duplication. You should take a look at those calls to see how they operate.

share|improve this answer

Two approaches are possible

  • On the server side make use of a flag on the Task object that would indicate the extraction status of the record and include the FOR UPDATE clause in your query. That way you can dispense with the timestamp approach and simply pull all records with a 'False' status

  • Modify the query from

    SELECT LastModifiedDate
    FROM Task
    WHERE LastModifiedDate >= 2013-12-18T21:50:43.000+0000
    

    To

    SELECT LastModifiedDate
    FROM Task
    WHERE LastModifiedDate >= 2013-12-18T21:50:43.000+0000
    AND Id NOT IN :extractedIds
    

    Where extractedIds represents your prior request's collection of extracted Task ids (limited to the last second if you wish)


Look at this example which illustrates completeness without duplication. (Ignoring hours and minutes for clarity.)

Assume the server adds three records

{Id: A, LastModifiedDate: ...42}
{Id: B, LastModifiedDate: ...43}
{Id: C, LastModifiedDate: ...43}

Then imagine a client makes a query in the middle of the 43rd second:

SELECT ... WHERE LastModifiedDate >= ...43 AND Id NOT IN ()

The results come back:

{Id: A, LastModifiedDate: ...42}
{Id: B, LastModifiedDate: ...43}
{Id: C, LastModifiedDate: ...43}

The server creates a new record shortly thereafter before the 44th second hits:

{Id: D, LastModifiedDate: ...43}

The client later makes another query which uses the timestamp from the prior query's latest result, and the Ids of the results that fell on that timestamp:

SELECT ... WHERE LastModifiedDate >= ...43 AND Ids NOT IN ('B', 'C')

(See how the timestamp is from B or C and the Ids correspond to B and C)

The results come back:

{Id: D, LastModifiedDate: ...43}
share|improve this answer
2  
With a caveat that the time stamp is accurate only to the second (not millisecond), and it is possible to miss records that are in flight during the query. –  sfdcfox Dec 18 '13 at 16:00
    
Thanks @sfdcfox can you help me understand? –  max Dec 19 '13 at 12:00
    
The query is only accurate at the time the query is executed for all records that have been committed, and not those not yet committed to the database. In theory, it's possible to miss those records if one isn't careful, especially records that were committed, say, 1 ms after the query executed. It's a small window of opportunity (less than 1000 ms), but the possibility exists. It's better to backtrack a few minutes when using this solution to be sure you don't miss records, and filter out the duplicates as you've suggested. –  sfdcfox Dec 19 '13 at 14:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.