E-PT: Locking at Database Level on PSLOCK and/or PSVERSION Tables (Doc ID 1951231.1)

Last updated on JULY 05, 2016

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.49 and later
Information in this document applies to any platform.
What we are trying to do is to allow multiple processes access these tables without locking it up.
The issue you are experiencing is not a bug or a problem. It is working as desired except that any time a select for update is issued, the dbms places an exclusive lock thus preventing other sessions to perform any kind of work.

Two parameters are responsible: INITRANS which is the initial number of concurrent transactions that access one block, and MAXTRANS which is the maximum number of concurrent transactions that may access the same block.
For example, if a table is very large and only a small number of users simultaneously access the table, the chances of multiple concurrent transactions requiring access
to the same data block is low. Therefore, INITRANS can be set low, especially if space is an issue with the database.

The MAXTRANS is already 255 but INITRANS is 1 which now we are setting to 16 thus allowing a higher concurrency.

Goal

 With large number of processes queuing up a significant delay is observed in processing because of locks on PSLOCK and PSVERSION tables.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms