Locking / Documentos / Soporte Técnico / Servicios / Tilsor
 
 

Detecting and Resolving Locking Conflicts using TopSessions

Creation Date: 15-NOV-2001
Last Revision Date: 23-JAN-2002

***************************************************
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
**************************************************

Introduction
============

The focus of this paper is to give some concrete steps and examples in resolving the most common locking issues.
We will focus on Oracle Enterprise Manager's TopSessions 8i.

When checking for locking scenarios, you should:
1. find out which session is actually busy to do valuable work and if those sessions are blocked by any other sessions
2. find out which sql statements are executed by each session involved in the blocking diagram
3. analyze and understand the blocking situation
4. get the row locked in case of row locking

To resolve locking contention, one needs to free the resource by:
1. Asking the HOLDER to commit or rollback,
2. Killing the session which holds the lock
3. ROLLBACK FORCE or COMMIT FORCE if 2pc pending transaction.

To kill the blocking session, one need to ALTER SESSION KILL SESSION sid, serial#;
or use KILL USER SESSION menu found in TopSessions.


1. Using OEM TopSessions to find the blocking/blocked sessions
====================================

When you start OEM TopSessions, you should first go to the 'Options' in the 'Session' menu in order to specify that
you want to retrieve the data of all the sessions by choosing the 'Display all sessions' button in the 'Count' leaflet.
You should then order the sessions on the 'status' column to retrieve all active sessions. If a session has a known
command and the status is active it means the session is doing some work but it can also mean it is waiting for a lock.
The screen should look like:

USERNAME SID OSUSER *UserCol* COMMAND STATUS MACHINE PROGRAM
----------------------------------------------------------------
BACKGROUND 1 oracle8i UNKNOWN ACTIVE MYSERVER oracle@test (PMON)
BACKGROUND 2 oracle8i UNKNOWN ACTIVE MYSERVER oracle@test (DBW0)
BACKGROUND 3 oracle8i UNKNOWN ACTIVE MYSERVER oracle@test (LGWR)
BACKGROUND 4 oracle8i UNKNOWN ACTIVE MYSERVER oracle@test (CKPT)
BACKGROUND 5 oracle8i UNKNOWN ACTIVE MYSERVER oracle@test (SMON)
BACKGROUND 6 oracle8i UNKNOWN ACTIVE MYSERVER oracle@test (RECO)
REPADMIN 7 oracle8i UNKNOWN ACTIVE MYSERVER
SYSTEM 8 Administ UNKNOWN ACTIVE MYPC
SCOTT 11 oracle8i DELETE ACTIVE MYSERVER sqlplus@ladsrv1 (TNS V1-V3)
SCOTT 19 oracle8i UNKNOWN INACTIVE MYSERVER sqlplus@ladsrv1 (TNS V1-V3)

Name Description

USERNAME The name of the user connected to the instance.
SID Session Identifier
OSUSER Operating System username
*UserCol* This is a configurable column.
To select go to SESSIONS menu ->Options->Sort tab.
COMMAND Whatever the session is doing at that moment.
if a session has UNKNOWN then it is idle, except for the background processes
which always have UNKNOWN.
STATUS Status of the session.
MACHINE The computer from where the connection is originated.
PROGRAM The program used to connect to the instance.

To find out if its waiting on any lock, select that session and choose from the
SESSIONS menu -> Details. Then go to the leaflet 'Locks' and choose
'All Locks'. The screen should look like:

Session Lock Mode Mode Object Object Object Resource Resource
Username ID Type Held Requested Name Owner Type ID1 ID2
-----------------------------------------------------------------
+-SCOTT 11 TM Row Exclusive None Dept SCOTT TABLE 4933 0
|
+ SCOTT 19 TX Exclusive None R01 SYS Rollba 131157 82446
+-SCOTT 11 TX None Exclusive R01 SYS Rollba 131157 82446

Username User name.
Session Id SID - Session Identifier.
Type of Lock TX, TM, or any enqueue resource
Mode Held Lock mode held.
Mode Requested Lock mode requested.
Object name The object which has a lock on it.
Object Owner The object owner.

The type of the lock determines the signification of the resource id 1 and 2:
e.g.
Type of Lock Resource ID1 Resource ID2
TX(Transaction) Decimal representation of Decimal representation of
rollback segment number "wrap" number (number
and slot number times the rollback
slot has been reused)
TM(Table Locks) Object id of table being Always 0.
modified.

In the example session 11 is waiting on the session 19 to release the TX lock.
That means session 11 is trying to modify the same row as session 19;


2. Which sql statements are executed by each session?
=====================================================

You can find the statement that is executed by each session directly
in the Cursors leaflet. This is just the nice thing about using TopSessions
for debugging locking issues.


3. Understand the blocking situation
====================================

If the blocking lock type is a TX enqueue, it is a row locking situation: different
sessions are trying to change the same row in one particular table.

If the blocking lock type is anything else, then it is an enqueue locking issue.
The type of enqueue and the statement involved should explain the situation.
(see [NOTE:15476.1])


4. Which row is locked?
=======================

Session 11 has a TM lock on object 4933 (Table SCOTT.DEPT), but most of the time applications
update several tables and might not be very clear in the detail window what object has the lock.
To be more certain, the following queries show the info :

select sid,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
from v$session where sid =11;

SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---- ------------- -------------- --------------- -------------
11 4933 11 1022 13

select object_type,owner,object_name
from dba_objects
where object_id=4933;

OBJECT_TYPE OWNER OBJECT_NAME
------------------ ------- -----------
TABLE SCOTT DEPT

SELECT * FROM EMP WHERE rowid =
DBMS_ROWID.ROWID_CREATE(1, 4933, 11, 1022, 13);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------- ------ ----- --------- --------- -------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1430 10


The File#,block#,row# give us up to what row is currently waiting
but it does not tell us if it will be waiting for more after this
lock is cleared even with this same sql statement.

If it becomes imperative to know what rows are being locked by any
user see
Note:132629.1 How to know which row is locked by what user.
Note:74089.1 Find Session or User who is Locking Record


5. Killing the blocking session
===============================

Normally, you should use the KILL USER SESSION menu found in TopSessions.
You can also issue an ALTER SESSION KILL SESSION sid, serial#; in an sqlplus
session.

When on unix or vms,you can kill the unix/vms shadow process directly. Is is not recommended.
When killing the shadow process, please be careful of shared servers in a multi-threaded environment.
e.g.
kill -9 6246 (shadow process on unix)
stop/id=<SESS SPID> (PROC SPID=SESS SPID on vms running single task)

To find process information, one can execute this query:

column "ORACLE USER" format a11
column SERIAL# format 9999999
column "OS USER" format a8

select distinct substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER", p.spid "PROC SPID",
s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'
and a.sid in (select l.sid from v$lock l
where (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=l.id1 and
b.id2=l.id2 and b.request>0));

ORACLE USER PROCESS ID SESSION ID SERIAL# OS USER PROC SPID SESS SPID LOCK WAI
----------- ---------- ---------- -------- -------- ---------
SCOTT 17 11 3313 oracle8i 6247 6246 801113A4
SCOTT 20 19 3611 oracle8i 6258 6257


References:
===========

[NOTE:15476.1] Faq about detecting and resolving Locking Issues
[NOTE:132629.1] How to know which row is locked by what user.
[NOTE:74089.1] Find Session or User who is Locking Record