Dead Lock while database check ora:00060 oracle 10G ECC6

[Former Member]

Good Day;

I am getting oracle dead lock while doing database check . I went through the trace files /oracle/<sid>/saptrace/....and did the statistics for the Table which required( cause dead lock). after doing statictics i did a database check again and there was no deadlock ..everything is fine.

I have scheduled background job daily morning for Database check. but everyday am getting the same error

Please advice to do the needful to avoid the deadlocks.

Thanks in advance

Seethy

[rsil_team]

Hi

whenvr u r facing problem go to Tr DB01and see the lock waits and deadlock occured.Identify process which is causing deadlock & analyze it.

Note 901313 - ORA-60

Surendra

[Former Member]

Hi ;

Thanks for reply . Am still getting error ,, No locks on DB01.

The Trace file is as follows ..

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-00340015-0003ad4a 235 1949 X 92 2127 X

TX-005c0026-00016126 92 2127 X 235 1949 X

session 1949: DID 0001-00EB-0000000C session 2127: DID 0001-005C-00000004

session 2127: DID 0001-005C-00000004 session 1949: DID 0001-00EB-0000000C

Rows waited on:

Session 2127: obj - rowid = 00013175 - AAATF1AEMAACq9iAAo

(dictionary objn - 78197, file - 268, block - 700258, slot - 40)

Session 1949: obj - rowid = 0001320D - AAATINAE5AAD4AwAAw

(dictionary objn - 78349, file - 313, block - 1015856, slot - 48)

Information on the OTHER waiting sessions:

Session 2127:

pid=92 serial=8 audsid=3177858 user: 27/SAPSR3

O/S info: user: erpadm, term: , ospid: 2314408, machine: eccprd01

program: dw.sapERP_DVEBMGS00@eccprd01 (TNS V1-V3)

client info: 0

application name: RMCSS009 , hash value=3992826687

action name: 1088, hash value=1907004743

Thanks & Regards

seethy

[anindya_bose]

From my previous post could you now configure that it is NOT an ORACLE deadlock.

>TX-00340015-0003ad4a 235 1949 X 92 2127 X

had it been an oracle error then you would have "S" instead of "X" as the last entry in the above line.

Did you go thorough OSS 84348 and check INITRANS and MAXTRANS values ?

Most probable reason of this Deadlock is wrong INITRANS , MAXTRANS on some tables.

Did you find the information from the log or dump on which table this dead lock is occuring? Check the INITRANS and MAXTRANS and change it according to OSS note given above..

[Former Member]

Good Day;

Thanks for the Reply. Will do and update.

Regards

seethy

[Former Member]

Hi Anindhya;

Presently am unable to access the SAP NOTE "The requested SAP Note is either in reworking or is released internally only "

There is no dump or log which indicates the table locking. I can find only tables starting with "S" (s001,s003,s014,etc)shows deadlock( from trace file).

we are using Oracle 10G and its locally managed tablespaces with automatic segment space management , so is it necessary to increase the parameters?

If you have the sapnote copy please mention it .

Thanks for your help Much appreciated.

regards

seethy

[anindya_bose]

Hmm..Seethy

I know that NOTE is not accesible now. I mentioned that in my first post...

No worries, I have a copy with me.. Here it is..

01.12.2008 Page 1 of 6

Note 84348 - Oracle deadlocks, ORA-00060

Note Language: English Version: 18 Validity: Valid from 20.07.2004

Summary

Symptom

ORA-00060: deadlock detected while waiting for resource

More Terms

ora-60 ora-000060

Cause and Prerequisites

In most cases, the deadlock is caused by a problem in the application. It

can be traced back to a programming error.

Under certain conditions, a deadlock may also be triggered by Oracle.

This deadlock has the following cause:

If a data record is locked in the database, this lock information is

written to the block. The space provided in the block header is used for

this. The space is defined when the table is created with the parameters

INITRANS and maxtrans. If a block becomes too full, the database cannot

allocate any more space to store this block and must wait for memory space

in the corresponding block. As a result, row level locking can become block

level locking.

If some parallel scripts now lock a large number of data records that are

in the same block, two or more of the scripts may sometimes cause a

deadlock, not because they lock the same data record, but because no

additional memory space can be allocated for the lock.

To find out whether this is a deadlock in Oracle, you need to examine the

trace file written by Oracle in a lot more detail. The file is usually

stored in the /oracle/<SID>/saptrace/usertrace directory. In addition, the

exact file name/directory can usually be determined from the ORA-00060

error message in /oracle/<SID>/saptace/background/alert<SID>.log.

