Optimistic concurrency - Isn't there a problem?

Question

I've read the article.

The article describes the next solution to situations when many users can write to the same DB.

You as a user need to:

1. Retrieve the row and the last modified dateTime of the row.
2. Make the calculations you want, but don't write anything to the DB yet.
3. After the calculations, just before you want to write the result to the DB, retrieve the last modified dateTime of the same row again.
4. Compare the date time of #1 to the dateTime of #2. If they equal - everything is ok, commit, and write the current time as the last modified date time of the row. else - other user was here - Rollback.

This process seems logical, BUT I see the next hole in it:

In #3 the user retrieves the last modified dateTime of the row, but what if between the reading of this dateTime (in #3), and the time of writing in #4, an other user enters, writes its data and get out? The first user can never know about it, and it will override the second user's data.

Isn't it possible?

Answer 1

The algorithm you describe does indeed have an opportunity of missing concurrent updated between step #3 and #4.

The part about testing for optimistic concurrency violations says:

When an update is attempted, the timestamp value in the database is compared to the original timestamp value contained in the modified row. If they match, the update is performed and the timestamp column is updated with the current time to reflect the update. If they do not match, an optimistic concurrency violation has occurred.

Although not mentioned explicitly, the idea is for the compare and update step to happen atomically on the server. This can be done with an UPDATE statement containing a WHERE clause involving the timestamp and its original value. Similar to the example mentioned in the article where all the original column values in a row still match those found in the database.