Database Locking Inquiry

In a multi-user network environment, the different operators using the software need to be able to share the data files.
When a user is updating a record in a data file, other users may view that file, inquire and generate reports from that file, but they may not make changes to that same record in the file, at the same time.
The software has to be able to identify and save any changes, before another user can update the same record.

If a user attempts to write to a record being updated by another user, a warning is displayed at the bottom of the screen:

WAITING FOR DATABASE RESPONSE - FILE: RSKF
or
RECORD LOCKED - WAITING - FILE: ARCF

Example:

ARCF (Customer File) in this example is the filename being locked, preventing a Customer Payment from being accepted, because another session had the same customer record open.
It is important to note this file name (ARCF) displayed in the error lock message at the bottom of the screen.

Use this Database Locking Inquiry utility to identify the operators currently locking a specific Oracle, MSSQL, or Pro-Isam file.
Then approach the identified users to request that they complete activity and free up the locked data file. When the record is released, the warning message will disappear from the screen and your program will continue.

Note: To prevent unnecessary file locking, it is recommended that users always exit to the menu screen when a task is completed, and do NOT leave any screen sitting in program option unnecessarily.

The prompts in this record locking inquiry are:

FILE LOCK BASE ON

Select one of the Database or Terminal locking situations and proceed as follows:

• Database Locks

  Click on Database in the radio group if a data table or file is locked.
  Oracle tables, MSSQL tables, and Pro-Isam files can both experience locking situations.
  The prompts that apply to a search for a 'Database' lock include:

  ALL DATABASE TABLES

  Check this box to look for locks on all Oracle or MSSQL tables.
  

  Note: This prompt applies only to database locks and does not apply to Pro-Isam locks as Pro-Isam lock searches must be based on a specific file name.

  When a database lock search is requested, a listbox is generated that displays all database locks regardless of whether or not a session is "Waiting" for the file, so this could be quite a large list on busy systems.

  The database lock results includes the Object Locked (e.g. ARCF), locking operator, SRM username, Operating System username, Terminal ID, Process ID, Session ID, IP address, and all the same values for the Waiting operator.

  Double click on any record to drill down to the detail information of that locked session.
  The following example information displays about the user locking a record in ARCF:

  Record Lock - ARCF
          Waiting                          Locking
                Operator: LLB DIV3            Operator: XXX DIV2
            TSI Username: Laurel          TSI Username: Start-Up
            O/S Username: lbates          O/S Username: tslomer
             Terminal ID: pts/2            Terminal ID: pts/3
                  Process ID: 26925         Process ID: 19575
                  Session ID:   353         Session ID:    15
          Oracle Serial#: 49679         Oracle Serial#: 36583
  
          Database Information
                   Object Locked: "RS+SLOT#589830  WRP#2627"
                    Table Locked:  ARCF
  
          SQL Command
          SELECT  ARCF_CUST_N  FROM  ARCF  WHERE  ARCF-CUST_N  = :P04194  FOR  UPDATE
  

  No Locks:
  If no database locks are found, the following message is displayed:
  No locks have been found based on the search criteria.

  Uncheck this box to look for a lock on a specific file.
  If it is a Pro-Isam file that is locked, the specific file name must always be entered.

  DATABASE TABLE NAME

  This field is only enabled if the search on All Database Tables is not selected, in order to search for a lock on a specific file or on a Pro-Isam file.
  Enter the table name displayed in the warning message on the locked screen.
  Example file locks could be:

          ARCF - Customer Information
          RSKF - Kit File
          RSPF - Product File
          APVF - Vendor Information
          RSCH - Contract Header File
          RSIH - Current Invoice Header File
          RSRH - Reservation Header File
          etc.
  

  Lock Results:

  • Locked Pro-Isam file or a locked document:

    Operator   SRM Username              O/S Username    Terminal
    --------   ------------              ------------    --------
    LLB DIV3   Texada Software            lbates          pts/2
    

  • Locked Oracle or MSSQL table:
    The same format of information is displayed as outlined above when All Database tables are searched.

  • Export:
    Click the EXPORT button to output the locking information to a spreadsheet.

  • No Locks:
    If no locks are found, the following message is displayed:
    No locks have been found base on the search criteria.

• Terminal Locks

  Click on Terminal in the radio group to view the locking information for a specific terminal session.

  TERMINAL ID

  This field is only enabled if the option to check for a locked Terminal is selected.
  Enter the relevant terminal id.

  The Terminal ID's used by a session is displayed on the black bar at the bottom of the screen, beside the Division.
  It can also be determined by clicking on Help in the menu bar at the top of the GUI session. The click on About ProIV and scroll down.
  e.g. pts_2

  Note: Although the format of the Terminal ID in the Help->About ProIV, is displayed with an underscore, this is converted to a slash for LINUX to recognize.
  e.g. pts_2 is translated to pts/2

  When a terminal is Waiting for a locked file, or is Locking a file, the record locking information is displayed as previously described for a All Database Tables Oracle lock.

  If no locks are found, the following message is displayed:
  No locks have been found base on the search criteria.

ACTIONS:
Additional actions provided by buttons on the Database Locking Inquiry screen include:

• Generate Lock Utility
  Sometimes it is difficult to identify the source of a locked file when a date record is in use and cannot be accessed by a second session, but because the second session is prevented access no actual lock is registered by the software.
  Click GENERATE LOCK button to force a lock on a specific file, that can then be identified and viewed in the Database Locking Inquiry as follows:
  • In the resulting Generate Database Lock window, a list of potential Records To Lock is listed.
    Double click on the type of record to lock, or highlight the record and the click SELECT.

  • A pop-up Generate Lock window will appear prompting to enter the specific data record to be locked.
    e.g. Customer#, Document Number, Product

  • If a lock is found on the specific record, the Generate Database Lock window will be frozen in a forced lock condition.
    This indicates that the specific data record is in use somewhere on another session. The forced lock will remain locked until the other session releases the record.

    To identify the source of the lock, run the Database Locking Inquiry on a different session of the software, using the Oracle Database - ALL DATABASE TABLES option, as previously explained.

  • If no locks are found, the following message is displayed:
    This record is not currently being locked by another user.

• View Session Information:
  Click the SHOW USERS button to launch a list of all current SRM sessions with available information including the link between the pts# and the Citrix Username which is the key data that is not available at the Linux OS level.
  Users sitting on the sign in screen are included in this list.

  • The My Sessions check box displays only your own sessions, which includes sessions that originate from the same computer, matching the IP address or browser token, Linux login name, workstation name, and windows login name if not using a browser.

  • The All Environments option displays sessions for all companies.
    On Windows, environments are determined by the Program and Data directories.
    On Linux, this checkbox is only enabled for the root user, and the environments are then determined by the Oracle Database credentials.

  On Linux systems, there is also a TERMINATE button to terminate the selected session.
  When logged in as root any session can be terminated, otherwise only your own sessions can be terminated.

  Note: In SaaS systems, the Terminate User Processes flag in the "Miscellaneous" window of the Security Role Permissions controls whether the operator has the ability to kill sessions for the same Linux user ID even if they are for different people.
  This allows SaaS users or Open Client users to kill sessions for other users within the same company.