|
Archives of the TeradataForumMessage Posted: Tue, 27 May 2008 @ 17:02:22 GMT
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
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||