Re: Hibernate / other ORM and pg_advisory_lock - Mailing list pgsql-jdbc
From | Craig Ringer |
---|---|
Subject | Re: Hibernate / other ORM and pg_advisory_lock |
Date | |
Msg-id | 47F9FC9E.7030206@postnewspapers.com.au Whole thread Raw |
In response to | Re: Hibernate / other ORM and pg_advisory_lock (Craig Ringer <craig@postnewspapers.com.au>) |
List | pgsql-jdbc |
> 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
pgsql-jdbc by date: