How the Locking Level is Determined

Database Administrator Guide > Database Administrator Guide > Understanding the Locking System > How the Locking System Works > How the Locking Level is Determined

Was this helpful?

The lock level selected for a table is determined in the following order of precedence:

• SET LOCKMODE ON table_name WHERE LEVEL = lock_level

• SET LOCKMODE SESSION WHERE LEVEL = lock_level

• The configured system_lock_level

Initial Locking Level

In evaluating the query on which the lock is being requested, the optimizer determines the level of lock as follows:

• If a query involves a single table with only a primary key, page level locks are requested.

• If the optimizer estimates that no more than maxlocks pages are needed, the page level locks are requested.

• If the optimizer estimates that a query is touching more than maxlocks pages, the query is executed with a table level lock.

• If the optimizer estimates that a query is touching all the pages in the table, the query is executed with a table level lock.

This strategy saves the overhead of accumulating multiple page-level locks and prevents the contention caused by lock escalation. For example, on a query that is not restrictive or does not use a key to locate affected records, the locking system grants a table-level lock at the beginning of query execution.

Escalation of Locks

Lock escalation can occur under either of the following conditions:

• The number of logical locks held on a specific table exceeds the "maxlocks" value for the table

• The number of logical locks held by the transaction exceeds the configured "per_tx_limit"

If either of these conditions occurs, lock escalation:

• Acquires an appropriate S or X table-level lock on the table whose lock request exceeded the limit

• Releases all other locks associated with the table (PAGE, ROW, VALUE)

• Continues the transaction with a lock level of TABLE on the affected table

Note: The issuing of lock escalation messages is configurable.

Methods for Changing How Locking is Handled

The following methods can be used to change how Ingres handles locking:

• Set the system_lock_level parameter in the DBMS Server component in CBF. This parameter sets the default locking level for an entire Ingres instance. The system administrator can override the default system_lock_level default, which is set to DEFAULT.

Other valid values are:

– ROW

– MVCC

– PAGE

– TABLE

Each of the default lock levels other than TABLE is subject to escalation.

For details on the default behavior, see How the Locking Level is Determined.

Use the SET LOCKMODE statement to change parameters that determine how locking is handled. For example, using MAXLOCKS can reset the maximum number of logical locks that can be requested per table per query before escalating to a table-level lock.

Note: The SET LOCKMODE statement cannot be issued in a transaction.

For details, see User-Controlled Locking--SET LOCKMODE.

Last modified date: 11/28/2023