Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-6207

Excessive DB lock contention resulting from readForUpdateQueryStr execution in GenericTableHandler

    Details

    • Support Ticket IDs:

      Description

      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:

      https://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                cgdrake Chris Drake
                Reporter:
                dhogan Dirk Hogan
                QA Assignee:
                Tinghua.Xu
              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: