Deadlock Error

Former Member · ‎12-12-2005

Hello ALL

Iam getting error like DEADLOCK.Why this error comes and how to rectify this.

Pl help me out ,its very urgent

Regards

balaji

Former Member · ‎01-25-2006

Hi guys,

I am getting the "Caller 70 Missing" error while loading the data in the ODS.

I referred to the OSS notes mentioned but they seem to talk about indexes in the Cubes.

But this solution does not seem to be applicable while loading data into ODS.

Also, our BW system is not installed on Oracle.

Can anybody let me the possible cause for the problem...

Its really urgent !!

Thanks in advance,

Punkuj.

Former Member · ‎12-12-2005

This can happen when SMON is running or any DBA

If the update mode is delta, technical status of job is changed to red and request is deleted from the datatarget. Infopackage for Delta update is triggered again to get delta from R/3 back. If its full update,request is deleted from the datatarget and Infopackage is triggered again to get full update.

Former Member · ‎12-12-2005

Hi Balaji,

First you need a service account. You can contact your BASIS for this as the service account is based on the Installation/License number.

Once you have the service account login id and password use this URL http://service.sap.com/notes

Bye

Dinesh

Former Member · ‎12-12-2005

Hi,

log on to www.service.sap.com under SAP Support Portal u find Quick Links under that first one is sapnotes search, once u click on that it will ask the username and password. After u enter those things u will enter to SAP Support Portal in that screen u find number where u have to enter the note no from starting with out any gap.Then press that arrow or just enter.

Hope it is clear.

Regards

Siddhu

Former Member · ‎12-12-2005

Hi,

Yes u r right,

When you install BW on an ORACLE database, BITMAP indexes are created on the fact tables to improve the reporting performance of the system.

Both r same, what u have to do is just log on to www.service.sap.com there at bottom right u find new user sign in just click it and then proceed.

Regards

Siddhu

Former Member · ‎12-12-2005

Hi Balaji,

Here you go on the OSS Notes:

631668

Symptom

The loading of data into InfoCubes terminates with an ORA 60, "deadlock detected". The monitor log shows a "CALLER 70 missing" entry and the request is flagged as red.

Other terms

Caller 70, DEADLOCK, ora0060, bitmap, indexes

Reason and Prerequisites

For basic InfoCubes in SAP BW, there are two fact tables for including transaction data: The F and the E fact table.

Unlike the E fact table, the F fact table contains the information about the request from where the transaction data originates.

When loading data (when receiving requests), the system writes data into the F fact table.

During "compression", the system summarizes data from different requests and writes it to the E fact table.

To ensure good reporting performance, it is important that you only keep data from a few requests in the F fact table, which means that you should compress requests as soon as possible.

Also bear in mind that deleting transaction data according to the request is only possible from the F fact table, as only this table contains the information regarding which data belongs to which request.

You should therefore compress a request as soon as you are certain that the data loaded is correct.

When you install BW on an ORACLE database, BITMAP indexes are created on the fact tables to improve the reporting performance of the system.

These indexes have a negative effect on system performance when you load data; the deadlock (ORA 60) mentioned above may also occur so that data loading terminates completely. The DEADLOCKs occur during parallel insert operations because ORACLE does not support a blocking concept for BITMAP indexes at data record level.

Solution

Before loading transaction data, delete secondary indexes on the fact tables and recreate them after you have loaded the data.

On the screen for administering data targets in the Administrator Workbench (transaction RSA1, context menu of an InfoCube: Administer), you can set this to be executed automatically by the system.

Alternatively, you can also load requests serially into InfoCubes.

This avoids the deadlock problem, however, you should note that after some loading processes, it is necessary to reorganize the BITMAP indexes because they degenerate very heavily.

751203

Symptom

The "SAPSQL_ARRAY_INSERT_DUPREC" runtime error for the array insert in a fact table. The termination point is in the generated ABAP:

INSERT (l_facttab) FROM TABLE GU. " KFL01

Other terms

"CX_SY_OPEN_SQL_DB" exception, "WRITE_ICFACT", duplicate key, duplicate record

Reason and Prerequisites

This is caused by a database error.

Solution

The correction is made in SAP DB 7.4.03.35 (PTS 1129540) and MaxDB 7.5.00.16 (PTS 1129548).

You can avoid the error in BW by loading the data serially into the InfoCube. Another workaround is to reduce the maximum size of the data packages.

84348

Symptom

ORA-00060: deadlock detected while waiting for resource

Other terms

ora-60 ora-000060

Reason 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:

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.

If you have to adjust maxtrans:

sqlplus "sapr3/<passwd>"

SQL> ALTER TABLE <TABLE NAME> maxtrans 255;

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

1. 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.

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

3. 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.)

4. Export the affected table (sapdba).

5. 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

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>';

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.

Are some of the indexes bitmap indexes?

The problem with bitmap indexes, as opposed to b-tree indexes, is 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.

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

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.

Bye

Dinesh

Former Member · ‎12-12-2005

Hi,

There are many situations where we come across this deadlock error, they r while loading, activating,dead locks on update rules etc...

Here I,m giving the reason for loading in cube