Understanding Inventory Table Locks

In an active inventory environment, the system may be processing orders from multiple sources as users initiate requests using both online pages and background processes. This can lead to table contention issues as more than one process needs access to the same table at the same time. For example, two separate process instances could run against the same material stock request order number at the same time, both needing access to this row in the demand table. PeopleSoft Inventory uses a table locking feature and temporary tables to control balance table access.

In order to lock a demand line in the fulfillment table (IN_DEMAND), a row is inserted into the IN_DEMAND_LOCK table. A record only exists on IN_DEMAND_LOCK when the record is locked for update processing. All processes sensitive to locking will check the existence of a row in this table. When the lock is removed, the field LAST_PROCESS_INST on IN_DEMAND is updated with the current process instance and then the row is deleted from the IN_DEMAND_LOCK table.

On the Setup Fulfillment-Fulfillment Tasks page, you can select the Release Additional Locks check box to instruct the fulfillment engine to release any locks not identified by the first processing group of data determined at runtime. When a batch job starts it sometimes selects and locks rows beyond what can actually be processed after further validation. This check box releases the locks on rows that are de-selected and will not actually be processed by the job. If this check box is not selected, then all fulfillment transaction requests and demand lines meeting those requests are soft locked until they are finished processing. When the locks are released using this option, both fulfillment transaction requests and demand lines that were not included in the initial processing group are released to be processed by concurrent fulfillment engine processes.

PeopleSoft Inventory uses two types of table locks:

• Soft Locks are applied to a row within the table when a process is using the row.

  The existence of a row on the Demand Lock table for the demand line indicates that the row is in process. Soft locks alert other processes that the data is being worked on.

• Hard Locks are applied when a completed process is updating the row in the live production tables, not the temporary tables.

  This is a database level lock that potentially blocks other SQL processes by obtaining exclusive use of the row. The system attempts to hold hard locks for the shortest time possible.