IBM Books

Administration Guide

Locking

The database manager provides concurrency control and prevents uncontrolled access by means of locks. A lock is a means of associating a database manager resource with an application to control how other applications can access the same resource. The application with which the resource is associated is said to hold or own the lock.

The database manager imposes locks to prohibit applications from accessing uncommitted data written by other applications (unless the uncommitted read isolation level is used). This principle protects data integrity (that is, the consistency and security of data). Locks can also prohibit the updating of rows (such as for a repeatable read application).

To satisfy data integrity, the database manager acquires locks implicitly, under database manager control. Except for the uncommitted read isolation level, it is never necessary for an application to request a lock explicitly to ensure that uncommitted data is hidden from other processes.

Because of the basic principle of locking, you do not need to take action to control locks in most cases. Still, applications acquire locks on the basis of certain general parameters. Knowledge of your local situation can help you make better use of your system resources by changing those parameters. To assist you, the following topics on locking are discussed:

Attributes of Locks

Database manager locks have the following basic attributes:

Object
The resource being locked. The only types of explicitly lockable objects are tables. The database manager also imposes locks on other types of resources, such as rows, tables and table spaces. The object being locked represents the granularity of the lock.

Duration
The length of time a lock is held. Lock durations are affected by isolation levels which are discussed in "Concurrency".

Mode
The type of access allowed for the lock owner as well as the type of access permitted for concurrent users of the locked object. It is sometimes referred to as the state of the lock.

Modes and their effects are shown in order of increasing control over resources:

IN (Intent None)
The lock owner can read any data in the table, including uncommitted data, but cannot change any of it. No row locks are acquired by the lock owner. Other concurrent applications can read or update the table. Both table spaces and tables can be locked in this mode.

IS (Intent Share)
The lock owner can read data in the locked table, but not change this data. When an application holds the IS table lock, the application acquires an S or NS lock on each row read. In either case, other applications can read or update the table. Both table spaces and tables can be locked in this mode.

NS (Next Key Share)
This lock is acquired on rows of a table, instead of a Share lock. The lock owner and all concurrent applications can read, but not change, the locked row. Only individual rows can be locked in NS mode. This lock is acquired in place of a share (S) lock on data that is read with the RS or CS isolation levels.

S (Share)
The lock owner and any concurrent applications can read, but not change, the locked data. Individual rows can be Share locked. If a table is Share locked, no row locks are acquired by the lock owner. Other concurrent applications can read the table. Both rows and tables can be locked in this mode.

IX (Intent Exclusive)
The lock owner and concurrent applications can read and change data in the table. When the owner reads data, it acquires an S, NS, X, or U lock on each row. It also acquires an X lock on each row that it updates. Other concurrent applications can both read and update the table. Both table spaces and tables can be locked in this mode.

SIX (Share with Intent Exclusive)
The lock owner can both read and change data in the table. The lock owner acquires X locks on the rows it updates, but does not acquire locks on rows that it reads. Other concurrent applications can read the table. Only a table object can be locked in this mode.

U (Update)
The lock owner can update data in the locked object and acquire X locks on the rows prior to updates. Other units of work can read the data, but cannot attempt to update it. Both rows and tables can be locked in this mode.

NX (Next Key Exclusive)
This lock is acquired on the next row when a row is deleted from an index or inserted into the index of a table. The lock owner can read but not change the locked row. Only individual rows can be locked in NX mode. This is similar to an X lock except that it is compatible with the NS lock.

NW (Next Key Weak Exclusive)
This lock is acquired on the next row when a row is inserted into the index of a non-catalog table. The lock owner can read but not change the locked row. Only individual rows can be locked in NW mode. This is similar to X and NX locks except that it is compatible with the W and NS locks.

X (Exclusive)
The lock owner can both read and change data in the locked object. Tables can be Exclusive locked, meaning that no row locks will be acquired. Only uncommitted read applications can access the locked table. Both rows and tables can be locked in this mode.

W (Weak Exclusive)
This lock is acquired on the row when a row is inserted into a non-catalog table. The lock owner can change the locked row. Only individual rows are locked in W mode. This lock is similar to an X lock except that it is compatible with the NW lock. Only uncommitted read applications can access the locked row.

Z (Superxclusive)
This lock is acquired on a table in certain conditions, such as when the table is altered or dropped, an index on the table is created or dropped, or a table is reorganized. No other concurrent application can read or update the table. Both table space and table objects can be locked in this mode.

