Oracle Alert log報錯資訊：

Thread 1 cannot allocate new log, sequence 7356
Checkpoint not complete
Current log# 2 seq# 7355 mem# 0: /data/flash_recovery_area/WIND/onlinelog/o1_mf_2_3lqjqcb2_.log
Current log# 2 seq# 7355 mem# 1: /u01/product/oradata/WIND/redo02.log
Thread 1 advanced to log sequence 7356
Current log# 3 seq# 7356 mem# 0: /data/flash_recovery_area/WIND/onlinelog/o1_mf_3_3lqjqcn6_.log
Current log# 3 seq# 7356 mem# 1: /u01/product/oradata/WIND/redo03.log
Wed Jun 3 11:01:08 2009
ORA-00060: Deadlock detected. More info in file /u01/product/admin/wind/udump/wind_ora_18786.trc.
Wed Jun 3 11:01:09 2009
ORA-00060: Deadlock detected. More info in file /u01/product/admin/wind/udump/wind_ora_18780.trc.
Wed Jun 3 11:01:21 2009
Thread 1 advanced to log sequence 7357
Current log# 1 seq# 7357 mem# 0: /data/flash_recovery_area/WIND/onlinelog/o1_mf_1_3lqjqbz1_.log
Current log# 1 seq# 7357 mem# 1: /u01/product/oradata/WIND/redo01.log
Wed Jun 3 11:01:26 2009
ORA-00060: Deadlock detected. More info in file /u01/product/admin/wind/udump/wind_ora_18784.trc.
Wed Jun 3 11:01:41 2009
Thread 1 advanced to log sequence 7358
Current log# 2 seq# 7358 mem# 0: /data/flash_recovery_area/WIND/onlinelog/o1_mf_2_3lqjqcb2_.log
Current log# 2 seq# 7358 mem# 1: /u01/product/oradata/WIND/redo02.log
Wed Jun 3 11:02:08 2009
Thread 1 cannot allocate new log, sequence 7359
Checkpoint not complete

Oracle Trace 檔案資訊：

/u01/product/admin/wind/udump/wind_ora_18772.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/product/oracle
System name:    Linux
Node name:      PDM8_NewDB
Release:        2.6.9-42.ELsmp
Version:        #1 SMP Wed Jul 12 23:32:02 EDT 2006
Machine:        x86_64
Instance name: wind
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 18772, image:

*** 2009-06-03 10:59:05.019
*** SERVICE NAME:(SYS$USERS) 2009-06-03 10:59:04.921
*** SESSION ID:(1069.36453) 2009-06-03 10:59:04.921
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
INSERT INTO ControlBranch(classnameA2A2,updateCountA2,adHocStringIdentifier,
hasArchiveInfoarchiveInfo,classnamekeyC5,idA3C5,branchPointUfid,classnamekeyB5,
idA3B5,classnamekeyD5,idA3D5,oneOffVersionId,classnamekeyA5,idA3A5,sessionOwner,
markForDeleteA2,versionId,viewId,wipState,updateStampA2,createStampA2,modifyStampA2,idA2A2)
VALUES ('wt.vc.ControlBranch',1,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21)

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name          process session holds waits process session holds waits
TX-00020006-0000ee35        34    1069     X             36    1066           S
TX-0009004f-0000f851        36    1066     X             34    1069           S
session 1069: DID 0001-0022-0001F25E    session 1066: DID 0001-0024-00000014
session 1066: DID 0001-0024-00000014    session 1069: DID 0001-0022-0001F25E
Rows waited on:
Session 1066: obj - rowid = 00017071 - AAAXBxABTAAACpzAAA
(dictionary objn - 94321, file - 83, block - 10867, slot - 0)
Session 1069: obj - rowid = 00016FCF - AAAW/PABcAAABIDAAA
(dictionary objn - 94159, file - 92, block - 4611, slot - 0)
Information on the OTHER waiting sessions:
Session 1066:
pid=36 serial=1171 audsid=2795304 user: 40/PDM8
O/S info: user: , term: , ospid: 1234, machine: pdm8ap1
            program:
Current SQL Statement:

