Archives of the TeradataForum

Message Posted: Fri, 01 Jan 2010 @ 15:02:35 GMT


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

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

From:		Victor Sokovin

Hi Suhail,

Flexibility is not the issue over here. It was difficult to provide transactional and conditional execution capability using TPUMP. Hence we went for multi-statement request and made sure that every statement in the request will be a 1-AMP operation.

As I said before, any form of help from experts will be great. I am open to all kinds of suggestions and ideas. Just needed to know whether there is any way to avoid a RH deadlock in teradata when two concurrent MSR's are running at the same time, on the same table, on the same set of rows with the same NUPI value, but updating different columns.

From what you have described I gather that the "normal" scenario would be blocking, not deadlocking. There is a very good thread in the Archives explaining in great detail the difference so I am just quoting it to add all that context to the discussion:

www.teradataforum.com/teradata/20030220_231621.htm

The big question is, therefore, how the deadlock is even possible?

That's something you need to do additional research on. My guess is that there is lock level escalation to table level. If all locks are on hash level than two sessions cannot acquire such lock for the same hash, so no deadlock at all.

If this escalation is indeed taking place, then is it a feature (and a function of the current system configuration / price) or a bug? Not always easy to tell but in both cases you are pretty much stuck in your today's production environment.

On a more practical way, check out this thread:

www.teradataforum.com/teradata/20080129_164359.htm

Perhaps you hit the MSR exception mentioned in it? Changing the order of execution of the statement may change something.

Victor


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

Attachments

Library

Quick Reference