Archives of the TeradataForum
Message 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.
UPDATE COUNTRY1DB1.VIEW_TO_SHARED_TABLE SET DATA_COL = 'TU1' WHERE PI_COL = COUNTRY1DB2.TABLE1.PI_COL;
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: 28 Jun 2020|