1

I'm trying to prevent the insertion of duplicate records from concurrent transactions. FOR UPDATE does not seem like a good candidate because the records are not updated, so there is nothing to lock. Field-based unique constraint does not seem like a good candidate because the criteria are a bit more complex.

Unique-constrained formula fields are reasonable candidates, but feel a bit clunky.

Do duplicate rules work at the database-level, similar to unique constraint? Do they prevent insertion of duplicate records across concurrent transactions?

1

Yes, Duplicate Rules will block concurrent duplicates. The use of Duplicate Rules can cause some database contention, but prevents duplicates. As an aside, the FOR UPDATE SOQL keyword will also wait on inserts and deletes. The goal is to make sure that there are no prior pending "updates" to the database.

4
  • Could you clarify the waiting on insert mechanism? Thank you!
    – ipavlic
    Jan 29 '20 at 19:52
  • @ipavlic Basically, the point of locking is to make sure the business logic is always honored. This includes any in-flight transactions, such as insert, update, delete, and undelete, in order to keep unique constraints and so on coherent. If you use duplicate rules, you are guaranteed no duplicates will be created, and using FOR UPDATE will always ensure that you have the latest data, even if some records are in-flight.
    – sfdcfox
    Jan 29 '20 at 20:08
  • So if at T0 two concurrent transactions both select for update to verify there is no record, both would find nothing there. Then at T1, transaction 1 inserts a record and at T2 transaction 2 also inserts. Does for update make the entire transaction atomic (so that both selection and insertion are done “simultaneously”), and impose ordering on transactions? More importantly, is this documented anywhere for Salesforce?
    – ipavlic
    Jan 29 '20 at 22:47
  • @ipavlic honestly, aside from avoiding deadlocks, all I know for sure is that even two transactions from two users executed at the exact same millisecond will still somehow play nice with each other w.r.t. FOR UPDATE. Salesforce.com has been at this for a long time, and they're very good at optimizing database transactions. If I find more, I'll edit, but it might be worth having a question of its own.
    – sfdcfox
    Jan 30 '20 at 3:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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