11

If I make a SOQL query in a transaction, then make the same query later, does Salesforce actually run the query the second time, or can it just return the same results as before?

I have some code which creates Contacts in a race with an external integration. My code starts by querying to see if the Contact exists, and only creates one if required. Since the external system changed, it seems to have got faster and now we have what appears to be an occasional sequence of:

  1. My code queries for an existing Contact but finds none
  2. The integration creates one
  3. My code finishes preparing the Contact and tries to insert
  4. Duplicate matching rules prevent the insert

Obviously, one possibility is that my code to find the existing Contact is buggy. But, it's been unit tested and definitely works most of the time (presumably when the sequence above is 2, 1, 3, insert). We have seen it finding that existing Contact some times.

So, I have been catching the Exception for the duplicate, then re-querying Contacts and going through the sequence again. This process never seems to find the new Contact.

So, I wonder if the SOQL is being cached, or an Apex transaction's view of the database is protected from external changes until it completes?

Any thoughts?

1 Answer 1

10

Yes, there are caches in the platform. However, they have nothing to do with what you're experiencing.

The main problem is transaction isolation. If user A and user B (which may well be integrations, not actual people) do the same thing at the same time, changes they make while "in-flight" won't be visible to the other process, until the transaction is finally committed. It's entirely possible that the exact same query will return different results if a record finally commits during the course of your program's execution.

To minimize this effect, you should use locking statements (e.g. SELECT ... FROM ... WHERE ... FOR UPDATE). If the integration has an in-flight record matching the criteria, then your code will wait until the transaction aborts or completes (or possibly times out the query if the other transaction takes longer than about 5 seconds to commit or abort).

In other words:

an Apex transaction's view of the database is protected from external changes until it completes?

Yes, all in-flight transactions are isolated from other in-flight transactions. Only records successfully committed to the database can be queried in a different process.

2
  • Thanks @sfdcfox, really useful information. But I've read your answer a few times and I don't think this entirely explains the behaviour. My code cannot insert its Contact because the dupe matching rules blocks it. Which implies to me that the integration's transaction must have completed and committed to the database.
    – Aidan
    Jan 10, 2018 at 8:48
  • @Aidan Did you use a locking statement (FOR UPDATE)? Did you remember to query the record immediately before doing the DML? You should minimize the time between the query and the subsequent DML operation so that there's a minimum amount of time that the race condition can exist.
    – sfdcfox
    Jan 10, 2018 at 14:40

You must log in to answer this question.

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