Open the file - the 'deadlock graph' appears on the first two pages.

The deadlock graph is as follows:

-

---

Blocker(s)--

---

-

---

Waiter(s)---

Resource Name process sess. holds waits process sess. hold waits

TX-00090004-00011000 43 39 X 35 46 S

TX-0006001a-0001397d 35 46 X 43 39 S

Here, the last column that specifies the Waits as type 'S' is important.

If an 'X' is displayed instead in the graph, it is NOT an Oracle deadlock.

Which object is it?

After the deadlock graph, the system immediately displays further

information on the object for which the deadlock was generated:

Rows waited on:

Session 39: obj - rowid = 000016F2 - 0003BC42.0000.0093

Session 46: obj - rowid = 000018C2 - 0001012D.004B.0016

Although the select statement of the session terminated by the deadlock is

usually also displayed, you can also refer to these two lines to see which

table it is:

01.12.2008 Page 2 of 6

Note 84348 - Oracle deadlocks, ORA-00060

000016F2 [hex value] corresponds to 5874 [decimal]

000018C2 [hex value] corresponds to 6338 [decimal]

sqlplus "sapr3/<passwd>"

SQL> SELECT owner, object_name, object_id FROM DBA_OBJECTS

WHERE object_id=5874;

SQL> SELECT owner, object_name, object_id FROM DBA_OBJECTS

WHERE object_id=6338;

Is it an index?

If no rows are displayed here, for example,

Rows waited on:

Session 39: no row

Session 46: no row

the deadlock most probably occurred while index blocks were being locked.

Solution

Measures to eliminate/minimize the problem:

The problem, in most cases, is that the system tries to dynamically extend

the block space, but the block is too full. INITRANS defines the static

value reserved for each block for transaction information. However, this

value can increase to MAXTRANS if several transactions simulaneously try to

perform a lock. To prevent this error from occurring, you have to extend

the INITRANS value as much as possible so that a dynamic extension is not

required. The default value for INITRANS is 1. This is usually sufficient

for 'standard' tables/indexes. However, it is necessary to adjust this

value for special tables/indexes, for example, spool tables (TST01, TST03)

and BW tables/indexes into which data is loaded simultaneously.

You can use the following statement to determine the current value of

INITRANS and Maxtrans:

sqlplus "sapr3/<passwd>"

SQL> SELECT table_name, owner, ini_trans, max_trans FROM dba_tables

WHERE table_name = '<TABLE NAME>';

SQL> SELECT index_name, owner, ini_trans, max_trans

FROM dba_indexes WHERE table_name = '<TABLE NAME>';

If the error occurs when jobs are being executed in parallel (usual data

loads), the INITRANS value should be set to the maximum number of parallel

running jobs. Otherwise, 20 is an appropriate value.

1. The default value for max_trans is usually 255 for both the table and

indexes. If a value other than 255 (0, for example) is returned for

max_trans, increase this value to at least the adjusted INITRANS

value. We recommend that you set max_trans to 255.

While the value could be extended using the commands

sqlplus "sapr3/<passwd>"

SQL> ALTER TABLE <TABLE NAME> INITRANS 20;

SQL> ALTER INDEX "<INDEX NAME>" INITRANS 20;

This would only apply to newly created blocks.

01.12.2008 Page 3 of 6

Note 84348 - Oracle deadlocks, ORA-00060

If you have to adjust maxtrans:

sqlplus "sapr3/<passwd>"

SQL> ALTER TABLE <TABLE NAME> maxtrans 255;

SQL> ALTER TABLE "<INDEX NAME>" maxtrans 255;

2. Check in accordance with the list below whether this is a 'Special

database object'. If this is the case, you may also have to perform

these actions for additional objects.

3. To extend the value for 'old' blocks as well, follow the steps below:

4. Extend the INITRANS/MAXTRANS values for ALL necessary objects. (These

include tables, indexes, in the case of 'special objects' and also for

these additional objects.)

5. Export the affected table (sapdba).

6. Import the table.

Object change by SAP

There are situations in which an object must be newly created or changed

from SAP. Possible changes include:

- Deletion and recreation of indexes in BW with data loads

- Transporting a 'new' object into another system in your

landscape

- Creating a missing object from transaction DB02

- Converting an object due to the deletion of fields (transaction

ICNV)

Unfortunately, the information on the previous INTRANS/MAXTRANS

value is NOT saved in the SAP ABAP Dictionary. At present, this

type of new object creation currently causes the INITRANS value to

be reset to default.

Special database objects

