Hibernate oplocks

From PostgreSQL wiki

The Hibernate Java ORM tool uses optimistic locking implemented using field-based row versioning instead of using the database's built-in transactional row locking. It does this to permit very short transactions where the transaction need not be held open during user "think time", blocking other transactions waiting on held locks and causing problems with VACUUM. However, this means that Hibernate sessions do not block other database writers from making changes to a record between when Hibernate reads it and when Hibernate later writes a changed record with an incremented version counter in a different transaction.

... would result in the loss of the UPDATE by 'Other writer' and an end value for 'somefield' of 'baz'. Hibernate took a copy of the record, modified it, and wrote it back in a separate transaction - something that's a classic SQL no-no for exactly this reason. Hibernate has a built-in workaround for that issue, though, using row versioning for optimistic locking. With Hibernate's optimistic locking, we'd get:

Hibernate expects the rowversion to be incremented by any other write to the record, so it expects that here its UPDATE would fail because the 'rowversion = 0' part of the WHERE clause would fail to match. However, because the other writer doesn't know what Hibernate expects and hasn't updated the rowversion column, its changes get clobbered by Hibernate.

Rather than change all your other clients to know about row versioning just because you've introduced a Hibernate-based client, you can introduce a trigger that tests if the row version has been incremented during an UPDATE and, if it hasn't, ensures that the increment is applied.

CREATEORREPLACEFUNCTION zz_row_version() RETURNS TRIGGERAS $$
BEGINIF tg_op ='UPDATE'ANDNEW.rowversion = old.rowversion
ANDROW(NEW.*)ISDISTINCTFROMROW(old.*)THEN-- Row is being updated by an application that does not know-- about row versioning. It's changed data in the row, but hasn't-- incremented the version. We'll do that for it.NEW.rowversion :=NEW.rowversion +1;ENDIF;RETURNNEW;END;
$$ LANGUAGE 'plpgsql';COMMENTONFUNCTION zz_field_version()IS'Increments the record version if a row is changed by an update and ''its version was not incremented by the UPDATE issuer. Allows ORM rowvers oplocking like Hibernate to coexist ''with normal DB transactional locking. ''Target tables must have an integral not-null field named rowversion with a default of 0.';

( Change the version column name as desired - it's often called 'oplock' instead ).