A customer throughput issue revealed almost 1.3 second latency for the execution of the readForUpdate query in GenericTableHandler#readForUpdate against a PostgreSQL database. Note that this latency applied ONLY for the execution of the single query - not for its preparation or subsequent processing. The query is as follows:
SELECT obj.* FROM openidm.managedobjects obj INNER JOIN openidm.objecttypes objtype ON obj.objecttypes_id = objtype.id AND objtype.objecttype = ? WHERE obj.objectid = ? FOR UPDATE
Examination of the query through an explain statement revealed proper index use. Running this query against the customer database not in the context of a stress load revealed a <20ms execution time. Documentation here:
seemed to indicate that the FOR UPDATE clause will lock all rows from tables specified in the join clause - meaning the row in the objecttypes table corresponding to the managed user - which would be a row common to all queries. It was thus hypothesized that the ~1.3 second latency was primarily related to DB lock contention on the row in the managed object type table. The locking part of the FOR UPDATE clause is the pillar of IDM's MVCC implementation. The documentation at the link above also indicated that a OF sub-clause of the FOR UPDATE clause could explicitly specify which table whose rows should be locked, rather than locking the rows of all tables targeted by the query. Because the managed object type table is never updated as part of the UPDATE requests executed following the SELECT...FOR UPDATE, it made sense to exclude this joined row from the DB lock set.
Such more-targeted locking could thus possibly reap significant performance gains for managed object updates for all database types which support the OF sub-clause.
See the 'BBC 13559 Tech' HipChat room for more details.