Analyze lock wait cases in DB2 for Linux,unix,and windows

DB2PD Options for lock monitoring

DB2PD is a utility for monitoring various DB2 database activities and troubleshooting. It is a stand-alone utility that starts with the DB2 engine, starting with the DB2 V8.2, and looks and functions like the Informix onstat utility. DB2PD is executed from the command line in an optional interactive mode. The utility runs very fast because it does not need to acquire any locks and runs outside the engine resources (which means it can even work on a pending engine). Snapshot monitoring also collects many of the monitor data provided by DB2PD, but the output format of DB2PD and snapshot monitoring varies significantly. This allows the DBA to choose a monitoring alternative that is more in line with the user's needs. This article focuses on the DB2PD option for lock monitoring.

The following figure shows the DB2PD option for lock monitoring:

Figure 1. DB2PD Options for lock monitoring

TRANHDL: Used to specify a transaction handle to monitor only locks held by a particular transaction.

Showlocks: This sub option expands the lock name into meaningful explanations. For a row lock, this option displays the following information: The table Space ID, the table ID, the partition ID, the page, and the slot. Syscat by using a catalog visual map. A query on tables, it is easy to map the table Space ID and table ID to the corresponding table name:

Listing 1. Map table Space ID, table ID to table schema, table name

SELECT TABSCHEMA, TABNAMe
FROM SYSCAT.TABLEs
WHERE TBSPACEID = tbspaceid AND TABLEID = tableid

Wait: If you specify a wait child option, DB2PD only displays the lock that the transaction is currently waiting for and the lock that is responsible for the waiting situation. This sub option greatly simplifies the lock wait analysis because it restricts the output to locks that participate in the lock wait case.

The DB2PD database and file options are not specific to lock monitoring, but apply to (almost) all DB2PD calls. The database option restricts the monitor data returned by DB2PD to the monitor data for a database. While the file option allows you to define a file to write the DB2PD output to the file.

Lock wait Analysis scene

Next, we start by using the DB2PD option described earlier to analyze an example lock wait case. To do this, we create the DB2 SAMPLE database:

Listing 2. Create a SAMPLE database

db2sampl

User A performs transaction A to provide them with a 10% bonus according to each manager's salary:

Listing 3. Update operations performed by transaction A

UPDATE EMPLOYEe
SET BONUS = SALARY * 0.1
WHERE JOB = 'MANAGER'

When transaction A is still running (because user A has not terminated the transaction with COMMIT or ROLLBACK), User B executes transaction B to increase the salary of each employee by 2%:

Listing 4. Update operations performed by transaction B

UPDATE EMPLOYEe
SET SALARY = SALARY * 0.02

Because transaction B is not complete, User B requests that the DBA determine the cause of the problem. The DBA then invokes DB2PD to see if there is a lock wait condition:

Listing 5. Check lock wait case

db2pd -db sample -locks wait showlocks
Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:33:05
Locks:
Address　　TranHdl　　Lockname　　　　　　　　　 Type　　　 Mode Sts Owner　　　Dur
0x050A0240 6　　　　　02000600050040010000000052 Row　　　　..X　W　 2　　　　　1
0x050A0DB0 2　　　　　02000600050040010000000052 Row　　　　..X　G　 2　　　　　1
HoldCount　Att　ReleaseFlg
0　　　　　0x00 0x40000000　 TbspaceID 2　TableID 6　PartitionID 0 Page 320 Slot 5
0　　　　　0x00 0x40000000　 TbspaceID 2　TableID 6　PartitionID 0 Page 320 Slot 5

There is a lock wait situation on a table with an ID of 6 in the table space with the DB2PD report ID 2. by checking Syscat. Tables,dba concluded that there was indeed a lock waiting on the table EMPLOYEE.

Listing 6. Determine the table involved in the lock wait situation

SELECT TABSCHEMA, TABNAMe
FROM SYSCAT.TABLEs
WHERE TBSPACEID = 2 AND TABLEID = 6
TABSCHEMA　　　　　　　　　　　　　　　 TABNAMe
--------------------------------------------------------------------------------
FECHNER　　　　　　　　　　　　　　　　 EMPLOYEe
1 record(s) selected.

For transaction 2 (column TRANHDL), the Status column (Sts) of the db2pd-locks output displays a "G". G represents "granted", meaning that a transaction handle of 2 has a row lock. In addition, column Mode shows that transaction 2 holds an X lock. The waiting transaction (a transaction that displays "W" ("Wait") in the column Sts) is a transaction with a handle of 6. The transaction is requesting an X lock on the same line as transaction 2. You can see this by looking at the owner column (which shows that transaction 2 is the owner of the lock) and comparing Lockname (the two entries in Db2pd-locks are the same).

Next, the DBA maps the transaction handle to the application. This can be done using another DB2PD option-transactions:

Listing 7. Mapping a transaction handle to an application

db2pd -db sample -transactions
Database Partition 0 -- Database SAMPLE -- Active -- Up 3 days 08:34:47
Transactions:
Address　　AppHandl [nod-index] TranHdl　　Locks　　　State　 Tflag　　　Tflag2
0x05141880 30　　　 [000-00030] 2　　　　　9　　　　　WRITE　 0x00000000 0x00000
0x05144880 34　　　 [000-00034] 6　　　　　5　　　　　WRITE　 0x00000000 0x00000