How the Locking System Works

The locking system controls locking by doing the following:

Managing and queuing lock requests
Detecting deadlock situations

Lock Requests

When you either issue a statement or a command or perform the equivalent operation using VDBA, implicit requests for locks are made.

In addition, the locking system considers the following factors to determine what mode and level of lock, if any, to take:

Are there any locks available in the system?
Does the query involve reading or changing data?
What resources are affected by the query?
Are any other locks held on the affected resources?

Available Locks in the System

When the system administrator configures the logging and locking system, the total number of available locks is set. As each lock is used, a counter is decremented to reflect the number of locks still available. If a lock request is received after all available locks have been used, the request cannot be satisfied until a lock is freed.

If this happens frequently, your system administrator can reconfigure the maximum number of locks. For details, see the System Administrator Guide.

Lock Grants

Whether the locking system can grant a lock depends on whether another transaction holds a lock on the resource requested and, if so, what mode of lock that other transaction holds. If another transaction already holds an exclusive lock on the resource in question, a new lock cannot be used. The second request must wait.

The locking system uses intended shared and intended exclusive locks on a table to determine quickly whether a table-level lock can be used on that table, as follows:

An intended shared lock on the table means that a shared lock has been used on at least one page of the table; nevertheless, a shared lock, if available, can still be used at either the page or table level.
An intended exclusive lock on the table means that an exclusive lock has been used on at least one page of the table; no table-level lock can be used on the table on behalf of another user until the current page-level lock has been released.

Lock Mode Compatibility

The following table shows which granted lock modes are compatible with the requested mode:



Req. Mode	NL	IS	IX	S	SIX	U	X
NL	Yes	Yes	Yes	Yes	Yes	Yes	Yes
IS	Yes	Yes	Yes	Yes	Yes	No	No
IX	Yes	Yes	Yes	No	No	No	No
S	Yes	Yes	No	Yes	No	No	No
SIX	Yes	Yes	No	No	No	No	No
U	Yes	No	No	Yes	No	No	No
X	Yes	No	No	No	No	No	No

For meaning of the lock mode abbreviations, see Lock Modes.

How the Default Lock Mode is Determined

The locking system determines the default lock mode as follows:

When you perform a read operation from the database, such as a select operation, a shared lock is requested on the affected resources for the transaction.
When you perform an operation that writes to the database, such as an update, insert, or delete operation, the locking system requests an exclusive lock on the affected resources for the transaction. Update lock protocols are used by Ingres to increase concurrency and reduce deadlocks, because update mode locks can be converted to shared locks for rows and pages that are not updated. Update mode locks are converted to exclusive locks for rows and pages that are updated.

The default state of the locking system ensures that no user can read data being changed and no user can change data being read. However, users can read data that is being read by other users. This means that the locking system can grant an:

S lock for User2 on resource R, provided User1 does not already hold an X lock on R.
X lock on resource R for User2, provided User1 does not already hold an S or X lock on R.
S lock on resource R for User2, even if User1 already holds an S lock on R.

This default strategy is adequate for most situations. When it is not, you can establish a different strategy using the set lockmode statement. For details, see User-Controlled Locking.

How the Locking Level is Determined

By default, Ingres determines the best locking level to use. Ingres selects the locking level as page or table, depending on the optimizer's estimates of the number of pages to be read.

If the estimated number of pages to be read is greater than the session maxlocks limit, or if the entire table is to be read, Ingres uses table level locking. Otherwise, Ingres uses page level locking, and then escalates to table level locking if the number of page locks requested exceeds the session maxlocks per table, per query limit.

Initial Locking Level

In evaluating the query on which the lock is being requested, the Query 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 Query Optimizer estimates that a query is touching more than the number of pages to which maxlocks is set, the query is executed with a table-level lock.
If the Query 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

When page locking, if the number of pages in a table on which locks are held reaches maxlocks during a query, the locking system escalates to table-level locks. To do this it:

Stops accumulating page-level locks
Acquires an appropriate (S or X) table-level lock
Releases all the page-level locks it has accumulated

The locking system also escalates to table-level locks in an attempt to complete a transaction if it exceeds the maximum number of locks allowed or the installation has run out of locks. If this occurs, an error is issued and the transaction is backed out. To avoid this situation in the future, the system administrator can bring down the installation and reconfigure the locking system.

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. The system administrator can override the default locking level by setting the system_lock_level parameter. This parameter sets the default locking level for an entire Ingres instance.
By default, system_lock_level is set to DEFAULT, in which Ingres decides the locking level. For details on the default behavior, see How the Locking Level is Determined. Other valid values for system_lock_level are ROW, PAGE, and TABLE. Each of the default lock levels is subject to escalation. For example, if system_lock_level is set to PAGE, the default locking level is page, and then Ingres escalates to a table-level lock if the number of page locks requested exceeds the session maxlocks per table, per query limit.
Use the set lockmode statement to change parameters that determine how locking is handled. For example, using maxlocks you can reset the maximum number of page-level 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.

Summary of Default Locks

The following table describes what mode and level of lock is invoked by default when a query is issued.

Statement or Command	Comment	Mode	Level
create index	On base table: On index:	X X X	Table lock Control lock Table lock
create rule	On base table:	X	Table lock
create table	On table:	X X	Table lock Control lock
create view	On view: On base table:	X X X	Table lock Control lock Table lock
drop	On table:	X	Table lock
grant	On base table:	X	Table lock
modify	On table:	X X	Table lock Control lock
select	For each table involved in the select: If query touches 50 (or maxlocks) pages:	IS and S S S	Table lock Page lock(s) on pages in table Control lock Table lock
sysmod	On database:	X	Database lock
update, insert, or delete	On table involved in update, insert, or delete: If query touches 50 (or maxlocks) pages: On other tables used in query but not being changed:	IX and X X S	Table lock Page lock(s) on pages in table Table lock See lock for select statement

Releasing of Locks

A transaction accumulates locks on resources until you commit or roll back. When a transaction is committed, the results are written to the database, and all the locks accumulated during the transaction are released. A rollback aborts the transaction and releases accumulated locks.

You commit transactions during a session by doing one of the following:

Issuing the commit statement after one or more SQL queries
Using the set autocommit on statement. This causes a commit to occur after every SQL query that was successfully executed during the session.
For details on the set statement using the autocommit parameter, see the SQL Reference Guide.

After a commit is executed, the current transaction is terminated and you are in a new transaction as soon as the next SQL statement is issued.

All open transactions are automatically committed when you end your session.

Important! If you do not issue the commit statement during a session when the set autocommit is off, all locks requested on the resources affected by your queries are held until your session ends. Your entire session is treated like one transaction and can cause concurrency problems.