HP 3000 Manuals

ALLBASE/SQL Performance and Monitoring Guidelines

Lock Subsystem 

Use the Lock subsystem to troubleshoot performance bottlenecks caused by
lock contention.  This section describes how to handle lock waits,
deadlocks, and lock allocation failures.

Lock Waits 

If an end user complains of a hung session, perhaps his or her session is
actually waiting for a lock.  A session will wait for a lock if another
session has already acquired an incompatible lock on the same object.
For more information on lock waits, see the chapter "Concurrency Control
through Locks and Isolation Levels" in the ALLBASE/SQL Database 
Administration Guide.

For example, we can use SQLMON to find out why the session of the user
logged on as BNORTON appears to be hung.  Since the user is connected to
the PartsDBE DBEnvironment, issue the following command:

     SQLMONITOR  OVERVIEW => SET DBENV PartsDBE 

To make the screens easier to read, set the lock filter to limit the lock
information displayed to locks for which at least one session is waiting
(W) and locks that are being converted to a stronger mode (C):

     SQLMONITOR  OVERVIEW => SET LOCKFILTER //WC// 

Overview Session Screen.   

Invoke the Overview Session screen to obtain the PINs of all database
sessions connected to the DBEnvironment:

     SQLMONITOR  OVERVIEW => /o s 

From the following screen, we can see that the BNORTON login has a CID of
4 and a wait status.


            

Lock Session Screen.   

To get locking information for CID 4, access the Lock Session screen:

     SQLMONITOR  OVERVIEW => /loc s 
     CID: 4 

The value of the GWC field in the following screen verifies that the
database session that appears to be hung is actually waiting to acquire a
lock.  The WAITS FOR column identifies CID 5 as the session that is
causing CID 4 to wait.


            

Now you can invoke the Lock Session screen for PID 6167, which has
acquired a lock on the PurchDB.Vendors table.  To make the screen easier
to read, limit the lock information displayed to the PurchDB.Vendors
table.  Change the lock filter so that all locks are displayed.

     SQLMONITOR  LOCK => SET LOCKTABFILTER PurchDB.Vendors 

     SQLMONITOR  LOCK => SET LOCKFILTER //// 

     SQLMONITOR  LOCK => /loc s 5 


            

Lock Impede Screen.   

To identify all sessions that are impeded by PIN 5, invoke the Lock
Impede screen:

     SQLMONITOR  LOCK => i 
     CID: 5 

The following screen reveals that the lock held by CID 5 is causing a
number of other sessions, including CID 4, to wait.


            

To solve this problem, you should investigate CID 5.  The Lock Session
screen reveals that CID 5 is running ISQL, and is run by the user logged
on as DGREEN. If CID 5 does not immediately end the transaction holding
the incompatible lock, you should consider executing the ISQL statement
TERMINATE USER. If the transaction has been marked with a label, you can
clearly identify the part of the program the user is running.  PID 6167
is executing a repeatable read (RR) transaction.

A better solution might be to change the transaction's isolation level.
The XID field displays a system-generated integer that uniquely
identifies the transaction, but it does not identify the SQL statements
that have been executed.  The LABEL field displays an 8-character string
that the application program defines in the BEGIN WORK or SET TRANSACTION
statements.  If the application program uses the LABEL field carefully,
it will be easy for you to debug all the transactions in your application
programs.

Deadlocks 

A deadlock occurs when two transactions are each waiting for a database
object which the other has locked.  ALLBASE/SQL automatically detects
deadlocks and resolves them by rolling back one of the transactions.  For
a detailed discussion on deadlocks, see the chapter "Concurrency Control
through Locks and Isolation Levels" in the ALLBASE/SQL Database 
Administration Guide.

To examine the locks that cause a deadlock, you must freeze one of the
sessions before ALLBASE/SQL rolls it back and releases its locks.  The
steps in the following example show how to create a deadlock, freeze a
session, and use SQLMON to examine the deadlocked session's locks.  To
perform these steps, you will need access to a Sample DBEnvironment and
the ability to create at least four windows on your workstation or PC.
For instructions on setting up the Sample DBEnvironment, see the chapter
"Practice with ALLBASE/SQL Using PartsDBE" in Up and Running with 
ALLBASE/SQL.

Step 1 Open Four Windows.   

Use the windows as follows:

Window 1              The first ISQL session that is deadlocked.

Window 2              The second deadlocked ISQL session.  Since this
                      session will be rolled back by ALLBASE/SQL, it is
                      the one you want to freeze.

Window 3              An SQLMON session for examining the locks held by
                      the deadlocked sessions.

Window 4              The window used to create the file that releases
                      the session in window 2.

