Re: Hibernate / other ORM and pg_advisory_lock

> I'll drop a short note here when that testing is done, as I imagine it
> might be of interest to others.

I've been unable to uncover any issues with concurrent updates being
made by both Hibernate and plain 'ol psql to the following schema:

CREATE OR REPLACE FUNCTION zz_field_version() RETURNS trigger AS $$
BEGIN
IF tg_op = 'UPDATE'
AND new.oplock = old.oplock
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.oplock := new.oplock + 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. Intended to allow ORM optimistic locking to coexist
with normal DB transactional locking. Only suitable for use as a trigger
on UPDATE.';

CREATE TABLE ver (
id SERIAL PRIMARY KEY,
counter INTEGER NOT NULL,
oplock INTEGER NOT NULL DEFAULT(0)
);

COMMENT ON TABLE ver IS 'Test table for versioning';

CREATE TRIGGER ver_ensure_version_updated BEFORE UPDATE ON ver FOR EACH
ROW EXECUTE PROCEDURE zz_field_version();

Two concurrent updaters that ignore the oplock column completely, like
two copies of:

#!/bin/bash
for n in `seq 1 1000`; do
psql craig -c 'update ver set counter = counter + 1'
done

can run quite happily along with an instance or two of the Hibernate
test case. The hibernate test case, without all the tracing code and
more detailed error handling, boils down to:

// Assume pre-declared & set up EntityManager instance `em'
// and EntityTransaction tx , as well as appropriately mapped class
// Ver with an @Version annotation on the `oplock' field:
//
int ver_id = 0; /* Pre-created record with id 0, counter 0 */
int successfulUpdates = 0;
while (successfulUpdates < 1000) {
try {
Ver v = em.find(Ver.class, );
v.setCounter( v.getCounter() + 1 );
tx.commit();
successfulUpdates ++;
} catch (javax.persistence.RollbackException exc) {
// Retry
}
}

The value of `counter' for the test record in `ver' always ends up with
the total number of updates done by psql plus `successfulUpdates' * the
number of hibernate test instances. So, if there are 2 shell updaters
and 2 hibernate updaters running the counter always increases by exactly
4000 . In other words, no updates are getting lost.

If a counter is added to report retried updates in the hibernate tests
it becomes clear that there *are* conflicts being detected and retries
being performed, even if only one hibernate test is running along with
one or more psql loop tests. The trigger is doing its job.

A version that explicitly increments the version with
em.lock(v, LockMode.WRITE)
and restarts the transaction if lock acquisition fails also works fine
when several copies are run along with several looping manual updates.

That's hardly conclusive proof that nothing can go wrong, but it seems
to be pretty solid, and I can't *see* any way it can break. Well, the
trigger needs to handle version overflow, but other than that fairly
minor issue it looks OK.

Think I'm missing anything?

Being able to use a trigger to let existing apps coexist with the ORM
row versioning will make things a *lot* easier. Doubly so if I can get
rid of the relatively expensive row comparison, but I'm not sure that's
safe with Hibernate-specific lock modes like LockMode.UPGRADE yet.

--
Craig Ringer