To provide concurrency control and prevent uncontrolled
data access, the database manager places locks on buffer pools, tables,
data partitions, table blocks, or table rows.
A lock associates a database manager resource with
an application, called the lock owner, to control how
other applications access the same resource.
The database manager uses row-level locking or table-level locking,
as appropriate, based on:
- The isolation level specified at precompile time or when an application
is bound to the database. The isolation level can be one of the following:
- Uncommitted read (UR)
- Cursor stability (CS)
- Read stability (RS)
- Repeatable read (RR)
The different isolation levels are used to control access to
uncommitted data, prevent lost updates, allow non-repeatable reads
of data, and prevent phantom reads. To minimize performance impact,
use the minimum isolation level that satisfies your application needs.
- The access plan selected by the optimizer. Table scans, index
scans, and other methods of data access each require different types
of access to the data.
- The LOCKSIZE attribute for the table. The LOCKSIZE clause on the
ALTER TABLE statement indicates the granularity of the locks that
are used when the table is accessed. The choices are: ROW for row
locks, TABLE for table locks, or BLOCKINSERT for block locks on multidimensional
clustering (MDC) tables only. When the BLOCKINSERT clause is used
on an MDC table, row-level locking is performed, except during an
insert operation, when block-level locking is done instead. Use the
ALTER TABLE...LOCKSIZE BLOCKINSERT statement for MDC tables when transactions
will be performing large inserts into disjointed cells. Use the ALTER
TABLE...LOCKSIZE TABLE statement for read-only tables. This reduces
the number of locks that are required for database activity. For partitioned
tables, table locks are first acquired and then data partition locks
are acquired, as dictated by the data that will be accessed.
- The amount of memory devoted to locking, which is controlled by
the locklist database configuration parameter.
If the lock list fills up, performance can degrade because of lock
escalations and reduced concurrency among shared objects in the database.
If lock escalations occur frequently, increase the value of locklist, maxlocks,
or both. To reduce the number of locks that are held at one time,
ensure that transactions commit frequently.
A buffer pool lock (exclusive) is set whenever a buffer pool is
created, altered, or dropped. You might encounter this type of lock
when collecting system monitoring data. The name of the lock is the
identifier (ID) for the buffer pool itself.
In general, row-level locking is used unless one of the following
is true:
- The isolation level is uncommitted read
- The isolation level is repeatable read and the access plan requires
a scan with no index range predicates
- The table LOCKSIZE attribute is TABLE
- The lock list fills up, causing lock escalation
- An explicit table lock has been acquired through the LOCK TABLE
statement, which prevents concurrent application processes from changing
or using a table
In the case of an MDC table, block-level locking is used instead
of row-level locking when:
- The table LOCKSIZE attribute is BLOCKINSERT
- The isolation level is repeatable read and the access plan involves
predicates
- A searched update or delete operation involves predicates on dimension
columns only
The duration of row locking varies with the isolation level being
used:
- UR scans: No row locks are held unless row data is changing.
- CS scans: Row locks are generally held only while the cursor is
positioned on the row. Note that in some cases, locks might not be
held at all during a CS scan.
- RS scans: Qualifying row locks are held only for the duration
of the transaction.
- RR scans: All row locks are held for the duration of the transaction.
DB2 Version 9.7 for Linux, UNIX, and Windows