o Is it a partitioned table?

Change to the INITRANS value only for reallocated

blocks:

In this case, if these are partitioned tables and indexes, you must

query other DBA views to receive the values for the individual

partitions. Then select as follows:

sqlplus "sapr3/<passwd>"

SQL> SELECT partition_name, ini_trans, max_trans FROM

dba_tab_partitions

WHERE table_name = '<TABLE NAME>'

AND table_owner = '<OWNER>';

01.12.2008 Page 4 of 6

Note 84348 - Oracle deadlocks, ORA-00060

SQL> SELECT partition_name, ini_trans, max_trans FROM

dba_ind_partitions

WHERE index_name = '<INDEX NAME>'

AND index_owner = '<OWNER>';

For new table partitions, you must change the default INITRANS

value:

sqlplus "sapr3/<passwd>"

SQL> ALTER TABLE <TABLE NAME> INITRANS 20;

Now use the SQL statement above with regard to the

dba_tab_partitions view to check the value of the INI_TRANS column

again.

If you have not also adjusted this value after you adjust the

default INITRANS value at table level, then you must change the

INITRANS value for each existing table partition.

For the indexes, the value must only be changed for the index

itself; the values for the index partitions (and the new index

partitions that have to be created) are automatically adjusted.

sqlplus "sapr3/<passwd>"

SQL> ALTER table <TABLE NAME> modify partition <PARTITION NAME>

INITRANS 20;

SQL> ALTER INDEX "<INDEX NAME>" INITRANS 20;

Change to the INITRANS values for old blocks and blocks

that are to be allocated:

This is only possible with reorganization of the affected table.

Create the reorganization scripts using sapdba and adjust every

occurrence of INITRANS in the tables and index SQL script.

Then run the scripts.

Background information

You can assign the INITRANS value when an object is created. If it

is not assigned at that stage, a hardcoded value of 1 for tables

and 2 for indexes is generated. Note that INITRANS defaults cannot

be defined for the tablespace, that is, there is no INITRANS column

in the dba_tablespaces.

During the creation of partitioned objects, this value is saved as

default to dba_part_indexes or in dba_part_tables.

When object partitions are created, an INITRANS that applies only

to the partition can also be assigned explicitly. If it is not

assigned, the default value is generated from dba_part_tables or

dba_part_indexes. During the initial creation of a partitioned

object with an initial partition, two INITRANS values can

subsequently be transferred (a default for the partitioned object

and an actual value for the partition).

In BW, a INITRANS value is never explicitly assigned when an object

is created. Provided that no changes are made with DB tools, the

hardcoded default value is generated.

o Are some of the indexes bitmap indexes?

The problem with bitmap indexes, as opposed to b-tree indexes, is

01.12.2008 Page 5 of 6

Note 84348 - Oracle deadlocks, ORA-00060

that there is no longer a 1:1 relationship between the index record

and the table data record. Data records in the Bitmap index are

stored in ranges. If this type of range is now extended, the entire

range must be locked. Deadlocks can occur if several parallel

processes try to access the same ranges simultaneously.

Extending the INITRANS value only helps under certain conditions in

this case. If this occurs very frequently, you should think about

alternatives.

- If the problem occurs during the loading process in InfoCubes,

you should change the loading process that deleted the bitmap

indexes previously and then recreate it. This has major

advantages from a performance point of view.

Problem: the automatic new creation from SAP causes your

previous values for INITRANS to be set back to the default (1).

- Unfortunately, it is not yet possible to automatically

incorporate this into the process for ODS objects. If the

problem occurs with ODS objects, a job that was running BEFORE

the loading process would be able to drop these indexes and

another job could create them again later. This means that you

can also specify a higher value for INITRANS.

- If neither of these two options are viable, you should consider

to what extent the indexes may be converted to b-tree indexes.

o Is this a table with a LOB field/is this a LOB object?

Under certain circumstances, a self-deadlock can also occur with

LOB objects.

In this case, the deadlock graph usually looks slightly different:

Deadlock graph:

-

---

Blocker(s)--

---

-

---

Waiter(s)----

process sess. holds waits process sess. holds waits

20017-0000010c 8 8 X 8 8 S

session 8: DID 0001-0008-00000001

session 8: DID 0001-0008-00000001

Rows waited on: Session 8: no row

There is only ONE row in the deadlock graph.

- You can use the following statement to determine which fields

have an LOB data type:

sqlplus "sapr3/<passwd>"

SQL> SELECT table_name, column_name, data_type FROM

dba_tab_columns

