Learning Visually with Examples [Electronic resources]

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 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.

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 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\SQL00002Input 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.

Deadlocks and also check the

With details option. Then click

OK .

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).

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.

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.

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.

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.

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.

As applications are connected to the database, the activity and status of each will be listed under

Report data (see Figure 11.40).

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).

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).

You can also see the lock details for each node by selecting

Show Lock Details as shown in Figure 11.43.

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

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.