Oracle-L: RE: Deadlock Handling

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Mon, 11 Mar 2002 11:07:00 -0800
Message-ID: <F001.00424C4D.20020311110700@fatcity.com>

Ganesh,

Your comment is bit inaccurate. THis is because of the ITL Entry shortage
(no-row) and bumping PCTFREE for the respective tables will solve the
problem.

Best Regards,
K Gopalakrishnan
Bangalore, INDIA

-----Original Message-----
Sent: Monday, March 11, 2002 5:38 AM
To: LazyDBA.com Discussion

Comments Embedded...

Best Regards,
Ganesh R
Tel : +971 (4) 397 3337 Ext 420
Fax : +971 (4) 397 6262
HP : +971 (50) 7456019

-----Original Message-----
Sent: Monday, March 11, 2002 5:08 PM
To: LazyDBA.com Discussion

Transaction 1                 Transaction 2 (Runs CONCURRENTLY)
Statement 1 on Row A          Statement 3 on Row B

Statement 2 on Row B          Statement 4 on Row A

On Occurence of a Deadlock Error ORA-60 Which of the Statements 1,2,3,4 Will Fail ?

The Session that Dedected the deadlock will be Killed it will left to Oracle.

Qs Will the Contentious SQL Queries Still be Present in v$session , v$sqlarea , v$process
even after the Occurence of Deadlock ?

V$session - Contains No Query Information.
v$SQLAREA - May Have the Query.
v$Process - Contains No Query info.

Qs Can Insufficient Values of PCTFREE for respective Tables Cause a Deadlock ?

AFAIK... No

Qs Any Other Advice on How to approach Deadlocks Handling ?

Do Proper Locking inside u'r Application.

Following part of Trace File Coming with a Deadlock

Dump file /oracle/OraHome1/rdbms/log/abnprod_ora_10495.trc Unix process pid: 10495, image: oracle_at_fin-db-1 (TNS V1-V3) Deadlock graph:

                       ---------Blocker(s)--------
---------Waiter(s)---------
Resource Name          process session holds waits  process session holds
waits
TX-000a0052-0000bbac        16      38     X             51     625
S
TX-0013001f-0000b1f9        51     625     X             16      38
X
session 38: DID 0001-0010-00000002	session 625: DID 0001-0033-00000002
session 625: DID 0001-0033-00000002	session 38: DID 0001-0010-00000002

Rows waited on:
Session 625: no row
Session 38: obj - rowid = 00001044 - AAAEfaAALAAAD89AAD

SQL> select acid ,sol_id from gam
2 where rowid='AAAEfaAALAAAD89AAD';

ACID SOL_ID
----------- --------
AB640163 282

THANKS

Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to oracledba-unsubscribe_at_LAZYDBA.com To subscribe: send a blank email to oracledba-subscribe_at_LAZYDBA.com Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these
terms:http://www.lazydba.com/legal.html

Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: K Gopalakrishnan
  INET: kaygopal_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may


also send the HELP command for other information (like subscribing).

Received on Mon Mar 11 2002 - 13:07:00 CST