where table_name='<table name>' and data_type like '%LOB';

- You can change the INITRANS/MAXTRANS value as follows:

SQL> ALTER table "<table name>" modify lob (<field name>)

(index (INITRANS 20 maxtrans 255));

- If the problem persists, you must reorganize the table as

described above so that the change is applied to all blocks.

01.12.2008 Page 6 of 6

Note 84348 - Oracle deadlocks, ORA-00060

Header Data

Release Status: Released for Customer

Released on: 20.07.2004 10:42:12

Priority: Correction with low priority

Category: Program error

Main Component BC-DB-ORA Oracle

The note is not release-dependent.

Related Notes

Number Short Text

1108581 DIMP: Error ORA-00060 - deadlock detected for table CPZP

1044110 INITRANS for InfoCube indexes in BW 7

851980 Deadlock occurs when transaction dependent MRP is called

845823 Deadlock on tables MDVM/DBVM/J_3AMRPR/MDKP for AFS Solution

831234 Deadlock ORA-00060 during parallel loading into BW ODS

[Former Member]

Hi Anindhya;

Thanks you very much.

Will update...

Regards

seethy

[stefan_koehler]

Hello Anindya,

>Did you go thorough OSS 84348 and check INITRANS and MAXTRANS values ?

>Most probable reason of this Deadlock is wrong INITRANS , MAXTRANS on some tables.

But you don't understand the OSS note. If there is a "X" then it is no block level locking .. it is row level locking and in this case the INITRANS and MAXTRANS parameter are not the cause of this problem.

@ Seethy:

All information that you need is in the deadlock trace (database objects, etc.). Please attache the complete deadlock trace to this thread (maybe via rapdishare). Normally you have to go through the SAP programs to find the cause of this deadlock. The program names should also be in the deadlock trace.

Regards

Stefan

[Former Member]

Hi Stefan;

Thanks for response ...

Below mentioned are the important lines on the trace file..(will try to share the trace file through rapid share).

It shows an application name RMCSS012 .

PBUP" = :A6 AND "VKORG" = :A7 AND "VTWEG" = :A8 AND "SPART" = :A9 AND "KUNNR" = :A10 AND "VBTYP" = :A11 FOR UPDATE

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-00280005-0004d602 164 1900 X 81 2134 X

TX-000f0003-00076d4a 81 2134 X 164 1900 X

session 1900: DID 0001-00A4-000000B3 session 2134: DID 0001-0051-00000004

session 2134: DID 0001-0051-00000004 session 1900: DID 0001-00A4-000000B3

Rows waited on:

Session 2134: obj - rowid = 00013175 - AAATF1AEMAACq9iAAo

(dictionary objn - 78197, file - 268, block - 700258, slot - 40)

Session 1900: obj - rowid = 0001320D - AAATINAE5AAD4AwAAw

(dictionary objn - 78349, file - 313, block - 1015856, slot - 48)

Information on the OTHER waiting sessions:

Session 2134:

pid=81 serial=19 audsid=3177838 user: 27/SAPSR3

O/S info: user: erpadm, term: , ospid: 3035328, machine: eccprd01

program: dw.sapERP_DVEBMGS00@eccprd01 (TNS V1-V3)

client info: 0

application name: RMCSS012 , hash value=3481756193

action name: 13265, hash value=3653347526

Current SQL Statement:

SELECT /*+ FIRST_ROWS (1) */ * FROM "S009" WHERE "MANDT" = :A0 AND "SSOUR" = :A1 AND "VRSIO" = :A2 AND "SPMON" = :A3 AND "SPTAG" = :A4 AND "SPWOC" = :A5 AND

"SPBUP" = :A6 AND "VKORG" = :A7 AND "VTWEG" = :A8 AND "SPART" = :A9 AND "KUNNR" = :A10 AND "VBTYP" = :A11 FOR UPDATE

End of information on OTHER waiting sessions.

===================================================

PROCESS STATE

-

---

Process global information:

process: 7000004853bce78, call: 7000004768bdff0, xact: 7000004805d6d70, curses: 7000004835c9030, usrses: 7000004835c9030

-

---

SO: 7000004853bce78, type: 2, owner: 0, flag: INIT/-/-/0x00

(process) Oracle pid=164, calls cur/top: 7000004768bdff0/7000004768bdff0, flag: (0) -

int error: 0, call error: 0, sess error: 0, txn error 0

(post info) last post received: 0 0 192

last post received-location: kgskbwt: posting new actses to run

last process to post me: 7000004823d5b38 3 0

last post sent: 0 0 9

