DB2 10.5 for Linux, UNIX, and Windows

Lock management

Lock management is one of the factors that affect application performance. Review this section for details about lock management considerations that can help you to maximize the performance of database applications.

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.
Lock granularity
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.
Lock attributes
Database manager locks have several basic attributes.
Factors that affect locking
Several factors affect the mode and granularity of database manager locks.
Lock type compatibility
Lock compatibility becomes an issue when one application holds a lock on an object and another application requests a lock on the same object. When the two lock modes are compatible, the request for a second lock on the object can be granted.
Next-key locking
During insertion of a key into an index, the row that corresponds to the key that will follow the new key in the index is locked only if that row is currently locked by a repeatable read (RR) index scan. When this occurs, insertion of the new index key is deferred until the transaction that performed the RR scan completes.
Lock modes and access plans for standard tables
The type of lock that a standard table obtains depends on the isolation level that is in effect and on the data access plan that is being used.
Lock modes for MDC and ITC tables and RID index scans
The type of lock that a multidimensional clustering (MDC) or insert time clustering (ITC) table obtains during a table or RID index scan depends on the isolation level that is in effect and on the data access plan that is being used.
Lock modes for MDC block index scans
The type of lock that a multidimensional clustering (MDC) table obtains during a block index scan depends on the isolation level that is in effect and on the data access plan that is being used.
Locking behavior on partitioned tables
In addition to an overall table lock, there is a lock for each data partition of a partitioned table.
Lock conversion
Changing the mode of a lock that is already held is called lock conversion.
Lock waits and timeouts
Lock timeout detection is a database manager feature that prevents applications from waiting indefinitely for a lock to be released.
Deadlocks
A deadlock is created when two applications lock data that is needed by the other, resulting in a situation in which neither application can continue executing.