INSERT INTO ControlBranch(classnameA2A2,updateCountA2,adHocStringIdentifier,hasArchiveInfoarchiveInfo,classnamekeyC5,idA3C5,branchPointUfid,classnamekeyB5,idA3B5,classnamekeyD5,idA3D5,oneOffVersionId,classnamekeyA5,idA3A5,sessionOwner,markForDeleteA2,versionId,viewId,wipState,updateStampA2,createStampA2,modifyStampA2,idA2A2) VALUES ('wt.vc.ControlBranch',1,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21)
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
Process global information:
     process: 0x1fe638ea0, call: 0x1f5de63e8, xact: 0x1f8d6ba30, curses: 0x1fe9d2c08, usrses: 0x1fe9d2c08
----------------------------------------
SO: 0x1fe638ea0, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=34, calls cur/top: 0x1f5de63e8/0x1f5de63e8, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 112
              last post received-location: kcbzww
              last process to post me: 1fe63cde0 141 0
              last post sent: 0 0 112
              last post sent-location: kcbzww
              last process posted by me: 1fe63cde0 141 0
    (latch info) wait_event=0 bits=10
      holding    (efd=13) 600096b0 Parent+children enqueue hash chains level=4
        Location from where latch is held: ksqcmi: kslgpl:
        Context saved from call: 0
        state=busy, wlstate=free
          recovery area:
Dump of memory from 0x00000001FE5EDC10 to 0x00000001FE5EDC30
1FE5EDC10 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
    Process Group: DEFAULT, pseudo proc: 0x1fe727b30
    O/S info: user: oracle, term: UNKNOWN, ospid: 18772
    OSD pid info: Unix process pid: 18772, image:
Dump of memory from 0x00000001FE5F3320 to 0x00000001FE5F3528
1FE5F3320 00000016 00000000 F89568D8 00000001 [.........h......]
1FE5F3330 00000007 0003139D F89569B8 00000001 [.........i......]
1FE5F3340 00000007 0003139D F8956A98 00000001 [.........j......]
1FE5F3350 00000007 0003139D F8956B78 00000001 [........xk......]
1FE5F3360 00000007 0003139D F8956C58 00000001 [........Xl......]
1FE5F3370 00000007 0003139D F8956D38 00000001 [........8m......]
1FE5F3380 00000007 0003139D F8956E18 00000001 [.........n......]
1FE5F3390 00000007 0003139D F8956EF8 00000001 [.........n......]
1FE5F33A0 00000007 0003139D F8956FD8 00000001 [.........o......]
1FE5F33B0 00000007 0003139D F89570D0 00000001 [.........p......]
1FE5F33C0 00000007 0003139D F89571B0 00000001 [.........q......]
1FE5F33D0 00000007 0003139D F8957290 00000001 [.........r......]
1FE5F33E0 00000007 0003139D F8957370 00000001 [........ps......]
1FE5F33F0 00000007 0003139D F8957450 00000001 [........Pt......]
1FE5F3400 00000007 0003139D F8957610 00000001 [.........v......]
1FE5F3410 00000007 0003139D F89577D0 00000001 [.........w......]
1FE5F3420 00000007 0003139D F895A930 00000001 [........0.......]
1FE5F3430 00000007 0003139D F8960F58 00000001 [........X.......]
1FE5F3440 00000007 0003139D F8961818 00000001 [................]
1FE5F3450 00000007 0003139D F89618F8 00000001 [................]
1FE5F3460 00000007 0003139D F8961E50 00000001 [........P.......]
1FE5F3470 00000007 0003139D F8961F30 00000001 [........0.......]
1FE5F3480 00000007 0003139D F894E048 00000001 [........H.......]
1FE5F3490 00000007 0003139D F894E128 00000001 [........(.......]
1FE5F34A0 00000007 0003139D F894E680 00000001 [................]
1FE5F34B0 00000007 0003139D F894F9D8 00000001 [................]

Oracle Metalink 資訊：

---------------------------------------------------------------------------

Error: ORA 60
Text:   deadlock detected while waiting for resource
-------------------------------------------------------------------------------
Cause: Transactions deadlock one another waiting for resources
Action: Look at the trace file to see the transactions and resources
        involved. Retry if necessary.

*** Important: The notes below are for experienced users - See

** For CUSTOMER issues with ORA-60 send them:
   This details the most common ORA-60 scenarios.

Explanation:
        A deadlock has been detected so your statement has been rolled
        back to allow the other party to continue.

Diagnosis:
        Note the lock TYPES and MODES in the DEADLOCK graph.
   The most common deadlocks involve one of the following:
Lock type Mode Problem
   TX      X   Application row lock deadlock.See below.
   TX        S   Many reasons - see
   TM     SSX Usually unindex foreign key constraints.

TX requested X mode:
~~~~~~~~~~~~~~~~~~~~~
        This is usually an application design issue in that transactions
        are modifying tables A and B in opposite orders.
        Eg: 1 updates A
            2 updates B
            1 tries to update B but blocks
            2 tries to update A but blocks
            ** Deadlock as neither 1 nor 2 will ever complete.

        a) Has ANYTHING in the schema been changed (Eg: A foreign key INDEX
           may have been dropped) as this could affect locking of tables.

        b) There should be a user trace with the deadlock TX information
           in USER_DUMP_DEST. This will indicate who was involved in the
           deadlock

        c) Is the insert into a clustered table ? If so see

        d) What are MAXTRANS / INITRANS for the tables in the deadlock ?

