Understanding DB2: Learning Visually with Examples (2nd Edition)

2017-07-07 02:10:07

11.6. Diagnosing Lock Problems

We have discussed how isolation levels affect the DB2 locking strategy. The various lock modes allow DB2 to provide diversified concurrent scenarios. For many applications the locking mechanism works transparently, but for others issues such as lock waits, deadlocks, and lock escalations can occur.

DB2 has a comprehensive set of tools that you can use to obtain information about locking. In the following sections we will look at some of the tools that are available and how they can be used to troubleshoot locking problems.

11.6.1. Using the list applications Command

The list applications command issued with the show detail clause shows the status of each application. Use this command as the first diagnostic step if you suspect a lock wait condition exists. You can also use the Control Center to get similar information. From the object tree right-click on the desired instance name and choose Applications. Note, however, that not all the columns from the list applications show detail command are reported by the Control Center.

Figure 11.27 shows the output of the list applications show detail command. The output is over 240 bytes wide; to understand locking behavior, focus on the output columns listed in Table 11.5.

Table 11.5. Output Columns of the list applications show detail Command
Output Column	Description
Status	A value of Lock-wait means the application is blocked by a lock held by a different application. Don't be confused by a value of UOW Waiting, which means that the application (unit of work) is in progress and not blocked by a lock. It is simply not doing any work at the moment.
Status Change Time	This is of particular interest for an application with Lock-wait status. The value shows when the lock wait began. Note that the UOW monitor switch must be on for the status change time to be reported.
Appl. Handle	The handle is a unique ID for an active application. Being able to identify the application handle is important when it is holding locks that are causing contention problems. You can use the application handle in the `FORCE APPLICATION` command to terminate its current transaction.

Figure 11.27. Output of the list applications show detail command

11.6.2. Using the force application Command

You can use the force application command in conjunction with the list applications command to resolve concurrency problems. A typical scenario occurs when user Bob issues a query that does not COMMIT. He then goes for a one-hour coffee break, leaving other users unable to continue their work because Bob's query is holding several locks on the same objects. In this scenario, a DBA can issue a list applications command to identify that the connection is from Bob by looking at the Appl. Handle column, as shown in Figure 11.28.

Figure 11.28. The force application command

Figure 11.28 shows there are three connections to the SAMPLE database. Next, the DBA identifies user BOB whose connection has the application handle of 208, and issues the command:

force application (208)

The command executes asynchronously, meaning that it will not wait for the connection to be terminated to return. After a few seconds, when he issues the list applications command again, he sees that Bob's connection has been removed, allowing the other connections to continue their work.

To force several connections in one command use the syntax:

force application (Appl. Handle, Appl. Handle, ... )

There may be situations when you need to force all the connections against all the databases in the instance. In such situations use the all option of the force application command:

force application all

NOTE

The force application command does not prevent other users from connecting to a database..

The force application command always preserves database integrity, so only users who are idling or executing interruptible database operations can be terminated.

11.6.3. Using the Snapshot Monitor

You can use the Snapshot Monitor to capture information about a database and any connected applications at a specific time. Snapshot monitoring provides the majority of the useful information for dealing with lock issues. Before you can obtain snapshot information in full extent, you must turn on the monitor switches. See section 16.7, Snapshot Monitoring, for a detailed discussion on setting monitor switches and capturing information. In this section we focus on the relevant commands required to continue with our lock diagnostic discussion.

Turn on all the monitor switches with this command:

update monitor switches using bufferpool on lock on sort on statement on table on timestamp on uow on

To get a database snapshot, issue:

get snapshot for all on database_name

From the output of this command you obtain the following snapshot monitoring components in sequence. Snapshots that are most relevant to locking are have an asterisk (*) after them.

Database snapshot*

Buffer pool snapshot

Dynamic SQL snapshot

Application snapshot*

Table space snapshot

Database lock snapshot*

Table snapshot

The database snapshot part of the result contains a good summary of the locking information for the specified database. Figure 11.29 shows only the pertinent lines to locking from a sample database snapshot output.

If you want to "zoom" into each application and understand the types of locks they are holding, examine the application snapshots. Figure 11.30 shows the most important subset of information for an application in a lock wait situation.

Figure 11.29. Database snapshot with lock-related information

