How does one correctly implement optimistic locking in MySQL?
Our team has deduced that we must do #4 below or else there is a risk that another thread can update the same version of the record, but we'd like to validate that this is the best way to do it.
- Create a version field on the table you want to use optimistic locking for e.g. column name = "version"
- On selects, make sure to include the version column and make note of the version
- On a subsequent update to the record, the update statement should issue "where version = X" where X is the version we received in #2 and set the version field during that update statement to X + 1
- Perform a
SELECT FOR UPDATE
on the record we are going to update so that we serialize who can make changes to the record we are trying to update.
To clarify, we are trying to prevent two threads which select the same record in the same time window where they grab the same version of the record from overwriting each others if they were to try and update the record at the same time. We believe that unless we do #4, there is a chance, that if both threads enter their respective transactions at the same time (but have not issued their updates yet), when they go to update, the second thread that will use the UPDATE ... where version = X will be operating on old data.
Are we correct in thinking we must do this pessimistic locking when updating even though we are using version fields/optimistic locking?
SELECT ... FOR UPDATE
or optimistic locking by row versioning, not both. See detail in answer.