Can someone explain this, pl?

[gsprince]

I have the following situation. I have two session 10, 40 having the same OS process ID (24905). Session 40 is holding lock on a resource and other sessions are waiting for it.

On tracing the session 40 (spid= 24905), I get the following lines.

WAIT #1: nam='enqueue' ela= 16 p1=1415053316 p2=196628 p3=1397
WAIT #1: nam='enqueue' ela= 307 p1=1415053316 p2=196628 p3=1397


select s.sid sid, p.spid from v$session s, v$process p where s.sid in (10,40) and s.paddr = p.addr ;

SID SPID
---- ------
10 24905
40 24905

select * from v$session ;

SADDR SID SERIAL# AUDSID PADDR COMMAND OWNERID TADDR LOCKWAIT STATUS SERVER PROCESS SQL_ADDR
-------- --- ------- ------ -------- ------- ---------- -------- -------- -------- --------- ------- --------
580A7DBC 10 13499 329589 5808C554 2 2147483644 586B9554 5812C3EC ACTIVE DEDICATED 8132 589186E8

580B753C 40 56156 329590 5808C554 0 2147483644 586BB82C INACTIVE DEDICATED 8132 58979DA8


On tracing the parent process (Apache, it seems it is waiting on Oracle). But Quesry on Oracle (V$session_wait) shows its waiting for the response from the client.

Now my main question is,

Can anyone tell me, when does more than one session (SIDs) have the same OS process id?

How to find who is actually waiting for who? (from the trace Oradebug and os
process trace it looks to me Oracle is waiting for the Apache/Client process
and Apache is waiting for the Oracle Process)

Any suggestion of you will be greatly appreciated.

Thanks,

[sambavan]

It is possible in the MTS setting.

Sam

[marist89]

I don't think it is related to MTS, as the v$session.server was DEDICATED.

What process was running at PID 24905?

To help you diagnose your enqueue problem, look at [url]http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=102925.1[/url]

[gsprince]

The process for 24905 is an "oracle" process.
(from ps -ef: oracle 24905 1 0 Feb28 ? 00:00:01 oracleORCL oracleORCL (DESCRIPTION=(LOCAL=n

Can someone tell me whether the following could be a cause and could it be possible? (Just my theory).

There may be a client process, which has two connection open at the same time.
The first connection holds an exclusive row lock (from v$sqlarea, its an insert stmt) and the other connection is holding exclusive lock on another table and wants the lock on the former's table.
Since its within the same process, oracle may not be able to detect it as a time out/dead lock. (These processes are waiting for more than a day)

Thank you for any of your feedback.