Where/when to check the version of the row when doing optimistic locking?

Question

I want to implement optimistic locking for a relational database.

Suppose there is a users table

id	name	version
1	Jhon	1
2	Jhane	1

My application fetches the Jhon user to change his name

SELECT id, name, version FROM users WHERE id = 1;

jhon = get_user_by_id('1');

jhon.change_name_to('Jhin');

jhon.save() // this method should fail or succeed depending on the version of the row in the database

So where do I need to compare the version of the selected row with the version of the row that is in the database?

Is a database transaction a good place to fetch the existing version of the row and compare it with the already fetched record?

transaction_begin()

jhon = get_user_by_id('1')

if (jhon.version !== updated_jhon.version) { // Ensures that version match
  // If no rollback
  transaction_rollback();
} else {
  // If yes, update and commit
  query("UPDATE table SET name = {updated_jhon.name}, SET version = {jhon.version + 1} WHERE id = 1;")
}

transaction_commit()

score 0 · Accepted Answer · answered Aug 16 '22 at 18:23

I found an answer in a similar asked question

How does Hibernate do row version check for Optimistic Locking before committing the transaction

The answer would be not to read a version at all.

Optimistic locking does not require any extra SELECT to get and check the version after the entity was modified

In order to update a record (user), we also need to pass a version

UPDATE users SET name = Jhin WHERE id = 1 AND VERSION = 1;

If the number of affected records is greater than 0, it means that the row was not affected by someone else during the name update.

If the number of affected records is equal to 0. It means that someone else has changed the row during our modification.

Where/when to check the version of the row when doing optimistic locking?

1 Answers1