Oracle Concepts - Managing the Database Oracle Tips by Burleson Consulting

Managing the Database

 There are many tasks that a DBA must perform when managing the database. Among the most important (besides backup and recovery which we already covered) is the ability to resolve lock and deadlock situations. A lock situation is when one user is holding a lock on a resource when another user requires it. A lock situation usually involves one of two things, either a poorly written application or a user who has not committed a transaction. Both of these lock situations are resolved by finding the problem session or user and either picking up the phone and getting the user themselves to correct the situation or killing the users session via database commands.

Dead-lock situations occur when users require the same resource in such a manner that neither that can release it without the other releasing first. Usually Oracle will resolve deadlocks by rolling back the transaction of the first user process that detects the deadlock.

Find USER locking others/Kill problem USER

In order to find a lock holding user I suggest that the DBA be proactive and create the DBA_WAITERS and DBA_BLOCKERS views using the catblock.sql script which is located in the $ORACLE_HOME/rdbms/admin directory or its equivalent. Once the waiter and blocker views are created  a DBA can simply query either view to find the lock situation. Sources 29 and 30 show scripts to query these views.

rem NAME: blockers.sql
rem FUNCTION: Show all processes causing a dead lock
rem HISTORY: MRA 1/15/96 Created
rem
COLUMN username         FORMAT a10 HEADING 'Holding|User'
COLUMN session_id                         HEADING 'SID'
COLUMN mode_held        FORMAT a20 HEADING 'Mode|Held'
COLUMN mode_requested   FORMAT a20 HEADING 'Mode|Requested'
COLUMN lock_id1         FORMAT a20 HEADING 'Lock|ID1'
COLUMN lock_id2         FORMAT a20 HEADING 'Lock|ID2'
COLUMN type                               HEADING 'Lock|Type'
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF
START title132 'Sessions Blocking Other Sessions Report'
SPOOL rep_out\&db\blockers
SELECT
      a.session_id,
      username,
      type,
      mode_held,
      mode_requested,
      lock_id1,
      lock_id2
FROM
      sys.v_$session b,
      sys.dba_blockers c,
      sys.dba_lock a
WHERE
      c.holding_session=a.session_id and
      c.holding_session=b.sid
/
SPOOL OFF
PAUSE press enter/return to continue
CLEAR COLUMNS
SET LINES 80 PAGES 22 FEEDBACK ON ECHO ON

Source 29: Example script to report on blocking locks

rem
rem FUNCTION: Report on sessions waiting for locks
rem
COLUMN busername FORMAT A10 HEADING 'Holding|User'
COLUMN wusername FORMAT A10 HEADING 'Waiting|User'
COLUMN bsession_id HEADING 'Holding|Sid'
COLUMN wsession_id HEADING 'Waiting|Sid'
COLUMN mode_held FORMAT A20 HEADING 'Mode|Held'
COLUMN mode_requested FORMAT A20 HEADING 'Mode|Requested'
COLUMN lock_id1 FORMAT A20 HEADING 'Lock|Id1'
COLUMN lock_id2 FORMAT A20 HEADING 'Lock|Id2'
COLUMN type HEADING 'LOCK|TYPE'
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF
START TITLE132 'Processes Waiting on Locks Report'
SPOOL rep_out/&db/waiters
SELECT
      holding_session bsession_id,
      waiting_session wsession_id,
      b.username busername,
      a.username wusername,
      c.lock_type type,
      mode_held, mode_requested,
      lock_id1, lock_id2
FROM
sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE
c.holding_session=b.sid and
c.waiting_session=a.sid
/
SPOOL OFF
PAUSE press enter/return to continue
CLEAR COLUMNS
SET LINES 80 PAGES 22 FEEDBACK ON ECHO ON
TTITLE OFF

Source 30: Example script to report on blocking and waiting sessions.

Example output from the blockers report is shown in Listing 29 for a simple lock situation.

Date: 02/08/00                                                                            Page:   1
Time: 07:01 AM                Sessions Blocking Other Sessions Report                     SYSTEM
                                       aultdb database
 

          Holding    Lock       Mode         Mode                 Lock                 Lock
      SID User       Type       Held         Requested            ID1                  ID2
--------- ---------- ---------- ------------ -------------------- -------------------- ------------
        9 LABUSER    USER       Row-X (SX)   None                 2821                 0                                   
        9 LABUSER    USER       Exclusive    None                 196702               5547                                

Listing 29: example Blockers Report

The output for the same lock situation from the waiters report is shown in Listing 30.

Date:02/08/00
                                          Page:   1
Time: 07:01 AM                   Processes Waiting on Locks Report
SYSTEM
                                     aultdb database

  Holding   Waiting Holding    Waiting     Lock         ModMode                 Lock                 Lock
      SID       SID User       User        Type         Held                 Requested            ID1                  ID2
--------- --------- ---------- ----------- ------------ -------------------- -------------------- -------------------- -----------
        9        13 LABUSER    LABUSER2   Transaction   Exclusive            Exclusive            196702              5547

Listing 30: Example Waiters report

While both reports show who is holding the lock, I prefer the waiters report since it also shows who is being blocked . Using the reports it becomes a simple matter to retrieve the required information from the V$SESSION view to either notify the locking process owner or simply kill the offending process outright if it is a non-critical process. Tools such as Precise*SQL and Q from Savant provide lock status and the Q product allows killing of processes in a fairly easy manner.

This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It’s only $19.95 when you buy it directly from the publisher here.