Tools for Monitoring Locking

You can identify problems with concurrency using one of the following lock monitoring tools:

• The Performance Monitor utility in VDBA, which displays locking data in a GUI environment
• The lock_trace trace flag, which displays specific locking activity
• The lockstat utility, which provides a summary listing and a "snapshot" of all of the locking activity in your installation
• The Interactive Performance Monitor (IPM), which provides locking data in a forms-based monitoring utility

Performance Monitor

The Performance Monitor utility allows you to view locking information in an easy-to-use GUI environment. By clicking on the Locking System branch in the window, you can immediately see a summary of the locking system information in the Detail pane.

Locking information you can view in the Performance Monitor includes:

• Lock lists
• Locked resources (databases, tables, pages, and others)
• Information about a lock (including the lock list, server, session, and resource of the lock)

The navigational tree in the left pane allows you to drill down to the information you need quickly, making it easy to identify locking conditions that need attention.

VDBA provides an alternative set of system administration tools, including monitoring performance. For instructions on using VDBA screens to monitor performance, see VDBA online help.

For more information on using the Performance Monitor utility, see the System Administrator Guide.

Set lock_trace Statement

The set lock_trace statement enables you to start and stop lock tracing at any time during a session. This statement has the following syntax:

set [no]lock_trace

Important! Use set lock_trace as a debugging or tracing tool only. The lock_trace option is not a supported feature. This means that you must not include this feature in any application-dependent procedure.

To use set lock_trace you can:

• Issue the set lock_trace statement from a terminal monitor. For example, to start tracing locks, issue the following statement:

  set lock_trace;

  To stop tracing locks, issue the following statement:

  set nolock_trace;

• Include the set lock_trace statement in an embedded language program.
• Specify set lock_trace with an environment variable or logical. For example, to start lock tracing with ING_SET issue the following statement at the operating system prompt:

  Windows:

  set ING_SET=set lock_trace

  UNIX:

  C shell:

  setenv ING_SET "set lock_trace"

  Bourne shell:

  ING_SET="set lock_trace"
  export ING_SET

  VMS:

  define ing_set "set lock_trace"

The same methods are used for set lockmode. For details on these methods, see Ways to Specify a Set Lockmode Statement.

When you use set lock_trace during a session, you receive information about locks used and released by your statements. This information is displayed with the results of your statement.

If you use an environment variable/logical to set the lock_trace flag, you receive output for utility startup queries as well as for query language statements.

lock_trace Output

An example of lock_trace output is shown here. The column headings above the example are added in this guide to help describe the output.

Action   Level   Qual.  Mode      Timeout       Key

LOCK:    PAGE    PHYS   Mode: S   Timeout: 0    Key: (inv,iiattribute,21)
UNLOCK:  PAGE    Key: (inv,iiattribute,21)
LOCK:    PAGE    PHYS   Mode: S   Timeout: 0    Key: (inv,iiindex,11)
UNLOCK:  PAGE    Key: (inv,iiindex,11)
LOCK:    TABLE   PHYS   Mode: IS  Timeout: 0    Key: (inv,parts)
LOCK:    PAGE           Mode: S   Timeout: 0    Key: (inv,parts,0)

The lock_trace output is in the following format:

action  level  qualifiers  Mode:  Timeout:  Key:

where:

• action

  Is the action, which can be LOCK, UNLOCK, or CONVERT. For example, a lock was used (LOCK) or released (UNLOCK).

• level

  Is the lock level, which can be TABLE, PAGE, ROW, or VALUE.

  Other strings may appear, such as SV_PAGE or BM_DATABASE, which are internal cache control locks.

• qualifiers

  Specify more information about the lock. The qualifier can be:

  NOWT—Do not wait if the lock is unavailable.

  PHYS—Lock can be released prior to end of transaction (physical lock).

  Blank—Lock is held until the transaction commits or aborts (logical lock).

  Other qualifiers that may appear have internal meaning only.

• Mode

  Is the lock mode. Values can be:

  S = shared lock

  U = update lock

  X = exclusive lock

  IS = intended share

  IX = intended exclusive

  N = null lock

  SIX = shared intended exclusive

