Re: Identifying a locked record

In article <C9wH0z.1C7_at_cbfsb.cb.att.com>, colten_at_cbnewsb.cb.att.com (marc.colten) writes:
|>
|> I'm trying to find a way to identify who is using a particular
|> record. Users find themselves locked out when another user
|> has the record up in a form (SQL*FORMS30). I have checked
|> the database adminstrator's book and tried to cross reference
|> the information in V$PROCESS, V$_LOCK, V$SESSION and others,
|> but nothing brings me closer to linking a user to the record
|> they are looking at.
|>
|> thanks in advance for any advice.
|>
|> marc colten

• Traduction of an article writen in the 'Bulletin Technique ORACLE FRANCE', number 8, summer 1992.

  ---

  How identify deadlock actors ?

  ---

  One of ORACLE DBA's actions is to resolve deadlocks. What's a deadlock ? The best thing to do, is to read your Oracle Database Administrator's Guide, chapter 12 Consistency and concurrency see Deadlock Detection (page 12-21 for Oracle version 6). When Oracle detect a deadlock (ORA-00060: deadlock detected while waiting for resource) it automaticly generates a trace file (generally in $ORACLE_HOME/rdbms/log/xxx.trc on UNIX). This trace file allow you to retrieve the actors of the deadlock. On viewing the head of the trace file, we can retrieve the user's process pid in cause. Example : DEADLOCK DETECTED Blocker: holds waits Waiter: holds waits TX-00020001-00000003 5 X 7 S TX-00010018-00000005 7 X 5 S Tue Jun 15 10:55:00 1993 ksedmp: internal or fatal error ORA-0060: deadlock detected while waiting for resource

The numbers under Blocker and Waiter are the transactions PID in cause. Here, process 5 and 7. Rest to determine :
- the exact identification of the users
- resources (tables or rows) in cause.
We must continue in the trace file on searching a char string '(process) pid=n', here n=(5,7). One (and only one) of the two searches find effectively :

	SO: 297436, type: 1, owner: 0, flag: INIT/-/-/0x00
	(process) pid=5 ospid=20608714 calls cur/top: 29d1d0/29d1d0, flag: -
	int error: 0, call error: 0, sess error 0
	(latch info) wait_event=0 last_status=2 bits=10
	holding 807c5da4 enqueues level=4 activity=0 busy
	-----------------------------------------

The OSPID (here 20608714) is the process number affected by the OS of the server to the transaction. According to the environment, it'd be a number (VAX/VMS or UNIX) or an explicit username.

If the deadlock is reproductible, or if we were in a SQL*DBA Monitor session at the moment of the incident, it's possible to retrieve Oracle identifiers of concerned transactions :

	xxxxxxxxx             ORACLE Process Monitor            date
	ORACLE    System System
	   PID       PID User Name Terminal Program
	------ --------- --------- -------- ---------------------------------------
             2   xxxxxxx xxxxxxx      xxxxx xxxxxxxxxx
             3   xxxxxxx xxxxxxx      xxxxx xxxxxxxxxx
             4   xxxxxxx xxxxxxx      xxxxx xxxxxxxxxx
             5   xxxxxxx xxxxxxx      xxxxx xxxxxxxxxx
             6  20608714 XXXXX        REMOTE $1$DUA0:[ORACLE.RDBMSPROD.][SQLPLUS]SQLPLUS_F
	...

Nota: it'd be necessary to increment of one the PID in the trace file. Here, PID 5 become 6 and correspond to a SQL*PLUS transaction. We retrieve too the OSPID 20608714.

Always in the trace file, on continuing the search on a string '(session)', we obtain the name of the user, example here is SCOTT :

	(session) num: 1, trans: 375ad0, flag: USR/-
        audit sess/ent: 122/0, oct: 6, prv: 5, user: 3/SCOTT

On continuing the search on a string '(enqueue)' RW (RW = Row Wait), we find :

      	SO: 29fe70, type: 4, owner: 2aded0, flag: INIT/-/-/0x00
	(enqueue) RW-0200000D-0000000A lv: 00 01 00 18 .............
	req: NLCK, process: 297436, serial: 3
	----------------------------------------

Numbers behind (enqueue) are hexadecimal ones :

_ the two first chars of the first number are the file ID
_ the six chars after are the block ID
_ the second number is the internal row number in this block.

Repartition 2 & 6 is Oracle block size dependant. For a 4K size (MVS and VM), this repartition is 3 & 5. So we have :
_ file ID = 2 (nn)
_ block ID = '000D' = 13 decimal (nnn)

Finally we've to use the following SQL statement with DBA priv.

	select * from sys.dba_extents
	where nnn between block_id and (block_id + blocks)
	and file_id = nn;

------->

	select * from sys.dba_extents
	where 13 between block_id and (block_id + blocks)
	and file_id = 2;
	

==============================================================================
NOEL Yves                                        |  Phone : (33)  20.43.42.70
Oracle DBA                                       |  Fax   : (33)  20.43.66.25
C.I.T.I.                                         |  Email : noel_at_citilille.fr
Universite des Sciences & Technologies de Lille  |
59655 Villeneuve d'Ascq Cedex - FRANCE           |
==============================================================================
----             I use ORACLE v6.0.36 on DEC RISC ULTRIX v4.3             ----
==============================================================================