4.4 Briefly describe the screens in the lock subsystem
Key points
- The DBEnvironment must be active to obtain any screen in the subsystem (in
other words, at least one user must be connected to the DBE). A warning is
returned if you try to access one of these screens if nobody is connected.
Menu
SQLMONITOR SUBSYSTEMS (and abbreviations):
OVERVIEW IO LOAD LOCK SAMPLEIO STATIC
/o /i /loa /loc /sa /st
CURRENT SUBSYSTEM SCREENS:
LOCK OBJECT SESSION IMPEDE MEMORY TABSUMMARY
l o s i m t
Lock screen
SQLMONITOR LOCK => l /* From the current subsystem */
SQLMONITOR OVERVIEW => /loc /* From another subsystem */
This screen provides information about lock activity for the entire
DBEnvironment.
Each line item represents a separate lock object, which can either be an
entire table or a particular page or row from the table. The "G"
field ("T","P", or "R" for Table, Page or Row)
indicates the granularity (size) of the object that is locked. The
"OWNER.TABLE"
and "PAGE/ROW ID" fields identify which particular data object is
locked. The "LOCK QUEUE" field displays one character for each
session that is trying to acquire a lock on the particular object.
The most important use of this screen is to identify the lock objects that
have waiters or converters. These lock objects are the bottlenecks in your
DBEnvironment which cause one or more sessions to wait before they may complete
their processing. Every effort should be made to tune your DBEnvironment to
minimize the amount of waiting that sessions must perform. You can issue the
SET LOCKFILTER command to restrict the display of locks on this screen, so that
only lock objects that are causing contention are shown. Afterward, you can
use the LOCK OBJECT screen to identify the sessions in the queue for each
particular lock.
SQLMONITOR LOCK => help tune
1 GENERAL LOCK INFORMATION
2 GENERAL LOCK INFORMATION - LOCK GRANULARITIES
3 GENERAL LOCK INFORMATION - LOCK MODE TYPES
4 GENERAL LOCK INFORMATION - LOCK MODE COMPATIBILITY
5 GENERAL LOCK INFORMATION - LOCK MODE SEVERITY
6 REMOVE NON-DATABASE PROCESSING FROM WITHIN TRANSACTIONS
7 MODIFY THE TRANSACTION LENGTH AND/OR USE SAVEPOINTS IN THE PROGRAM
8 CHANGE THE IMPLICIT LOCKING STRUCTURE ON A TABLE
9 USE THE LOCK TABLE STATEMENT TO OVERRIDE IMPLICIT LOCKING
10 MODIFY EACH TRANSACTION'S ISOLATION LEVEL TO IMPROVE CONCURRENCY
11 DISABLE DDL (DATA DEFINITION LANGUAGE) IN PRODUCTION ENVIRONMENTS
12 ISSUE A COMMIT WORK STATEMENT WHEN SECTIONS ARE REVALIDATED
13 HOW TO USE FREEZE/UNFREEZE TO RESOLVE A DEADLOCK
Lock object screen
SQLMONITOR LOCK => o /* From the current subsystem */
SQLMONITOR OVERVIEW => /loc o /* From another subsystem */
This screen provides information about all of the sessions that have been
granted access to a single lock object, are waiting for access to the lock
object, or are converting an existing lock on the lock object to a stronger
one.
Think of the LOCK OBJECT screen as "zooming in" or "clicking"
on the LOCK QUEUE field on the LOCK screen. The LOCK OBJECT screen provides
information about each session in the queue for a particular object,
including logon information, the name of the program the session is running,
and the isolation level and priority of the transaction in effect for the
session. MOD indicates the mode of the lock that has been granted to the
session, NEW indicates the mode of the lock that the session is waiting to
acquire.
SQLMONITOR LOCK => help tune
No additional tuning suggestions are available for this screen.
Lock session screen
NOTE: You are prompted for the CID of the session you wish to examine if
you do not provide it on the command line:
SQLMONITOR LOCK => s /* From the current subsystem */
CID: 1
SQLMONITOR OVERVIEW => /loc s /* From another subsystem */
CID: 1
You can provide the CID on the command which invokes the screen:
SQLMONITOR LOCK => s 1 /* From the current subsystem */
SQLMONITOR OVERVIEW => /loc s 1 /* From another subsystem */
This screen provides lock activity information for a single session.
The LOCK SESSION screen answers the question "What locks have been granted
to this session, and what locks is it waiting to acquire?".
SQLMONITOR LOCK => help tune
No additional tuning suggestions are available for this screen.
Lock impede screen
NOTE: You are prompted for the CID of the session you wish to examine if
you do not provide it on the command line:
SQLMONITOR LOCK => i /* From the current subsystem */
CID: 1
SQLMONITOR OVERVIEW => /loc i /* From another subsystem */
CID: 1
You can provide the CID on the command which invokes the screen:
SQLMONITOR LOCK => i 1 /* From the current subsystem */
SQLMONITOR OVERVIEW => /loc i 1 /* From another subsystem */
The LOCK IMPEDE screen identifies sessions that are waiting for locks granted
to this particular session. It answers the question "What locks have been
granted to this session that are causing other sessions to wait?"
SQLMONITOR LOCK => help tune
No additional tuning suggestions are available for this screen.
Lock memory screen
SQLMONITOR LOCK => m /* From the current subsystem */
SQLMONITOR OVERVIEW => /loc m /* From another subsystem */
The LOCK MEMORY screen allows you to identify how many locks have
been allocated to each session.
If you obtain error code -4008 ("ALLBASE/SQL shared memory lock allocation
failed in DBCORE") frequently, or if you simply want to minimize the
amount of shared memory used by the DBEnvironment, you can use the
LOCK MEMORY screen to identify the session(s) using the most number of
locks. For example, the screen above shows that CID 2 is using more locks
than CID 1 or CID 3.
After you have identified the session(s) using the most number of locks, use
the LOCK TABSUMMARY screen to determine the tables on which the largest number
of locks have been allocated, and the program that each session is running.
These programs may be good candidates for tuning for lock optimization.
SQLMONITOR LOCK => help tune
1 CHANGE THE IMPLICIT LOCKING STRUCTURE ON A TABLE
2 USE THE LOCK TABLE STATEMENT TO OVERRIDE IMPLICIT LOCKING
3 MODIFY EACH TRANSACTION'S ISOLATION LEVEL TO IMPROVE CONCURRENCY
4 HOW TO USE FREEZE/UNFREEZE TO RESOLVE LOCK ALLOCATION FAILURES
Lock tabsummary screen
NOTE: You can either summarize the locks that have been allocated for use
by a particular session, or summarize all locks that exist in the
DBEnvironment. You are prompted if you do not provide information on the
command line:
SQLMONITOR LOCK => t /* From the current subsystem */
CID [or ALL]: 2
SQLMONITOR OVERVIEW => /loc t /* From another subsystem */
CID [or ALL]: 2
You can provide the CID (or specify "ALL") on the command which
invokes the screen:
SQLMONITOR LOCK => t 2 /* From the current subsystem */
SQLMONITOR OVERVIEW => /loc t 2 /* From another subsystem */
This screen allows you to identify how many locks are allocated at each
granularity for each table.
To reduce shared memory, you may wish to change the implicit locking structure
on a table (for example, change a table from PUBLICROW (row level locking) to
PUBLIC (page level locking)) or use the LOCK TABLE statement to
override
implicit locking (for example, obtain a single SHARE lock on a table instead
of individual SHARE locks on many pages or rows). Or, you may want to
modify each transaction's isolation level to release locks more quickly.
SQLMONITOR LOCK => help tune
1 CHANGE THE IMPLICIT LOCKING STRUCTURE ON A TABLE
2 USE THE LOCK TABLE STATEMENT TO OVERRIDE IMPLICIT LOCKING
3 MODIFY EACH TRANSACTION'S ISOLATION LEVEL TO IMPROVE CONCURRENCY
4 HOW TO USE FREEZE/UNFREEZE TO RESOLVE LOCK ALLOCATION FAILURES
Page last updated on November 29, 1995
|
Printable version