• Timeout

  Is the default timeout or the timeout set with set lockmode statement.

• Key

  Describes the resource being locked. It consists of the database name, table name, partition and page number (shown as P.p where P is the physical partition number, and p is the page number), and (for row locking) the row number.

  For VALUE level locks, the Key is database name, table name, and three numbers describing the value being locked. If the table is partitioned, the table name may be shown as an internal partition name, which looks like "iiXXX ppPPP-table name" where XXX is an internally assigned number, and PPP is the physical partition number. For example:

  LOCK:  TABLE PHYS Mode: IX  Timeout:   0 Key: (emp,ii119 pp2-range_1)

lock_trace Example

The set lock_trace output for the following transaction is shown here.

select * from parts where color = 'red';
update parts set price = 10 where partno = 11;
commit;

This guide numbers the lines of output in the example. Each line number is explained.

Note: If you run the same query several times, you begin to receive less set lock_trace output because the system catalog information is being cached.

select * from parts where color = 'red'

+------+-------------+------+-----------+-----+
|partno|partname |color |wt |price|
----------------------------------------------

*********************************************************************************
(1) LOCK:   PAGE   PHYS  Mode: S  Timeout: 0 Key: (inv,iirelation,11)
(2) LOCK:   PAGE   PHYS  Mode: S  Timeout: 0 Key: (inv,iiattribute,21)
(3) UNLOCK: PAGE   Key:  (inv,iiattribute,21)
(4) LOCK:   PAGE   PHYS  Mode: S  Timeout:   Key: (inv,iiattribute,19)
(5) UNLOCK: PAGE   Key:  (inv,iiattribute,19)
(6) UNLOCK: PAGE   Key:  (inv,iirelation,11)
(7) LOCK:   PAGE   PHYS  Mode: S  Timeout: 0 Key: (inv,iiindex,11)
(8) UNLOCK: PAGE   Key:  (inv,iiindex,11)
(9) LOCK:   TABLE  PHYS: Mode: IS Timeout: 0 Key: (inv,parts)
(10)LOCK:   PAGE         Mode: S  Timeout: 0 Key: (inv,parts,0)
*********************************************************************************
|1A12 |Truck        |red     |290.000   | $16.00 |
|1B5  |Bean bag     |red     |198.000   | $18.00 |
|20G  |Laser        |red     |165.000   | $15.80 |
+-----+-------------+--------+----------+--------+

(3 rows)

update parts set price = 10 where partno = 20G
*********************************************************************************
(11)LOCK:   TABLE   PHYS Mode: IX  Timeout: 0 Key: (inv,parts)
(12)LOCK:   PAGE         Mode: U   Timeout: 0 Key: (inv,parts,0)
(13)LOCK:   PAGE         Mode: X   Timeout: 0 Key: (inv,parts,0)
*********************************************************************************

(1 row)

commit 
*********************************************************************************
(14)UNLOCK: ALL     Tran-id: 092903CB0A7 
*********************************************************************************
End of Request

The following is an explanation of the lock_trace output:

1. A shared physical lock was taken on page 11 of the iirelation table of the inv (inventory) database.

   Remember that physical locks are internal and are released as soon as possible.

2. A shared physical lock was taken on page 21 of the iiattribute table of the inventory database.
3. The lock on page 21 of the iiattribute table was released.
4. A shared physical lock was taken on page 19 of the iiattribute table of the inventory database.
5. The lock on page 19 of the iiattribute table was released.
6. The lock on page 11 of the iirelation table was released.
7. A shared physical lock was taken on page 11 of the iiindex table of the inventory database.
8. The lock on page 11 of the iiindex table was released.
9. An intended shared lock was taken on the parts table.

   This is the first lock in this example that was placed on a user table.

10. A shared lock was taken on page 0 of the parts table.
11. An intended exclusive lock was taken on the parts table.
12. An update lock was taken on page 0 of the parts table.
13. An exclusive lock was taken on page 0 of the parts table.
14. All locks used during this transaction were released.