# CHAPTER 12: Empress Locking

## 12.1 Introduction

The design of both databases and the applications that run on them is typically constrained by two needs:

1. to provide multiple processes with concurrent access to data, and
2. to maintain data integrity.

Since one of these objectives is usually achieved at the expense of the other, the challenge to Database Administrators and Application Developers is to reach a balance between them which best meets the needs of the system's users.

In most cases, data integrity is the primary concern. Processes which write to a database (e.g., insert, update, delete) have the potential to damage its integrity, and so must be regulated in their actions. With Empress, access to the database is mediated by the database engine, which governs a process's access to data, while maintaining its integrity by making the data secure from modification by any other processes. The mechanism which it uses to achieve this is known as locking.

The balance between process concurrency and speed which must be struck with locking is exhibited by processes which write to the database (either through insert, update or delete operations): any process performing these operations may cause data access restrictions to other processes, and hence can give rise to process bottlenecks. Empress allows users to tailor their applications so that these bottlenecks can be reduced by choosing locking schemes which are appropriate.

Database Administrators and Developers of database applications are the intended audience of this document, since the locking mechanism should ideally be invisible to the end user. This happy situation is achieved when:

- The Database Administrator (DBA) is able to effectively set up and maintain a database, and to tune that database to the demands put on it by users, and
- The Application Developer creates an application with the ideals of concurrency and responsiveness in mind.

By understanding locking in Empress, the trade-offs inherent in locking can be minimized, and solutions to lock contention issues can be realized.

In this document, we discuss locking in Empress as implemented in Version 8. Most other Empress versions (V6.x, V4.x) will be similar to what is described here, with differences occurring mostly in minor implementation details (such as new `MSxxx` variables, the timing of lock placement, etc.). Old versions of Empress (V1.x, V2.x) may differ in more substantial ways from what is described in this document.

This chapter covers the following topics:

- Basic Aspects of Locking:

  - lock categories and modes
    - user locks and table open modes
    - relational locks
    - physical locks
  - locking algorithm
  - Lock Managers

- Miscellaneous Aspects of Locking:

  - locking in the 4GL and AG
  - locking within transactions
  - DBA mode

- Managing Locks:

  - placing locks
    - explicit placement
    - implicit placement
  - clearing locks
  - creating Shared Memory Lock Managers

- Monitoring Locks:

  - lock monitoring tools
  - examples: locking used in simple SQL

- Tuning Empress Locking:

  - implicit locking schemes
  - locking parameters
  - Shared Memory vs. File Lock Managers
  - tuning the Lock Manager

## 12.2 Basic Aspects of Locking

In Empress, you can think of locks in any of three ways (categories) which correspond to rather broad divisions of the locking mechanisms on a logical and physical basis. The categories are as follows:

Table 12-1: Empress Locking Categories

Category | Description
Operational | Locks on the tables and views in a database's schema can be controlled by the user by specifying broad protocols for locking these entities. These determine what sorts of locks (at the relational category) Empress will place when implicit lock placement occurs. A protocol is chosen by the user to ensure a desired amount of data integrity; typically, a trade-off exists between increased data integrity and decreased process concurrency.
Relational | The locks in this category deal with such lower-level entities as data files, index files, and records. Locks are placed on tables explicitly or implicitly and operational category locks are translated into a set of relational locks on lower-level objects. While locks in this category should be somewhat transparent to the user, the issues of concurrency and efficiency often require the user to be aware of its workings.
Physical | Locks in this category are actually manifested as entries in a file or a shared memory partition.

The operational category defines a protocol by which locks are placed. The locks which get placed are described in the relational category, and the interaction between locks is determined by the physical category. The Lock Manager deals exclusively with physical locks, and so locking operation (and hence, conflict) can be understood as an interaction of physical locks. In turn, the presence of these physical locks can be understood from the relational and operational categories.

In general, while locks at the higher levels are implemented at the lower ones, locks at the lower levels cannot be associated directly with anything in the higher level: their operations are independent. For example, in the operational category there is the concept of table-level locking. However, in the relational category, there is no such entity as a table lock. Instead, there are collections of locks of various flavours which correspond to the concept of table-level locking.

### 12.2.1 Operational Category Locks

All locking in Empress is defined on a per-table basis. Whenever a table is opened (to read or update a set of records), the sets of locks which will be placed are determined by the implicit lock level of the table, or by explicitly opening the table at a specific lock level or in a specific mode. Refer to the section Managing Locks in this document for a summary of the more common user commands explicitly affecting locking.

The lock level determines the scope of locking applied to a table. It is specified by the table creator, and can be modified by any authorized user (refer to the `GRANT PRIVILEGE` command in the Empress SQL: Reference manual). The possible lock levels are summarized in the following table:

Table 12-2: Lock Level

Lock Level | Description
`record` | Each record is locked individually as needed; the lock is released when the next record is accessed.
`group` | All records in the `group` are locked; the locks are released when the next group is accessed. A group is the set of records which Empress must access to satisfy the `WHERE` clause. Note that in many cases this does not mean that just those records satisfying the `WHERE` clause are locked: if there is no indexing on the attributes in question, Empress must search the whole table for qualified attributes. In this case, group and table-level locking are identical.
`table` | When any table is opened (to access any number of records in that table), the entire table is locked.
`null` | When a table with null level locking is opened, no locks are placed on that table.

Note, however, that `record`, `group` or `table` locks do not exist per se: these locks are conceptual, and define a scheme of locking which is carried out by placing a series of locks from the relational category.

Another aspect of the operational category is the open mode of a table. The possible modes in which any table may be opened are:

Table 12-3: Open Table Lock Mode

Symbol | Mode | Description
`r` | read | A read lock will be placed on the table. Attempts to update, delete or insert will fail (since these require the table to have an update lock on it).
`d` | deferred | read locks are initially placed on a table. The table is not prevented from update, deletion or insertion operations. However, when these operations occur, the read lock which was initially placed on the table will be upgraded to an update lock. Once the operation is complete, this will be downgraded to a read lock.
`u` | update | An update lock will be placed on the table.
`n` | dirty read | No read lock will be placed (bypass any locks) when retrieving records. Attempts to update, delete or insert will fail.
`N` | deferred dirty read | An update lock will be placed on the table initially. If an update, insert or delete operation is performed, normal locking behavior applies. However, if a retrieval is performed, will get data bypass any locks.

### 12.2.2 Relational Category Locks

We begin by looking at the objects which are locked by Empress, since these are directly related to relational category locks.

In the course of servicing user requests, Empress must decide which objects it needs to access, and for what purpose (i.e., for reading or writing). For example:

       INSERT INTO table_name VALUES (1, "abcdefghijklmnopqrstuv");

Where the first attribute is indexed, and the second attribute is of type `TEXT`. This operation causes Empress to:

