Locks and concurrency control

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.