'ODF-0107:Object Definition is locked for updates, please retry' error message.

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

This error message indicates that a Clarity lock has been placed on one of the Studio Objects.
It may or may not be on the object that was currently being updated; it could be a different object.

Resolution

Query to find which object has got a lock on the prlock table:

Show all Current locks:

• Oracle: 
  • SELECT *
    FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate';
• SQL Server:
  • SELECT *
    FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate';
• PostgreSQL
  • SELECT *
    FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate';

Show Locks Older Than 1 Hour

• Oracle: 
  • SELECT *
    FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate'
      AND PRLOCKEDSINCE < SYSDATE - (1/24);
• SQL Server: 
  • SELECT *
    FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate'
      AND PRLOCKEDSINCE < GETDATE() - (1.0 / 24);
• PostgreSQL
  • SELECT *
    FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate'
      AND PRLOCKEDSINCE < NOW() - INTERVAL '1 hour';

If any records are returned, go to the UI and verify that these objects should not be locked.

Do not delete locks without confirming that no users are actively performing updates on the object.

If the records remain in the PRLOCK table, the next step is to delete the specific PRLOCK records found.  

DB deletions should be done ONLY when the record cannot be released through standard application functionality.  

Go to the URL browser address field and modify the URL to reveal the locks page, e.g. https://server/niku/nu#action:security.locks

Select the lock type for Type = 'Object Update' only
and Click button (Clear Locks).

Queries to delete the object definition locks:

NOTE: Back up the PRLOCK table and test resolution in a non-production environment first.

FOR ON PREMISE IMPLEMENTATIONS ONLY.

It is strongly recommended to back up the affected data before performing any delete operation.

Back up queries:

• Oracle:
  • CREATE TABLE PRLOCK_BACKUP AS
    SELECT *
    FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
    AND PRNAME = 'objectUpdate';
  • Optional: AND PRLOCKEDSINCE < SYSDATE - (1/24);
• SQL Server:
  • SELECT *
    INTO PRLOCK_BACKUP
    FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
    AND PRNAME = 'objectUpdate'; 
  • Optional: AND PRLOCKEDSINCE < GETDATE() - (1.0 / 24);
• PostgreSQL:
  • CREATE TABLE prlock_backup AS
    SELECT *
    FROM prlock
    WHERE prtablename = 'ODF_OBJECTS'
    AND prname = 'objectUpdate';
  • Optional: ... AND prlockedsince < NOW() - INTERVAL '1 hour';

If you want to back up the data that is older than an hour, just append the optional condition to the SELECT statements above.

Deleting all current locks:

• Oracle
  • DELETE FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate';
• SQL Server
  • DELETE FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate';
• PostgreSQL
  • DELETE FROM prlock
    WHERE PRTABLENAME  = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate';

Deleting locks older than 1 hour:

• Oracle
  • DELETE FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate'
      AND PRLOCKEDSINCE < SYSDATE - (1/24);
• SQL Server
  • DELETE FROM PRLOCK
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate'
      AND PRLOCKEDSINCE < GETDATE() - (1.0 / 24);
• PostgreSQL
  • DELETE FROM prlock
    WHERE PRTABLENAME = 'ODF_OBJECTS'
      AND PRNAME = 'objectUpdate'
      AND PRLOCKEDSINCE < NOW() - INTERVAL '1 hour';

Additional Information

Understanding Clarity PRLOCK Table

The PRLOCK table is used to prevent concurrent editing. The feature restricts 2 or more users from editing the same field in the database at one time.

All of them are legitimate locks, and should clear automatically, but the lock can not be released if the user transaction does not complete.

More information can be found at:
https://community.broadcom.com/communities/community-home/digestviewer/viewthread?MID=744933#bm25d3d2b5-4918-46b2-b815-bfb665fde9b2