Lock Subsystem

Invoke the Overview Session screen to obtain the PIDs 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 PID of 26180 and a wait status.

To get locking information for PID 26180, access the Lock Session screen:

  SQLMONITOR OVERVIEW => /loc s

  PID: 26180

The value of the GWC field in the following screen verifies that the database session that appears to be hung is actually waiting to convert a lock. The WAITS FOR column identifies PID 6167 as the session that is causing PID 26180 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 6167

To identify all sessions that are impeded by PID 6167, invoke the Lock Impede screen:

  SQLMONITOR LOCK => i

  PID: 6167

The following screen reveals that the lock held by PID 6167 is causing a number of other sessions, including PID 26180, to wait.

To solve this problem, you should investigate PID 6167. The Lock Session screen reveals that PID 6167 is running ISQL, and is run by the user logged on as DGREEN. If PID 6167 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.

Use the windows as follows:

In window 2, set the DBCORERR environment variable to 1024 before running ISQL. If you are using the C shell, issue the following command:

  % setenv DBCORERR 1024

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

  % DBCORERR=1024

  % export DBCORERR

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.

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

When you complete step g, you see a message in window 2:

   *****PID=4727******RC=1024 : Wed Mar 31 14:08:07 1993

This message indicates that the DBEnvironment session having PID 4727 (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 “Deadlock Example”.

Figure 7-1 Deadlock Example

PID 4687 is running in window 1, and PID 4727 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.

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 PID 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, PID 4687 has been granted (G) a share plus intent exclusive (6) lock on the PurchDB.Parts table. PID 4727 is waiting to acquire a share (S) lock on PurchDB.Parts. A share (S) lock has been granted to PID 4727 on the PurchDB.SupplyPrice table, and PID 4687 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 PID 4687, invoke the Lock Impede screen as follows:

  SQLMONITOR LOCK => i

  PID: 4687

In the following screen, PID 4727 is listed in the WAITING column. PID 4727 is waiting for PID 4687 to release the lock it holds on PurchDB.Parts.

If you specify PID 4727, you see the following screen.

Note that PID 4687 is waiting for PID 4727 to release the lock on the table PurchDB.Supplyprice. These two sessions are indeed deadlocked.

In window 4, edit a file named /tmp/SQLunfrz.ALL and enter the deadlock error number, 1024, on the first line. When you save the file, the session is released, and a message such as the following is displayed in window 2:

   ***** UNFREEZING PID=4727******RC=1024 : Wed Mar 31 14:54:38 1993

   Deadlock detected.  (DBERR 14024)

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

In window 1, set the DBCORERR environment variable to 1035. If you are using the C shell, issue the following command:

  % setenv DBCORERR 1035

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

  % DBCORERR=1035

  % export DBCORERR

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 standard output:

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

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, PID 17325 has a total of 324 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.

  Leslie-Anne says that a new screen is needed

In window 3, edit a file named /tmp/SQLunfrz.ALL and enter the lock allocation failure error number, 1035, on the first line. When you save the file, the session is released, and a message such as the following appears in window 1:

   ***** UNFREEZING PID=17325******RC=1035 : Wed Apr  7 11:29:26 1993

  ALLBASE/SQL shared memory lock allocation failed in DBCore.  (DBERR 4008)