Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Sat, 02 Jan 2010 @ 14:39:58 GMT

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

Subj:   Re: Row Hash Deadlock in Active D/W due to concurrent MSR
From:   Mohd Suhail

Hello Victor,

  hope this cannot happen for actual rows because hash level is the lowest granularity Teradata locking mechanism can consider. If you just oversimplified the WHERE clauses in the example and in reality you can have them as NUPI in (Value_1, Value2, ...) in both transactions then a deadlock might be possible for those values both trascations are trying to lock.  

This is not the case we have in our active D/W. The simplified examples I explained are exactly the ones that are used in reality. There is a where clause on only one NUPI value in both the update statements. Sadly, the dreadful deadlock occurs when both the where clauses refer the same nupi value in the concurrent updates.

  What about secondary structures on the table? Any triggers, SI, JI, HI?  

Yes there is a NUSI on a particular column. But this column is not being used anywhere in both the update statements that are getting deadlocked . Not in the set clause, nor in the where clause. Although i shall double check this. I can actually suggest my team to try and remove the NUSI and see if anything different happens. May be worth a try.

I'll keep you and all other experts posted about my progress in minimizing these deadlocks. Till that time, any new ideas/suggestions(no matter how wild they are) are always welcome.



  <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: 15 Jun 2023