Articles / Known Issues:
       ORA-60 against UET$ (Fixed 7.2)
       ORA-60 from ANALYZE ... VALIDATE ... command:
NOTE: It is possible to get this if the dictionary has
been analyzed and has statistics present. Delete
the statistics.

V6: If this occurs on startup it is likely that a file may be missing.
        There should be a trace file produced and an entry in the alert log
        which will tell you the location of the problem file. Either:
a) restore access to the datafiles
OR b) offline the datafiles and drop the tablespace when it
   opens to clean up the data dictionary.
   (obviously you have lost all the data in this case)

------------------------------------------------------------

Subject:	TX Transaction locks - Example wait scenarios
	:	62354.1	Type:	TROUBLESHOOTING
	Modified Date :	04-AUG-2008	Status:	PUBLISHED


Introduction
~~~~~~~~~~~~
  This short article gives examples of TX locks and the waits which can 
  occur in various circumstances. Often such waits will go unnoticed unless
  they are of a long duration or when they trigger a deadlock scenario (which
  raises an ORA-60 error).

  The examples here demonstrate fundamental locking scenarios which should
  be understood by application developers and DBA's alike. 
  The examples require select privilege on the V$ views.


Useful SQL statements 
~~~~~~~~~~~~~~~~~~~~~
  If you encounter a lock related hang scenario the following SQL statements
  can be used to help isolate the waiters and blockers:

    Show all sessions waiting for any lock:

	select event,p1,p2,p3 from v$session_wait 
	 where wait_time=0 and event='enqueue';

    Show sessions waiting for a TX lock:

	select * from v$lock where type='TX' and request>0;

    Show sessions holding a TX lock:

	select * from v$lock where type='TX' and lmode>0;


What is a TX lock ?
~~~~~~~~~~~~~~~~~~~
  A TX lock is acquired when a transaction initiates its first change and is 
  held until the transaction does a COMMIT or ROLLBACK. It is used mainly as
  a queuing mechanism so that other sessions can wait for the transaction to
  complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction
  ID of the active transaction.


Example Tables
~~~~~~~~~~~~~~
  The lock waits which can occur are demonstrated using the following
  tables. Connect as SCOTT/TIGER or some dummy user to set up the test
  environment using the following SQL:

    DROP TABLE tx_eg;
    CREATE TABLE tx_eg ( num number, txt varchar2(10), sex varchar2(10) )
      INITRANS 1 MAXTRANS 1;
    INSERT into tx_eg VALUES ( 1, 'First','FEMALE' );
    INSERT into tx_eg VALUES ( 2, 'Second','MALE' );
    INSERT into tx_eg VALUES ( 3, 'Third','MALE' );
    INSERT into tx_eg VALUES ( 4, 'Fourth','MALE' );
    INSERT into tx_eg VALUES ( 5, 'Fifth','MALE' );
    COMMIT;

  In the examples below three sessions are required: 

	Ses#1 	indicates the TX_EG table owners first session
	Ses#2 	indicates the TX_EG table owners second session
	DBA 	indicates a SYSDBA user with access to 


  The examples covered below include:

	Waits due to Row being locked by an active Transaction
	Waits due to Unique or Primary Key Constraint enforcement
	Waits due to Insufficient 'ITL' slots in the Block
	Waits due to rows being covered by the same BITMAP index fragment


