Lock attributes

Database manager locks have several basic attributes.

These attributes include the following:
Mode
The type of access allowed for the lock owner, as well as the type of access allowed for concurrent users of the locked object. It is sometimes referred to as the state of the lock.
Object
The resource being locked. The only type of object that you can lock explicitly is a table. The database manager also sets locks on other types of resources, such as rows and table spaces. Block locks can also be set for multidimensional clustering (MDC) or insert time clustering (ITC) tables, and data partition locks can be set for partitioned tables. The object being locked determines the granularity of the lock.
Lock count
The length of time during which a lock is held. The isolation level under which a query runs affects the lock count.

Table 1 lists the lock modes and describes their effects, in order of increasing control over resources.

Table 1. Lock Mode Summary
Lock Mode Applicable Object Type Description
IN (Intent None) Table spaces, blocks, tables, data partitions The lock owner can read any data in the object, including uncommitted data, but cannot update any of it. Other concurrent applications can read or update the table.
IS (Intent Share) Table spaces, blocks, tables, data partitions The lock owner can read data in the locked table, but cannot update this data. Other applications can read or update the table.
IX (Intent Exclusive) Table spaces, blocks, tables, data partitions The lock owner and concurrent applications can read and update data. Other concurrent applications can both read and update the table.
NS (Scan Share) Rows The lock owner and all concurrent applications can read, but not update, the locked row. This lock is acquired on rows of a table, instead of an S lock, where the isolation level of the application is either RS or CS.
NW (Next Key Weak Exclusive) Rows When a row is inserted into an index, an NW lock is acquired on the next row. This occurs only if the next row is currently locked by an RR scan. The lock owner can read but not update the locked row. This lock mode is similar to an X lock, except that it is also compatible with NS locks.
S (Share) Rows, blocks, tables, data partitions The lock owner and all concurrent applications can read, but not update, the locked data.
SIX (Share with Intent Exclusive) Tables, blocks, data partitions The lock owner can read and update data. Other concurrent applications can read the table.
U (Update) Rows, blocks, tables, data partitions The lock owner can update data. Other units of work can read the data in the locked object, but cannot update it.
X (Exclusive) Rows, blocks, tables, buffer pools, data partitions The lock owner can both read and update data in the locked object. Only uncommitted read (UR) applications can access the locked object.
Z (Super Exclusive) Table spaces, tables, data partitions, blocks This lock is acquired on a table under certain conditions, such as when the table is altered or dropped, an index on the table is created or dropped, or for some types of table reorganization. No other concurrent application can read or update the table.