Step 2 Set Up the Freeze.     

In window 2, set the DBCORERR environment variable to 1024 before running
ISQL:

     :SETVAR DBCORERR '1024'

When troubleshooting a production system, set the DBCORERR environment
variable for all sessions that might be involved in the deadlock, since
it is difficult to predict which session will be rolled back.  See the
section "Freezing DBEnvironment Sessions" in this chapter for more
information on setting the DBCORERR environment variable.

Step 3 Create a Deadlock.   

Run ISQL in windows 1 and 2, and issue the following statements:

Window 1              CONNECT TO PartsDBE

Window 2              CONNECT TO PartsDBE

Window 1              UPDATE PurchDB.Parts SET SalesPrice = 1.2 *
                      SalesPrice

Window 2              SELECT * FROM PurchDB.SupplyPrice

Window 2              Exit from the ISQL browser but do not commit work.

Window 1              UPDATE PurchDB.SupplyPrice SET UnitPrice = 1.2 *
                      UnitPrice

Window 2              SELECT * FROM PurchDB.Parts

After step g has been completed, a message is displayed in window 2:

     ***** PIN = 99 RC = 1024 : Wed Mar 31 14:08:07 1993 ****
     *** ERROR - Failed to open SQLUNFRZ twice ***

This message indicates that the DBEnvironment session having PIN 99 (that
is, the session running in window 2) is frozen on DBCORE error
1024, which means that a deadlock has been detected.  Under these
circumstances, ALLBASE/SQL does not roll back the session until you
release it.  This will be described in "Step 5 Release the Frozen
Session" .

The deadlock is illustrated in Figure 7-1 .


          Figure 7-1.  Deadlock Example 

CID 6 is running in window 1, and CID 2 is running in window 2.  The
solid lines represent locks that have been granted, and the dashed lines
represent sessions that have been waiting to acquire locks.  The letter S
designates a share lock, and the number 6 represents a share plus intent
exclusive (SIX) lock.  This lock information is displayed by the SQLMON
screens accessed in the next step.

Step 4 Examine the Locks with SQLMON.   

In window 3, run SQLMON and set the DBEnvironment to PartsDBE. Issue the
SET LOCKFILTER command to limit the lock information displayed to
sessions that are waiting to acquire or convert locks.

     : SQLMON 

     SQLMONITOR  OVERVIEW => SET DBENV PartsDBE 

     SQLMONITOR  OVERVIEW => SET LOCKFILTER //WC// 

Invoke the Lock screen to see which tables have been locked:

     SQLMONITOR  OVERVIEW => /lock 


            

In the Lock screen, you can see the PurchDB.Parts and PurchDB.SupplyPrice
tables.  There are two locks on each table:  a share plus intent
exclusive (6) lock, and a share (S) lock.  The characters displayed in
inverse video represent locks held by sessions that are waiting.

To identify the CID of each session in the lock queue of each of the
objects shown above, access the Lock Object screen:

     SQLMONITOR  LOCK => o 

In the following screen, CID 6 has been granted (G) a share plus intent
exclusive (6) lock on the PurchDB.Parts table.  CID 2 is waiting to
acquire a share (S) lock on PurchDB.Parts.  A share (S) lock has been
granted to CID 2 on the PurchDB.SupplyPrice table, and CID 6 is waiting
to acquire a share plus intent exclusive (6) lock on PurchDB.SupplyPrice.


            

Use the Lock Impede screen to verify that the sessions are deadlocked.
To identify which sessions are impeded by CID 6, invoke the Lock Impede
screen as follows:

     SQLMONITOR  LOCK => i 
     CID: 6 

In the following screen, CID 2 is listed in the WAITING column.  CID 2 is
waiting for CID 6 to release the lock it holds on PurchDB.Parts.


            

If you specify CID 2, you see the following screen.


            

Note that CID 6 is waiting for CID 2 to release the lock on the table
PurchDB.Supplyprice.  These two sessions are indeed deadlocked.

Step 5 Release the Frozen Session.   

In window 4, edit a file named SQLUNFRZ and enter the deadlock error
number, 1024, on the first line.  The file's group and account must match
the frozen session's or DBEnvironment's group and account.  When you save
the file, the session is released, and a message such as the following is
displayed in window 2:

     ***** UNFREEZING PIN = 99 RC=1024 : Wed Mar 31 14:54:38 1993 *****
     Deadlock detected.  (DBERR 14024)

Lock Allocation Failures 

If a session requests a lock when space is not available in the runtime
control block, a lock allocation error (DBERR 4008) occurs.  In the
example that follows, the session that encounters DBERR 4008 is frozen,
allowing you to examine the locks with SQLMON.

