Lock Levels
Locks can be of several levels. The level of a lock refers to the scope of the resource on which the lock is requested or used, for example, whether the lock affects:
• A single row
• A single page
• A table as a whole
• An entire database
Row, page, and table lock levels are subject to user control, whereas database and table "control" locks are taken by commands and utilities (such as the SQL statement MODIFY or the ckpdb utility).
Transaction lock levels are as follows:
Row
The most granular lock level. Individual rows are locked as they are encountered by a query. Before a row lock is acquired, its page is intent locked.
A transaction using a lock level of ROW will hold intent table locks, intent page locks, and shared and/or exclusive row locks.
Row-level locking is supported for tables with a page size greater than 2 KB.
Row-level locking does not work for the iidbdb, for non-fast_commit servers, when Distributed Multi-Cache Management (DMCM) is turned on, or in a VMS Cluster installation.
MVCC
Row level granularity, without the need for intent locks on data pages. Rows are never locked for read and only exclusively locked when updated. Readers do not block writers, and writers do not block readers. A point-in-time read-consistent image of a row’s page is produced as needed by undoing changes to the page, and then presented to the query.
A transaction using a lock level of MVCC will hold intent table locks and exclusive row locks on updated rows.
For further details, see the chapter "Understanding Multiversion Concurrency Control."
Page
Locks an entire page.
A transaction using a lock level of PAGE will hold intent table locks and shared and/or exclusive page locks.
Table
Locks an entire table.
A transaction using a lock level of TABLE will hold shared and/or exclusive table locks.
Database
While not a specifiable lock level, an application can request exclusive access to a database when connecting to the database (see the CONNECT statement). Some Ingres utilities (such as rollforward and checkpoint) use database level locks to prevent concurrent updates to the database while running.