Database Snapshot . . . . Locks held currently = 8 Lock waits = 0 Time database waited on locks (ms) = 315704 Lock list memory in use (Bytes) = 1692 Deadlocks detected = 0 Lock escalations = 0 Exclusive lock escalations = 0 Agents currently waiting on locks = 1 Lock Timeouts = 0

Figure 11.30. Application snapshot with lock-related information

Application Snapshot Application handle = 14 (1) Application status = Lock-wait Status change time = 08-15-2004 14:30:36.907312 Snapshot timestamp = 08-15-2004 14:30:43.414574 Time application waited on locks (ms) = 6507 (2) Total time UOW waited on locks (ms) = 6507 UOW start timestamp = 08-15-2004 14:30:36.889356 Statement start timestamp = 08-15-2004 14:30:36.890986 Dynamic SQL statement text: select * from org (3) ID of agent holding lock = 13 Application ID holding lock = *LOCAL.DB2.011905182946 Lock name = 0x02000200000000000000000054 Lock attributes = 0x00000000 Release flags = 0x00000001 Lock object type = Table Lock mode = Exclusive Lock (X) (4) Lock mode requested = Intention Share Lock (IS) (5) Name of tablespace holding lock = USERSPACE1 Schema of table holding lock = WILKINS Name of table holding lock = ORG Lock wait start timestamp = 08-15-2004 14:30:36.907318

In Figure 11.30:

(1) You can see that application handle 14 is in a lock-wait state.

(2) It has been waiting for 6,507 milliseconds for locks.

(3, 5) It is currently executing a SELECT statement and requesting for an Intent Share (IS) lock on a table.

(4) However, application handle 13 holds an exclusive (X) lock on the same table.

To further investigate on the problem, you can use the list application command and see what application handle 13 is doing and check its application snapshot for more information.

Like the application snapshot, the database lock snapshot has a section for each connected application (see Figure 11.31).

Figure 11.31. Database lock snapshot

Database Lock Snapshot Database name = SAMPLE Database path = C:\DB2\NODE0000\SQL00002\ Input database alias = SAMPLE Locks held = 3 Applications currently connected = 1 Agents currently waiting on locks = 0 Snapshot timestamp = 03-04-2004 13:39:06.465057 Application handle = 18 Application ID = *LOCAL.DB2.01D3C4183155 Sequence number = 0007 Application name = db2bp.exe CONNECT Authorization ID = CLARALIU Application status = UOW Waiting Status change time = Not Collected Application code page = 1252 Locks held = 3 Total wait time (ms) = 0 List Of Locks Lock Name = 0x02000500040000000000000052 Lock Attributes = 0x00000020 Release Flags = 0x40000000 Lock Count = 1 Hold Count = 0 Lock Object Name = 4 Object Type = Row Tablespace Name = USERSPACE1 Table Schema = CLARALIU Table Name = EMPLOYEE Mode = X Lock Name = 0x94928D848F9F949E7B89505241 Lock Attributes = 0x00000000 Release Flags = 0x40000000 Lock Count = 1 Hold Count = 0 Lock Object Name = 0 Object Type = Internal P Lock Mode = S Lock Name = 0x02000500000000000000000054 Lock Attributes = 0x00000000 Release Flags = 0x40000000 Lock Count = 1 Hold Count = 0 Lock Object Name = 5 Object Type = Table Tablespace Name = USERSPACE1 Table Schema = CLARALIU Table Name = EMPLOYEE Mode = IX

The snapshot in Figure 11.31 shows that application handle 18 is holding 3 locks. One of them is an exclusive (X) lock on a row in the employee table, another lock is an internal P lock, and the last one is an Intent Exclusive (IX) lock on the table employee. (Internal P locks are internal locks managed by DB2; there is nothing you can do about them.)

11.6.4. Using Snapshot Table Functions

You can also invoke SQL functions to produce locking information displayed in a table format. The function SNAPSHOT_LOCK produces one row for each lock held, and SNAPSHOT_ LOCKWAIT produces one row for each lock wait condition. Each row contains the same data that is provided in the snapshot monitoring output discussed in the previous section.

To invoke these snapshot table functions, use:

SELECT * FROM TABLE ( SNAPSHOT_LOCK ('sample', 0) ) AS s SELECT * FROM TABLE ( SNAPSHOT_LOCKWAIT ('sample', 0) ) AS s

The first argument of the snapshot function specifies the database you want to monitor and the second argument is the database partition number.

11.6.5. Using the Event Monitor

You can use a DB2 Event Monitor to obtain performance information on events as they occur on the server, such as statement or transaction completion and deadlock resolution. For DB2 locking issues, the Event Monitor is particularly useful for collecting deadlock information. Snapshots can provide counts on the number of deadlocks that are occurring. However, you need to obtain application details before the deadlock is detected and rolled back by the deadlock detector. The only way to guarantee that you get detailed information on each deadlock is to create and activate an Event Monitor for deadlocks with details. Chapter 4, Using the DB2 Tools, and Chapter 16, Database Performance Considerations, also discuss Event Monitors.

Figure 11.32 shows how to create a deadlock Event Monitor from the Control Center. To display the Create Event Monitor window, right-click on the Event Monitors folder under database you want to monitor, and then click on Create. In the Create Event Monitor window, specify the name of the Event Monitor. Under Event Types, select Deadlocks and also check the With details option. Then click OK.

Figure 11.32. Creating a deadlock Event Monitor

After clicking OK, the new Event Monitor is created and started.

If a deadlock occurs, the DB2 deadlock detector identifies the two applications involved and rolls back one of the transactions. From the Control Center, right-click on the Event Monitor you just created and choose Stop Event Monitoring (see Figure 11.33). Next, from the Control Center right-click again on the Event Monitor you just created and choose Analyze Event Monitor Records. This displays the Event Analyzer window, (see Figure 11.34).

Figure 11.33. Stopping event monitoring and analyzing Event Monitor records

Figure 11.34. Navigating to the deadlocked connection

From the Event Analyzer window, select the Deadlocked Connection as shown in Figure 11.34.

At this point you will see the connections that were involved in the deadlock. You can then drill down to the Data Elements on any connection for more information as shown in Figure 11.35.

Figure 11.35. Drilling down to the data elements of a particular application

In the Data Elements window (see Figure 11.36), you will see the statements that are involved and the locks the application is holding. For example, from Figure 11.36 you can tell that five locks were held on the employee table when the statement SELECT * FROM employee was executing.

Figure 11.36. Data elements in an application

11.6.6. Using the Activity Monitor

Chapter 4, Using the DB2 Tools, introduced the activity monitor. We limit our discussion in this chapter to locking-related topics.

Set up the Activity Monitor by selecting the database you want to monitor as illustrated in Figure 11.37.

Figure 11.37. Setting up the Activity Monitor

Select or create a monitoring task. There are few system-defined monitoring tasks. One of them is to capture locking information, which is highlighted in Figure 11.38. You can also create a new monitoring task by clicking the New button.

Figure 11.38. Selecting or creating a monitoring task

You can see In Figure 11.39 that you can choose to monitor all or selected applications. Click Finish to complete the Activity Monitor setup.

Figure 11.39. Specifying applications to be monitored

As applications are connected to the database, the activity and status of each will be listed under Report data (see Figure 11.40).

Figure 11.40. Selecting the type of information to be reported

To zoom into a particular application and examine its associated lock chains, right-click on the application handle number and choose Show Lock Chains (see Figure 11.41).

Figure 11.41. Showing an application's lock chains

You will get a pictorial view of the locks being held by the application in the Lock Chain dialog,. Click on the Legend button to find out what each icon means (see Figure 11.42).

Figure 11.42. The application lock chain legend

You can also see the lock details for each node by selecting Show Lock Details as shown in Figure 11.43.

Figure 11.43. Showing an application's lock details

You can use the similar information (shown in Figure 11.44) for detailed locking analysis.

Figure 11.44. Lock details

11.6.7. Using the Health Center

The Health Center is a graphical tool used to analyze and improve the health of DB2. It provides four indicators in the Application Concurrency category: lock escalation rate, lock list utilization, percentage of applications waiting on locks, and deadlock rate. You can set warning and alarm levels for the indicators, enable the indicators, and define an action to be taken when the thresholds are reached, such as taking a snapshot in the Health Center. The Health Center is discussed in more detail in Chapter 4, Using the DB2 Tools.