1. Open the table:

   - determine which `.rel` file table `table_name` corresponds to;
   - read `sys_dictionary` for a table name matching `table_name` (this implies using the index on attribute `dict_tabname`;
   - place an `ADMIN` `r` lock on the table to indicate that the table is in use (this prevents some other user from altering (`ALTER`) the table while you are accessing it).

2. Read the free record list of the table to find which free space in the `.rel` file can be used (or reused) for the insert;

3. Insert the record including insert the data into the overflow (`.dtf`) file, if necessary.

4. Update the free list;

5. Update the index;

6. Close the table:

   - release the `ADMIN` `r` lock on the table.

In general, Empress may require access to any of the following objects in the course of its operation; and for each object, certain types of relational category locks are used:

Table 12-4 Relational Category Locks on the Objects

Object | Lock Type
`.rel` files - table header (free record list) - records | `ADMIN` `CRIT` `RECORD`, `ALLRECS`
`.dtf` files | `CRIT`
`.ix` and `.ixl` files | `CRIT`

Of these, the table header deserves special explanation: it contains a pointer to the free record list, as well as a free record counter.

  Every table has a free record list (referred to as the table's free list), which is a (singly) linked list of records which can be used to store new data. Thus, as records are deleted from a table, pointers to the deleted records are added to the end of the list (thus, deleting records does not make a `.rel` file physically smaller) and the information stored in the `.rel` file header is updated. Similarly, inserting records to a table also makes use of the free list (thus, inserting records does not necessarily make a `.rel` file larger).

Any process which modifies a table's free list (for example, through insert or delete operations) must have exclusive access to it, since otherwise inconsistencies might arise which could compromise the integrity of the data. For this reason, Empress applies a `CRIT` lock during any of these operations.

Locks in the relational category are described in terms of their mode and type. There are two important points to be made regarding how lock type and mode affect conflict between locks:

1. Locks can be of various types, and locks of different types can never interfere. Only locks with the same type can cause locking conflict.

2. Within locks of the same type, the lock mode determines its compatibility with other locks.

The possible lock types are:

Table 12-5 Lock Types

Type | Description
`ADMIN` | These are placed to indicate that a table is being used (i.e., opened for some reason).
`CRIT` | These are placed whenever a time-critical resource is used. Examples of such resources are indices, table free lists, and table overflow files.
`RECORD_n` | These are placed whenever the nth record in the table is accessed; `n` is the physical record number in the table.
`ALLRECS` | These are placed for efficiency. They indicate that at least one record in the table is being accessed.

The possible lock modes are `r` and `rr` (for read operations), `u` and `uu` (for update operations). The compatibility of locks of the same type, but different mode, is given in the following table. Here, a `yes` at the intersection of column A and row B means that locks with modes A and B can co-exist. Similarly, `no` means that the desired lock B cannot co-exist with the existing lock A: the process desiring to place lock B must wait until the existing lock A is released by the process which placed it.

Table 12-6: Compatibility of Lock Modes of the Same Lock Type

Lock Mode |  | `rr` | `r` | `uu` | `u`
`rr` |  | `yes` | `yes` | `yes` | `no`
`r` |  | `yes` | `yes` | `no` | `no`
`uu` |  | `yes` | `no` | `yes` | `no`
`u` |  | `no` | `no` | `no` | `no`

Note that a lock of any type may have mode `r` or `u`. Only a lock of type `ALLRECS` may have mode `rr` or `uu`.

The `rr` and `uu` lock modes were introduced with the `ALLRECS` lock type. An `ALLRECS` lock is acquired by Empress when any operation is done on a table. It indicates the presence of an operation (i.e., read or update) somewhere in the table, and was introduced to make the (operational) implicit table-level locking scheme more efficient.

Under the table-level locking scheme, read and update operations will attempt to acquire `r` and `u` `ALLRECS` locks (respectively); under record-level locking, read and update operations will first attempt to acquire `rr` and `uu` `ALLRECS` locks (respectively). Examination of the lock mode compatibility table (above) will show that `u` locks cannot co-exist, whereas `uu` locks can. By using `ALLRECS` locks, Empress can implement a table-level locking scheme, as well as a record-level locking scheme, using the same physical locking mechanism.

Occasionally, you may see a mode reported (see the section Monitoring Locks in this document) as `rr|uu`. This will only occur for `ALLRECS` type locks, since the "`|`" symbol means that both types of locks (`rr` and `uu`) are present. Thus, an `ALLRECS` `rr|uu` indicator would mean that some records in the table had read locks (`r`) on them, while others had update (`u`) locks.

Empress places relational category locks by sending lock requests to the Lock Manager (see the subsection Physical Category Locks ). Lock requests are sets of statements to the Lock Manager to place or release one or more locks on a given table. For each operation it is asked to perform, Empress chooses which sets of locks it needs to place or release for each step of that operation, and then sends a request to the lock manager to carry out this set of locking operations.

Every lock request has one of three results:

a) | success,
b) | failure, because exclusive access to the Lock Manager was not attained,
c) | failure, because at least one of the locks requested to be placed was refused by the Lock Manager.

In case a), all the individual requests succeed. In case b), none of the individual requests succeed. In case c), if any individual lock placement is refused (because of a conflict detected by the Lock Manager), then all lock placements in the request will fail. However, all lock releases in the request will succeed.

For example: suppose the placement of an `ADMIN` and `CRIT` lock on table `table_name` occurs in a single lock request. If the `ADMIN` lock could be successfully placed, but the `CRIT` lock could not, then the entire request would fail, and neither lock would be placed.

For more details on which relational category locks get placed in response to various SQL queries, see the section Monitoring Locks in this document.

### 12.2.3 Physical Category Locks

The Empress Lock Manager deals exclusively with physical category locks. The task of the Lock Manager is to arbitrate the acquisition and release of locks. To the Lock Manager, a lock is simply an object with properties determining how it is acquired and released. Properties which are inherent to a lock include the following:

- the table number the lock is applied to
- the lock type
- the lock mode
- the record number (if applicable)
- the Empress ID of the process which owns the lock.

When the Lock Manager receives a request to place a lock, it gathers all this information and then checks whether the lock can be placed or not; the rules for determining this are given in the section Relational Category Locks.

If the lock cannot be placed, the Lock Manager will retry the operation through the Empress locking algorithm which is designed to avoid deadlock situations. A deadlock occurs when two or more processes are waiting to get records that the other has locked. Neither of the processes contending for a locked record will be able to continue unless they cooperate. Since any process which fails to acquire a lock (after repeated attempts) does not know whether the failure is due to a deadlock situation, it must release all its currently acquired locks to ensure that deadlock does not occur. By doing this, the records which it currently holds can be made available to other processes, and so prevent deadlocks in the system.

The locking algorithm is displayed in the figure below:

Figure 12-1: Locking Algorithm

The algorithm retries a number of times to acquire a lock, and on each failure to do so it waits a certain amount of time before retrying. The number of retries and the amount of time waited after each failure can be set by the System Administrator. There are eight relevant `MS` variables; four have names which are prefixed with `MS`, and four with `MSIA`. The `MSIAxxx` variables are for use in interactive environments, while the `MSxxx` variables are used in batch environments. All these variables are defined in `$EMPRESSPATH/config/initfile`:

`MSEXCLRETRY` | This controls the number of times the lock file open is attempted.
`MSLOCKRETRY` | This controls the number of times acquisition of the lock is attempted.
`MSEXCLSLEEP` | This specifies the amount of time to sleep (seconds) between attempts to open the lock file.
`MSLOCKSLEEP` | This specifies the amount of time to sleep (seconds) between attempts to acquire the lock.

For situations in which lock contention is expected to be fierce, you can set the `...SLEEP` parameters to different values for different processes in order to reduce the number of locking collisions.

In the special case of index files, Empress needs to set a critical read lock before proceeding. Two environment variables are provided to control behavior of Empress during the acquisition of this lock:

`MSINDEXRETRY` | Similar to `MSxxxRETRY` above, except it is used only during the acquisition of a critical lock needed for read access to an index file. If this variable is not set, the default value is 0 (zero).
`MSINDEXSLEEP` | Similar to `MSxxxSLEEP` above, except it is used only during the acquisition of a critical lock needed for read access to an index file. If this variable is not set, the default value is 1 second.

### 12.2.4 Lock Managers

Most versions of Empress allow two physical implementations of a Lock Manager: File Lock Managers, and Shared Memory Lock Managers. However, every Empress Lock Manager can be described in terms of:

1. the maximum number of processes which can access it concurrently, and
2. the maximum number of locks which it can hold.

Furthermore, these parameters can be adjusted by the user to meet the needs of the applications which access the database (see the section Tuning Empress Locking).

File Lock Managers are the default physical locking implementation. In a database `db`, there will exist a lock directory `db/_lock` which will contain a number of files with names like `xxxx.lck`. The numbers `xxxx` correspond to the table number in the table's `.rel` file name. Thus, if table `table_name` corresponds to `.rel` file `db/0092.rel`, then (assuming a File Lock Manager) its lock file will be `db/_lock/0092.lck`. If a File Lock Manager is removed, the next time that Lock Manager is needed to hold a lock, it will be automatically created.

You can choose to put the locking for any given table into shared memory. Unlike File Lock Managers in which every `xxxx.lck` file corresponds one-to-one with a table, Shared Memory Lock Managers can contain locks for more than one table. Also unlike File Lock Managers, in the event of a system failure the shared memory partitions holding the Lock Managers must be re-created when the machine is restarted.

Shared Memory Lock Managers are created through the Empress utilities `empadm` and `empshm`. For details on how to use these utilities, refer to the chapter Shared Memory of this manual.

## 12.3 Miscellaneous Aspects of Locking

### 12.3.1 Locking and Transactions

During a transaction, Empress places locks in exactly the same way as it does outside of a transaction. That is, a given SQL command (for example) on a particular table will result in the same sequence of lock placement requests (that is, the locks in each request are the same, and the requests come in the same order). However, Empress behaves differently inside a transaction in two important respects:

1. the sequence of lock release, and
2. the acquisition of locks at the end of a transaction.

During a transaction, the sequence of lock release is different from normal behavior of Empress in that most locks which are placed during a transaction are maintained for the duration of that transaction. The exception to this rule is:

- all locks of type `CRIT`: these locks get released immediately after use.

The end of a transaction is signalled by either `ROLLBACK` or `COMIT`, and behavior of Empress at these points includes accessing the locking mechanism. When Empress does a rollback, the following locking actions occur:

- acquire a `CRIT` lock on tables where certain operations have occurred during the transaction:

  - insert, delete: all cases,
  - update: in cases of updated attributes having an index, or bulk/text attributes which have been updated,

- undo any operations which have occurred on these tables,

- release these `CRIT` locks,

- release all remaining locks held by the transaction.

In the case of a commit, Empress takes the following locking actions:

