Archives of the TeradataForum
Message Posted: Sat, 02 Jan 2010 @ 12:15:56 GMT
| 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!
Victor
| 
