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>>

Attachments

Library

Quick Reference