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

Source: Internet
Author: User
Tags command line db2 dba table name linux

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.