Note that only tables and table spaces will obtain the "intent" lock modes. That is, intent locks are not obtained for rows.

Locks and Application Performance

Application programmers need to be aware of several related factors concerning the uses of locks and their effect on the performance of applications. These factors include the following:

Concurrency and Granularity

A lock held by one application can prevent access by another application. Therefore, for maximum concurrency, a row level lock is better than a table lock. But locks require storage and processing time to manage. Therefore, for minimizing storage and processing time, a single table lock is better than many row locks.

You can define the size (granularity) of locks at row or table level through ALTER TABLE. By default, row locks are used. With permanent table locks, as defined by ALTER TABLE, only S and X locks are granted. Performance is improved since the application does not need to acquire and release as many row locks. You may prefer to get a permanent table lock using ALTER TABLE rather than a single transaction table lock using LOCK TABLE statement in the following cases:

Use of this option will not prevent normal lock escalation from occurring.

In addition, note that using ALTER TABLE to push locks to the table level is a global approach, affecting all applications and users that access that table. Another choice is for individual applications to use the LOCK TABLE statement. This allows you to go to table locks at an application level, not a database level.

Lock Compatibility

Table 35 indicates whether a lock request is granted if another process holds or is requesting a lock on the same resource in a given state. A no indicates that the requestor must wait until all incompatible locks are released by other processes. Note that a timeout can occur when waiting for a lock. A yes indicates that the lock is granted (unless someone else is waiting for the resource).

Table 35. Lock Type Compatibility

State of Held Resource
State Being Requested none IN IS NS S IX SIX U NX X Z NW W
none yes yes yes yes yes yes yes yes yes yes yes yes yes
IN yes yes yes yes yes yes yes yes yes yes no yes yes
IS yes yes yes yes yes yes yes yes no no no no no
NS yes yes yes yes yes no no yes yes no no yes no
S yes yes yes yes yes no no yes no no no no no
IX yes yes yes no no yes no no no no no no no
SIX yes yes yes no no no no no no no no no no
U yes yes yes yes yes no no no no no no no no
NX yes yes no yes no no no no no no no no no
X yes yes no no no no no no no no no no no
Z yes no no no no no no no no no no no no
NW yes yes no yes no no no no no no no no yes
W yes yes no no no no no no no no no yes no
Abbreviations:
I
Intent
N
None
NS
Next Key Share
S
Share
NX
Next Key Exclusive
X
Exclusive
U
Update
Z
Super Exclusive
NW
Next Key Weak Exclusive
W
Weak Exclusive

For details of these lock types, refer to the discussion in "Attributes of Locks".

Legend:
  • yes - grant lock requested immediately
  • no - wait for held lock to be released or timeout to occur

Assume that application A holds a lock on a table that application B also wants to access. The database manager requests, on behalf of application B, a lock of some particular mode. If the mode of the lock held by A permits the lock requested by B, the two locks (or modes) are said to be compatible.

If the lock mode requested for application B is not compatible with the lock held by application A, application B cannot continue. Instead, it must wait not only until application A releases its lock, but until all existing incompatible locks are released.

Lock Conversion

Lock conversion occurs when a process accesses a data object on which it already holds a lock, and the mode of access requires a more restrictive lock than the one already held. A process can hold only one lock on a data object at any time, although it can (indirectly through a query) request a lock many times on the same data object. The operation of changing the mode of the lock already held is called a conversion.

The conversion case for rows is simple: As an example, a conversion occurs if an X is needed and an S or U is held.

There are more distinct lock modes for tables than for rows. IX (Intent Exclusive) and S (Shared) locks are special cases, however. Neither S nor IX is considered to be more restrictive than the other, so if one of these is held and the other required, the resulting conversion is to a SIX (Share with Intent Exclusive) lock. All other conversions result in the requested lock mode becoming the mode of the lock held, if the requested mode is more restrictive.

A query to update a row can also produce a dual conversion. Suppose the row had been read through an index access and was locked as S. The table containing the row would have a covering intention lock. Suppose it is an IS rather than an IX. Then, if the row is subsequently changed, the table lock is converted to an IX, and the row to an X.

As a reminder, the application of locks usually takes place implicitly during the execution of a query. Understanding the kinds of locks obtained for different queries and table and index combinations can assist you in designing and tuning your application. See "Factors Affecting Locking" for more information on this topic.

Lock Escalation

Lock escalation is an internal mechanism to reduce the number of locks held. Escalation is from many row locks (in a single table) to a single table lock.

