Deadlock Issue

Database
JGP
Enthusiast

Deadlock Issue

CREATE SET TABLE ETL_LOG ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      ETL_Rec_Id BIGINT GENERATED ALWAYS AS IDENTITY

           (START WITH 1 

            INCREMENT BY 1 

            MINVALUE 1 

            MAXVALUE 999999999999999999 

            NO CYCLE),

      ETL_Proc_Id INTEGER NOT NULL,

      ETL_Bus_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

      ETL_Excptn_Ts TIMESTAMP(6) NOT NULL,

      ETL_File_Id INTEGER,

      ETL_Trfm_Id INTEGER,

      ETL_Sys_Id INTEGER,

      ETL_File_Ptrn_Id INTEGER,

      ETL_Trfm_Job_Id INTEGER,

      ETL_Proc_Tp_Cd CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,

      ETL_Proc_Stat_Id SMALLINT,

      ETL_Flow_Id INTEGER,

      ETL_TD_Sess_Id INTEGER,

      ETL_Tool_Sess_Id INTEGER,

      ETL_Tgt_TblVw_DB_Nm VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      ETL_Tgt_TblVw_Nm VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      ETL_Excptn_Id VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

      ETL_Excptn_Desc VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

      ETL_Excptn_Col_Nm VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,

      ETL_Excptn_Col_Val VARCHAR(15000) CHARACTER SET UNICODE NOT CASESPECIFIC,

      ETL_Excptn_Src_Txt VARCHAR(15000) CHARACTER SET UNICODE NOT CASESPECIFIC,

      ETL_Updt_Usr VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

      ETL_Updt_Ts TIMESTAMP(6))

PRIMARY INDEX ( ETL_Rec_Id )

PARTITION BY RANGE_N(ETL_Bus_Dt  BETWEEN DATE '2011-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' DAY ,

 NO RANGE OR UNKNOWN);

And View on the same table with the below definition.

REPLACE VIEW ETL_LOG

  (

     ETL_Rec_Id

    ,ETL_Proc_Id

    ,ETL_Bus_Dt

    ,ETL_Excptn_Ts

    ,ETL_File_Id

    ,ETL_Trfm_Id

    ,ETL_Sys_Id

    ,ETL_File_Ptrn_Id

    ,ETL_Trfm_Job_Id

    ,ETL_Proc_Tp_Cd

    ,ETL_Proc_Stat_Id

    ,ETL_Flow_Id

    ,ETL_TD_Sess_Id

    ,ETL_Tool_Sess_Id

    ,ETL_Tgt_TblVw_DB_Nm

    ,ETL_Tgt_TblVw_Nm

    ,ETL_Excptn_Id

    ,ETL_Excptn_Desc

    ,ETL_Excptn_Col_Nm

    ,ETL_Excptn_Col_Val

    ,ETL_Excptn_Src_Txt

    ,ETL_Updt_Usr

    ,ETL_Updt_Ts

  )

AS LOCKING ROW FOR ACCESS

SELECT

     ETL_Rec_Id

    ,ETL_Proc_Id

    ,ETL_Bus_Dt

    ,ETL_Excptn_Ts

    ,ETL_File_Id

    ,ETL_Trfm_Id

    ,ETL_Sys_Id

    ,ETL_File_Ptrn_Id

    ,ETL_Trfm_Job_Id

    ,ETL_Proc_Tp_Cd

    ,ETL_Proc_Stat_Id

    ,ETL_Flow_Id

    ,ETL_TD_Sess_Id

    ,ETL_Tool_Sess_Id

    ,ETL_Tgt_TblVw_DB_Nm

    ,ETL_Tgt_TblVw_Nm

    ,ETL_Excptn_Id

    ,ETL_Excptn_Desc

    ,ETL_Excptn_Col_Nm

    ,ETL_Excptn_Col_Val

    ,ETL_Excptn_Src_Txt

    ,ETL_Updt_Usr

    ,ETL_Updt_Ts

FROM

  ETL_LOG                  

;

The ETL jobs are getting failed with Deadlock when simultaneous Insert/Delete operations are performed on this table using the View.

Any sort of help is greatly appreciated.

Tags (1)
2 REPLIES
Junior Contributor

Re: Deadlock Issue

"Locking row for access" is only for selects, insert/delete still set a write lock.

Do the ETL jobs use PI access in multistatements or PACKs?

Which ETL tool? TPump? Then the SERIALIZE option might help.

Dieter

JGP
Enthusiast

Re: Deadlock Issue

Hi Dieter,

The ETL tool is datastage.

The ETL Delete query is not based on PI.

DELETE FROM ETL_LOG WHERE ETL_Bus_Dt = 'Date' and ETL_Proc_Id = '1' and ETL_Trfm_Id = '1'