Archives of the TeradataForum
Message Posted: Tue, 27 May 2008 @ 18:54:14 GMT
> 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
Both queries will result in two table locks, read on table1 and write on shared table.
And this is the root of the deadlock:
If Teradata has to lock several tables it will do those pseudo-table lock steps in an order based on the tableids.
If you check the explains you'll see SHARED_TABLE locked sometimes in the first step and sometimes in the second step.
This will work.
Drop the tables and recreate shared_table first, followed by those table1 tables.
Just joking, i know it's not a viable solution :-)
If the table1 is really just a single row table, how about replacing it with a view:
create view table1 as select 'bla' as pi_col;
Thus there's only a single lock.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|