Lock escalation occurs when too many locks (of any type) are currently held.

Lock escalation can occur for a specific database agent if the agent exceeds its allocation of the lock list (see "Maximum Percent of Lock List Before Escalation (maxlocks)").

Such escalation is handled internally; the only externally detectable result might be a reduction in concurrent access on one or more tables. Normally, in a properly configured database, lock escalation occurs infrequently.

An example of lock escalation is when an application designer uses an index on a large table to increase performance and concurrency; however, the application accesses a large percentage of records in the table. The database manager is not able to predict (in this case) that so much of the table will be locked, and locks each record individually rather than only locking the table either S or X.

Sometimes, the process receiving the escalation request (internally) holds few or no record locks on any table. The reason for this escalation is that one process (or processes) can be holding many locks (although this amount is below the database configuration parameter of locks per process) but not quite enough to trigger the escalation request. The process might not request another lock or access the database again except to end the transaction. Then another process can request the lock or locks that trigger the escalation request.

If lock escalation reduces concurrency to an unacceptable level, you can do the following:

Lock Waits and Timeouts

Without lock timeout detection, in an abnormal situation, your application may have to wait for a lock to be released. This might occur, for example, when a transaction is waiting for a lock held by another user's application, and the other user has left their workstation without performing some interaction to allow their application to commit their transaction which would release the lock. Obviously, this results in poorer application performance. To avoid stalling your program in such a case, you can use the locktimeout configuration parameter to set the maximum time that any application waits to obtain a lock. (See "Lock Timeout (locktimeout)".)

Using this parameter helps avoid global deadlocks, especially in distributed unit of work (DUOW) applications. If the lock times out, that is, if the time that the lock request is pending is greater than the locktimeout value, your application receives an error and your transaction is rolled back. For example, if program1 tries to acquire a lock which is already held by program2, program1 returns SQLCODE -911 (SQLSTATE 40001) with reason code 68 if the timeout is expired.

Deadlocks

In the database manager, contention for locks by processes using the database can result in deadlocks. For example, Process 1 locks table A in X (exclusive) mode and Process 2 locks table B in X mode; if Process 1 then tries to lock table B in X mode and Process 2 tries to lock table A in X mode, the processes will be in a deadlock. In a deadlock, both processes are suspended until their second lock request is granted, and neither request is granted until one of the processes performs a commit or rollback. This state remains indefinitely until an external agent activates one of the processes and forces it to perform a rollback.

Deadlocks in the lock system are handled in the database manager by an asynchronous system background process called the deadlock detector. The deadlock detector becomes active periodically as determined by the dlchktime configuration parameter (see "Time Interval for Checking Deadlock (dlchktime)"). When the deadlock detector becomes active, it examines the lock system for deadlocks. If the database has been partitioned then each partition sends lock graphs to the catalog node where global deadlock detection takes place.

If a deadlock is found, the deadlock detector selects a deadlocked process to roll back. The selected process is awakened, and it returns to the calling application with SQLCODE -911 (SQLSTATE 40001), with reason code 2. The database manager rolls back the selected process automatically. When the rollback has completed, the locks belonging to the victim process are released, and the other processes involved in the deadlock can eventually proceed.

Selecting the proper interval for the deadlock detector is necessary to ensure good performance. An interval that is too short would cause unnecessary overhead, and one that is too long would allow a deadlock to delay a process for an unacceptable amount of time. For example, a wakeup interval set to 30 minutes could allow a deadlock to exist for nearly 30 minutes. The application designer must balance the possible delays in resolving deadlocks with the overhead of detecting them.

In a partitioned database, the interval should be the same on all partitions (the dlchktime configuration parameter must be updated to the same value on all partitions). If the value is smaller at the catalog node than at other partitions, phantom deadlocks may be detected. If the value is larger at the catalog node than at other partitions, it may appear as if more than two intervals pass before a deadlock is detected. If a large number of deadlocks are detected in a partitioned database, you should increase the value of the dlchktime parameter to account for lock waits and communication waits.

Another problem can occur when an application with more than one independent process accessing the database is structured in such a way as to make deadlocks likely. An example is an application in which several processes access the same table for reads and then writes. If the processes do read-only SQL queries at first and then do SQL updates on the same table, the chances of deadlocks occurring increase because of potential contention between the processes for the same data. For instance, if two processes read the table, and then update the table, they get into a state where process A is trying to get an X lock on a row, on which process B has an S lock and vice versa. The result could be a deadlock. To avoid these deadlocks, applications that access data with the intention of modifying it should use the FOR UPDATE OF clause when performing a select. This clause ensures that a U lock is imposed when process A attempts to read the data.

