DB2 10.5 for Linux, UNIX, and Windows

Locking behavior on partitioned tables

In addition to an overall table lock, there is a lock for each data partition of a partitioned table.

This allows for finer granularity and increased concurrency compared to a nonpartitioned table. The data partition lock is identified in output from the db2pd command, event monitors, administrative views, and table functions.

When a table is accessed, a table lock is obtained first, and then data partition locks are obtained as required. Access methods and isolation levels might require the locking of data partitions that are not represented in the result set. After these data partition locks are acquired, they might be held as long as the table lock. For example, a cursor stability (CS) scan over an index might keep the locks on previously accessed data partitions to reduce the cost of reacquiring data partition locks later.

Data partition locks also carry the cost of ensuring access to table spaces. For nonpartitioned tables, table space access is handled by table locks. Data partition locking occurs even if there is an exclusive or share lock at the table level.

Finer granularity allows one transaction to have exclusive access to a specific data partition and to avoid row locking while other transactions are accessing other data partitions. This can be the result of the plan that is chosen for a mass update, or because of the escalation of locks to the data partition level. The table lock for many access methods is normally an intent lock, even if the data partitions are locked in share or exclusive mode. This allows for increased concurrency. However, if non-intent locks are required at the data partition level, and the plan indicates that all data partitions might be accessed, then a non-intent lock might be chosen at the table level to prevent data partition deadlocks between concurrent transactions.

LOCK TABLE statements

For partitioned tables, the only lock acquired by the LOCK TABLE statement is a table-level lock. This prevents row locking by subsequent data manipulation language (DML) statements, and avoids deadlocks at the row, block, or data partition level. The IN EXCLUSIVE MODE option can be used to guarantee exclusive access when updating indexes, which is useful in limiting the growth of indexes during a large update.

Effect of the LOCKSIZE TABLE option on the ALTER TABLE statement

The LOCKSIZE TABLE option ensures that a table is locked in share or exclusive mode with no intent locks. For a partitioned table, this locking strategy is applied to both the table lock and to data partition locks.

Row- and block-level lock escalation

Row- and block-level locks in partitioned tables can be escalated to the data partition level. When this occurs, a table is more accessible to other transactions, even if a data partition is escalated to share, exclusive, or super exclusive mode, because other data partitions remain unaffected. The notification log entry for an escalation includes the impacted data partition and the name of the table.

Exclusive access to a nonpartitioned index cannot be ensured by lock escalation. For exclusive access, one of the following conditions must be true:

The statement must use an exclusive table-level lock
An explicit LOCK TABLE IN EXCLUSIVE MODE statement must be issued
The table must have the LOCKSIZE TABLE attribute

In the case of partitioned indexes, exclusive access to an index partition is ensured by lock escalation of the data partition to an exclusive or super exclusive access mode.

Interpreting lock information

The SNAPLOCK administrative view can help you to interpret lock information that is returned for a partitioned table. The following SNAPLOCK administrative view was captured during an offline index reorganization.

SELECT SUBSTR(TABNAME, 1, 15) TABNAME, TAB_FILE_ID, SUBSTR(TBSP_NAME, 1, 15) TBSP_NAME,
       DATA_PARTITION_ID, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_ESCALATION L_ESCALATION
  FROM SYSIBMADM.SNAPLOCK 
  WHERE TABNAME like 'TP1' and LOCK_OBJECT_TYPE like 'TABLE_%' 
  ORDER BY TABNAME, DATA_PARTITION_ID, LOCK_OBJECT_TYPE, TAB_FILE_ID, LOCK_MODE


TABNAME TAB_FILE_ID TBSP_NAME  DATA_PARTITION_ID LOCK_OBJECT_TYPE LOCK_MODE L_ESCALATION
------- ----------- ---------- ----------------- ---------------- --------- ------------
TP1           32768 -                         -1 TABLE_LOCK       Z                    0
TP1               4 USERSPACE1                 0 TABLE_PART_LOCK  Z                    0
TP1               5 USERSPACE1                 1 TABLE_PART_LOCK  Z                    0
TP1               6 USERSPACE1                 2 TABLE_PART_LOCK  Z                    0
TP1               7 USERSPACE1                 3 TABLE_PART_LOCK  Z                    0
TP1               8 USERSPACE1                 4 TABLE_PART_LOCK  Z                    0
TP1               9 USERSPACE1                 5 TABLE_PART_LOCK  Z                    0
TP1              10 USERSPACE1                 6 TABLE_PART_LOCK  Z                    0
TP1              11 USERSPACE1                 7 TABLE_PART_LOCK  Z                    0
TP1              12 USERSPACE1                 8 TABLE_PART_LOCK  Z                    0
TP1              13 USERSPACE1                 9 TABLE_PART_LOCK  Z                    0
TP1              14 USERSPACE1                10 TABLE_PART_LOCK  Z                    0
TP1              15 USERSPACE1                11 TABLE_PART_LOCK  Z                    0
TP1               4 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1               5 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1               6 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1               7 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1               8 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1               9 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1              10 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1              11 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1              12 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1              13 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1              14 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1              15 USERSPACE1                 - TABLE_LOCK       Z                    0
TP1              16 USERSPACE1                 - TABLE_LOCK       Z                    0

  26 record(s) selected.

In this example, a lock object of type TABLE_LOCK and a DATA_PARTITION_ID of -1 are used to control access to and concurrency on the partitioned table TP1. The lock objects of type TABLE_PART_LOCK are used to control most access to and concurrency on each data partition.

There are additional lock objects of type TABLE_LOCK captured in this output (TAB_FILE_ID 4 through 16) that do not have a value for DATA_PARTITION_ID. A lock of this type, where an object with a TAB_FILE_ID and a TBSP_NAME correspond to a data partition or index on the partitioned table, might be used to control concurrency with the online backup utility.