Specifying the size of locks for a table space

The LOCKSIZE clause of CREATE and ALTER TABLESPACE statements specifies the size for locks held on a table or table space by application processes.

About this task

Begin program-specific programming interface information. You can use the ALTER TABLESPACE statement to change the lock size for user data. You can also change the lock size of any Db2 catalog table space that is not a LOB table space.The relevant options are:

LOCKSIZE TABLESPACE: A process acquires no table, page, row, LOB, or XML locks within the table space. That improves performance by reducing the number of locks maintained, but greatly inhibits concurrency.
LOCKSIZE TABLE: A process acquires table locks on tables in a segmented table space without partitions. If the table space contains more than one table, this option can provide acceptable concurrency with little extra cost in processor resources.
LOCKSIZE PAGE: A process acquires page locks, plus table, partition, or table space locks of modes that permit page locks (IS, IX, or SIX). The effect is not absolute: a process can still acquire a table, partition, or table space lock of mode S or X, without page locks, if that is needed. In that case, the bind process issues a message warning that the lock size has been promoted as described under Lock promotion.
LOCKSIZE ROW: A process acquires row locks, plus table, partition, or table space locks of modes that permit row locks (IS, IX, or SIX). The effect is not absolute: a process can still acquire a table, partition, or table space lock of mode S or X, without row locks, if that is needed. In that case, the bind process issues a message warning that the lock size has been promoted as described under Lock promotion.
LOCKSIZE ANY: Db2 chooses the size of the lock, usually LOCKSIZE PAGE. For LOB table spaces Db2 usually chooses LOCKSIZE LOB.
LOCKSIZE LOB: If a LOB must be accessed, a process acquires LOB locks and the necessary LOB table space locks (IS or IX). This option is valid only for LOB table spaces. See Locks for LOB data for more information about LOB locking.
LOCKSIZE XML: If XML must be accessed, a process acquires XML locks and the necessary XML table space locks (IS or IX). This option is valid only for XML table spaces. See Locks for XML data for more information about XML locking.

Db2 attempts to acquire an S lock on table spaces that are started with read-only access. If the LOCKSIZE is PAGE, ROW, or ANY and Db2 cannot get the S lock, it requests an IS lock. If a partition is started with read-only access, Db2 attempts to get an S lock on the partition that is started RO. For a complete description of how the LOCKSIZE clause affects lock attributes, see How Db2 chooses lock types.

The default option is LOCKSIZE ANY, and the LOCKRULE column of the SYSIBM.SYSTABLESPACE catalog table records the current value for each table space.

If you do not use the default, base your choice upon the results of monitoring applications that use the table space.

Procedure

The question of whether to use row or page locks depends on your data and your applications, and no single choice is best for every case. Consider the following trade-offs when deciding which LOCKSIZE option to use for a table space:

Generally, use LOCKSIZE PAGE as a design default.
Page-level locking generally results in better CPU time because fewer lock and unlock requests are required for sequential data access and manipulation. The amount of resources required to acquire, maintain, and release each lock is about the same in both row-level and page-level locking. Therefore, when page-level locking is used, a table space or index scan of data that has 10 rows per page might require only one-tenth of the CPU resource that would be used for row-level locking. Under page-level locking, sequentially inserted rows are also more likely to be in the same data page.
Use LOCKSIZE ROW when applications that access a table space encounter significant numbers of deadlock and timeout situations under LOCKSIZE PAGE.
Locking single rows instead of entire pages, might reduce the chances of contention with other process by 90%, especially when access is random. (Row locking is not recommended for sequential processing.)
Lock avoidance is important when row-level locking is used. Therefore, the recommendation is for applications to use ISOLATION(CS) and CURRENTDATA(NO) bind options whenever possible. In many cases, Db2 can avoid acquiring a lock when reading data that is known to be committed. Consequently, if only 2 of 10 rows on a page contain uncommitted data, Db2 must lock the entire page when using page lock. However, Db2 but might ask for locks on only the two uncommitted rows when using row-level locks. In that case, the resource required for row locks would be only twice as much, not 10 times as much, as that required for page-level locks.

However, if two applications update the same rows of a page, and not in the same sequence, then row locking might even increase contention. With page locks, the second application to access the page must wait for the first to finish and might time out. With row locks, the two applications can access the same page simultaneously. However, they might encounter a deadlock while trying to access the same set of rows.
When considering changing the lock size for a Db2 catalog table space, remember that internal processes such as bind, authorization checking, and utility processing might also access the catalog.