- acquire `CRIT` locks on all tables accessed during the transaction, if any write operations have occurred on any table.

- update the header information in these tables (e.g., the free record list).

- release these `CRIT` locks,

- release all remaining locks held by the transaction.

The environment variable `MSTRANSTABLELOCK` is available for ensuring serializability of transactions. By default, it is turned off since its use incurs a strong concurrency penalty. Effectively, turning this variable on causes Empress to treat any tables involved in a transaction as if they have table-level locking. If transactions are not used, this variable has no effect.

When `MSTRANSTABLELOCK` is turned on, and a transaction is begun, Empress places `ALLRECS` locks on every currently open table. For every table opened during the transaction, these locks are also applied. The modes of these locks depend on the open mode of the table:

table `OPEN` mode |  | `r` | `d` | `u`
`ALLRECS` mode |  | `r` | `u` | `u`

Only when the transaction is committed or rolled back are these locks released.

### 12.3.2 Single User DBA Mode

An Empress database can be temporarily disabled by the Database Administrator (DBA) by issuing the command:

       empadm database_name singlemode

This command puts an `ADMIN` `u` lock on `sys_dictionary`, which prevents other users from accessing the database. This is because any database access needs to open `sys_dictionary`, and hence an `ADMIN` `r` lock on `sys_dictionary` is required: this lock will not be obtainable, since it will conflict with the `ADMIN` `u` lock placed by entering `singlemode`. Conversely, if any user is accessing the database, that user will have obtained an `ADMIN` `r` lock on `sys_dictionary`, and so the DBA will not be able to enter `singlemode` since the required `ADMIN` `u` lock will not be obtainable.

Note that when you are in DBA mode, you should not issue a global lock clearance command (such as `empadm database_name lockclear`), since this will effectively take you out of Single User DBA mode.

### 12.3.3 Locking in the 4GL and AG

The Empress 4GL and Application Generator (AG) require special behavior with regard to locking. There are two requirements:

1. user applications (which are stored in the `sys_4gl_xxx` and `sys_ag_xxx` tables) need to be executable even when the application is being modified;

2. if an application is being modified by one user, no other user should be able to modify it at the same time.

Since the executable 4GL application and the application's definition (i.e., source code) are stored in different tables, Empress' record-level locking mechanism allows requirement (1) to be met. The normal Empress locking mechanism is insufficient, however, for the second requirement.