Waits due to Row being locked by an active Transaction
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  When a session updates a row in a table the row is locked by the sessions
  transaction. Other users may SELECT that row and will see row as it was
  BEFORE the UPDATE occurred. If another session wishes to UPDATE the same
  row it has to wait for the first session to commit or rollback. The 
  second session waits for the first sessions TX lock in EXCLUSIVE mode.

  Eg:
	Ses#1:	update tx_eg set txt='Garbage' where num=1;
	Ses#2:	update tx_eg set txt='Garbage' where num=1;
	DBA:	select SID,TYPE,ID1,ID2,LMODE,REQUEST 
		 from v$lock where type='TX';

	SID        TY ID1        ID2        LMODE      REQUEST
	---------- -- ---------- ---------- ---------- ----------
	         8 TX     131075        597          6          0
	        10 TX     131075        597          0          6

	> This shows SID 10 is waiting for the TX lock held by SID 8 and it
	> wants the lock in exclusive mode (as REQUEST=6).

	The select below is included to demonstrate that a session waiting
	on a lock will show as waiting on an 'enqueue' in V$SESSION_WAIT
	and that the values of P1RAW, P2 and P3 indicate the actual lock
	being waited for. When using Parallel Server the EVENT will be
	'DFS enqueue lock acquisition' rather than 'enqueue'.
	This select will be omitted from the following examples.

	DBA:	select sid,p1raw, p2, p3
		  from v$session_wait 
		 where wait_time=0 and event='enqueue';

	SID        P1RAW    P2         P3
	---------- -------- ---------- ----------
	        10 54580006     131075        597
	>	   ~~~~  ~~	~~~~~~	      ~~~
	>	   type|mode       id1	      id2
	>	    T X	  6	131075	      597


	The next select shows the object_id and the exact row that the
	session is waiting for. This information is only valid in V$SESSION
	when a session is waiting due to a row level lock. The statement
	is only valid in Oracle 7.3 onwards. As SID 10 is the waiter above
  	then this is the session to look at in V$SESSION:

	DBA:	select ROW_WAIT_OBJ#,
		       ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
     		 from v$session
     		where sid=10;

	ROW_WAIT_O ROW_WAIT_F ROW_WAIT_B ROW_WAIT_R
	---------- ---------- ---------- ----------
	      3058          4       2683          0

	> The waiter is waiting for the TX lock in order to lock row 0
	> in file 4, block 2683 of object 3058.

	Ses#1:	rollback;
	Ses#2:	rollback;


Waits due to Unique or Primary Key Constraint enforcement
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  If a table has a primary key constraint, a unique constraint
  or a unique index then the uniqueness of the column/s referenced by
  the constraint is enforced by a unique index. If two sessions try to 
  insert the same key value the second session has to wait to see if an
  ORA-0001 should be raised or not.

  Eg: 
	Ses#1:  ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );
	Ses#1:	insert into tx_eg values (10,'New','MALE');
	Ses#2:	insert into tx_eg values (10,'OtherNew',null);
        DBA:    select SID,TYPE,ID1,ID2,LMODE,REQUEST
                 from v$lock where type='TX';

	SID        TY ID1        ID2        LMODE      REQUEST
	---------- -- ---------- ---------- ---------- ----------
	         8 TX     196625         39          6          0
	        10 TX     262155         65          6          0
	        10 TX     196625         39          0          4

	This shows SID 10 is waiting for the TX lock held by SID 8 and it
	wants the lock in share mode (as REQUEST=4). SID 10 holds a TX lock 
	for its own transaction.

	Ses#1:	commit;
	Ses#2:  ORA-00001: unique constraint (SCOTT.TX_EG_PK) violated
	Ses#2:	rollback;


