Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Tue, 27 May 2008 @ 18:54:14 GMT


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


Subj:   Re: Serialization of statements to avoid deadlocks
 
From:   Dieter Noeth

martin.fuchs wrote:

  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.  


Both queries will result in two table locks, read on table1 and write on shared table.


  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  


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.


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


This will work.


  Are there better options ?  


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.


Dieter



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020