4GL applications are stored as sets of records in the following tables (there is a similar set of tables for the Application Generator; see the manual Empress 4GL: Administrator's Guide):

|  |  | `sys_4gl_application sys_4gl_compile sys_4gl_domain` | `sys_4gl_field sys_4gl_form sys_4gl_key` | `sys_4gl_link sys_4gl_script sys_4gl_window`

Because 4GL applications are stored in this distributed fashion, the usual Empress locking scheme is not capable of ensuring that only one user will modify any part of a given application. Thus, an additional locking mechanism is needed.

Empress provides 4GL and AG locks, though these are not "locks" in the sense discussed above. These locks are simply attributes in the `sys_4gl_application` and `sys_ag_definition` tables. They are not examined through locking mechanism of Empress, but are instead examined by the 4GL and AG applications themselves. Thus, the 4GL and the AG impose a second level of locking on top of standard locking mechanism in Empress.

The attributes which represent locks in the 4GL and AG are lock in both `sys_4gl_application` and `sys_ag_definition`. These attributes are set to `y` if a user is editing an application. When set, another user would not be allowed to edit the application, but would be able to execute it.

To clear these locks, the user can use

       empclrlk -ag Database_name

       empclrlk -4gl Database_name

The `empclrlk` utility nulls the lock attribute in the appropriate table and, in the case of `-ag`, empties various `sys_ag_xxx` tables. Note that this utility also clears any regular database locks.

## 12.4 Managing Locks

Empress can place locks implicitly or explicitly in a database. Implicit lock placement is used in most situations, since the details of lock placement/removal should be transparent to the user. However, Empress also allows users to place locks explicitly in some of its interfaces such as: Empress SQL, Empress 4GL and the mr C-programming layer.

The Report Writer, Fortran Interface, Embedded SQL (static and dynamic), and mx C programming interface do not provide an explicit lock placement mechanism.

The behavior of implicit lock placement scheme in Empress can be modified by certain environment variables.

In the normal progress of events, any locks which Empress places also get released. However, it sometimes happens that locks get left behind (usually, this is the result of a process which died and did not get a chance to clean up the locks it had placed), and this can cause problems for other processes by preventing them from accessing data. Because of this, it is sometimes necessary to clear locks manually, and this is done with the help of the `empadm` utility.

Finally, locks usually can be placed in either File or Shared Memory Lock Managers. File Lock Managers are the default, and are created at the time of table creation. Shared Memory Lock Managers are set up by the DBA after table creation, and must be re-created when your machine is rebooted.

### 12.4.1 Explicit Locking

The following is a synopsis of the most common user commands explicitly concerned with locking:

SQL: |
`LOCK LEVEL [ON] table [IS]` | `|TABLE |GROUP [(n)] |RECORD [(n)] |NULL` | `|; | | |`
this command sets the implicit lock level of a table.
`LOCK table [IN]` | `|EXCLUSIVE |EXCL |SHARE` | `| [MODE]; | |`
this command has effect only during a transaction. `EXCLUSIVE` or `EXCL` mode acquires an `ALLRECS` `u` lock on the table. `SHARE` mode acquires an `ALLRECS` `r` lock on the table. this is released at the end of the transaction. this is effectively a table-level read/update lock.
4GL: |
`LOCK table [IN]` | `|EXCLUSIVE |EXCL |SHARE` | `| [MODE]; | |`
this command is identical to the SQL version (above). this is effectively a table-level read/update lock.
mr: |
    mrlkrec (record_descriptor)
acquires a `RECORD_n` `r` or `u` lock on the record, if possible. this is effectively a record-level read/update lock.
    mrlktab (table_descriptor)
acquires an `ALLRECS` `r` or `u` lock on the table, if possible. this is effectively a table-level read/update lock.
    mrulrec (record_descriptor)
releases a `RECORD_n` `r` or `u` lock on the record, plus any `ALLRECS` lock on the table if the `RECORD_n` lock which was released was the only outstanding record lock.
    mrultab (table_descriptor)
releases the `ALLRECS` `r` or `u` lock on the table, if it exists. The lock mode which is acquired depends on the open mode of the table. Recall that the possible table open modes are `r` (read), `d` (deferred), `u` (update), `n` (dirty read) and `N` (deferred dirty read). For more information, refer to : Empress: SQL Reference, Empress: C Kernel Level Interface - mr Routines Empress 4GL Language Reference manuals, or Basic Aspects of Locking in this document.

### 12.4.2 Implicit Locking

When implicit locking is in effect, the locks which get placed depend on the following factors:

1. the implicit lock level of the table,
2. the open mode of the table,
3. the operation being performed, and
4. various Empress `MSxxx` system variables.

Operations in Empress can be divided into two broad categories:

- data manipulation language (DML) commands which retrieve or modify records stored in a database, and
- data definition language (DDL) commands which define and modify the table or database structures themselves.

For this discussion, we will further classify commands in the DML and DDL groups as being `update` or `read` operations.

Examples of these types of commands for some of interfaces in Empress are:

- |  | SQL Interface | mr Program | 4GL Interface
DML-Update |  | `INSERT DELETE UPDATE` | `mradd () mrdel () mrput ()` | `insert delete update`
DML-Read |  | `CREATE TABLE DROP TABLE ALTER TABLE CREATE INDEX DROP INDEX` | n/a | n/a
DDL-Read |  | `DISPLAY TABLE` | n/a | n/a

For each of these broad categories of operations, a number of implicit locks will be placed. The following table is an example showing which locks could be placed through the Interactive SQL Interface (note that some tables may not have indices, and hence would not place `CRIT` locks to flag usage of the `.ix` and `.ixl` files), but do not indicate the order of the operations (see the section Monitoring Locks in this document for examples of this).

DML Operation, Record-level locking:

Operation Category
Object Being Locked | Type | Mode
DML-Update |
`.rel` file | `ADMIN` | `r`
`.rel` file | `ALLRECS` | `uu`
`.rel` file header | `CRIT` | `u`
`.ix`, `.ixl` files | `CRIT` | `u`
`.dtf` files | `CRIT` | `u`
`.rel` file record | `RECORD_n` | `u`

DML-Read

`.rel` file | `ADMIN` | `r`
`.rel` file | `ALLRECS` | `rr`
`.rel` file header | `CRIT` | `r`
`.ix`, `.ixl` files | `CRIT` | `r`
`.dtf` files | `CRIT` | `r`
`.rel` file record | `RECORD_n` | `r`

DML Operation, Group-level locking:

Operation Category
Object Being Locked | Type | Mode
DML-Update |
`.rel` file | `ADMIN` | `r`
`.rel` file | `ALLRECS` | `uu`
`.rel` file header | `CRIT` | `u`
`.ix`, `.ixl` files | `CRIT` | `u`
`.dtf` files | `CRIT` | `u`
`.rel` file record | `RECORD_n` | `u`

DML-Read

`.rel` file | `ADMIN` | `r`
`.rel` file | `ALLRECS` | `rr`
`.rel` file header | `CRIT` | `r`
`.ix`, `.ixl` files | `CRIT` | `r`
`.dtf` files | `CRIT` | `r`
`.rel` file record | `RECORD_n` | `r`

DML Operation, Table-level locking:

Operation Category
Object Being Locked | Type | Mode
DML-Update |
`.rel` file | `ADMIN` | `r`
`.rel` file | `ALLRECS` | `u`
`.rel` file header | `CRIT` | `u`
`.ix`, `.ixl` files | `CRIT` | `u`
`.dtf` files | `CRIT` | `u`
`.rel` file record | `RECORD_n` | `u`

DML-Read

`.rel` file | `ADMIN` | `r`
`.rel` file | `ALLRECS` | `r`
`.rel` file header | `CRIT` | `r`
`.ix`, `.ixl` files | `CRIT` | `r`
`.dtf` files | `CRIT` | `r`
`.rel` file record | `RECORD_n` | `r`

DML Operation, Null-level locking:

Operation Category
Object Being Locked | Type | Mode
DML-Update |
`sys_dictionary` | - | `r`
DML-Read |
`sys_dictionary` | - | `r`

DDL Operations:

Operation Category | Object Being Locked | Type | Mode
DML-Update
       create/drop comment
       create/drop index
       create/drop range check
       create/drop referential
       create/drop table
       create/drop view
       grant/revoke privilege
       lock level
       lock table
       rename table
| `sys_dictionary` | - | `u`
DDL-Read
       display db
       display privilege
       display table
| `sys_dictionary` | - | `r`

The system tables (`sys_dictionary`, `sys_tables`, `sys_attrs`, `sys_privs`, and `sys_attr_privs`) are assumed to have their default lock levels (i.e., `RECORD`) in the above. If this is not the case (for example, the DBA can set the lock level on `sys_tables` to `NULL`), then certain operations will place locks differently from the description above.

The `sys_dictionary` table is created and maintained by Empress. It is a compiled version of `sys_tables`, `sys_privs`, `sys_attrs`, and `sys_attr_privs` tables.

In the DML operations, `sys_dictionary` is always accessed first since it stores the location and definition of the table(s) being queried or updated. Also, since table `sys_dictionary` is an Empress table like those created by the user, it too has implicit locking which occurs whenever it is accessed. The implicit locking which is applied to `sys_dictionary` is as follows:

Operation Category
Type | Mode
DML-Update |
`ADMIN` | `r`
`CRIT` | `r`
`RECORD_n` | `r`
DML-Read |
`ADMIN` | `r`
`CRIT` | `r`
`RECORD_n` | `r`
DDL-Update |
`ADMIN` | `r`
`CRIT` | `r`, `u`
`RECORD_n` | `u`
DDL-Read |
`ADMIN` | `r`
`CRIT` | `r`
`RECORD_n` | `r`

where `RECORD_n` is the nth record in `sys_dictionary`, which corresponds to the `.rel` file number of the table involved in the operation. The `.rel` file number of a table is stored in attribute `tab_number` of the system table `sys_tables`. For example, suppose you have a database with a table `tab`. Then do the following select to find what table number this corresponds to:

       SELECT tab_number FROM sys_tables WHERE tab_name = 'tab';

and suppose that `tab_number = 19`. Then, the `sys_dictionary` locking will show locks of type `RECORD_19`, indicating that the 19th record in `sys_dictionary` is being accessed (since this record contains the information for table `tab`).

Certain Empress operations (such as `ALTER` `TABLE`) transparently change the `tab_number` of a table, so you should not treat it as a fixed property of a table.

The open mode of a table affects implicit locking by placing different types of `ADMIN` locks on the opened table at the time of open. The C programming interfaces and the 4GL also allow the user to specify the open mode of a table.

### 12.4.3 Environment Variables

A number of `MSxxx` environment variables affect the behavior of the locking mechanism. Some of these variables cause Empress to change the timing (rather than the order) of how locks are acquired and released. As such, variables like `MSLKCOMMITADDEND`, can be viewed as minor optimizing switches. Other variables (such as `MSDBLOCKLEVEL`, `MSDBDICTLOCK`) change which locks get placed implicitly.

The following are brief descriptions of the Empress variables related to locking. Included are: where the variables are usually set, the name of the variable, and a brief description. Although many Empress variables can be set in more than one place (with an environment setting typically overriding a setting in the `$EMPRESSPATH/config/initfile`), we list here the places where the variables are most often set: in the environment, in the `$EMPRESSPATH/config/initfile`, or in data dictionary `tabzero`.

Name | Where | Description
`MSDBDICTLOCK` | `tabzero` | Can be set to `NULL`, `RECORD`, `GROUP` or `TABLE` level locking. It is the implicit locking level for `sys_dictionary`, and overrides the lock level shown in "`DISPLAY sys_dictionary all`" command.
`MSDBLOCKLEVEL` | `tabzero` | This is the implicit lock level assigned to new tables. Can be set to `NULL`, `RECORD`, `GROUP` or `TABLE`.
`MSDBLOCKSTATS` | `tabzero` | If set to anything, this causes Empress to accumulate statistics on locking, which then can be reported using the `empadm database_name lockstats` command. It is unset by default.
`MSEXCLRETRY` | `initfile` | This controls the number of times the lock file open is attempted.
`MSEXCLSLEEP` | `initfile` | This specifies the amount of time (in seconds, and with microsecond precision) to sleep between attempts to open the lock file.
`MSFILELOCKMAXPROCS` | `initfile` | The maximum number of processes that are allowed access to the File Lock Manager simultaneously. This value cannot normally be raised by the user, as its maximum value (the default) is determined by the Empress licence.
`MSFILELOCKNBUCKETS` | `tabzero`, `initfile` | The number of hash buckets used by Empress to store the locks in a lock file.
`MSFILELOCKNLOCKS` | `tabzero`, `initfile` | The maximum number of locks that may be stored in a single lock file at a given time.
`MSIAEXCLRETRY` | `tabzero`, `initfile` | Interactive version of `MSEXCLRETRY`.
`MSIAEXCLSLEEP` | `tabzero`, `initfile` | Interactive version of `MSEXCLSLEEP`.
`MSIALOCKRETRY` | `tabzero`, `initfile` | Interactive version of `MSLOCKRETRY`.
`MSIALOCKSLEEP` | `tabzero`, `initfile` | Interactive version of `MSLOCKSLEEP`.
`MSINDEXRETRY` | `tabzero`, `initfile` | Like `MSLOCKRETRY`, except applicable only to the acquisition of index critical locks for read operations.
`MSINDEXSLEEP` | `tabzero`, `initfile` | Like `MSLOCKSLEEP`, except applicable only to the acquisition of index critical locks for read operations.
`MSLKCOMMITADDEND` | `tabzero` | When set, causes the timing of lock placement to be more efficient for a sequence of insert operations when the table (to which the records are added) has record-level locking.
`MSLOCKPLAN` | environment | When set, lock placement/release is shown. When set to "`t`", the time of lock placement is also shown.
`MSLOCKRETRY` | `tabzero`, `initfile` | This controls the number of times acquisition of the lock is attempted.
`MSLOCKSLEEP` | `tabzero`, `initfile` | This specifies the amount of time (in seconds, and with microsecond precision) to sleep between attempts to acquire the lock.
`MSLOCKSTATS` | environment | When set, locking statistics for the process in question are reported when the process finishes.
`MSPARTLOCKNAME` | `tabzero` | Shared Memory Lock Manager partition name.
`MSPARTLOCKNBUCKETS` | `tabzero` | Number of hash function buckets in a Shared Memory Lock Manager.
`MSPARTLOCKNLOCKS` | `tabzero` | Total number of locks available in a Shared Memory Lock Manager.
`MSTRANSTABLELOCK` | `tabzero`, `initfile` | When set, this ensures serializability of transactions, at the cost of reduced process concurrency. When a transaction starts, all currently open tables have `CRIT` and `ALLRECS` locks applied; any tables opened during the transaction also have these locks applied. These trapped locks are released when the transaction is committed.
`MSTRANSUFNGFL` | `tabzero` | This is the number of records which will be acquired from the table's free list by a process for inserting into that table; it is set to 10 by default.

The remainder of this section, we discuss certain of these variables which deserve special comment.

The variable `MSLKCOMMITADDEND` affects the timing of when locks are placed when inserting records into a table with record-level locking. In effect, it allows you to simulate the locking behavior associated with group-level locking. To illustrate, we consider the following mr program fragment:

         1    for (i=0; i N; i++)

         2    {
         3         mrputvi (rec_desc, attr_desc, i);
         4         mradd (rec_desc);
         5    }
         6
         7    mraddend (rec_desc);

The effect of setting `MSLKCOMMITADDEND` will be apparent from the following table (the notation here is the same as that used by `MSLOCKPLAN`):

       Line No.    MSLKCOMMITADDEND=x         MSLKCOMMITADDEND=

            4      [if ALLRECS uu lock
                   does not exist, then
                   get it:
                   . -> ALLRECS uu ]          . -> ALLRECS uu
                   . -> RECORD_n u            . -> RECORD_n u
                   RECORD_n-1 u ->> .

                   (add record)               (add record)

                                              RECORD_n u -> .

                                              ALLRECS uu -> .

           7       ALLRECS uu -> .

Thus, setting `MSLKCOMMITADDEND` causes Empress to:

- acquire only one `ALLRECS` lock over the loop (i.e., when `mradd()` is first called),
- acquire the new `RECORD` lock and release the old `RECORD` lock (if there is one) in the same request, and
- release the `ALLRECS` lock only when `mraddend()` is called.

Thus, setting this variable causes about half as many lock requests to be sent, and so will be more efficient when many inserts are done before `mraddend()` is called.

The variable `MSTRANSUFNGFL` is an integer which controls the number of records initially allocated from a table's free list when doing inserts. When Empress is asked to insert a record into a table, it acquires a set of records specified by `MSTRANSUFNGFL` from the table's free list and reserves them for insert. The idea is that if you are doing a series of consecutive inserts, then it is more efficient to acquire number of records specified by `MSTRANSUFNGFL` in a single lock request than it is to request and release locks individually, for each record inserted. The default value for `MSTRANSUFNGFL` is 10.

When Empress is asked to begin inserting records into a table, it acquires and locks (in a single request) the first available records specified by `MSTRANSUFNGFL` in the free list. If these records do not exist (this would occur if you were inserting into an empty table, for example), then they are created at the end of the `.rel` file. When the inserts are complete, any remaining records which were acquired and locked but not used are released for use by other processes. The beginning of the insert operation is signalled by the routine `mradd()` or `mrtadd()`; the end of insertion is signalled by invoking `mraddend()`. These routines are invoked - sometimes transparently - in all of interfaces in Empress.

The environment variable `MSTRANSTABLELOCK` can be turned on to ensure serializability of transactions (at the cost of lower concurrency of processes accessing the database) by trapping `ALLRECS` locks which get placed during a transaction, and releasing them only when the transaction is either committed or rolled back. This variable is turned off by default. Refer to Locking and Transactions of this manual.

### 12.4.4 Clearing Locks

Locks can be explicitly cleared by using the `empadm` and `empclrlk` commands. Their format are:

       empadm database_name lockclear [-f] [empress_id ...]

       empclrlk [-4gl] [-ag] [-recov] database_name [application ... ]

The `empclrlk` command is simply an interface to the `empadm` command, but which also allows the user to clear any 4GL or AG locks. Note, however, that in all cases, the `empclrlk` command clears normal locks as well. Both of these commands are discussed fully in the chapter Database Administration of this manual.

### 12.4.5 Creating Shared Memory Lock Managers

The procedure for creating Shared Memory Lock Managers is described in detail in the chapter Shared Memory of this manual.

Shared Memory Lock Managers are conceptually the same as file-based Lock Managers in Empress. However, note the following:

1. Shared Memory Lock Managers can contain the locks for more than one table; File Lock Managers contain locks for exactly one table.
2. The number of locks in a Shared Memory Lock Manager is the total number of locks which can be placed there, and these locks are shared by all the tables using that Lock Manager.
3. If `sys_dictionary` locking is to be placed in shared memory, it should be given its own Lock Manager.

The main advantage to using Shared Memory Lock Managers is that they are faster than their file-based counterparts.

## 12.5 Monitoring Locks

Users can determine what locks are being placed on tables by several mechanisms. These mechanisms can be divided into two groups: the internal and external lock monitors. The internal lock monitor gives information about locking carried out by a single Empress process, while the external lock monitor gives cumulative locking statistics about a single database.

### 12.5.1 Internal Lock Monitors

The environment variable `MSLOCKPLAN` causes Empress to display what locks are being held on a table, and what locks are being added or deleted. In addition, timing information (accurate to the nearest second) is optionally available. This output is run on a per-process basis, and so cannot be used to monitor dynamically all locks being placed on a database by all processes. The (voluminous) output of this mechanism groups lock placement and release temporally, and in terms of lock requests.

The environment variable `MSLOCKSTATS` can be used to print out the statistics concerned with lock and Lock Manager accesses. This information is of use in trying to determine locking bottlenecks by reporting the pattern of lock usage in an Empress process. Unlike the `MSLOCKPLAN` variable which reports to the user as locks are placed, `MSLOCKSTATS` reports only when the process ends.

#### 12.5.1.1 MSLOCKPLAN

Setting the environment variable `MSLOCKPLAN` will cause Empress to report on locks as they are being placed and released. This is valuable for situations where the timing of lock placement is important. The following example shows the settings of this variable in the UNIX environment:

       setenv MSLOCKPLAN x           (standard locking information)

       setenv MSLOCKPLAN t           (standard locking information
    	                          plus timing information)

An analysis of the `MSLOCKPLAN` output from a simple `SELECT` of a table `t` and attribute `a` with two records is shown below; this examples has record-level locking on both the data dictionary and the user tables. Note that each lock request is contained in a section of the output headed by the title `LOCKS: Table #n`.

       * SELECT * FROM t;

       LOCKS: Table #1
           ADMIN: . -> r       (indicate desire to read sys_dictionary)
         SUCCEEDED
       LOCKS: Table #1
           ADMIN: r            (still holding ADMIN read lock)
           CRIT: . -> r        (place read lock on index name sysdidx)
                               (search index for table name t)
         SUCCEEDED
       LOCKS: Table #1
           ADMIN: r            (still holding ADMIN read lock)
           CRIT: r -> .        (release lock on index; found table name t)
         SUCCEEDED
       LOCKS: Table #1
           ADMIN: r            (still holding ADMIN read lock)
           ALLRECS: . -> rr    (indicate desire to read a record in
                                sys_dictionary)
           RECORD 7: . -> r    (acquire a read lock on 7th. record in
                                sys_dictionary: this corresponds to the
                                record for table t)
         SUCCEEDED
       LOCKS: Table #7
           ADMIN: . -> r       (acquire ADMIN read lock on table t
                                i.e., rel file 0007.rel)
         SUCCEEDED
       LOCKS: Table #1
           ADMIN: r            (still holding ADMIN read lock on sys_dictionary)
           ALLRECS: rr -> .    (release sys_dictionary ALLRECS lock)
           RECORD 7: r -> .    (release sys_dictionary lock on record 7)
         SUCCEEDED

            a                  (print out the attribute header)

                               (If the table contained no records,
                                then the following section between the dashed
                                lines would be absent.)

                               ------------------------------------------
       LOCKS: Table #7
           ADMIN: r            (still holding ADMIN read lock on t)
           ALLRECS: . -> rr    (acquire ALLRECS rr lock on t to indicate
                                that you wish to read some record in t)
           RECORD 1: . -> r    (acquire a read lock on the first record)
         SUCCEEDED
            1                  (report the record found)
       LOCKS: Table #7
           ADMIN: r            (still holding ADMIN read lock on t)
           ALLRECS: rr         (still holding ALLRECS rr lock on t)
           RECORD 1: r -> .    (release lock on record which was found)
           RECORD 2: . -> r    (acquire lock on next qualified record)
         SUCCEEDED
            2                  (report the record found)
       LOCKS: Table #7
           ADMIN: r            (still holding ADMIN read lock on t)
           ALLRECS: rr -> .    (release ALLRECS rr lock on t. When
                                there are no more records, this lock will
                                be released.)
           RECORD 2: r -> .    (release lock on record which was found)

                                ------------------------------------------

       LOCKS: Table #7         (release the ADMIN lock on table t to
                                indicate that you are no longer reading it)
           ADMIN: r -> .
         SUCCEEDED
       LOCKS: Table #1         (release the ADMIN lock on table
                                sys_dictionary to indicate that you are
                                no longer reading it)
           ADMIN: r -> .
         SUCCEEDED