Step 1 Open Three Windows.   

To perform the steps in this example, your workstation or PC must have a
windows environment.  The windows will be used as follows:

Window 1              The DBEnvironment session that encounters the lock
                      allocation failure

Window 2              An SQLMON session

Window 3              The window used to create the file that releases
                      the session in window 1

Step 2 Set Up the Freeze.     

In window 1, set the DBCORERR environment variable to 1035:

     :SETVAR DBCORERR '1035'

Step 3 Generate the Error.   

In window 1, run the application that encounters the lock allocation
failure.  When DBCORE detects the failure, it freezes the session and
displays a message such as the following on $STDLIST:

      *****PIN=17325******RC=1035 : Wed Apr  7 11:09:57 1993

Step 4 Investigate the Session with SQLMON.   

In window 2, run SQLMON, set the DBEnvironment option, and invoke the
Overview screen:

     : SQLMON 

     SQLMONITOR  OVERVIEW => SET DBENV DBEnvironmentName 

     SQLMONITOR  OVERVIEW => o 

The Overview screen shown below indicates that the runtime control block
space is nearly full and no more control blocks can be allocated.


            

Access the Lock Memory screen as follows:

     SQLMONITOR  OVERVIEW => /loc m 

In the screen that follows, CID 1 has a total of 228 locks.  Most of the
locks are held on rows, because the table in this example was created
with the PUBLICROW type.  To solve this problem, you should either alter
the table to PUBLIC to avoid numerous row level locks, or allocate
additional runtime control block pages.


            

Step 5 Release the Frozen Session.   

In window 3, edit a file named SQLUNFRZ and enter the lock allocation
failure error number, 1035, on the first line.  The file's group and
account must match the frozen session's or DBEnvironment's group and
account.  When you save the file, the session is released, and a message
such as the following appears in window 1:

      ***** UNFREEZING PIN=17325******RC=1035 : Wed Apr  7 11:29:26 1993
     ALLBASE/SQL shared memory lock allocation failed in DBCore.  (DBERR 4008)

Freezing DBEnvironment Sessions 

ALLBASE/SQL allows you to freeze a session whenever it encounters a
certain DBCORE error.  While the session is frozen, you can use SQLMON to
examine the session's locks.

To freeze a session, set the DBCORERR environment variable to one or more
DBCORE error numbers.  When the session encounters the errors you
specify, it freezes.  For example, to freeze a session that encounters
either DBCORE error 1024 (deadlock) or 1035 (lock allocation failure),
set DBCORERR from the C shell as follows:

     % setenv DBCORERR "1024 1035"

Korn shell and Bourne shell users should use the following commands:

     % DBCORERR="1024 1035"
     % export DBCORERR

If you set DBCORERR to a negative number, a session freezes if it
encounters an error whose number is greater than the absolute value of
DBCORERR. For example, if DBCORERR is set to -4000, the session freezes
if it encounters a DBCORE error greater than 4000.

When a session freezes, a message appears on standard output.  The CID
identifies the frozen session, and the RC field designates the DBCORE
error number:

     *****PID=18250******RC=1024 : Mon Mar 22 11:14:53 1993

DBCORE is a set of internal ALLBASE/SQL routines.  DBCORE errors have
different error numbers than DBERR errors.  For example, the DBCORE error
number for detecting deadlocks is 1024, but the DBERR number is 14024.
When setting the DBCORERR environment variable, be sure to use the DBCORE
error number, not the DBERR error number.

Releasing DBEnvironment Sessions 

To release a session, use a text editor to enter DBCORE error numbers in
a file.  When you save the file, the frozen session resumes execution.
The DBCORE error numbers in the file must correspond to the value of the
DBCORERR environment variable.  To release a session on any DBCORE error
number, enter an ASCII zero in the file.

The file can contain one or more lines, and each line can contain one or
more numbers.  You can name the file as follows:

SQLunfrz             Releases only those sessions running from the
                     subdirectory containing this file.

/tmp/SQLunfrz.xxx    Releases only the session whose PID is specified in
                     xxx.  For example, if the CID is 8895, the name of
                     the file should be /tmp/SQLunfrz.8895.

/tmp/SQLunfrz.ALL    Releases only the frozen sessions that qualify,
                     according to the error numbers entered in the file.

For example, to release any session that froze because it encountered
either DBCORE error 1024 or 1035, use an editor to create a file named
/tmp/SQLunfrz.ALL, and add the following lines to it:

     1024
     1035

When ALLBASE/SQL releases a session, it displays a message on standard
output like the one below:

     ***** UNFREEZING PID=18250******RC=1024 : Mon Mar 22 11:16:59 1993

MPE/iX 5.0 Documentation