Lock Subsystem

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.

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

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.

Use the windows as follows:

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.

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

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 Title not available.

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.

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.

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)

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:

   :SETVAR DBCORERR '1035'

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

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.

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)