In this output one can see clearly the order in which locks are placed and released by a single process. This is different from the `empadm` command in which locks from all processes but from a single instant in time are reported. Recall that this output also shows lock requests: each lock request is grouped within a section headed by `LOCKS: Table #n`. In each of these sections, the currently held locks on the table are shown, and in addition the lock placements and releases contained in the current request are also shown (e.g., at the start of the example above, two separate requests are made to get an `ADMIN` and `CRIT` lock on `sys_dictionary` (Table #1); each of these requests is headed by `LOCKS: Table #1`).

#### 12.5.1.2 MSLOCKSTATS

The `MSLOCKSTATS` variable can be set in your environment to two values which are recognized:

1. When this variable is set to the value `a` (uppercase or lowercase), Empress collects statistics on:
   - locking by table
   - locking by lock type
   - locking by lock manager

2. When this variable set to any other value, Empress collects statistics on:
   - locking by table
   - locking by Lock Manager

In all cases, the output is to the standard error (UNIX), and is divided into two sections, corresponding to the inner and outer loops in the locking algorithm. The first section is titled `Access statistics to locks`, and gives the table and lock type (if requested) statistics; this corresponds to the inner loop of the locking algorithm. By table, the statistics gathered are:

- the total number of lock requests on that table
- the number of successful lock requests
- the number of failed lock requests
- the number of retried lock requests.

There is one output line per table summarizing these items. If lock type statistics are being gathered, then this section also contains a column entitled `Lock Item` which breaks down the per-table statistics into types of locks; for each type of lock placed on that table, a separate line containing an access summary is printed.

Note that:

1. Since only lock requests can be summarized, the figures for the table summary will be less than or equal to the sum of the figures for each of the listed lock types.

2. The number of retried requests is only that number which eventually led to success; retries eventually leading to failure are not included.

3. The sum of the successful and failed requests should equal the total number of requests, for the table as a whole, and for each lock type.

The second part of the output is titled `Access statistics to Lock Manager`, and gives a report of accesses to the Lock Managers; this corresponds to the outer loop in the locking algorithm (see the section, Basic Aspects of Locking).

For each Lock Manager, a single line summary is printed with the following information:

- the name of the Lock Manager,
- the Lock Manager type (this can be either `F` for a File Lock Manager, or `P` for a Shared Memory Lock Manager),
- the number of lock requests,
- the number of successful lock requests,
- the number of failed lock requests,
- the number of retried lock requests.
  - retried requests are only those retries which were eventually successful.
  - the number of lock requests should equal the sum of the successful and failed lock requests.

Sample statistics output resulting from a brief SQL session follows:

       Access statistics to locks
       --------------------------

               Table #   Lock Item      #reqsts   #succ   #fail   #retries
               -------   ---------      -------   -----   -----   --------
                     6      -                 6       6       0          0
                         ADMIN                2       2       0          0
                         CRIT                 0       0       0          0
                         ALLRECS              2       2       0          0
                         Rec #1               2       2       0          0
                         Rec #2               2       2       0          0
                         Rec #3               2       2       0          0

                    1       -                24      24       0          0
                         ADMIN                8       8       0          0
                         CRIT                 8       8       0          0
                         ALLRECS              8       8       0          0
                         Rec #6               2       2       0          0
                         Rec #8               6       6       0          0

       Access statistics to Lock Manager
       ---------------------------------

               Lock Man. Name    Type   #reqsts   #succ   #fail   #retries
               --------------    ----   -------   -----   -----   --------
               00006.lck         F            6       6       0          0
               00001.lck         F           24      24       0          0

### 12.5.2 External Lock Monitors

The command `empadm database_name lockinfo` shows all locks on a database, who they belong to, and what table they are on. It also reports which Lock Managers are currently in use, and what the characteristics of these are. This is especially useful for investigating locks in Shared Memory Lock Managers. This command provides a snapshot of the locks at a moment in time.

The command `empadm database_name lockstats` shows more detailed information on overall access to the Lock Managers in a given database. This command provides cumulative information about Lock Manager access from any number of processes over a period of time. The period over which lock statistics is gathered can be controlled with the statistics reset command `empadm database_name lockrststats`.

#### 12.5.2.1 Locking Information

This command is described in detail in the chapter Database Administration of this manual. Certain options which it takes allow any user to examine a database to see which locks have been placed, and also which processes placed the locks.

A sample of `empadm` output for the case of starting a transaction, and adding a single record to table `t`, is as follows:

       Lock Managers

       Table name             Lock Man. Name  Type     MAXPROCS  BUCKETS  NLOCKS
       sys_attr_privs         00005.lck       F              32       11     200
       sys_attrs              00003.lck       F              32       11     200
       sys_dictionary         00001.lck       F              32       11     200
       sys_privs              00004.lck       F              32       11     200
       sys_tables             00002.lck       F              32       11     200
       t                      00006.lck       F              32       11     200

       Active Locks

       Table name              Type       Record#      Status    Empress ID
       sys_dictionary          ADMIN          ---      r         1426877923
       sys_dictionary          RECORD           6      r         1426877923
       sys_dictionary          ALLRECS        ---      rr        1426877923
       t                       ALLRECS        ---      uu        1426877923
       t                       RECORD           3      u         1426877923
       t                       ADMIN          ---      r         1426877923

          Empress ID   User name       Process ID      Host ID
          1426877923   empress               3625      192.255.255.255

The output is reported in three sections:

1. the Lock Managers,
2. the currently active locks, and
3. the processes currently holding locks.

Various combinations of this output can be had by using different options of the `empadm` command.

#### 12.5.2.2 Lock Statistics

To enable the collection of lock statistics on a database, edit the database's `tabzero` file and change the `MSDBLOCKSTATS` line to:

       MSDBLOCKSTATS=x        (i.e., any value)

Once this is done, statistics will be accumulated; they can be examined by invoking `empadm database_name lockstats`, and they can be cleared (i.e., reset) by invoking `empadm database_name lockrststats`. The output of the `lockstats` option is a listing of lock statistics per table; each table's statistics are reported in four sections:

1. Lock Manager information

   The Lock Manager's defining parameters are reported.

2. High water mark information

   The maximum number of processes accessing the table concurrently, and the maximum number of locks held on the table concurrently, are reported. The efficiency indicates how well the hashing function is operating; it should be close to 100% for good operation.

3. Locking hash bucket statistics

   The number of hits on each hash bucket is gathered, and is grouped together on a number-of-hits basis. For each number-of-hits, the number of hash buckets having this number is printed, as well as which buckets these were. In the example, 5 of 11 total buckets contained all the hits (buckets 2, 5, 6, 7 and 9). Also printed is the minimum number of buckets having 50%, 75% and 90% of all the hits. For the locking hash function to be efficient, the locks should be evenly distributed over the available hash buckets. In general, you need a long observation period for these statistics to be useful.

4. Locking hash bucket chain length

   The maximum chain length is recorded, with the number of buckets having the same maximum chain length grouped together. The actual buckets which make up this set are reported as well. For more information on this, see the section Tuning Empress Locking.

A sample output for a user table "`t`" follows:

       Table name: t

               Lock Man. Name  Type     MAXPROCS NBUCKETS   NLOCKS
               00006.lck       F              32       11      200

               NPROCS  MAXNPROCS       CURNLKS MAXNLKS EFFICIENCY
                    1          1             0       3       100%

               # buckets       % of hits
                       3             60%
                       4             80%
                       5            100%

               # hits          # buckets       bucket #
                    0                  6         0,   1,   3,   4,   8,
                                                10
                    2                  5         2,   6,   7,   5,   9

               Max. chain length       # buckets       bucket #
                               0               6         0,   1,   3,   4,  8,
                                                        10
                               1               5         2,   6,   7,   5,  9

### 12.5.3 Relational Level Default Locks for Selected Queries

This section describes the typical locks which occur for the common DML commands under various conditions:

1. implicit locking schemes,
   - record, group and table

2. operations,
   - insert, update, select, delete

3. attribute indexing.
   - indexed or non-indexed attribute

Note that these scenarios are considered only outside of a transaction. The effect of transactions on locking is considered in the section Miscellaneous Aspects of Locking.

The following operations are done on a table `t`:

       CREATE TABLE t (a int);

All the locks which are reported are on table `t`'s Lock Manager; the locks for the data dictionary are not reported here. The locks are reported in the order in which they would be placed, and are usually annotated with a reason for their placement. Finally, note that `MSTRANSUFNGFL=10` in these examples, and that all inserts are done on initially empty tables.

1. Record-level locking, Indexed.

  i) Insert

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -to indicate the table is opened.
         acquire    CRIT         u       -lock the free list
                    ALLRECS      uu
                    RECORD_n     u
                                         -insert the record
                                         -update the index file

         (repeat for each additional inserted record:)

         release    CRIT         u
                    ALLRECS      uu
                    RECORD_n     u
         acquire    CRIT         u
                    ALLRECS      uu
                    RECORD_n     u
                                         -insert the record
                                         -update the index file

         (end repeat section)

                                         -no more records, so release locks
         release    CRIT         u
                    ALLRECS      uu
                    RECORD_n     u
         acquire    CRIT         u       -update .rel file header
         release    CRIT         u
         release    ADMIN        r

  ii) Select

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r
                    CRIT         r       -read index file to find qualified records
         release    CRIT         r
         acquire    ALLRECS      rr

         (repeat for all qualified records:)

         acquire    RECORD_n     r
                                         -read the record
         release    RECORD_n     r

         (end repeat section)

         release    ALLRECS      rr
         release    ADMIN        r

  iii) Update

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    ALLRECS      uu

         (repeat for all qualified records:)

         acquire    RECORD_n     u
         acquire    CRIT         u
                                         -update record and index
         release    CRIT         u
         release    RECORD_n     u

         (end repeat section)

         release    ALLRECS      uu
         release    ADMIN        r

  iv) Delete

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    CRIT         r
                                         -find qualified records by traversing index.
         release    CRIT         r

         (repeat for all qualified records:)

         acquire    CRIT         u
                    ALLRECS      uu
                    RECORD_n     u
                                         -update index, delete record.
         release    RECORD_n     u
                    ALLRECS      uu
                    CRIT         u

         (end repeat section)

         acquire    CRIT         u
                                         -update .rel file header
         release    CRIT         u
         release    ADMIN        r

