Managing Inventory Table Locks

This chapter provides an overview of inventory table locks and discusses how to release locks on inventory tables.

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.

Releasing Locks on Inventory Tables

The Release Inventory Locks page is intended for system administrator use only. Administrators can use this page to release the soft locks placed on inventory tables by a process that ended abnormally. The tables are released by deleting the demand line rows from the demand lock table and resetting the status on process scheduler. The process status is automatically set to Canceled when the soft locks are released. This cancellation prevents potential data corruption that could occur without releasing the appropriate soft lock. Soft locks for processes that are currently running with a status of Processing cannot be released using this page. These processes must first be canceled from PeopleSoft Process Scheduler. Once they are in a Canceled, Deleted, or Not Successful status, then soft locks can be released using this page.

The Release Inventory Locks page works on the following tables:

• IN_DEMAND_LOCK

• BCT_CTL

• STAGED_INF_INV

• STAGED_ITEM_INV

Page Used to Release Locks on Inventory Tables

Using the Release Inventory Locks Page

Access the Release Inventory Locks page (Inventory, Release Inventory Locks).

Select Lines to Release Soft Locks

This section displays the results of the search criteria. To release the locks on the inventory tables, locate the process that you want to unlock, select the check box and save this page.