14 Replies Latest reply: Jan 25, 2006 1:44 PM by Pankaj L RSS

Deadlock Error

Balaji iyer
Currently Being Moderated

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

  • Re: Deadlock Error
    Prasad Nannuri
    Currently Being Moderated

    Hi,

     

    Refer this Oss notes

     

    <b>631668</b>

     

    Regards

    Siddhu

    • Re: Deadlock Error
      Anil Kumar
      Currently Being Moderated

      Hi Balagi,

       

      Why do not you give more information about the problem?

      Where it has occured and what is the log from Dump.

       

      Any way :

       

      For ODS activation , refer :634458

       

      In  data loading , refer: 562632

       

      With rgds,

      Anil Kumar Sharma .P

      • Re: Deadlock Error
        Balaji iyer
        Currently Being Moderated

        Hi

        Anil

         

        Thanks.Actually I did'nt get this error but I  came to know about this.What I want to know is what will be the reason for this error message.

         

        Iam not having access to chk the OSS notes,that's the reason Y iam looking for

         

        Regards

        balaji

  • Re: Deadlock Error
    Dinesh Kumar
    Currently Being Moderated

    Hi Balaji,

     

    Refer this post:

    Re: Deadlock - error

     

    Bye

    Dinesh

  • Re: Deadlock Error
    Prasad Nannuri
    Currently Being Moderated

    Hi,

     

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

     

    <i>Here I,m giving the reason for loading in cube</i>

     

    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.

     

    <b>To</b> have access to service.sap u just need the installation number which is there in system status and other details... contact ur basis guy he will help u in this regard...

     

    Regards

    Siddhu

    • Re: Deadlock Error
      Balaji iyer
      Currently Being Moderated

      Hi

      Siddu

       

      Thanks a lot for your informations.

      Then u mean to say that by default BITMAP indexes are created if u install BW on Oracle database.

       

      For having access to OSS notes do v need to have access to SAP Service Market Place orelse is the both are different to one another

       

      Pl give me information

       

      Regards

      balaji

  • Re: Deadlock Error
    Dinesh Kumar
    Currently Being Moderated

    Hi Balaji,

     

    Here you go on the OSS Notes:

    <u><i><b>631668</b></i></u>

     

    <u><b>Symptom</b></u>

    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.

     

    <u><b>Other terms</b></u>

    Caller 70, DEADLOCK, ora0060, bitmap, indexes

     

     

    <b><u>Reason and Prerequisites</u></b>

    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.

     

     

    <u><b>Solution</b></u>

    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.

     

     

     

    <u><i><b>751203</b></i></u>

    <u><b>Symptom</b></u>

    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

     

    <u><b>Other terms</b></u>

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

     

    <u><b>Reason and Prerequisites</b></u>

    This is caused by a database error.

     

    <u><b>Solution</b></u>

    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.

     

     

    <i><b><u>84348</u></b></i>

    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

     

    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

  • Re: Deadlock Error
    Prasad Nannuri
    Currently Being Moderated

    Hi,

     

    Yes u r right,

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

     

     

    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

    • Re: Deadlock Error
      Balaji iyer
      Currently Being Moderated

      Hi

      Sidhartha

       

      Thanks for your message.

      Can u tell me where to chk this OSS notes is there any tool for this or any URL or is  there in SAP Service Market Place .

      I hope it will be available in the SAP Service Market Place.If so where exactly there v can find this option?

       

      Regards

      balaji

  • Re: Deadlock Error
    Prasad Nannuri
    Currently Being Moderated

    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

  • Re: Deadlock Error
    Dinesh Kumar
    Currently Being Moderated

    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

  • Re: Deadlock Error
    Srikanth Reddy
    Currently Being Moderated

    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.

  • "Caller 70 Missing" Error
    Pankaj L
    Currently Being Moderated

    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.

Actions