2. Record-level locking, Not Indexed.

  i) Insert

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    CRIT         u       -get available records from free list.
         release    CRIT         u

         (repeat for all records:)

         acquire    ALLRECS      uu
                    RECORD_n     u
                                         -do insert
         release    ALLRECS      uu
                    RECORD_n     u

         (end repeat section)

         acquire    CRIT         u
                                         -update free list
         release    CRIT         u
         release    ADMIN        r

  ii) Select

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
                                         -print header information
         acquire    ALLRECS      rr
                    RECORD_n     r

         (repeat for all records:)

         release    RECORD_n     r
         acquire    RECORD_n+1   r
                                         -print record n contents

         (end repeat section)

         release    RECORD_n+1   r
         release    ALLRECS      rr
         release    ADMIN        r

  iii) Update

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    ALLRECS      uu
                    RECORD_n     u

         (repeat for all records:)

                                         -do update
         release    RECORD_n     u
         acquire    RECORD_n+1   u

         (end repeat section)

         release    RECORD_n+1   u
         release    ALLRECS      uu
         release    ADMIN        r

  iv) Delete

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
                    ALLRECS      uu
                    RECORD_n     u

         (repeat for all records:)

                                         -do delete of record
         release    RECORD_n     u
         acquire    RECORD_n+1   u

         (end repeat section)

         release    RECORD_n+1   u
                    ALLRECS      uu
         acquire    CRIT         u
                                         -update .rel file header
         release    CRIT         u
         release    ADMIN        r

