If one application holds a lock on a database object, another
application might not be able to access that object. For this reason,
row-level locks, which minimize the amount of data that is locked
and therefore inaccessible, are better for maximum concurrency than
block-level, data partition-level, or table-level locks.
However, locks require storage and processing time, so a single
table lock minimizes lock overhead.
The LOCKSIZE clause of the ALTER TABLE statement specifies the
granularity of locks at the row, data partition, block, or table level.
Row locks are used by default. Use of this option in the table definition
does not prevent normal lock escalation from occurring.
The ALTER TABLE statement specifies locks globally, affecting all
applications and users that access that table. Individual applications
might use the LOCK TABLE statement to specify table locks at an application
level instead.
A permanent table lock defined by the ALTER TABLE statement might
be preferable to a single-transaction table lock using the LOCK TABLE
statement if:
- The table is read-only, and will always need only S locks. Other
users can also obtain S locks on the table.
- The table is usually accessed by read-only applications, but is
sometimes accessed by a single user for brief maintenance, and that
user requires an X lock. While the maintenance program is running,
read-only applications are locked out, but in other circumstances,
read-only applications can access the table concurrently with a minimum
of locking overhead.
For a multidimensional clustering (MDC) table, you can specify
BLOCKINSERT with the LOCKSIZE clause in order to use block-level locking
during insert operations only. When BLOCKINSERT is specified, row-level
locking is performed for all other operations, but only minimally
for insert operations. That is, block-level locking is used during
the insertion of rows, but row-level locking is used to lock the next
key if repeatable read (RR) scans are encountered in the record ID
(RID) indexes as they are being updated. BLOCKINSERT locking might
be beneficial when:
- There are multiple transactions doing mass insertions into separate
cells
- Concurrent insertions into the same cell by multiple transactions
is not occurring, or it is occurring with enough data inserted per
cell by each of the transactions that the user is not concerned that
each transaction will insert into separate blocks
DB2 10.5 for Linux, UNIX, and Windows