All Forums Tools
achikan01 13 posts Joined 06/12
10 Oct 2014
TPT deadlock Issue.

When trying to insreat into log table getting deadlock on TPT script

  I am getting deadlock issue, when trying to insert into log table ( TABLE_LOG_TABLE_1).
 
I am not sure what is wrong? Can you please let me know you need more information
 
 
  ====================================================== =============
     =                                                                  =
     =                 Target/Error Table Information                  =
     =                                                                  =
     ====================================================== =============
LOAD_OPERATOR: preparing target table
DOBEGINLOAD: entering
PC_DOREQUEST: entering
ISSUEBEGINLOAD: entering
**** 13:10:58 This job will use the following tables:
 
              Target Table:  'TEST_PRELOAD_T.TABLE_1'
              Error Table 1: 'TEST_WORK_T.ER1_TABLE_1'
              Error Table 2: 'TEST_WORK_T.ER2_TABLE_1'
PC_PROCESSREQ: entering
PC_INITIATEREQ: entering with request: 'BEGIN LOADING TEST_PRELOAD_T.TABLE_1 ERRORFILES TEST_WORK_T.ER1_TABLE_1, TEST_WORK_T.ER2_TABLE_1 WITH INTERVAL;'
PC_INITIATEREQ: request length: 177
PC_INITIATEREQ: Using length: 0
PC_INITIATEREQ: leaving with return code: 0
PC_FETCHRESP: entering, expecting flavor: 8
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 8
PC_ERRORSET: Success!, Activity Count: 42
PC_ERRORSET: stmnt #:     1
PC_ERRORSET: field ct:    0
PC_ERRORSET: act type:    40
PC_ERRORSET: warningcode: 0
PC_ERRORSET: warning len: 0
PC_ERRORSET: error code is:     0
PC_ERRORSET: leaving with return code: 0
PC_FETCHRESP: leaving with return code: 0
PC_PROCESSREQ: leaving with result: 0
PC_ISREQUESTDEADLOCKED: entering
PC_ISREQUESTDEADLOCKED: CLI/DBS result 0 is not a deadlock error
PC_ISREQUESTDEADLOCKED: leaving
PC_FETCHRESP: entering, expecting flavor: 10
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 10
PC_ERRORSET: error code is:     0
PC_ERRORSET: leaving with return code: 0
PC_FETCHRESP: leaving with return code: 0
PROCESSAMPINFO: entering
PROCESSAMPINFO: source: 1 (1-BEGIN LOADING,2-LOGTABLE)
PROCESSAMPINFO: AMP map length: 3282
PROCESSAMPINFO: total AMP count: 546
PROCESSAMPINFO: up AMP count: 546
PROCESSAMPINFO: down AMP count: 0
PROCESSAMPINFO: Amp Array length: 3276
PROCESSAMPINFO: leaving with return code: 0
PC_FETCHRESP: entering, expecting flavor: 11
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 11
PC_ERRORSET: error code is:     0
PC_ERRORSET: leaving with return code: 0
PC_FETCHRESP: leaving with return code: 0
PC_FETCHRESP: entering, expecting flavor: 8
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 8
PC_ERRORSET: Success!, Activity Count: 1
PC_ERRORSET: stmnt #:     2
PC_ERRORSET: field ct:    1
PC_ERRORSET: act type:    51
PC_ERRORSET: warningcode: 0
PC_ERRORSET: warning len: 0
PC_ERRORSET: error code is:     0
PC_ERRORSET: leaving with return code: 0
PC_FETCHRESP: leaving with return code: 0
PC_FETCHRESP: entering, expecting flavor: 10
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 10
PC_ERRORSET: error code is:     0
PC_ERRORSET: leaving with return code: 0
PC_FETCHRESP: leaving with return code: 0
PC_FETCHRESP: entering, expecting flavor: 11
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 11
PC_ERRORSET: error code is:     0
PC_ERRORSET: leaving with return code: 0
PC_FETCHRESP: leaving with return code: 0
PC_FETCHRESP: entering, expecting flavor: 12
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 12
PC_ERRORSET: error code is:     0
PC_ERRORSET: leaving with return code: 0
PC_FETCHRESP: leaving with return code: 0
PC_ENDREQUEST: entering
PC_ENDREQUEST: leaving
ISSUEBEGINLOAD: leaving
PC_DOREQUEST: leaving successfully
DOBEGINLOAD: Action:   0
DOBEGINLOAD: DBS flag: 0
DOBEGINLOAD: table is new
DOBEGINLOAD: delete rows except LOGT_INIT row in log table
PC_DOREQUEST: entering
PC_ISSUESQLAUX: entering
PC_ISSUESQLAUX: Issuing SQL statement: 'DELETE FROM TEST_WORK_T.LOG_TABLE_1 WHERE (LOGTYPE <> 50);'
PC_SIMPLEREQ: entering
PC_SIMPLEREQ: request length: 80
PC_SIMPLEREQ: Using length: 0
PC_PROCESSREQ: entering
PC_INITIATEREQ: entering with request: 'DELETE FROM TEST_WORK_T.LOG_TABLE_1 WHERE (LOGTYPE <> 50);'
PC_INITIATEREQ: request length: 80
PC_INITIATEREQ: Using length: 0
PC_INITIATEREQ: leaving with return code: 0
PC_FETCHRESP: entering, expecting flavor: 8
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 8
PC_ERRORSET: Success!, Activity Count: 0
PC_ERRORSET: stmnt #:     1
PC_ERRORSET: field ct:    0
PC_ERRORSET: act type:    5
PC_ERRORSET: warningcode: 0
PC_ERRORSET: warning len: 0
PC_ERRORSET: error code is:     0
PC_ERRORSET: leaving with return code: 0
PC_FETCHRESP: leaving with return code: 0
PC_PROCESSREQ: leaving with result: 0
PC_FINREQUEST: entering
PC_FETCHRESP: entering, expecting flavor: 11
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 11
PC_ERRORSET: error code is:     0
PC_ERRORSET: leaving with return code: 0
PC_FETCHRESP: leaving with return code: 0
PC_FETCHRESP: entering, expecting flavor: 12
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 12
PC_ERRORSET: error code is:     0
PC_ERRORSET: leaving with return code: 0
PC_FETCHRESP: leaving with return code: 0
PC_ENDREQUEST: entering
PC_ENDREQUEST: leaving
PC_FINREQUEST: leaving with result: 0
PC_SIMPLEREQ: leaving with result: 0
PC_ISREQUESTDEADLOCKED: entering
PC_ISREQUESTDEADLOCKED: CLI/DBS result 0 is not a deadlock error
PC_ISREQUESTDEADLOCKED: leaving
PC_ISSUESQLAUX: leaving with result: 0
PC_DOREQUEST: leaving successfully
DOBEGINLOAD: insert AMP info into log table
PC_DOREQUEST: entering
INSERTAMPINFO: entering
INSERTAMPINFO: request: 'USING TAMP(SMALLINT),UAMP(SMALLINT),DAMP(SMALLINT) ,AARRAY(VARBYTE(24576)) INSERT INTO TEST_WORK_T.LOG_TABLE_1 (LOGTYPE,OPERSEQ,TAMP,UAMP,DAMP,AARRAY) VALUES (130,10,:TAMP,:UAMP,:DAMP,:AARRAY);'
INSERTAMPINFO: InsLength = 3284
INSERTAMPINFO: Inserting AMPCount: 546
INSERTAMPINFO: Inserting UpAMPs: 546
INSERTAMPINFO: Inserting DnAMPs: 0
INSERTAMPINFO: Inserting varlen for amp array: 3276
INSERTAMPINFO: Inserting phase: 130
INSERTAMPINFO: Inserting sequence: 10
PC_SIMPLEREQ: entering
PC_SIMPLEREQ: request length: 253
PC_SIMPLEREQ: Using length: 3284
PC_PROCESSREQ: entering
PC_INITIATEREQ: entering with request: 'USING TAMP(SMALLINT),UAMP(SMALLINT),DAMP(SMALLINT) ,AARRAY(VARBYTE(24576)) INSERT INTO TEST_WORK_T.LOG_TABLE_1 (LOGTYPE,OPERSEQ,TAMP,UAMP,DAMP,AARRAY) VALUES (130,10,:TAMP,:UAMP,:DAMP,:AARRAY);'
PC_INITIATEREQ: request length: 253
PC_INITIATEREQ: Using length: 3284
PC_INITIATEREQ: leaving with return code: 0
PC_FETCHRESP: entering, expecting flavor: 8
PC_ERRORSET: entering with CLI error code: 0
PC_ERRORSET: parcel received: 9
PC_ERRORSET: error code is:     2631
PC_ISEXCEPTION: entering with error code: 2631
PC_ISEXCEPTION: checking original exception code
PC_ISEXCEPTION: checking new exception code list
PC_ISEXCEPTION: number of codes in list: 0
PC_ISEXCEPTION: error does not match any exception code
PC_ISEXCEPTION: leaving with FALSE
PC_OUTDBSER: entering
PC_LOGJOBMETADATA: entering
PC_LOGJOBMETADATA: leaving
PC_OUTDBSER: DBS Error set to 2631
PC_OUTDBSER: placing message in global memory: Transaction ABORTed due to Deadlock.

 