last post sent-location: ksqrcl

last process posted by me: 7000004873bbe28 1 0

PROCESS STATE

-

---

Process global information:

process: 7000004853bce78, call: 7000004768bdff0, xact: 7000004805d6d70, curses: 7000004835c9030, usrses: 7000004835c9030

-

---

SO: 7000004853bce78, type: 2, owner: 0, flag: INIT/-/-/0x00

(process) Oracle pid=164, calls cur/top: 7000004768bdff0/7000004768bdff0, flag: (0) -

int error: 0, call error: 0, sess error: 0, txn error 0

(post info) last post received: 0 0 192

last post received-location: kgskbwt: posting new actses to run

last process to post me: 7000004823d5b38 3 0

last post sent: 0 0 9

last post sent-location: ksqrcl

last process posted by me: 7000004873bbe28 1 0

(latch info) wait_event=0 bits=10

holding (efd=7) 700000010009700 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 0x070000048738CFC8 to 0x070000048738CFE8

70000048738CFC0 00000000 00000000 [........]

70000048738CFD0 00000000 00000000 00000000 00000000 [................]

70000048738CFE0 00000000 00000000 [........]

Process Group: DEFAULT, pseudo proc: 7000004824523a8

O/S info: user: oraerp, term: UNKNOWN, ospid: 839728

OSD pid info: Unix process pid: 839728, image: oracleERP@eccprd01

Dump of memory from 0x07000004843DCDB0 to 0x07000004843DCFB8

7000004843DCDB0 00000014 00000000 07000004 7589E5A0 [............u...]

7000004843DCDC0 00000007 0003139D 07000004 7589E680 [............u...]

7000004843DCDD0 00000007 0003139D 07000004 78AC9340 [............x..@]

regards

seethy

[Former Member]

Good DAy;

The Issue is resolved. What I do from my side is just did statistics for the tables shown dead lock , from the trace files. Eventhough previously I did the same But was not resolved. Now I am surprised that there is no more deadlocks on database check.

Thanks for all helps Anindhya and Stefan.

Thanks&Regards

Seethy

[stefan_koehler]

Hello Seethy,

could you please upload/post the deadlock trace ?

Regards

Stefan

[Former Member]

Hi,

Can you put here error in details here so that we can provide exect solution.

Anil

[anindya_bose]

Seethy

Dead Lock can occur due to many reasons..It would be greate to have the details of the problem.

perhaps you can post Sm21 detail log and also check if you are getting any dump in ST22 related to this dead lock.

Like DBIF_RSQL_SQL_ERROR...if yes then please paste those logs

[Former Member]

Hi ;

Thanks for the response.

There is no error in sm21 and st22. The warning are only on trace files.on DB14 the log for DBCHECK.

The trace files are more than a GIGA BYTE. I will delete the trace file and get new one and will post it after the next DBCHECK.

some contents on log is mentioned below..

SELECT /*+ FIRST_ROWS (1) */ * FROM "S014" WHERE "MANDT" = :A0 AND "SSOUR" = :A1 AND "VRSIO" = :A2 AND "SPMON" = :A3 AND "SPTAG" = :A4 AND "SPWOC" = :A5 AND

"SPBUP" = :A6 AND "VKORG" = :A7 AND "VTWEG" = :A8 AND "SPART" = :A9 AND "KUNNR" = :A10 AND "VBTYP" = :A11 FOR UPDATE

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:

'

regards

seethy

regards

seethy

[anindya_bose]

Seethy

OSS note 84348 may help you to diagonise the issue. But for the time being this note is under reworking I guess. So, you can not have that from SMP. Please check if you have a copy of this note with you.

I am pasting some portion of the note here.

"To find out whether this is a deadlock in Oracle, you need to examine the

trace file written by Oracle in a lot more detail. The file is usually

stored in the /oracle/<SID>/saptrace/usertrace directory. In addition, the

exact file name/directory can usually be determined from the ORA-00060

error message in /oracle/<SID>/saptace/background/alert<SID>.log.

Open the file - the 'deadlock graph' appears on the first two pages.

The deadlock graph is as follows:

-

---

Blocker(s)--

---

-

---

Waiter(s)---

Resource Name process sess. holds waits process sess. hold waits

TX-00090004-00011000 43 39 X 35 46 S

TX-0006001a-0001397d 35 46 X 43 39 S

Here, the last column that specifies the Waits as type 'S' is important.

If an 'X' is displayed instead in the graph, it is NOT an Oracle deadlock."

[JPReyes]

Moved to SAP on Oracle forum