Factors Affecting Locking

The mode and granularity of database manager locks are determined by a combination of factors: the type of processing the application performs, how it accesses data, and several parameters that you can specify.

Application Processing

For the purpose of determining lock attributes, processing can be classified as one of four types:

Read-only
This type includes all select-statements which are intrinsically read-only (refer to the SQL Reference for information about cursors), have an explicit FOR READ ONLY clause, or are ambiguous but for which the SQL compiler presumes to be read-only due to the value of the BLOCKING option specified on the PREP or BIND command. It requires only Share locks (S or IS).

Intent to change
This type includes all select-statements with the FOR UPDATE clause, or which the SQL compiler presumes to be intended for change as a result of the interpretation of the ambiguous statement. It uses Share and Update locks (S, U, and X for rows, IX, U, X for tables).

Change
This type includes UPDATE, INSERT, and DELETE, but not UPDATE WHERE CURRENT OF or DELETE WHERE CURRENT OF. It requires Exclusive locks (X or IX).

Cursor controlled
This type includes UPDATE WHERE CURRENT OF and DELETE WHERE CURRENT OF. It also requires Exclusive locks (X or IX).

A statement that inserts, updates or deletes against a target table, based on the result from a sub-select statement, does two types of processing. The locks for the tables returned in the sub-select are determined by the rules for read-only processing; for the target table, by the rules for change processing.

Access Paths

An access path is the method selected by the optimizer for retrieving data from a specific table reference. (See "Data Access Concepts and Optimization".) The access path chosen by the optimizer can have a significant effect on the lock modes. For example, when an index scan is used to locate a specific row, the optimizer will likely choose row-level locking (IS) for the table. This type of access would be used to select information for a single employee from the EMPLOYEE table, that has an index on employee number (EMPNO), with a statement such as the following: 

     SELECT *
       FROM EMPLOYEE
       WHERE EMPNO = '000310';

Similarly, when no index is used, the entire table must be scanned in sequence to find the selected rows, and may acquire a single table level lock (S). For example, this type of access might be used to select all the male employees, using a statement such as this: 

     SELECT *
       FROM EMPLOYEE
       WHERE SEX = 'M';

The following tables provide an overview of which locks are obtained for what kind of access plan. See "Application Processing" for definitions of the column headings. Also see "Data Access Concepts and Optimization" for definitions of the access method. Note that cursor controlled type processing uses the lock mode of the underlying cursor until the application finds a row to update or delete. For this type of processing, no matter what the lock mode of a cursor, an exclusive lock will always be obtained to perform the update or delete.

In the following tables, if only one lock mode is shown, it is a table level lock mode. If two lock modes are shown, the first is the table level lock mode and the second is the row level lock mode.

Table 36. Lock Modes for Table Scans
Isolation Level Read-only Intent to Change Change
Access Method: Table scan with no predicates
RR S U X
RS IS / NS IX / U IX / X
CS IS / NS IX / U IX / X
UR IN IX / U IX / X 
Access Method: Table Scan with predicates
RR S U U
RS IS / NS IX / U IX / U
CS IS / NS IX / U IX / U
UR IN IX / U IX / U

Table 37. Lock Modes for Index Scans
Isolation Level Read-only Intent to Change Change
Access Method: Index Scan with no predicates
RR S IX / U X
RS IS / NS IX / U IX / X
CS IS / NS IX / U IX / X
UR IN IX / U IX / X 
Access Method: Index Scan a single qualifying row
RR IS / S IX / U IX / X
RS IS / NS IX / U IX / X
CS IS / NS IX / U IX / X
UR IN IX / U IX / X 
Access Method: Index Scan with start and stop predicates only
RR IS / S IX / S IX / X
RS IS / NS IX / U IX / X
CS IS / NS IX / U IX / X
UR IN IX / U IX / X 
Access Method: Index Scan with predicates
RR IS / S IX / S IX / U
RS IS / NS IX / U IX / U
CS IS / NS IX / U IX / U
UR IN IX / U IX / U

Table 38 shows the lock modes for cases in which reading of the data pages is deferred to allow the list of rows to be:

