Hibernate oplocks
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.
For example, given a table:
CREATE TABLE tab (
id INTEGER PRIMARY KEY,
somefield text not null
);
INSERT INTO tab(id, somefield) VALUES (1,'fred');
The following sequence of operations (time line is downward):
| Hibernate | Other writer |
|---|---|
| BEGIN; SELECT id, somefield FROM tab WHERE id = 1; COMMIT; | |
| BEGIN; UPDATE tab SET somefield = 'foo' WHERE id = 1; COMMIT; | |
| BEGIN; UPDATE tab SET somefield = 'baz' WHERE id = 1; COMMIT; |
... 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:
CREATE TABLE tab (
id INTEGER PRIMARY KEY,
somefield text not null,
rowversion INTEGER NOT NULL DEFAULT(0)
);
INSERT INTO tab(id, somefield) VALUES (1,'fred');
| Hibernate | Other writer |
|---|---|
| BEGIN; SELECT id, somefield, oplock FROM tab WHERE id = 1; COMMIT; | |
| BEGIN; UPDATE tab SET somefield = 'foo' WHERE id = 1; COMMIT; | |
| BEGIN; UPDATE tab SET somefield = 'baz', rowversion = 1 WHERE id = 1 AND rowversion = 0; COMMIT; |
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.
CREATE OR REPLACE FUNCTION zz_row_version() RETURNS trigger AS $$
BEGIN
IF tg_op = 'UPDATE'
AND new.rowversion = old.rowversion
AND ROW(new.*) IS DISTINCT FROM ROW (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;
END IF;
RETURN new;
END;
$$ LANGUAGE 'plpgsql';
COMMENT ON FUNCTION 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 ).
By --Ringerc 05:11, 5 November 2009 (UTC) (Craig Ringer)
