When would you use Hibernate optimistic lock over postgres serializable isolation level?

Question

Good day , I understand what is a serializable isolation level and how it differs from REPEATABLE READ in Postgres. Serializable transaction is able to detect read write cycles so only the first commit will succeed .

With this in mind, does it make sense to use Hibernate's optimistic locking based on row versioning ? The row versioning will behave in exactly the same manner, if version column was updated then Java exception will be thrown which will rollback the transaction. Moreover, according to Postgres wiki , the trigger has to be created if some updates were done out of application level code (like plain sql query ran by psql). So in my humble opinion , Serializable level is a drop in replacement for Optimistic locking , is it so or there are some use cases where you would prefer Optimistic locking instead ?

user_6464308 · Answer

Don't mix up REPEATABLE READ and SERIALIZABLE: the latter is stronger than the former, and the former does not incur additional performance costs. REPEATABLE READ is enough for optimistic locking.

I would normally prefer optimistic locking with database techniques, as this will be cheaper. There is, however, one case in which I would prefer optimistic locking on the application side: if the resulting database transaction would take long.

With REPEATABLE READ, you have to perform the SELECT and the final UPDATE in the same database transaction. Now transactions have to be short for the database to work well, so if there is for example a user interaction involved, using REPEATABLE READ transactions would be a non-starter.

If you want to know what is bad about long REPEATABLE READ transactions:

they hold locks, potentially blocking concurrent activity indefinitely long (imagine you want to run an ALTER TABLE in this database)
they prevent the progress of autovacuum, thereby bloating your tables

user_986437 · Answer

One use case would be Long conversations, see here: https://vladmihalcea.com/preventing-lost-updates-in-long-conversations/)

In this case though in order to avoid lost updates, (see here) you should use Optimistic locking in addition to REPEATABLE READ isolation level if using postgres, in mysql I think SERIALIZABLE would be needed.

When would you use Hibernate optimistic lock over postgres serializable isolation level?

2 Answers