Tags:
gebel 1 post Joined 02/15
10 Feb 2015

Hi achikan01,
we have the same problem each day within our batch window. We have about 1000 load jobs each day and 2 in average are aborted with this problem. We do not have a solution for it so far.
We are running the load job with Ab Initio and the method PT_api_load. We use TD 14.10.04.05.
An example from DBQL that produces the error "Transaction ABORTed due to Deadlock." is the following SQL "INSERT INTO p_load.t_load_client_LOG (LOGTYPE, OPERSEQ) VALUES (50, 0);"
Did you already find a solution? Or at least a good workaround?
Kind regards

suhailmemon84 47 posts Joined 09/10
03 May 2016

Has anyone found a resolution to this? I would appreciate if someone can share their insights.
We also receive deadlock errors with some of our TPT LOAD jobs while trying to insert into the log table.
The concurrency of utility jobs at the time of deadlock is also very low. When the deadlock error occured, at most 10 fastload jobs were concurrent.
-Suhail

feinholz 1234 posts Joined 05/08
03 May 2016

Any time we get a 2631 from the DBS while accessing the restart log table, we retry the request.
I believe we retry 5 times.
If we still get the 2631 after the retries, we will terminate with that error code.
The job output shown above only shows the code receiving the 2631 the first time. It does not show anything after that.
In order to help (you all) with TPT issues, at a minimum we would need to know the version of TPT (not the database), and the platform.

