As far as I understand Salesforce supports optimistic (by default) and pessimistic (using FOR UPDATE) locking for records. But what about transactions? If there direct analogy for typical SQL isolation levels in Salesforce, like: READ COMMITTED, READ UNCOMMITTED, etc. I mean what is the closest SQL analogy for Salesforce transaction.
My understanding is that a SQL
READ UNCOMMITTED (or old school
WITH (NOLOCK)) will allow you to query a value while another transaction has a lock on that record.
This would correspond to where one Salesforce transaction has done a
FOR UPDATE SOQL query on a record and hasn't completed the transaction yet (the entire request must complete successfully to commit). During that open transaction another clients query attempts to read the record.
In this case you will get a dirty read,
but you won't be able to use that data to update the record until the first transaction has committed or rolled back.
While the records are locked by a client, the locking client can modify their field values in the database in the same transaction. Other clients have to wait until the transaction completes and the records are no longer locked before being able to update the same records. Other clients can still query the same records while they’re locked. Source Locking Statements
Note also, from the same documentation:
If a client attempts to modify a locked record, the update operation might succeed if the lock gets released within a short amount of time after the update call was made. In this case, it is possible that the updates will overwrite those made by the locking client if the second client obtained an old copy of the record. To prevent this from happening, the second client must lock the record first. The locking process returns a fresh copy of the record from the database through the SELECT statement. The second client can use this copy to make new updates.