3. Group-level locking, Indexed.

  i) Insert

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    CRIT         u       -lock free list and index
         acquire    ALLRECS      uu
                    RECORD_10    u
                    RECORD_9     u
             ...
                    RECORD_1     u
                                         -acquire MSTRANSUFNGFL (=10 by
                                          default) records from the free list.
                                         -insert record and update index
         release    CRIT         u       -release free list
         release    RECORD_1     u       -release lock on inserted record.

         (repeat for all qualified records:)

                                         -once MSTRANSUFNGFL records are inserted,
                                          Empress will go back and acquire another set
                                          of MSTRANSUFNGFL records from the free
                                          list in anticipation of inserting them
                                          into the table.
         acquire    CRIT         u       -lock free list and index
                                         -insert record and update index
         release    RECORD_2     u
         release    CRIT         u

         (end repeat section)

                                         -release all outstanding records:
         release    RECORD_3     u
                    RECORD_4     u
             ...
                    RECORD_10    u
         release    CRIT         u
         release    ALLRECS      uu
         release    ADMIN        r

  ii) Select

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open

         (repeat for all qualified records:)

         acquire    CRIT         r       -lock index for reading
         release    CRIT         r
         acquire    ALLRECS      rr
         acquire    RECORD_2     r       -read the record
         release    RECORD_2     r
         release    ALLRECS      rr

         (end repeat section)

         release    ADMIN        r

  iii) Update

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    CRIT         r       -lock index for read
         release    CRIT         r

         (repeat for all qualified records:)

         acquire    ALLRECS      uu
                    RECORD_2     u
                                         -update record
         acquire    CRIT         u
                                         -update index and header
         release    CRIT         u
         release    ALLRECS      uu
                    RECORD_2     u

         (end repeat section)

         release    ADMIN        r

  iv) Delete

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    CRIT         r       -lock index for read to find qualified records
         release    CRIT         r

         (repeat for all qualified records:)

         acquire    ALLRECS      uu
         acquire    RECORD_n     u
                                         -update record
         acquire    CRIT         u
                                         -update index
         release    CRIT         u

         (end repeat section)

         release    ALLRECS      uu

                    RECORD_n     u
         acquire    CRIT         u       -update .rel file header
         release    CRIT         u
         release    ADMIN        r

4. Group-level locking, Not Indexed.

  i) Insert

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    CRIT         u       -lock free list
         release    CRIT         u
         acquire    ALLRECS      uu
                    RECORD_1     u
          ......

                    RECORD_10    u
                                         -get and lock MSTRANSUFNGFL records
                                          in free list (or add these to free
                                          list if they do not already exist)

         (repeat for all records:)       -If more than MSTRANSUFNGFL records are
                                          inserted, then Empress will
                                          attempt to acquire u locks on the
                                          next available set of records.
                                          As records are inserted, these locks
                                          will be released as in the example.

                                         -do insert
         release    RECORD_1     u

         (end repeat section)

         release    RECORD_2     u       -release lock on next record in list.
         acquire    CRIT         u       -update .rel file header and free list
         release    CRIT         u
         release    ALLRECS      uu
         release    RECORD_3     u

          ......

                    RECORD_10    u
                                         -release any outstanding record locks
         release    ADMIN        r

  ii) Select

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    ALLRECS      r
                                         -select the record(s)
         release    ALLRECS      r
         release    ADMIN        r

  iii) Update

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    ALLRECS      u
                                         -update record(s)
         release    ALLRECS      u
         release    ADMIN        r

  iv) Delete

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    ALLRECS      u
                                         -delete record(s)
         release    ALLRECS      u
         acquire    CRIT         u
                                         -update free list, .rel file header
         release    CRIT         u
         release    ADMIN        r

5. Table-level locking, Indexed.

  i) Insert

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
                    ALLRECS      u       -lock records in table
         acquire    CRIT         u       -lock free list and get records
                                          from it.
         release    CRIT         u       -release free list
         acquire    CRIT         u       -lock index file
                                         -insert record(s) and modify index
         release    CRIT         u
         acquire    CRIT         u       -lock free list and .rel file header
                                         -update free list and header
         release    CRIT         u
         release    ALLRECS      u
         release    ADMIN        r

  ii) Select

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
                    ALLRECS      r       -read lock on all records
                    CRIT         r       -read lock on index
                                         -traverse index and do select
         release    CRIT         r
         release    ALLRECS      r
         release    ADMIN        r

  iii) Update

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    ALLRECS      u       -lock all records in table
         acquire    CRIT         r       -traverse index to find qualified records
         release    CRIT         r
         acquire    CRIT         u
                                         -update the record(s) and index
         release    CRIT         u
         release    ALLRECS      u
         release    ADMIN        r

  iv) Delete

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    ALLRECS      u       -lock all records in table
         acquire    CRIT         r       -traverse index to find all qualified records
         release    CRIT         r
         acquire    CRIT         u
                                         -delete record(s) and modify index file
         release    CRIT         u
         acquire    CRIT         u
                                         -update free list and .rel file header
         release    CRIT         u
         release    ALLRECS      u
         release    ADMIN        r

6. Table-level locking, Not Indexed.

  i) Insert

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
                    ALLRECS      u       -lock all records in table
                    CRIT         u       -lock free list
                                         -insert record(s)
         release    CRIT         u
         acquire    CRIT         u
                                         -update free list and .rel file header
         release    CRIT         u
                    ALLRECS      u
                    ADMIN        r

  ii) Select

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    ALLRECS      r
                                         -select record(s)
         release    ALLRECS      r
         release    ADMIN        r

  iii) Update

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    ALLRECS      u       -lock all records in table
                                         -update record(s)
         release    ALLRECS      u
         release    ADMIN        r

  iv) Delete

         Action     Type         Mode    Reason
         ------     ----         ----    ------

         acquire    ADMIN        r       -indicate table is open
         acquire    ALLRECS      u       -lock all records in table
         acquire    CRIT         u       -lock free list
                                         -delete record(s)
                                         -update free list and header count
         release    CRIT         u
         release    ALLRECS      u
         release    ADMIN        r

Note the similarity of this case to that of group-level locking. In the table-level locking case, the lock on the free list is held for the duration of the operation, whereas in the group-level locking case, that lock is acquired and held only at the end of the operation.

