Tuning Subsystems › Locking and Contention

Locking and Contention

DB2 uses locking to ensure consistent data based on user requirements and to avoid losing data updates. You must balance the need for concurrency with the need for performance. You want to minimize the following items:

Suspension

An application process is suspended when it requests a lock that is already held by another application process and cannot be shared. The suspended process temporarily stops running.

Time out

An application process times out when it is terminated because the process has been suspended for longer than a preset interval. DB2 terminates the process and returns a -911 or -913 SQL code.

Deadlock

A deadlock occurs when two or more application processes hold locks on resources that the others need and without which they cannot proceed.

Use the following methods to monitor DB2 locking problems:

• Use the -DISPLAY DATABASE command to find out what locks are held or waiting at any moment.
• Use EXPLAIN to monitor the locks that are required by a particular SQL statement or all the SQL in a particular plan or package.
• Activate a DB2 statistics class 3 trace with IFCID 172. This report outlines all the resources and agents involved in a deadlock and the significant locking parameters, such as lock state and duration, which is related to their requests.
• Activate a DB2 statistics class 3 trace with IFCID 196. This report shows detailed information on timed-out requests, including the holders of the unavailable resources.

The way DB2 issues locks is complex. Lock issuance depends on the type of processing being done, the LOCKSIZE parameter that is specified when the table was created, the isolation level of the plan or package being executed, and the method of data access.