How the Locking Level is Determined
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, assuming system_lock_level and/or LEVEL are set to DEFAULT:
• 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 Actian Ingres handles locking:
Configure the system_lock_level parameter in the DBMS Server component in CBF. This parameter defines the locking level for the entire Actian Ingres instance.
The system default is DEFAULT. A system administrator can change it to ROW, MVCC, PAGE, or TABLE.
When system_lock_level is DEFAULT, the optimizer determines the locking level automatically. Specifying any other level overrides the optimizer’s automatic locking-level selection for the instance. All lock levels except TABLE are subject to escalation.
You can override the system-wide setting for a session, when necessary, using SET LOCKMODE. For details, see
When to Change the Locking Level.
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.
Last modified date: 09/11/2026