Archives of the TeradataForum

Message Posted: Tue, 27 May 2008 @ 17:02:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello, especially Dieter

I can give you the additional information you requested. The statement which causes the deadlock is a very small one.

Job1 performs

     UPDATE COUNTRY1DB1.VIEW_TO_SHARED_TABLE
     SET DATA_COL = 'TU1'
     WHERE PI_COL = COUNTRY1DB2.TABLE1.PI_COL;

Job2 performs

     UPDATE COUNTRY2DB1.VIEW_TO_SHARED_TABLE
     SET DATA_COL = 'TU1'
     WHERE PI_COL = COUNTRY2DB2.TABLE1.PI_COL

The VIEW_TO_SHARED_TABLE is a view to a table in another database, I call it here SHARED_DB.

Each COUNTRYnDB1 has its own view referring to the SHARED_TABLE.

Table1 also exists in each COUNTRYnDB2 with column PI_COL as unique primary index, but the column PI_COL has CHARACTER SET LATIN in some databases and CHARACTER SET UNICODE in others. Each Table1 contains exactly one row

The SHARED_TABLE exists only once (thus the deadlock) and looks like that

     CREATE SET TABLE SHARED_DB.SHARED_TABLE
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           PI_COL CHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,
           COL4 CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL DEFAULT ' ',
           DATA_COL CHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL DEFAULT '   ',
           COL5 CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL DEFAULT ' ')
     UNIQUE PRIMARY INDEX PI_SHARED_TABLE ( PI_COL );

Adding LOCKING SHARED_TABLE FOR WRITE does not change the explain output, so I do not think that it will deliver a different (locking) result. I know that the Teradata.PseudoTable should prevent the deadlock, but it does not.

(there is no program that updates table1, so this table cannot be involved in the deadlock;

there is no other program that updates SHARED_TABLE;

only this program reads SHARED_TABLE in a preceding step, but uses there LOCKING FOR ACCESS).

Therefore, the big question remains: How to prevent the deadlock ? Is the one-amp table described in my first note a possible solution ? Are there better options ?

Mit freundlichen Gr��en / With best regards

i.A. Martin Fuchs
Gruppenleiter / Teamleader
Business Intelligence Systems / BI Design

	<Prev	Next>		<<First	<Prev	Next>	Last>>