Waits due to Insufficient 'ITL' slots in a Block
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Oracle keeps note of which rows are locked by which transaction in an area
  at the top of each data block known as the 'interested transaction list'.
  The number of ITL slots in any block in an object is controlled by
  the INITRANS and MAXTRANS attributes. INITRANS is the number of slots
  initially created in a block when it is first used, while MAXTRANS places
  an upper bound on the number of entries allowed. Each transaction which
  wants to modify a block requires a slot in this 'ITL' list in the block.

  MAXTRANS places an upper bound on the number of concurrent transactions
  which can be active at any single point in time within a block.

  INITRANS provides a minimum guaranteed 'per-block' concurrency.

  If more than INITRANS but less than MAXTRANS transactions want to be 
  active concurrently within the same block then the ITL list will be extended
  BUT ONLY IF THERE IS SPACE AVAILABLE TO DO SO WITHIN THE BLOCK.

  If there is no free 'ITL' then the requesting session will wait on one
  of the active transaction locks in mode 4.

  Eg:   Ses#1:  update tx_eg set txt='Garbage' where num=1;
        Ses#2:  update tx_eg set txt='Different' where num=2;
        DBA:    select SID,TYPE,ID1,ID2,LMODE,REQUEST
                 from v$lock where type='TX';

	SID        TY ID1        ID2        LMODE      REQUEST
	---------- -- ---------- ---------- ---------- ----------
	         8 TX     327688         48          6          0
	        10 TX     327688         48          0          4

	This shows SID 10 is waiting for the TX lock held by SID 8 and it
	wants the lock in share mode (as REQUEST=4). 

	Ses#1:	commit;
	Ses#2:	commit;
	Ses#1:	ALTER TABLE tx_eg MAXTRANS 2;
        Ses#1:  update tx_eg set txt='First' where num=1;
        Ses#2:  update tx_eg set txt='Second' where num=2;
	
	Both rows update as there is space to grow the ITL list to 
	accommodate both transactions.

	Ses#1:	commit;
	Ses#2:	commit;

Also from 9.2 you can check the ITL Waits in v$segment_statistics 
with a query like :
     SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
     FROM v$segment_statistics t
     WHERE t.STATISTIC_NAME = 'ITL waits' 
     AND t.VALUE > 0;

If need be, increase INITTRANS and MAXTRANS. 

Waits due to rows being covered by the same BITMAP index fragment
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  Bitmap indexes index key values and a range of ROWIDs. Each 'entry' 
  in a bitmap index can cover many rows in the actual table.
  If 2 sessions wish to update rows covered by the same bitmap index
  fragment then the second session waits for the first transaction to
  either COMMIT or ROLLBACK by waiting for the TX lock in mode 4.

  Eg: 	Ses#1:  CREATE Bitmap Index tx_eg_bitmap on tx_eg ( sex );
        Ses#1:  update tx_eg set sex='FEMALE' where num=3;
        Ses#2:  update tx_eg set sex='FEMALE' where num=4;
        DBA:    select SID,TYPE,ID1,ID2,LMODE,REQUEST
                 from v$lock where type='TX';

	SID        TY ID1        ID2        LMODE      REQUEST
	---------- -- ---------- ---------- ---------- ----------
	         8 TX     262151         62          6          0
	        10 TX     327680         60          6          0
	        10 TX     262151         62          0          4

	This shows SID 10 is waiting for the TX lock held by SID 8 and it
	wants the lock in share mode (as REQUEST=4). 

	Ses#1:	commit;
	Ses#2:	commit;


Other Scenarios
~~~~~~~~~~~~~~~
  There are other wait scenarios which can result in a SHARE mode wait for a TX
  lock but these are rare compared to the examples given above. 
  Eg: If a session wants to read a row locked by a transaction in a PREPARED
      state then it will wait on the relevant TX lock in SHARE mode (REQUEST=4).
      As a PREPARED transaction should COMMIT , ROLLBACK or go to an in-doubt
      state very soon after the prepare this is not generally noticeable..

ORA-00600 "deadlock detected while waiting for resource"

相關文章