--SteveF

suhailmemon84 47 posts Joined 09/10
03 May 2016

The TPT version we use is: 15.00.00.00. We're on Teradata database version: 15.0.
Here is my platform info:

usr/tmp> uname -a

#1 SMP Tue Apr 7 09:13:12 PDT 2015 x86_64 x86_64 x86_64 GNU/Linux

/usr/tmp> cat /etc/*release

Enterprise Linux Server release 5.4 (Tikanga)

 

here is the tbuild script I have. Is there any way to specify the deadlock retry value here?

 
 

USING CHARACTER SET UTF8
DEFINE JOB 20160501070035_13896720
(
    DEFINE SCHEMA SCHEMA1
    (
        col1 DECIMAL(18,0),
        col2 DECIMAL(10,2),
        col3 DECIMAL(10,2),
        col4 VARCHAR(3)
    );
    DEFINE OPERATOR DC_OP
    TYPE DATACONNECTOR PRODUCER
    SCHEMA SCHEMA1
    ATTRIBUTES
    (
        FileName = '/tmp/2016.05.01.07.00.35_61765335_749_47455851394240_1873460999.dat',
        Format = 'BINARY',
        IndicatorMode = 'Y',
        OpenMode = 'Read'
    );
    DEFINE OPERATOR LOAD_OP
    TYPE LOAD
    SCHEMA *
    ATTRIBUTES
    (
        UserName = @UserName,
        UserPassword = @UserPassword,
        QueryBandSessInfo = 'TASK=xyz.dxt;PID=744;',
        TargetTable = '"STG"."tbl1"',
        LogTable = '"WRK"."t20160501070035_749103033591"',
        ErrorTable1 = '"WRK"."t20160501070035_749193041517"',
        ErrorTable2 = '"WRK"."t20160501070035_749208382984"',
        ErrorLimit = 1,
        MaxSessions = 4,
        TdpId = 'SYSTEM1'
    );
    STEP Load_Target_Table
    (
        APPLY
        (
            'insert into "edw_stage"."tbl1" ("col1", "col2", "col3", "col4" )
values ( :col1, :col2, :col3, :col4);
'
        )
        TO OPERATOR (LOAD_OP)
        SELECT * FROM OPERATOR (DC_OP);
    );
);

 

suhailmemon84 47 posts Joined 09/10
04 May 2016

Here is the log file we have:
 

usr/tmp> cat xyz.log
Teradata Parallel Transporter Version 15.00.00.00
Job log: /opt/teradata/client/15.00/tbuild/logs/20160502073945_85315420-5632.out
Job id is 20160502073945_85315420-5632
Teradata Parallel Transporter DC_OP[1]: TPT19006 Version 15.00.00.00
Teradata Parallel Transporter Load Operator Version 15.00.00.00
LOAD_OP: private log not specified
DC_OP[1]: TPT19003 NotifyMethod: 'None (default)'
DC_OP[1]: TPT19008 DataConnector Producer operator Instances: 1
DC_OP[1]: TPT19003 ECI operator ID: 'DC_OP-3414'
LOAD_OP: connecting sessions
DC_OP[1]: TPT19222 Operator instance 1 processing file '/tmp/2016.05.01.07.00.35_61765335_749_47455851394240_1873460999.dat'.
LOAD_OP: preparing target table
LOAD_OP: TPT10508: RDBMS error 2631: Transaction ABORTed due to Deadlock.
LOAD_OP: disconnecting sessions
LOAD_OP: Total processor time used = '1.88 Second(s)'
LOAD_OP: Start : Mon May  2 07:39:48 2016
LOAD_OP: End   : Mon May  2 07:40:11 2016
DC_OP[1]: TPT19221 Total files processed: 0.
Job step Load_Target_Table terminated (status 12)
Job 20160502073945_85315420 terminated (status 12)
Job start: Mon May  2 07:39:45 2016
Job end:   Mon May  2 07:40:11 2016

 
Here is the tpt log
 

/usr/tmp> tlogview -l /opt/teradata/client/15.00/tbuild/logs/20160502073945_85315420-5632.out -f"*" -g | more

Public log:


Using memory mapped file for IPC

TPT_INFRA: TPT04101: Warning: TMSM failed to initialize
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Executor Version 15.00.00.00
Teradata Parallel Transporter Coordinator Version 15.00.00.00
Teradata Parallel Transporter DC_OP[1]: TPT19006 Version 15.00.00.00
DC_OP[1]: TPT19206 Attribute 'TraceLevel' value reset to 'Statistics Only'.
DC_OP[1]: TPT19010 Instance 1 directing private log report to 'dtacop-prod-3414-1'.
Teradata Parallel Transporter Load Operator Version 15.00.00.00
LOAD_OP: private log not specified
DC_OP[1]: TPT19003 NotifyMethod: 'None (default)'
DC_OP[1]: TPT19008 DataConnector Producer operator Instances: 1
DC_OP[1]: TPT19003 ECI operator ID: 'DC_OP-3414'

     ===================================================================
     =                                                                 =
     =                  TERADATA PARALLEL TRANSPORTER                  =
     =                                                                 =
     =              LOAD OPERATOR     VERSION 15.00.00.00              =
     =                                                                 =
     =          OPERATOR SUPPORT LIBRARY VERSION 15.00.00.00           =
     =                                                                 =
     = COPYRIGHT 2001-2014, TERADATA CORPORATION. ALL RIGHTS RESERVED. =
     =                                                                 =
     =                     Process I.D.:     3413                      =
     =                                                                 =
     ===================================================================

**** 07:39:48 Processing starting at: Mon May  2 07:39:48 2016

     ===================================================================
     =                                                                 =
     =                      Module Identification                      =
     =                                                                 =
     ===================================================================

     Load Operator for Linux release 2.6.18-404.0.0.0.1.el5 on 
     LoadMain   : 15.00.00.03
     LoadCLI    : 15.00.00.04
     LoadUtil   : 14.10.00.01
     PcomCLI    : 15.00.00.23
     PcomMBCS   : 14.10.00.02
     PcomMsgs   : 15.00.00.01
     PcomNtfy   : 14.10.00.05
     PcomPx     : 15.00.00.07
     PcomUtil   : 15.00.00.07
     PXICU      : 15.00.00.00
     TDICU      : 15.00.00.00
     CLIv2      : 15.00.00.00

     ===================================================================
     =                                                                 =
     =                      Attribute Definitions                      =
     =                                                                 =
     ===================================================================

**** 07:39:48 Options in effect for this job:
              OperatorType:  Consumer
              Instances:     1
              Character set: Not specified; will use default
              Checkpoint:    No checkpoint in effect
              Notify:        Not enabled
              Buffer size:   Maximum allowable
              Error limit:   1 rejected record(s)
              Tenacity:      4 hour limit to successfully connect
              Sleep:         6 minute(s) between connect retries
              Pause Acq:     Not in effect

     ===================================================================
     =                                                                 =
     =                     Column/Field Definition                     =
     =                                                                 =
     ===================================================================

     Column Name                    Offset Length Type
     ============================== ====== ====== ========================
     col1                                0     40 VARCHAR
     col2                               42     40 VARCHAR
     col3                               84     40 VARCHAR
     col4                              126     40 VARCHAR
     col5                              168     19 CHAR
     ============================== ====== ====== ========================
     INDICATOR BYTES NEEDED: 1
     EXPECTED RECORD LENGTH: 188

     ===================================================================
     =                                                                 =
     =                   Control Session Connection                    =
     =                                                                 =
     ===================================================================

LOAD_OP: connecting sessions
**** 07:39:48 Connecting to RDBMS:    'SYSTEM1'
**** 07:39:48 Connecting with UserId: 'USER'
DC_OP[1]: TPT19222 Operator instance 1 processing file '/tmp/2016.05.02.07.39.45_365483415_3222_46994505561280_121273854.dat'.
**** 07:39:49 This job is submitting the following request:

     SET QUERY_BAND = 'TASK=xyz.dxt;PID=744;' UPDATE FOR SESSION;


     ===================================================================
     =                                                                 =
     =                  Teradata Database Information                  =
     =                                                                 =
     ===================================================================

**** 07:39:49 Teradata Database Version:      '15.00.02.07                     '
**** 07:39:49 Teradata Database Release:      '15.00.02.06                   '
**** 07:39:49 Maximum request size supported: 1MB
**** 07:39:49 Session character set:          'ASCII'
**** 07:39:49 Data Encryption:                supported
**** 07:39:49 Enhanced Statement Status Level: 1
**** 07:39:50 Restart log table '"WRK"."t20160502073945_322264028161"' has been created

     ===================================================================
     =                                                                 =
     =                   Special Session Connection                    =
     =                                                                 =
     ===================================================================

**** 07:40:01 Number of sessions adjusted due to TASM:      40

              Instance Assigned Connected Result
              ======== ======== ========= ======================
                  1        40       40    Successful
              ======== ======== ========= ======================
                Total      40       40    Successful

     ===================================================================
     =                                                                 =
     =                 Target/Error Table Information                  =
     =                                                                 =
     ===================================================================

LOAD_OP: preparing target table
**** 07:40:01 This job will use the following tables:

              Target Table:  '"STG"."TBL1"'
              Error Table 1: '"WRK"."t20160502073945_322274840705"'
              Error Table 2: '"WRK"."t20160502073945_322217997408"'
**** 07:40:02 Deadlock error received. Retrying Request.

LOAD_OP: TPT10508: RDBMS error 2631: Transaction ABORTed due to Deadlock.
**** 07:40:10 TPT10508: RDBMS error 2631: Transaction ABORTed due to Deadlock.
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0

     ===================================================================
     =                                                                 =
     =                        Logoff/Disconnect                        =
     =                                                                 =
     ===================================================================

LOAD_OP: disconnecting sessions
**** 07:40:10 Logging off all sessions

              Instance      Cpu Time
              ========  ================
                   1        1.88 Seconds

**** 07:40:11 Total processor time used = '1.88 Second(s)'
     .        Start : Mon May  2 07:39:48 2016
     .        End   : Mon May  2 07:40:11 2016
     .        Highest return code encountered = '0'.
LOAD_OP: Total processor time used = '1.88 Second(s)'
LOAD_OP: Start : Mon May  2 07:39:48 2016
LOAD_OP: End   : Mon May  2 07:40:11 2016
**** 07:40:11 This job terminated
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0, Total Rows Sent = 0
DC_OP[1]: TPT19221 Total files processed: 0.
Job step Load_Target_Table terminated (status 12)
Job 20160502073945_85315420 terminated (status 12)
Job start: Mon May  2 07:39:45 2016
Job end:   Mon May  2 07:40:11 2016
Total available memory:          20000676
Largest allocable area:          20000676
Memory use high water mark:         47256
Free map size:                       1024
Free map use high water mark:          18
Free list use high water mark:          0


Private log TWB_SRCTGT:


Load_Target_Table    SELECT_2[0001]       DC_OP            FILE                                                                               /tmp/2016.05.02.07.39.45_365483415_3222_46994505561280_121273854.dat
Load_Target_Table    APPLY_1[0001]        LOAD_OP          DBS                  SYSTEM1                                                        "STG"."TBL1"

Private log PXCRM:


CheckPoint Resource Manager initialized.
Checking whether a valid CheckPoint exists for restart.

Private log TWB_STATUS:


Load_Target_Table    APPLY_1[0001]        Success              LOAD_OP             1    1 INITIATE-Started     07:39:48     0.0000     0.0000      65000          0                0                0     0     0 N Y
Load_Target_Table    SELECT_2[0001]       Success              DC_OP               1    1 INITIATE-Started     07:39:48     0.0000     0.0000      65000          0                0                0     0     0 N Y
Load_Target_Table    APPLY_1[0001]        Fatal Error          LOAD_OP             1    1 INITIATE-Ended       07:40:10    22.0000     1.8800      65000          0                0                0     0     0 N Y
Load_Target_Table    APPLY_1[0001]        Success              LOAD_OP             1    1 TERMINATE-Started    07:40:10     0.0000     0.0000      65000          0                0                0     0     0 N Y
Load_Target_Table    APPLY_1[0001]        Success              LOAD_OP             1    1 TERMINATE-Ended      07:40:11     1.0000     0.0000      65000          0                0                0     0     0 N Y
Load_Target_Table    SELECT_2[0001]       Success              DC_OP               1    1 TERMINATE-Started    07:40:11     0.0000     0.0000      65000          0                0                0     0     0 N Y
Load_Target_Table    SELECT_2[0001]       Success              DC_OP               1    1 TERMINATE-Ended      07:40:11     0.0000     0.0000      65000          0                0                0     0     0 N Y

Private log TWB_EVENTS:


20160502073945_85315420-5632,17,0,OperatorEnter,Load_Target_Table,LOAD_OP,1,2016-05-02,,1,0
20160502073945_85315420-5632,116,5,UtilityName,Load_Target_Table,LOAD_OP,1,2016-05-02,TPT Load Operator,1,0
20160502073945_85315420-5632,0,5,LoadVersionId,Load_Target_Table,LOAD_OP,1,2016-05-02,15.00.00.00,1,0
20160502073945_85315420-5632,115,1,UtilityId,Load_Target_Table,LOAD_OP,1,2016-05-02,1,1,0
20160502073945_85315420-5632,132,5,LoadTdpId,Load_Target_Table,LOAD_OP,1,2016-05-02,SYSTEM1,1,0
20160502073945_85315420-5632,1,5,LoadUserName,Load_Target_Table,LOAD_OP,1,2016-05-02,USER,1,0
20160502073945_85315420-5632,128,5,LoadDbase,Load_Target_Table,LOAD_OP,1,2016-05-02,USER,1,0
20160502073945_85315420-5632,109,1,LoadRDBMSError,Load_Target_Table,LOAD_OP,1,2016-05-02,2631,1,0
20160502073945_85315420-5632,147,5,LoadRDBMSMsgTxt,Load_Target_Table,LOAD_OP,1,2016-05-02,RDBMS error 2631: Transaction ABORTed due to Deadlock.,1,0
20160502073945_85315420-5632,135,0,LoadSessEnd,Load_Target_Table,LOAD_OP,1,2016-05-02,,1,0
20160502073945_85315420-5632,18,1,OperatorExit,Load_Target_Table,LOAD_OP,1,2016-05-02,0,1,0

Private log dtacop-prod-3414-1:


     =                  TraceFunction: 'NO (defaulted)' (=0)                  =
     ==========================================================================
     =                                                                        =
     =                     TERADATA PARALLEL TRANSPORTER                      =
     =                                                                        =
     =              DATACONNECTOR OPERATOR VERSION  15.00.00.00               =
     =                                                                        =
     =           DataConnector UTILITY LIBRARY VERSION 15.00.00.17            =
     =                                                                        =
     =    COPYRIGHT 2001-2011, Teradata Corporation.  ALL RIGHTS RESERVED.    =
     =                                                                        =
     ==========================================================================

     Operator name: 'DC_OP' instance 1 of 1 [Producer]

**** 07:39:48 Processing starting at: Mon May  2 07:39:48 2016

     ==========================================================================
     =                                                                        =
     =                    Operator module static specifics                    =
     =                                                                        =
     =                 Compiled for platform: '32-bit LINUX'                  =
     =          Operator module name:'dtacop', version:'15.00.00.00'          =
     =                                                                        =
     = pmdcomt_HeaderVersion: 'Common 15.00.00.01' - packing 'pack (push, 1)' =
     = pmddamt_HeaderVersion: 'Common 13.10.00.01' - packing 'pack (push, 1)' =
     =                                                                        =
     ==========================================================================

     ==========================================================================
     =                                                                        =
     =                   > General attribute Definitions <                    =
     =                                                                        =
     =                             TraceLevel: ''                             =
     =                   EndianFlip: 'NO (defaulted)' (=0)                    =
     =                       IndicatorMode: 'YES' (=1)                        =
     =                  NullColumns: 'YES (defaulted)' (=1)                   =
     =                       SYSTEM_CharSetId: 'ASCII'                        =
     =                                                                        =
     ==========================================================================

     LITTLE ENDIAN platform
     WARNING!  NotifyMethod: 'None (default)'
     Operator 'dtacop' main source version:'15.00.00.15'
     DirInfo global variable name: 'DirInfo'
     FileNames global variable name: 'FileNames'
     DC_PREAD_SM_TOKENS global variable name: 'DC_PREAD_SM_TOKENS'

     ==========================================================================
     =                                                                        =
     =                   > Operator attribute Definitions <                   =
     =                                                                        =
     ==========================================================================

     DirectoryPath defaulting to: '/home/prod'
     FileList: 'NO (defaulted)' (=0)
     MultipleReaders: 'NO (defaulted)' (=0)
     RecordsPerInstance: (use default calculation per schema)
     EnableScan: 'Yes (defaulted)'
     Initializing with CharSet = 'ASCII'.
     Alphabetic CSName=ASCII
     Established character set ASCII
     Single-byte character set in use

     ==========================================================================
     =                                                                        =
     =                       > Module Identification <                        =
     =                                                                        =
     ==========================================================================

     DataConnector operator for Linux release 2.6.18-404.0.0.0.1.el5 on 
     TDICU................................... 15.00.00.00
     PXICU................................... 15.00.00.00
     PMPROCS................................. 15.00.00.08
     PMRWFMT................................. 15.00.00.02
     PMTRCE.................................. 13.00.00.02
     PMMM.................................... 03.00.00.01
     DCUDDI.................................. 15.00.00.13
     PMHEXDMP................................ 14.10.00.01
     PMHDFSDSK............................... 15.00.00.00
     PMUNXDSK................................ 15.00.00.02

     >> Enter DC_DataConFileInfo
     Job Type=0
     UseGeneralUDDIcase: 'NO (defaulted)' (=0)
     WriteBOM: 'NO (defaulted)' (=0)
     AcceptExcessColumns: 'NO (defaulted)' (=0)
     AcceptMissingColumns: 'NO (defaulted)' (=0)
     TruncateColumnData: 'NO (defaulted)' (=0)
     TruncateColumns: 'NO (defaulted)' (=0)
     TruncateLongCols: 'NO (defaulted)' (=0)
     WARNING!  RecordErrorFilePrefix attribute not specified, there is no default
     RecordErrorVerbosity: OFF (default) (=0)
     FileName: '/tmp/2016.05.02.07.39.45_365483415_3222_46994505561280_121273854.dat'
     OpenMode: 'READ' (1)
     Format: 'BINARY' (2)
     IOBufferSize: 131072 (default)

     Full File Path: /tmp/2016.05.02.07.39.45_365483415_3222_46994505561280_121273854.dat
     Data Type              Ind  Length  Offset M
              VARCHAR (  7)   1      40       0 N
              VARCHAR (  7)   1      40      40 N
              VARCHAR (  7)   1      40      80 N
              VARCHAR (  7)   1      40     120 N
                 CHAR (  5)   1      19     160 N
     Schema is not all character data
     Schema is not compatible with delimited data
     Validating parsing case: 1200000.
     SBCS (QUOTED DATA: No), Delimiter[0]: ''
     Delimiter: x''
     Escape Delimiter: x''
     Open Quote: x''
     Close Quote: x''
     Escape Quote: x''
     ==========================================================================
     =                                                                        =
     =                    > Log will include stats only <                     =
     =                                                                        =
     ==========================================================================
**** 07:39:49 From file '/tmp/2016.05.02.07.39.45_365483415_3222_46994505561280_121273854.dat', starting to send rows.
     AllowBufferMode: 'YES (defaulted)' (=1)
     Files read by this instance: 0
**** 07:40:11 Total processor time used = '0.00 Seconds(s)'
**** 07:40:11 Total files processed: 0
(END)

 

suhailmemon84 47 posts Joined 09/10
04 May 2016

At line#147 in the tpt log I posted above, it does say it is retrying the request after receiving the initial deadlock error.
Does it retry only once?
Is there any setting (in DBS?) to specify how many times to retry before failing the job?
What is the reason for the deadlock to happen while inserting a row in the restart log? Is it because 2 utility jobs (running with the same user) tried to access dbc.accessrights (as part of the insert to the restart log table) at the same time and since the PI on dbc.accessrights is userid, deadlock occured?

feinholz 1234 posts Joined 05/08
04 May 2016

Please upgrade to the latest patch of 15.00, at least 15.00.00.08.

--SteveF

suhailmemon84 47 posts Joined 09/10
04 May 2016

Hi Steve,
Once we upgrade to the latest patch of 15.00, do you think these deadlocks will go away?
-Suhail

feinholz 1234 posts Joined 05/08
04 May 2016

Deadlocks cannot ever go away. If you have enough jobs running on the system, there are bound to be deadlocks.
The "fix" is that we will handle it better.
 

--SteveF

suhailmemon84 47 posts Joined 09/10
04 May 2016

Thank you so much for your inputs Steve. We will work on upgrading to the latest patch.
Just curious, can you advise on how the latest patch of TPT handles deadlocks better than the version we're currently on: 15.00.00.00?
Also, I read in TPT documentation that upon receiving deadlocks, TPT usually retires about 5 times before actually failing the job. But the job log I provided here shows that it retried only once before failing. (Line#147 above, also pasted below)
Is that essentially what the latest patch fixes? It will retry 5 times instead of doing it once in the current version: 15.00.00.00

LOAD_OP: preparing target table
**** 07:40:01 This job will use the following tables:
 
              Target Table:  '"STG"."TBL1"'
              Error Table 1: '"WRK"."t20160502073945_322274840705"'
              Error Table 2: '"WRK"."t20160502073945_322217997408"'
**** 07:40:02 Deadlock error received. Retrying Request.
 
LOAD_OP: TPT10508: RDBMS error 2631: Transaction ABORTed due to Deadlock.
**** 07:40:10 TPT10508: RDBMS error 2631: Transaction ABORTed due to Deadlock.
TPT_INFRA: TPT02255: Message Buffers Sent/Received = 0, Total Rows Received = 0

-Suhail

feinholz 1234 posts Joined 05/08
12 May 2016

I believe in this case, the "Retrying request" message was output in error.
As indicated above, we will handle it better.
There were some areas where requests resulting in the deadlock were not retried.
 

--SteveF

hvganipineni 14 posts Joined 09/09
30 Jun 2016

Hello Suhail, Just curious did you guys upgrade? And did it fix your problem?

Harsha Ganipineni

suhailmemon84 47 posts Joined 09/10
13 Jul 2016

Hi Harsh,
We haven't done the TPT upgrade yet. We're scheduled to go to Teradata version: 15.10 in next few days. It appears that when we upgraded our Test system to 15.10, the # of deadlocks reduced. 
So once we upgrade our production system to 15.10, we're hoping the deadlocks reduce too. If that doesn't happen, then we have a few options:
1. As feinholz suggested, upgrade tpt to latest patch and verify if deadlocks go away.
2. Analysis of locking logger data shows no deadlocks but does show an increased amount of blocking on 2 dbc tables: sometimes it is dbc.accessrights and sometimes it is dbc.eventlog. For dbc.accessrights, we plan to provide direct access rights to the user launching the tpt jobs rather than through roles. For dbc.eventlog, we plan to reduce the overall # of sessions the tpt user initiates and reduce the overall # of sessions being initiated by any other users.
3. Last but not the least, we're going to look at reducing the # of tpt jobs we run in parallel at any point in time. I don't know how much this would help because it seems we get deadlocks even when the overall concurrency is low(once we received deadlocks when only 2 load jobs were running)
HTH. I'll continue to update as this mystery unfolds :)
-Suhail
 

Johannes Vink 28 posts Joined 08/14
13 Jul 2016

15.00/15.10 has several improvements around locking for TPT: dbc.accessrights has another PI and there is the partition level locking.
I strongly suggest that you familiarize yourself with the new features of 15.10.
Direct roles and reducing sessions will not help. It is the creation of the error/temp tables of TPT that lock the dbc.accessrights as the rights are being written to the table. Only solution until you use 15.10 is using different users that use TPT as the PI is currently on (UserId ,DatabaseId). Or use another database.

venkata_k01 24 posts Joined 07/16
14 Jul 2016

Hi All,
I am also facing deadlock issue when i am trying to load the target table using tdload. The target table is a temporary table created by the script before running tdload. So, there won't be any other processes trying to load the target table but still i am getting the deadlock issue.
 
$INSERTER:TPT10508:RDBMS error 2631:Transaction ABORTed due to deadlock
$INSERTER:TPT14117 :An error occured while inserting row <259628> into the database by Instance <1>
 
Any help to fix the issue would be very helpful.
 
 
 

suhailmemon84 47 posts Joined 09/10
14 Jul 2016

Thanks Johannes. We are in the process of familiarizing ourselves with the new features of 15.10. You're right. The PI changes could help. Hopefully the deadlocks reduce a bit after the upgrade. I'll keep you guys posted.
-Suhail

feinholz 1234 posts Joined 05/08
14 Jul 2016

I see that you are using the Inserter operator.
Are you using multiple instances?
Multiple sessions?
In order to cut down on the deadlocks on the target table, you will need to restrict your Inserter to a single instance and single session.
 

--SteveF

You must sign in to leave a comment.