UniVerse Database Active Record Locks Test

UniVerse record and file locks control access to records and files among concurrent user processes. To control access to records and files, UniVerse supports two levels of lock granularity:

  • Fine granularity of record locks
  • Coarse granularity of file locks

Granularity refers to the level at which a process or program acquires a lock. Record locks affect a smaller element, the record, and provide a fine level of granularity, whereas file locks affect a larger element, the file, and produce a coarse level of granularity. Lock compatibility determines what a user’s process can access while other processes have locks on records or files. Record locks allow more compatibility because they coexist with other record locks, thus allowing more transactions to take place concurrently. However these “finer-grained” locks provide a lower isolation level. File locks enforce a high isolation level, more concurrency control, but less compatibility. Lock compatibility decreases and isolation level increases as strength and granularity increase. This may increase the possibility of deadlocks at high isolation levels. Within each granularity level, the strength of the lock can vary. UniVerse supports the following locks (in order of increasing strength):

  • Shared record lock
  • Update record lock
  • Shared file lock
  • Intent file lock
  • Exclusive file lock

The locks become less compatible as the granularity, strength, and number of locks increase. Therefore the number of lock conflicts increase, and fewer users can access records and files concurrently. Weaker locks can always be promoted to stronger locks or escalated to a coarser level of granularity if needed.

Whenever users complaints regarding data inaccessibility increase, it is good practice for administrators to check if too many file/record locks are being held, and if so, what their strength is. The UniVerse Database Active Record Locks test provides administrators with this information, instantly! This test automatically discovers the types of locks currently active in the target UniVerse database and reports the count of locks held per type. From the lock types, administrators can quickly infer the strength of the locks. If too many strong locks are active on the database, then the detailed diagnostics provided by this test can be used to identify the processes holding the locks, the users who own the processes, and the file and records locked. This way, administrators can precisely isolate those processes that are unnecessarily holding the locks and can initiate measures to have those locks released.

Target of the Test: A UniVerse database server

Agent running the test: An internal agent

Output of the test: One set of results for every group lock mode currently active in the target database. The table below discusses the probable locking modes and what they represent:

Descriptor Description
RU Update record lock
RL Shared record lock
FS Shared file lock
IX Shared file lock with intent to acquire an exclusive file lock
FX Exclusive file lock
XU Exclusive lock set by CLEAR.FILE
CR Shared file lock set by RESIZE
XR Exclusive file lock set by RESIZE

 

Configurable parameters for the test
Parameter Description

Test period

How often should the test be executed.

Host

The host for which the test is to be configured.

Port

The port number at which the specified Host listens to. By default, this will be 31438.

Universe Shell Path

This test uses UniVerse shell commands to pull the desired metrics from the server. To enable the test to run these commands, provide the path to the bin folder of the UniVerse install directory. For example, for a Windows installation of UniVerse, the universe shell path can be: C:\U2\UV.  For a Unix installation, your specification can be: \usr\uv.

Eclipse Path

U2 DBTools include Eclipse-based tools for programming and administration. Eclipse is an integrated development environment (IDE). It contains a base workspace and an extensible plug-in system for customizing the environment. Eclipse is written mostly in Java and its primary use is for developing Java applications. Where Eclipse IDE is installed, the eG agent should be configured to use the Eclipse environment for executing the UV shell commands. In this case therefore, type the full path to the Eclipse home directory. By default however, this parameter is set to none, indicating that by default, the eG agent runs the commands from the UniVerse shell only and not Eclipse.

Detailed Diagnosis

To make diagnosis more efficient and accurate, the eG Enterprise embeds an optional detailed diagnostic capability. With this capability, the eG agents can be configured to run detailed, more elaborate tests as and when specific problems are detected. To enable the detailed diagnosis capability of this test for a particular server, choose the On option. To disable the capability, click on the Off option.

The option to selectively enable/disable the detailed diagnosis capability will be available only if the following conditions are fulfilled:

  • The eG manager license should allow the detailed diagnosis capability
  • Both the normal and abnormal frequencies configured for the detailed diagnosis measures should not be 0.
Metrics reported by the test
Measurement Description Measurement Unit Interpretation

Active record locks

Indicates the number of active file/record locks of this type.

Number

Compare the value of this measure across types to determine the strength of the majority of locks currently held.

If too many strong locks are held, you can use the detailed diagnosis of this measure to identify the processes that are holding the strong locks, the users who initiated the processes, the files and records that are locked, the file system in which the files exist, and the host from which the lock originated. With the help of this information, administrators can instantly isolate the processes that are holding the locks unnecessarily and the users who are running those processes. Such processes can later be killed or the lock they hold released to clear processing bottlenecks (if any).

The table below discusses how each type of locks impact user access:

Lock type (strength) Allows other users to acquire Prevents other users from acquiring Is ignored if the current user already owns

Shared file lock

  • Shared record lock
  • Shared file lock
  • Intent file lock
  • Update record lock
  • Exclusive file lock
  • Shared record lock
  • Update record lock
  • Shared file lock
  • Intent file lock
  • Exclusive file lock

Update record lock

No locks

  • Shared record lock
  • Update record lock
  • Shared file lock
  • Intent file lock
  • Exclusive file lock
  • Update record lock
  • Exclusive file lock

Shared file lock

  • Shared record lock
  • Shared file lock
  • Update record lock
  • Intent file lock
  • Exclusive file lock
  • Shared file lock
  • Intent file lock
  • Exclusive file lock

Intent file lock

Shared record lock

  • Update record lock
  • Shared file lock
  • Intent file lock
  • Exclusive file lock
  • Intent file lock
  • Exclusive file lock

Exclusive file lock

No locks

  • Exclusive file lock No locks
  • Shared record lock
  • Update record lock
  • Shared file lock
  • Intent file lock
  • Exclusive file lock
  • Exclusive file lock