## 12.6 Tuning Empress Locking

With Empress, locking can be adjusted to improve the performance of your database by taking a combination of the following steps:

1. choose an appropriate implicit locking scheme for each table in the database;
2. adjust the overall locking parameters;
3. change the type and organization of the database's Lock Managers;
4. tune the database's Lock Managers.

The goal of tuning the locking mechanism is to improve performance by:

- reducing the time for a single process to place and release a lock, and
- by reducing the amount of contention between processes for locks.

### 12.6.1 The Implicit Locking Scheme

Choosing an implicit locking scheme for the tables in the database is often dictated by the operations which that table is expected to support. For example, if a table is to be used for look-up only, null-level locking may be sufficient. However, a table being accessed by multiple processes for both reading and writing would probably best be served with record-level locking.

When you have a choice of several acceptable implicit locking schemes, the number of lock requests should be minimized where possible. However, data integrity in the presence of concurrent processes generally implies an opposing trend:

Lock Level | No. Lock Requests | Concurrency | Integrity
`null` | minimum | maximum | minimum
`table` | fewer | less | maximum
`group (n)` | (medium) | (medium) | maximum
`group` | (medium) | (medium) | maximum
`record (n)` | (medium) | (medium) | maximum
`record` | greater | more | maximum

Of these two considerations, that of concurrency will be of greatest importance to most users (since null level locking is usually not viable).

### 12.6.2 Adjusting the Locking Parameters

Locking parameters control the behavior of Empress in situations where locks cannot be obtained immediately, or in situations where the acquisition/release of locks can be made more efficient by bundling the requests together. A list of Empress' `MSxxx` variables which directly affect locking is given in the section Environment Variable. Of particular importance are:

- the parameters affecting the physical locking scheme: the number of retries, and the time between retries (see the section, Basic Aspects of Locking),

- the parameters describing the lock manager itself: the number of processes which can access it concurrently, and the maximum number of locks it can hold.

Adjusting the physical locking parameters has a number of effects:

1. Making the number of retries large will help to ensure that locks will be acquired eventually. However, in interactive applications (e.g., 4GL applications) this could result in the application's users waiting at their terminals for a lock to be acquired. In such applications it may be more user-friendly to reduce the number of retries and simply print a message in the case of lock acquisition failure. Note that there are separate parameters applicable to interactive and non-interactive use.

2. The sleep time between lock acquisition attempts should be set to a time appropriate to the application in question. If a number of processes are accessing a single database, then it may be reasonable to set the sleep times for each process to slightly different values to attempt to reduce the possibility of repeated lock collisions between different processes. Note also that the repeat times should not be made too much shorter or longer than the amount of time a process would be expected to hold onto an average lock.

3. The maximum number of processes and number of locks which a Lock Manager can hold linearly affects the size of that Lock Manager. In the case of File Lock Managers, large lock files should be avoided because more than one or two disk blocks would have to be read with every access of that Lock Manager. This consideration is absent with Shared Memory Lock Managers.

### 12.6.3 Shared Memory vs. File Lock Managers

One of the most significant changes which can be made to locking in Empress to improve performance is to replace the default File Lock Managers with Shared Memory Lock Managers (note that Shared Memory Lock Managers may not be available in all versions of Empress). Shared Memory Lock Managers are much faster than their file-based counterparts: processes hold onto locks for less time overall, and this alleviates lock contention. In addition, for processes which need to place and release a large number of locks, individual process performance can be improved dramatically.

However, setting up Shared Memory Lock Managers is sometimes bewildering because of the (typically) large numbers of possible ways to do it.

To implement shared memory locking most effectively, you should first attempt to answer the following questions:

1. What is the maximum number of processes which will access each table at one time?

2. What is the maximum number of locks on each table at one time?

From the answers to these questions, you should be able to draw up a list which will allow you to determine what sort of lock managers are needed. Of course, the easiest route is to go to the extremes:

- Assign a separate Lock Manager having the same characteristics to each table. This will probably be somewhat wasteful of your shared memory.

- Assign a single Lock Manager for all tables. This will probably cause Lock Manager contention on a busy enough system.

We recommend a middle course:

1. Put `sys_dictionary` locking into its own partition, and do not put any other locking into that partition. Note that `sys_dictionary` is accessed every time a table is opened.

2. Identify the most heavily used tables, and give them their own Lock Managers, with individually adjusted sizes. You may find that combining a small number of tables to use the same Lock Manager may be useful, especially with applications that hold onto large numbers of locks through transactions.

3. Tables which are used less frequently can be lumped together into a few catch-all Lock Managers with appropriately adjusted sizes.

4. Tables which are used infrequently can be left as File Lock Managers.

It is also recommended that you create a script to do these steps automatically, since shared memory must be set up again after each system re-boot.

### 12.6.4 Tuning Lock Managers

Lock Manager tuning starts by using the output of the lock monitoring commands. Recall that the locking mechanism works by placing and releasing locks in a Lock Manager, i.e., a file or shared memory partition. To do this, it needs to locate that lock, and for this purpose it uses a hashing scheme. Each Lock Manager uses a number of buckets, into which locks are placed. Now, since the locks in a given bucket form a chain (one bucket can have more than one lock), it should be clear that the Lock Manager will be most efficient if it needs to search (on average) only short chains: a Lock Manager which uses only 1 of `n` buckets for all its locks will be least efficient.

In addition to the detailed information of the lock request distribution across the hashing buckets, a simple Lock Manager efficiency index is provided. This number lies between 0% and 100%, and is a simple way to determine whether the lock hashing function, as used by your application, is working as desired. The best efficiency is 100%.

Note that, as mentioned above, the ideal situation is to have all lock requests spread out evenly across the hashing buckets. Now, in any given application, it may be acceptable to have a certain amount of fluctuation (say, 50%) away from this ideal (i.e., average) number of locks per bucket, but beyond which is unacceptable. The efficiency calculation contains the adjustable parameter (`MSTHRESHCHAINLEN`) which determines the acceptable amount of fluctuation in lock request distribution; lock requests exceeding this level are counted as being inefficient.

The default for `MSTHRESHCHAINLEN` is 1.5, which corresponds to counting lock requests as being inefficient when they fall into buckets containing 50% more than the average number of lock requests. This variable should be set to a value which is found to be suitable for the application being investigated at the time. It has absolutely no effect on the behavior of the locking mechanism; rather, it affects the way the locking efficiency is calculated.

The locking efficiency is calculated as follows:

       efficiency = (1 - a/b) * 100%

where:

`a` | the number of lock placement operations at time `t` which cause the hash bucket chain length to exceed the threshold chain length.
`b` | the total number of lock placement operations at time `t`.

The threshold chain length is determined by:

       threshold chain length = MSTHRESHCHAINLEN * ideal_chain_length,

       ideal_chain_length = (total # locks at time t) / (# hash buckets available)

The locking efficiency index and the more detailed lock request distribution statistics can be examined to determine whether your locking is behaving properly. If, based on the `lockstats` output, you decide that you want to change the locking parameters, you have available two parameters which can be adjusted to change the behavior of the lock hashing function. These control the maximum number of locks which that lock manager can hold, and the number of hash buckets which it will use.

For the different manifestations of lock manager, the tuning variables are:

File Lock Manager | Shared Memory
`MSFILELOCKNBUCKETS MSFILELOCKNLOCKS` | `MSPARTLOCKNBUCKETS MSPARTLOCKNLOCKS`

In practice, the number of locks `MSxxxNLOCKS` which you will need will be determined by the expected usage pattern of the database, and so is sometimes not a candidate for tuning. If it is adjusted, it is almost always made larger (since making it smaller might cause you to run out of locks). However, note that making `MSFILELOCKNBUCKETS` too large incurs a file i/o overhead for every block that must be read in from the lock file. Of course, this problem does not occur with the variable `MSPARTLOCKNBUCKETS`.

While the number of hashing buckets `MSxxxNBUCKETS` is a viable tuning parameter, Empress does not recommend that it be adjusted without consultation. If it does need adjusting, the following guidelines should be kept in mind:

- It should be a number which is roughly equal to the square root of `MSxxxNLOCKS`, and
- It should be a prime number.

In the case of File Lock Managers, you can adjust `MSFILELOCKNBUCKETS` (or `MSFILELOCKNLOCKS`) to the desired value just before the table is created. If the table has been created already, and you wish to adjust these values without having to re-create the table (say, through an `ALTER` command, or through `SELECT FROM table_name INSERT INTO new_table_name`). you can remove the old Lock Managers by hand (e.g., `rm -f db/_lock/0027.lck`), and then set `MSFILELOCKNBUCKETS` to the desired value prior to doing any operation on the table which would require a lock. In such a case, a missing File Lock Manager will be automatically created with the desired parameter values.