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;
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
WHERE INS_UPD_FLG ='U';
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
FROM tIDWLoadReady. LR_ACCT_ALS;
What causes this state and what can be done to prevent it?
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
P.S. there are changes comming
CREATE SET TABLE DBC.AccessRights ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
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,
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 );
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.
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.
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.