The deferred access of the data pages implies that access to the row occurs in two steps and this results in more complex locking scenarios. There are two major categories which depend on the isolation level. Since the repeatable read isolation level keeps all locks acquired until the end of the transaction, the locks acquired in the first step are held and there is no need to acquire further locks in the second step. For the read stability and cursor stability isolation levels, locks must be acquired during the second step. To maximize concurrency, we don't acquire locks during the first step and rely on the re-application of all predicates to ensure that only qualifying rows are returned.

Table 38. Lock Modes for Index Scans used for Deferred Data Page Access
Isolation Level Read-only Intent to Change Change
Access Method: Index Scan with no predicates
RR IS / S IX / S X
RS IN IN IN
CS IN IN IN
UR IN IN IN
Access Method: Deferred Data Page Access, after an index scan with no predicates
RR IN IX / S X
RS IS / NS IX / U IX / X
CS IS / NS IX / U IX / X
UR IN IX / U IX / X 
Access Method: Index Scan with predicates
RR IS / S IX / S IX / S
RS IN IN IN
CS IN IN IN
UR IN IN IN
Access Method: Index Scan with start and stop predicates only
RR IS / S IX / S IX / X
RS IN IN IN
CS IN IN IN
UR IN IN IN
Access Method: Deferred Data Page Access, after an index scan with predicates
RR IN IX / S IX / S
RS IS / NS IX / U IX / U
CS IS / NS IX / U IX / U
UR IN IX / U IX / U

The access path is not controlled by the user; it is chosen by the Optimizer.

The access path used can affect the mode and granularity of a lock. For example, in an application using the repeatable read (RR) isolation level, an UPDATE query that uses a table scan without predicates, would use an X lock on the table. If rows were located through an index, the database manager might choose to lock individual rows of the table.

LOCK TABLE Statement

You can override the rules for acquiring initial lock modes by using the LOCK TABLE statement in an application.

The statement locks an entire table. Only the table specified in the LOCK TABLE statement is locked. Parent and dependent tables of the specified table are not locked. You must determine whether locking other tables that can be accessed is necessary to achieve the desired result in terms of concurrency and performance. The lock is not released until the unit of work is committed or rolled back.

If a table is normally shared among several users, you might want to lock it for the following reasons:

LOCK TABLE IN SHARE MODE
You want to access data that is consistent in time; that is, data current for a table at a specific point in time. If the table experiences frequent activity, the only way to ensure that the entire table remains stable is to lock it. For example, your application wants to take a snapshot of a table. However, during the time your application needs to process some rows of a table, other applications are updating rows you have not yet processed. This is allowed with repeatable read, but this action is not what you want.

As an alternative, your application can issue the LOCK TABLE IN SHARE MODE statement: no rows can be changed, regardless of whether you have retrieved them or not. You can then retrieve as many rows as you need, knowing that the rows you have retrieved have not been changed just before you retrieved them.

With LOCK TABLE IN SHARE MODE, other users can retrieve data from the table, but they cannot update, delete, or insert rows into the table.

LOCK TABLE IN EXCLUSIVE MODE
You want to update a large part of the table. It is less expensive and more efficient to prevent all other users from accessing the table than it is to lock each row as it is updated, and then unlock the row later when all changes are committed.

With LOCK TABLE IN EXCLUSIVE MODE, all other users are locked out; no other applications can access the table unless they are uncommitted read applications.

For more details on the LOCK TABLE statement, refer to the SQL Reference manual.

An alternative to the use of the LOCK TABLE statement is the ALTER TABLE statement with the LOCKSIZE parameter. For more details on the ALTER TABLE statement, refer to the SQL Reference manual.

CLOSE CURSOR WITH RELEASE

When you close a cursor with the CLOSE CURSOR statement that includes the WITH RELEASE clause, all read locks (if any) that have been held for the cursor are released. Read locks are IS, S, and U table locks as well as S, NS, and U row locks. For more information on lock modes, see "Attributes of Locks".

The WITH RELEASE clause has no effect for cursors that are operating under the CS or UR isolation levels. When specified for cursors that are operating under the RS or RR isolation levels, the WITH RELEASE clause ends some of the guarantees of those isolation levels. Specifically, an RS cursor may experience the nonrepeatable read phenomenon, and an RR cursor may experience either the nonrepeatable read or phantom read phenomenon.

If a cursor that is originally RR or RS is reopened after being closed using the WITH RELEASE clause, then new read locks will be acquired.

Summary of Locking Considerations

The following are points to remember about locking:

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]