Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Sat, 02 Jan 2010 @ 12:15:56 GMT

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

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

  As I said before, the table on which deadlock occured has an NUPI. Suppose that we have 5 rows for a particular NUPI value that need to be updated by 2 concurrent updates(that are PI based 1-amp operations)  

  Could the deadlock occur because one update acquired a R-H Lock on only 2 rows, and while it was trying to acquire the same lock on the rest of the 3 rows, the other update came into picture and acquired the same lock on the other 3 rows.  

In the original posting, you described your WHERE clauses as NUPI=Some_Value. When Teradata attempts to acquire a lock it will do it for the hash of that Some_Value. Does not matter how many rows actually correspond to it. Either all of them will be locked or none.

If there are two distinct values with the same hash (hash synonyms), those will be locked as well. Sort of undesired scenario from the functional point of view but inevitable technically. Fortunately, hash synonyms are very rare.

  This means that update#1 is waiting for the lock to be released on the 3 rows acquired by update#2 and update#2 is waiting for the lock to be released by update#1 on those 2 rows. A perfect deadlock situation where the younger request will fail after a stipulated time period. Is this possible? I am asking this question because only this scenario fits well with the locking logger that we have captured.  

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

  Will changing from NUPI to UPI help? I can probably go back and analyse the table to add another column to the existing NUPI to convert it into a UPI. Also the MSR's will be changed so that rather than having one update statement that updates few rows of a table based on a NUPI value, we will have a few more UPI-based statements , every statement updating 1 and only 1 row of the table.  

I personally don't think that UPI will behave differently as far as locking is concerned. Everything will still be on hash level and Teradata may still lock more than one row if hash synonyms occur.

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

Hey, good luck with further investigation of this case and please keep us all posted. I think that doing more OLTP in data warehousing is the trend that will be hot in 2010 so this discussion should be interesting to follow up. Thanks for sharing the experience!


  <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