Apex Code Development You need to sign in to do that
Don't have an account?
sdu Transaction isolation level
Anyone knows what is the default isolation level and if it is possible to change that? I have a use case as follows: I want to insert a record in an object if it does not exist, otherwise just update it. So I am doing a select (where say name = <some value>), if no record is returned, I create a new one, otherwise I update the existing one. But it appears that if two such calls are executed at the same time, I would end up creating two records (with same name). Anyone knows how to solve this problem?
So if I'm reading you right, you will indeed have a concurrency/race-condition: 2 different users submitting a transaction with the same parameters (i.e. both are looking for the same name, where the corresponding record does not exist yet) within a "very close" time window will cause a duplicate record insertion.
The length of that time window depends; it starts at the time of your query (for an existing record of matching name) and ends when salesforce finishes commiting your inserted/updated record to the database (which occurs after the last round of workflows and triggers fires) -- i.e. after termination of the current execution context. If your Object has lots of configured workflows/triggers, then this time window can become quite substantial. Either way, you can't count on your window of vulnerability ever being less than a few seconds.
Fortunately, as long as your system's sustained average traffic of these particular transactions is not TOO high, a good solution exists: when you append " FOR UPDATE" to your SOQL query, salesforce will place a hard lock on all returned rows; the lock is automatically released upon completion of the current execution context (regardless of whether it succeeded or failed with errors).
In your case you can't just use "For Update" on your current query; if the requested record name does not exist yet, then there's nothing to lock on. Instead, you could use a designated "sync" row in your table (or create a new designated "sync" Object with 1 row to synchronize on), and then always lock on that specific row for all requests of this type. Throw an exception if the sync row is not found (i.e. alert yourself to add the sync row). In this way, multiple concurrent requests for new records will become synchronized in a FIFO-queue fashion. I.e. multiple concurrent requests for the same record will create a record for the very first request (the request that get designated "first" becomes arbitrary, and non-important, as the time window shrinks), and perform an update for all subsequent requests.
Once again -- before implementing such a solution, make sure that your sustained request traffiic is low enough so that the sync queue won't get jammed up for any sustained period of time -- that would be a "live-lock" situation (different than deadlock but still with bad results).
All Answers
Use upsert.
I don't think that would work. Upsert works if you already have the ID in the object. In my case, I do not have the ID - because the other process has not created the object yet.
You'd have to create an external ID field.
So if I'm reading you right, you will indeed have a concurrency/race-condition: 2 different users submitting a transaction with the same parameters (i.e. both are looking for the same name, where the corresponding record does not exist yet) within a "very close" time window will cause a duplicate record insertion.
The length of that time window depends; it starts at the time of your query (for an existing record of matching name) and ends when salesforce finishes commiting your inserted/updated record to the database (which occurs after the last round of workflows and triggers fires) -- i.e. after termination of the current execution context. If your Object has lots of configured workflows/triggers, then this time window can become quite substantial. Either way, you can't count on your window of vulnerability ever being less than a few seconds.
Fortunately, as long as your system's sustained average traffic of these particular transactions is not TOO high, a good solution exists: when you append " FOR UPDATE" to your SOQL query, salesforce will place a hard lock on all returned rows; the lock is automatically released upon completion of the current execution context (regardless of whether it succeeded or failed with errors).
In your case you can't just use "For Update" on your current query; if the requested record name does not exist yet, then there's nothing to lock on. Instead, you could use a designated "sync" row in your table (or create a new designated "sync" Object with 1 row to synchronize on), and then always lock on that specific row for all requests of this type. Throw an exception if the sync row is not found (i.e. alert yourself to add the sync row). In this way, multiple concurrent requests for new records will become synchronized in a FIFO-queue fashion. I.e. multiple concurrent requests for the same record will create a record for the very first request (the request that get designated "first" becomes arbitrary, and non-important, as the time window shrinks), and perform an update for all subsequent requests.
Once again -- before implementing such a solution, make sure that your sustained request traffiic is low enough so that the sync queue won't get jammed up for any sustained period of time -- that would be a "live-lock" situation (different than deadlock but still with bad results).
mulveling,
I have similar problem, can you please explain what you mean by
" "sync" row in your table (or create a new designated "sync" Object with 1 row to synchronize on)"
Appreciate your help