8

I'm writing an integration that requires polling Salesforce to pull down changed data. I'm worried about concurrent updates to the records that would cause me to 'miss' updates.

Concrete example:

Service A starts updating records at 5:15:10

Service B starts updating records at 5:15:20

Service B finishes updating records at 5:15:25 (5 seconds)

Service C starts its request at 5:15:30 and requests all records modified on or after 5:15:00 (Service A's job is still running)

Service C receives a response at 5:15:35 (5 seconds)

Service A finally finishes at 5:16:00 (50 seconds)

I'd expect Service C to see all the records modified by Service B since they were committed before Service C started its request -- but none of the ones modified by Service A

I have a few choices about how I can ask for records next time I poll. I can use the highest SystemModstamp of the records returned in the first request as the 'high water mark'. Or I can return a DateTime.now() timestamp as from the server as part of the payload. But in either case, I would get a date that is higher than when Service A's transaction started -- so if SystemModstamp is the 'beginning of transaction', I'd miss all of those updates when Service C polls again

The three questions are:

Is SystemModstamp the beginning or end of the transaction?

Or, even worse, is SystemModstamp different for each record updated in a transaction?

Does anyone have a better solution out of this mess?

I clawed through all of the Salesforce docs I could find and there's not a shred of info about this.

The maximum duration of a transaction is documented to be 10 minutes so the brute force solution is to always ask for stuff that's 10 minutes older than my high water mark -- but that adds network overhead and extra client code to deal with the possible "repeats" in the stream of updates

Edit: @superfell's answer below is definitely exactly what I wanted -- but I didn't put in my initial question that my endpoints are Apex REST so I don't have access to getUpdated from Apex. I'm not going to mark that as the solution for this question.

Edit again: There is Database.getUpdated() available to Apex -- this question is answered thanks to @superfell

3 Answers 3

11

generally sysModStamp is sent when the row gets written to the db, but with triggers, workflow etc, there's no real way to generalize when it gets set vs the lifetime of the transaction. Its definitely never going to be transaction commit time.

If you use getUpdated() to do this, part of the response data is the timestamp of the start of the oldest inflight transaction, you can use this as the start time of your next poll. Otherwise, as you say, you can get holes due to the difference between when the sysModStamp is calculated vs when the transaction actually commits.

If you're doing anything remotely expensive on the receiving side, you'll want to have something that double checks the last sysModstamp you processed for each row to help cut down duplicate processing.

8
  • SystemModStamp/LastModifiedDate are set to the transaction start time, but because of ACID compliance, those records are isolated from the database until the transaction is fully completed.
    – sfdcfox
    Oct 24, 2013 at 18:07
  • 1
    They are not set to transaction start time, you can see this with triggers, where an additional row gets touched is sysModStamp can be different to the originating row that caused the trigger.
    – superfell
    Oct 24, 2013 at 18:17
  • Sub-transactions still set their record time stamps to the time that the sub-transaction started, though, isn't it? That's what I've observed. E.g. trigger X on Y { ... update Z; } records in Z would have the timestamp of when that line executed, while records in Trigger.new would still have the time that the original trigger called, no?
    – sfdcfox
    Oct 24, 2013 at 18:31
  • @superfell This is a great answer, thank you -- I learned something new today -- my one bummer is that my endpoints are Apex REST so I don't have access to getUpdated from Apex :-(
    – daveespo
    Oct 24, 2013 at 18:50
  • There is a REST version of getUpdated, but if you're doing additional processing in your apex REST endpoint, that won't work for you.
    – superfell
    Oct 24, 2013 at 18:59
1

Transactions running in the database are isolated from each other until the transaction cannot be rolled back. Since C started before A finished, it will only see the results of B; if C attempted to update a record from A, then there'd be a lock and C would have to wait or would fail because of row locks. All records modified during a single transaction take on a last modified value of the time when the transaction started processing, but this won't be visible to any transaction running in parallel. C also can't see any records that may have been modified after C started, because its view of the database must be consistent. Records modified during A will be visible within A's transaction for the remainder of A's execution (because they are isolated together). This is a fundamental design of "ACID" compliant databases (Atomicity, Consistency, Isolation, Durability).

2
  • Thanks for the reply but this doesn't actually address my question as I called out everything you said in the question -- I wanted to know how I would be able to know the SystemModstamp of the records affected in A
    – daveespo
    Oct 24, 2013 at 18:41
  • So... I was wrong... Something interesting here... Going to post.
    – sfdcfox
    Oct 24, 2013 at 19:02
1

The SystemModStamp/LastModifiedDate fields are set at the end of the transaction. I created some test code to verify the behavior, and here's what I came up with:

The Test

A trigger on the account would run recursively and cause a huge inter-transaction delay, and capture different time stamps on the records. Here's the trigger code:

The Trigger

trigger testTrigger on Account (before insert, after insert) {
    Account[] records = new Account[0];
    if(Trigger.isBefore)
        for(Account record: Trigger.new)
            record.Transaction_Start_Time__c = DateTime.Now();
    for(Integer i = 0; i < 500000; i++);
    if(Trigger.isAfter)
        for(Account record: Trigger.new)
            if(record.ParentId == null)
                records.add(new Account(Name=record.Name, ParentId=record.Id));
    insert records;
}

The Results

After testing this, I queried the accounts back out of salesforce.com, and I ended up with these results:

ID                  NAME    PARENTID            CREATEDDATE                 LASTMODIFIEDDATE            SYSTEMMODSTAMP              TRANSACTION_START_TIME__C
0014000000uWmSuAAK  Test    0014000000uWmStAAK  2013-10-24T18:54:27.000Z    2013-10-24T18:54:27.000Z    2013-10-24T18:54:27.000Z    2013-10-24T18:54:26.000Z
0014000000uWmStAAK  Test                        2013-10-24T18:54:25.000Z    2013-10-24T18:54:27.000Z    2013-10-24T18:54:27.000Z    2013-10-24T18:54:25.000Z

Conclusion

Time stamps are truncated on commit, so there's a margin of 999ms difference, but you can clearly see that the Created Date is set after the "before insert" trigger (so, during initial commit), while the Last Modified Date and SystemModStamp fields are set to the end of the transaction. You can tell this is true because of the difference between the "time now stamp" and the other timestamps.

... So the moral of the story is to use getUpdated, as mentioned in the more concise answer. This is merely informative in case someone is curious about the timings.

2
  • 1
    I can 100% guarantee that the sysModStamp is not set to commit time and that you can get a significant gap between sysModStamp on the row and when the transaction committed. (I've worked on the save processing code).
    – superfell
    Oct 24, 2013 at 19:39
  • The more you (I) know...
    – sfdcfox
    Oct 24, 2013 at 21:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .