Deadlock on dbc.accessrights

Database
Enthusiast

Deadlock on dbc.accessrights

The application team experienced a deadlock issue.

There were three different jobs running: Job A was blocked by Job B. Job B was blocked by Job A. Job C was blocked by both Job A and Job B.

When checking viewpoint, I see that the point of contention was on the background processing that Informatica does for TPT... creating and dropping temporary views.

Here are the SQL and BLOCKED BY statements.

For session 78296:

DROP VIEW PM_VCRL4GLVCLNFS75S7QHXQH4TLJM;

Username:

TIDWETLDLY

Host:

1

Session ID:

78297

Lock Type:

Write

Status:

Granted

Locked:

RowHash DBC.AccessRights

For session 78297:

CREATE VIEW PM_VQT3MDELLGH2OPVSH4X7DH6SZ4A (ALRT_CD, ALRT_RESPNS_TYP_CD, SYS_FUNC_CD, DLVRY_SYS_CD, UPDKEY_EFF_STRT_TS, UPD_END_TS) AS

SELECT ALRT_CD,

ALRT_RESPNS_TYP_CD,

SYS_FUNC_CD,

DLVRY_SYS_CD,

UPDKEY_EFF_STRT_TS,

UPD_END_TS

FROM tIDWLoadReady.LR_ALRT_RESPNS

WHERE INS_UPD_FLG ='U';

Username:

TIDWETLDLY

Host:

1

Session ID:

78296

Lock Type:

Write

Status:

Granted

Locked:

RowHash DBC.AccessRights

For session 78318:

CREATE VIEW PM_VMCHC5HG56ARRQNO7I6IRLJOUHU (ACCT_ID, EFF_STRT_TS, EFF_END_TS, ACCT_TYP_CD, ACCT_NBR, SRC_LAST_UPDTD_TS, SRC_SYS_IDN_CD, ABC_ID, DATA_QLTY_CATG_CD) AS

SELECT ACCT_ID,

EFF_STRT_TS,

EFF_END_TS,

ACCT_TYP_CD,

ACCT_NBR,

SRC_LAST_UPDTD_TS,

SRC_SYS_IDN_CD,

ABC_ID,

DATA_QLTY_CATG_CD

FROM tIDWLoadReady. LR_ACCT_ALS;

Username:

TIDWETLDLY

Host:

1

Session ID:

78297

Lock Type:

Write

Status:

Waiting

Locked:

RowHash DBC.AccessRights

Username:

TIDWETLDLY

Host:

1

Session ID:

78296

Lock Type:

Write

Status:

Waiting

Locked:

RowHash DBC.AccessRights

What causes this state and what can be done to prevent it?

6 REPLIES
Senior Supporter

Re: Deadlock on dbc.accessrights

This dbc table has a PI on databasename and username. 

As all three jobs want to create or drop objects with the same user on the same DB it can result in the dead lock situation you face.

Solution 1: Use different DBs to create the objects

Solution 2: Use different TD user for the different jobs

Solution 3 - worst: Run in sequences

Senior Supporter

Re: Deadlock on dbc.accessrights

P.S. there are changes comming

From the Partners Presentation on TD15.0:

Reindexing of DBC.Accessrights 

> Feature restructures the DBC.Accessrights table and will leverage the MLPPI feature for partitioning.

> The table will be partitioned by Database and Tablename in preparation for use in conjunction with the PPL feature in TD 15.10.

where my 15.0 table has currently the following structure

 

CREATE SET TABLE DBC.AccessRights ,FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      UserId BYTE(4) NOT NULL,

      DatabaseId BYTE(4) NOT NULL,

      TVMId BYTE(6) NOT NULL,

      FieldId SMALLINT FORMAT '---,--9' NOT NULL,

      AccessRight CHAR(2) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,

      WithGrant CHAR(1) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,

      GrantorID BYTE(4) NOT NULL,

      AllnessFlag CHAR(1) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,

      CreateUID BYTE(4),

      CreateTimeStamp TIMESTAMP(0),

      LastAccessTimeStamp TIMESTAMP(0),

      AccessCount INTEGER FORMAT '--,---,---,--9')

PRIMARY INDEX ( UserId )

PARTITION BY ( RANGE_N((ID2BIGINT(DatabaseId )) MOD  1073741824  BETWEEN 0  AND 1073741823  EACH 1 ),

RANGE_N(ID2BIGINT(TVMId ) BETWEEN 0  AND 4294967295.  EACH 1 )ADD 2 );


Enthusiast

Re: Deadlock on dbc.accessrights

Thank you so much!

Enthusiast

Re: Deadlock on dbc.accessrights

ulrich has given the appropriate solution. Here are some additional information for Informatica:

There is a checkbox on the second tab of the Informatica Task Editor which will cause Informatica to not create temporary views. If you are not performing updates, then you are still able to perform full Push Down Optimization (PDO).

Note: If you are performing updates, you can map to temporary views. You can also avoid updates by using an intermediate table to hold the results (if only certain fields are being updated). Then delete the records from the target and insert them from the intermediate table.

Additional settings:

Some customers seem to have further mitigated the deadlock occurrences issue from Informatica Application side  by:

- entering non-zero values in the PowerCenter Integration Service for NumOfDeadlockRetries and DeadlockSleep properties.

- selecting the Session retry on deadlock option in the Workflow Manager (under the session properties)

If Retry logic fails to initialize due to lack of buffer memory, one may need to:

- Increase  the DTM Buffer pool size

- Lower the commit interval for the target until the message goes away.

Junior Supporter

Re: Deadlock on dbc.accessrights

Hi,

We have faced the same issue with TD and informatica when using the PDO optimization. What Ulrich and manish has suggested are correct. However, as per my experience, the informatica solution is not that affective, we tried that. So, what we did id creraed 5 ids..abc.etl1.bcd, abc.etl2.bcd etc. Now, the ETL jobs pick up the etl id to run in a round robin fashion. So, since, the ids are changing, the deaklock has become lesser, however not gone altogether. This implementation is not very simple though, need disuccsion with your etl admin team and production support teams. 

--Samir

Enthusiast

Re: Deadlock on dbc.accessrights

This not tested, however worth a try. What about creating a workload in TASM to resolve this issue.
Classification criteria:
AppID=PMDTM
Query type=DDL
Throttle=1
Evaluation order: keep at top of other workloads
Note: AppID = PMDTM will pick only Informatica native jobs/queries. For example, if a TPTLoad is run through Informatica, AppID will be TPTLoad, not PMDTM.
And query type=DDL will ensure that only create view, drop view are qualified. By keeping the throttle at 1, it will ensure that the Create pdo views are run sequentially. There will be no requirement of design change in Informatica end.
In my experience, I have observed that the number of DDLs in PMDTM is rare. And the issue occurs when the same UserName tries to create 2 views at the same time and in the same Database from 2 different Sessions. Creating a view takes a fraction of seconds. So user may not even realize the delay due to this serialization.
Worth a try. Isn't it?